DB2-RACF Primary & Secondary Auth-IDs
Kim Leow
Hi all
My shop is using DB2 v2.3. We have a new application to be implemented
pretty soon.
The application is called FREIGHT. We want to create an ID (either RACF
user-id or secondary auth-ID) called FREIGHT. This ID will be used to create
all DB2 objects
(ie. database, tablespaces, tables etc...) by setting current SQLID = 'FREIGHT'
once DBADM is granted on database called FREIGHT.
I want to know
1) What privileges are required to SET CURRENT SQLID ? I read something about
the exit routine. Why is it needed ? How does it work with RACF and DB2 ? How
can I tell whether DB2 is using the sample exit routine ?
2) In SYSIBM.SYSTABLES table, I would like to capture the CREATOR (ie
FREIGHT) as
well as the userid in CREATEDBY who created the objects ? For example,
NAME CREATOR CREATEDBY
TABLE1 FREIGHT JACK
TABLE2 FREIGHT ANGELA
a) Let's assume FREIGHT is only an authorised RACF userid here.
If the DBA who has DBADM privilege on the database FREIGHT does a
SET CURRENT SQLID='FREIGHT' and have USER=JACK coded in the jobcard of the
JCL, will this userid, JACK be reflected in CREATEDBY of SYSIBM.SYSTABLES ?
If the answer is "yes", what if USER=JACK is not coded in the jobcard ?
b) Let's assume FREIGHT is now a secondary auth-ID. If I place userids JACK and
ANGELA in FREIGHT, SET CURRENT SQLID = 'FREIGHT' and NOT having USER=<userid>
coded in the jobcard, can DB2 pick up any of the userids (ie JACK or ANGELA)
defined in the group-ID and make an entry in SYSIBM.SYSTABLES like above. If
the answer is "yes", what if USER=<userid> is coded in the jobcard as well as
having SET CURRENT SQLID='FREIGHT' ? What will DB2 do ?
Thank heaps and in advance to anyone who can help me with the clarifications.
Regards
Stan Hoey
I will resist the obvious temptation to preach about getting
out of DB2 2.3 (ooooops - I didn't resist)
> 1) What privileges are required to SET CURRENT SQLID ? I
>read something about the exit routine. Why is it needed ?
>How does it work with RACF and DB2 ? How can I tell whether
>DB2 is using the sample exit routine ?
If you have SYSADM, you can set current SQLID to anything.
Otherwise, you can only set it to either your primary
authid, or a valid secondary authid. The latter means that
you must be connected to the RACF group of the same name.
>
> 2) In SYSIBM.SYSTABLES table, I would like to capture the
>CREATOR (ie FREIGHT) as well as the userid in CREATEDBY who
>created the objects ?
You get this for free - its what DB2 does.
> a) Let's assume FREIGHT is only an authorised RACF userid
>here. If the DBA who has DBADM privilege on the database
>FREIGHT does a SET CURRENT SQLID='FREIGHT' and have
>USER=JACK coded in the jobcard of the JCL, will this
>userid, JACK be reflected in CREATEDBY of SYSIBM.SYSTABLES?
YES.
>b) Let's assume FREIGHT is now a secondary auth-ID. If I
>place userids JACK and ANGELA in FREIGHT, SET CURRENT SQLID
>= 'FREIGHT' and NOT having USER=<userid> coded in the
>jobcard, can DB2 pick up any of the userids (ie JACK or
>ANGELA) defined in the group-ID and make an entry in
>SYSIBM.SYSTABLES like above. If the answer is "yes", what
>if USER=<userid> is coded in the jobcard as well as
>having SET CURRENT SQLID='FREIGHT' ? What will DB2 do ?
You have to distinguish between primary and secondary
authids. Specifying USER= on your job card means that you
are changing the job submitter id, and therefore the DB2
primary authid. Coding SET CURRENT SQLID= does at it says -
it set your current SQLID. Some DB2 processes can evaluate
all your authids (primary + secondary). Others use only your
current SQLID. The current SQL id is by default, your
userid, which can be overrided by the USER=
parameter in batch.
Every bit of SQL you code is explained in SQLREF, including
how DB2 decides authorisation. RT friendly M.
> Thank heaps and in advance to anyone who can help me with
the clarifications.
>
> Regards
Comfortably Numb
Stan Hoey
CIRCLE Computer Group, UK