Stored procedure auth.

Jim Brown

Stored procedure auth.
All,



I am getting a -551 (authority) error when trying to execute
a stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and
the package bound onto sub-system DB2A. This procedure references
aliases that point to objects on DB2B. My id is in a RACF group that
has SYSADM on both sub-systems and the owner of the package on DB2A has
all the necessary auth on DB2B. I am getting the -551 on the target
object on DB2B, not on the package or procedure, ie the proc will access
DB2B.creator.object_name and I get the -551 error on
DB2B.creator.object_name.



I am at a lost as to why I am getting a -551 when I has
SYSADM, it appears as though the secondary auth id exit is not taken
when using DDF connections to go across sub-systems. Does this make
sense and if so is there any parm I can modify to change this?



Thanks!



Jim



SunTrust Banks, Inc.

Suntrust Enterprise Information Services

Enterprise Architecture & Engineering

Database Administrator

Direct: (804) 553-9024 Network: 623-9024

Fax: (804) 261-9652 Cell: (757) 810-2896
************************************************
The information transmitted is intended solely
for the individual or entity to which it is
addressed and may contain confidential and/or
privileged material. Any review, retransmission,
dissemination or other use of or taking action
in reliance upon this information by persons or
entities other than the intended recipient is
prohibited. If you have received this email in
error please contact the sender and delete the
material from any computer.
************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Stored procedure auth.
(in response to Jim Brown)
I am very far from being an expert on DB2 security (being SYSADM, mostly I
don't have to care), but I've noticed the same just a few weeks ago:
executing commands through DDF won't pick up the secondary authids
(actually that's the working hypothesis I developed at the time - I did not
investigate whether this really explained all problems I had). I have not
checked why that is so, or whether it is documented behaviour, or how to
fix it...

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: "Brown.James" <[login to unmask email]>@IDUGDB2-L.ORG on
10-01-2005 10:27 EST

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subjec [DB2-L] Stored procedure auth.
t:








All,

I am getting a -551 (authority) error when trying to execute a
stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and
the package bound onto sub-system DB2A. This procedure references aliases
that point to objects on DB2B. My id is in a RACF group that has SYSADM on
both sub-systems and the owner of the package on DB2A has all the necessary
auth on DB2B. I am getting the -551 on the target object on DB2B, not on
the package or procedure, ie the proc will access DB2B.creator.object_name
and I get the -551 error on DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has SYSADM,
it appears as though the secondary auth id exit is not taken when using DDF
connections to go across sub-systems. Does this make sense and if so is
there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Stored procedure auth.
(in response to Michael Ebert)
Jim,

You don't mention if there are any entries in the USERNAMES table in the
CDB on DB2A. Is your user-id getting translated to something else,
perhaps by a 'blank' entry?

Out of curiosity, you don't mention if you are using private protocol or
DRDA. (I assume that the SQL in your stored procedure is embedded
(static), not dynamic.) That being the case, and if you are using DRDA,
what were the parameters used to bind the remote package on DB2B?

Lock Lyon
Compuware




All,

I am getting a -551 (authority) error when trying to execute a
stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and
the package bound onto sub-system DB2A. This procedure references aliases
that point to objects on DB2B. My id is in a RACF group that has SYSADM
on both sub-systems and the owner of the package on DB2A has all the
necessary auth on DB2B. I am getting the -551 on the target object on
DB2B, not on the package or procedure, ie the proc will access
DB2B.creator.object_name and I get the -551 error on
DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has
SYSADM, it appears as though the secondary auth id exit is not taken when
using DDF connections to go across sub-systems. Does this make sense and
if so is there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rob --- Sr. Database Administrator --- CFS Crane

Re: Stored procedure auth.
(in response to LL581@DAIMLERCHRYSLER.COM)
For packages bound remotely, they cannot use the privileges granted to PUBLIC, only those granted to PUBLIC AT ALL LOCATIONS. I would check the table privileges on DB2B and see if you have any using PUBLIC vs. PUBLIC* if you bound the package remotely vs locally.

If bound locally check the bind option SQLERROR, and make sure it was not SQLERROR(CONTINUE).

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Brown.James
Sent: Monday, January 10, 2005 8:28 AM
To: [login to unmask email]
Subject: [DB2-L] Stored procedure auth.

All,

I am getting a -551 (authority) error when trying to execute a stored procedure, via DDF, that is referencing aliases on another sub-system. The procedure is an external Cobol procedure registered and the package bound onto sub-system DB2A. This procedure references aliases that point to objects on DB2B. My id is in a RACF group that has SYSADM on both sub-systems and the owner of the package on DB2A has all the necessary auth on DB2B. I am getting the -551 on the target object on DB2B, not on the package or procedure, ie the proc will access DB2B.creator.object_name and I get the -551 error on DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has SYSADM, it appears as though the secondary auth id exit is not taken when using DDF connections to go across sub-systems. Does this make sense and if so is there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896


************************************************


The information transmitted is intended solely


for the individual or entity to which it is


addressed and may contain confidential and/or


privileged material. Any review, retransmission,


dissemination or other use of or taking action


in reliance upon this information by persons or


entities other than the intended recipient is


prohibited. If you have received this email in


error please contact the sender and delete the


material from any computer.


************************************************
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


*******************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
*******************************************************


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jim Brown

Re: Stored procedure auth.
(in response to Rob --- Sr. Database Administrator --- CFS Crane)
I'm using private protocol, ie the target of the alias is defined as
DB2B.creator.object_name and the package bound using a QUALIFIER equal
to the creator id of the alias and bound on the local sub-system. I
have no entries into the USERNAMES table for my id and no entries for
the link name. I then connect to the local sub-system, DB2A in this
case, to call the procedure which is bound using the alias name. DB2A
should then be connecting to DB2B (where the actual data resides) to
satisfy the query in the proc. The problem appears to be that it is
using my RACF id to authenticate instead of the owner of the package
related to the proc or the SYSADM group that I am part of in both
sub-systems.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Monday, January 10, 2005 10:53 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored procedure auth.




Jim,

You don't mention if there are any entries in the USERNAMES table in the
CDB on DB2A. Is your user-id getting translated to something else,
perhaps by a 'blank' entry?

Out of curiosity, you don't mention if you are using private protocol
or DRDA. (I assume that the SQL in your stored procedure is embedded
(static), not dynamic.) That being the case, and if you are using DRDA,
what were the parameters used to bind the remote package on DB2B?

Lock Lyon
Compuware




All,

I am getting a -551 (authority) error when trying to execute
a stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and
the package bound onto sub-system DB2A. This procedure references
aliases that point to objects on DB2B. My id is in a RACF group that
has SYSADM on both sub-systems and the owner of the package on DB2A has
all the necessary auth on DB2B. I am getting the -551 on the target
object on DB2B, not on the package or procedure, ie the proc will access
DB2B.creator.object_name and I get the -551 error on
DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has
SYSADM, it appears as though the secondary auth id exit is not taken
when using DDF connections to go across sub-systems. Does this make
sense and if so is there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
************************************************
The information transmitted is intended solely
for the individual or entity to which it is
addressed and may contain confidential and/or
privileged material. Any review, retransmission,
dissemination or other use of or taking action
in reliance upon this information by persons or
entities other than the intended recipient is
prohibited. If you have received this email in
error please contact the sender and delete the
material from any computer.
************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeffrey Schade

Re: Stored procedure auth.
(in response to Jim Brown)
In order for DDF threads to pick-up RACF secondary authids you need to
have the code in both the [login to unmask email] and [login to unmask email] exits that connects the
primary authorization id with the secondary ids. DDF only drives the
sign-on exit (I believe) and the default exit does not resolve secondary
ids.

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

Insurance Services Office, Inc.
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
FAX: (201) 748-1500
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Michael Ebert
Sent: Monday, January 10, 2005 10:40 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored procedure auth.

I am very far from being an expert on DB2 security (being SYSADM, mostly
I
don't have to care), but I've noticed the same just a few weeks ago:
executing commands through DDF won't pick up the secondary authids
(actually that's the working hypothesis I developed at the time - I did
not
investigate whether this really explained all problems I had). I have
not
checked why that is so, or whether it is documented behaviour, or how to
fix it...

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: "Brown.James" <[login to unmask email]>@IDUGDB2-L.ORG on
10-01-2005 10:27 EST

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subjec [DB2-L] Stored procedure auth.
t:








All,

I am getting a -551 (authority) error when trying to execute
a
stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and
the package bound onto sub-system DB2A. This procedure references
aliases
that point to objects on DB2B. My id is in a RACF group that has SYSADM
on
both sub-systems and the owner of the package on DB2A has all the
necessary
auth on DB2B. I am getting the -551 on the target object on DB2B, not on
the package or procedure, ie the proc will access
DB2B.creator.object_name
and I get the -551 error on DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has
SYSADM,
it appears as though the secondary auth id exit is not taken when using
DDF
connections to go across sub-systems. Does this make sense and if so is
there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ken Michalik

Re: Stored procedure auth.
(in response to Jeffrey Schade)
The V7 Admin guide says about private protocol: When a static SQL statement
is passed to the server, it is dynamically bound and then executed.

If this is the case, then is it possible that the problem could be related
to DYNAMICRULES?
Might you also try converting to DRDA and see if that fixes the problem?


Ken Michalik
Kraft Foods


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Brown.James
Sent: Monday, January 10, 2005 10:45 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored procedure auth.



I'm using private protocol, ie the target of the alias is defined as
DB2B.creator.object_name and the package bound using a QUALIFIER equal to
the creator id of the alias and bound on the local sub-system. I have no
entries into the USERNAMES table for my id and no entries for the link name.
I then connect to the local sub-system, DB2A in this case, to call the
procedure which is bound using the alias name. DB2A should then be
connecting to DB2B (where the actual data resides) to satisfy the query in
the proc. The problem appears to be that it is using my RACF id to
authenticate instead of the owner of the package related to the proc or the
SYSADM group that I am part of in both sub-systems.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Monday, January 10, 2005 10:53 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Stored procedure auth.




Jim,

You don't mention if there are any entries in the USERNAMES table in the CDB
on DB2A. Is your user-id getting translated to something else, perhaps by a
'blank' entry?

Out of curiosity, you don't mention if you are using private protocol or
DRDA. (I assume that the SQL in your stored procedure is embedded (static),
not dynamic.) That being the case, and if you are using DRDA, what were the
parameters used to bind the remote package on DB2B?

Lock Lyon
Compuware




All,

I am getting a -551 (authority) error when trying to execute a
stored procedure, via DDF, that is referencing aliases on another
sub-system. The procedure is an external Cobol procedure registered and the
package bound onto sub-system DB2A. This procedure references aliases that
point to objects on DB2B. My id is in a RACF group that has SYSADM on both
sub-systems and the owner of the package on DB2A has all the necessary auth
on DB2B. I am getting the -551 on the target object on DB2B, not on the
package or procedure, ie the proc will access DB2B.creator.object_name and I
get the -551 error on DB2B.creator.object_name.

I am at a lost as to why I am getting a -551 when I has SYSADM,
it appears as though the secondary auth id exit is not taken when using DDF
connections to go across sub-systems. Does this make sense and if so is
there any parm I can modify to change this?

Thanks!

Jim

SunTrust Banks, Inc.
Suntrust Enterprise Information Services
Enterprise Architecture & Engineering
Database Administrator
Direct: (804) 553-9024 Network: 623-9024
Fax: (804) 261-9652 Cell: (757) 810-2896

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

************************************************
The information transmitted is intended solely
for the individual or entity to which it is
addressed and may contain confidential and/or
privileged material. Any review, retransmission,
dissemination or other use of or taking action
in reliance upon this information by persons or
entities other than the intended recipient is
prohibited. If you have received this email in
error please contact the sender and delete the
material from any computer.
************************************************
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm