using SET SQLID with PEOPLESOFT

Melissa Rogers

using SET SQLID with PEOPLESOFT
Being new to DB2 (DB2 version 6 OS/390), we have not encountered a need to
use the SET SQLID command. I am having a little difficulty in understanding
when to use the command and how it will affect our implemented RACF security
(using secondary authids) that we have defined for DB2. Currently, our 3 DB2
DBA's have SYSADM authority in both RACF and DB2 internal security. It is
the DBA's responsibility to create all DB2 objects. The high level
qualifiers for tables and indexes are simply labels, either TST (test) or
PRD (prod).
Now, we are preparing to implement PEOPLESOFT which suggests that the set
current sqlid command will be issued to set the sqlid to the owner id. If
the sqlid gets set to the owner id, won't all the implicit privileges that
an owner has be given to the user? What affects does this have with RACF
security? I would really appreciate any information on how other people have
implemented security with PEOPLESOFT. The more I read, the more confused
I'm getting!!! Thanks in advance



James Kwan

Re: using SET SQLID with PEOPLESOFT
(in response to Melissa Rogers)
Melissa,

The short answer to your question is SET CURRENT SQLID will not impact your
existing security set up. The program will tell DB2 which ownerid.tables to
read. We used this technique to dynamically switching between different
tables. In your case, your owner id will simply be TST or PRD.
One thing you need to do differently is to bind plans or packages with both
TST or PRD as the qualifier.

James Kwan

IBM Certified Expert


In a message dated 1/2/02 11:44:05 AM Central Standard Time,
[login to unmask email] writes:


> Being new to DB2 (DB2 version 6 OS/390), we have not encountered a need to
> use the SET SQLID command. I am having a little difficulty in understanding
> when to use the command and how it will affect our implemented RACF security
> (using secondary authids) that we have defined for DB2. Currently, our 3 DB2
> DBA's have SYSADM authority in both RACF and DB2 internal security. It is
> the DBA's responsibility to create all DB2 objects. The high level
> qualifiers for tables and indexes are simply labels, either TST (test) or
> PRD (prod).
> Now, we are preparing to implement PEOPLESOFT which suggests that the set
> current sqlid command will be issued to set the sqlid to the owner id. If
> the sqlid gets set to the owner id, won't all the implicit privileges that
> an owner has be given to the user? What affects does this have with RACF
> security? I would really appreciate any information on how other people have
> implemented security with PEOPLESOFT. The more I read, the more confused
> I'm getting!!! Thanks in advance
>


Craig Theisen

Re: using SET SQLID with PEOPLESOFT
(in response to James Kwan)
Melissa,

Why do you need to use SET CURRENT SQLID with Peoplesoft?
With PeopleSoft you will need to define multiple
instances(peoplesoft environments) within a single DB2 test subsystem, so
just a "TST" table qualifier won't be adequate to keep them separate.
I think they require a minimum of 3, something like PSAUSYS, PSHRDEMO and
PSHRDEV for applying tax updates. Each has their own OWNER (psausys1,
pshrdmo1 etc). PeopleSoft uses the Set current sqlid to control which
table, view etc is being accessed. This is controlled through the
PEOPLETOOL tables(which are kind of a mini DB2 catalog).

What is the basic CONCEPT of how PeopleSoft Security and RACF work together?

1) You'll give the PeopleSoft GUI pretty open RACF authority to the
Peoplesoft objects. This is done with the Connect ID and AccessIDs (which
are userid's) you will grant access to the appropriate tables. The Connect
ID only accesses a few tables, but the AccessID has RACF authority to
almost everything. (AccessID passwords are encrypted in a peopletools table
to prevent their use outside of the GUI!)
2) The Peoplesoft (GUI) Application then has it's own method of
controlling security by groups and individual sign-ons. With their panels
your security administrator controls who has access to what panels and/or
menu options.
3) The Table changes/definitions (DDL) are executed outside the GUI by
the DBA with the tool of their choice. SPUFI, Command Center, WINSQL or
whatever. After the install these are done with each tax-update or fix you
download off of customer connection.


To really understand the details of PeopleSoft Security you'll need to read
the install manuals and know the differences between connect id,
accessID(s), operID(s) and what PEOPLETOOL tables each are stored in. I
remember it was not very intuitive but it works.


Hope this helps.

Craig Theisen
DataBase Administration
GuideOne Insurance



-----Original Message-----
From: Melissa Rogers [mailto:[login to unmask email]
Sent: Wednesday, January 02, 2002 11:13 AM
To: [login to unmask email]
Subject: using SET SQLID with PEOPLESOFT


Being new to DB2 (DB2 version 6 OS/390), we have not encountered a need to
use the SET SQLID command. I am having a little difficulty in understanding
when to use the command and how it will affect our implemented RACF security
(using secondary authids) that we have defined for DB2. Currently, our 3 DB2
DBA's have SYSADM authority in both RACF and DB2 internal security. It is
the DBA's responsibility to create all DB2 objects. The high level
qualifiers for tables and indexes are simply labels, either TST (test) or
PRD (prod).
Now, we are preparing to implement PEOPLESOFT which suggests that the set
current sqlid command will be issued to set the sqlid to the owner id. If
the sqlid gets set to the owner id, won't all the implicit privileges that
an owner has be given to the user? What affects does this have with RACF
security? I would really appreciate any information on how other people have
implemented security with PEOPLESOFT. The more I read, the more confused
I'm getting!!! Thanks in advance








Melissa Rogers

Re: using SET SQLID with PEOPLESOFT
(in response to Craig Theisen)
Thanks for the information. We received a document from a Peoplesoft person
which states that Peoplesoft requires that the current SQLID be the owner ID
of the objects because they do not fully qualify the objects in the DML
statements it issues. Examples were given as to how the current sqlid can be
the owner id. The explained "typical" scenario issues the set current sqlid
statement. Peoplesoft as well as DB2 are both very new to us so we are in
the learning stages. I really appreciate you help! Thanks


----Original Message-----
From: Theisen, Craig [mailto:[login to unmask email]
Sent: Wednesday, January 02, 2002 4:58 PM
To: [login to unmask email]
Subject: Re: using SET SQLID with PEOPLESOFT


Melissa,

Why do you need to use SET CURRENT SQLID with Peoplesoft?
With PeopleSoft you will need to define multiple
instances(peoplesoft environments) within a single DB2 test subsystem, so
just a "TST" table qualifier won't be adequate to keep them separate.
I think they require a minimum of 3, something like PSAUSYS, PSHRDEMO and
PSHRDEV for applying tax updates. Each has their own OWNER (psausys1,
pshrdmo1 etc). PeopleSoft uses the Set current sqlid to control which
table, view etc is being accessed. This is controlled through the
PEOPLETOOL tables(which are kind of a mini DB2 catalog).

What is the basic CONCEPT of how PeopleSoft Security and RACF work together?

1) You'll give the PeopleSoft GUI pretty open RACF authority to the
Peoplesoft objects. This is done with the Connect ID and AccessIDs (which
are userid's) you will grant access to the appropriate tables. The Connect
ID only accesses a few tables, but the AccessID has RACF authority to
almost everything. (AccessID passwords are encrypted in a peopletools table
to prevent their use outside of the GUI!)
2) The Peoplesoft (GUI) Application then has it's own method of
controlling security by groups and individual sign-ons. With their panels
your security administrator controls who has access to what panels and/or
menu options.
3) The Table changes/definitions (DDL) are executed outside the GUI by
the DBA with the tool of their choice. SPUFI, Command Center, WINSQL or
whatever. After the install these are done with each tax-update or fix you
download off of customer connection.


To really understand the details of PeopleSoft Security you'll need to read
the install manuals and know the differences between connect id,
accessID(s), operID(s) and what PEOPLETOOL tables each are stored in. I
remember it was not very intuitive but it works.


Hope this helps.

Craig Theisen
DataBase Administration
GuideOne Insurance



-----Original Message-----
From: Melissa Rogers [mailto:[login to unmask email]
Sent: Wednesday, January 02, 2002 11:13 AM
To: [login to unmask email]
Subject: using SET SQLID with PEOPLESOFT


Being new to DB2 (DB2 version 6 OS/390), we have not encountered a need to
use the SET SQLID command. I am having a little difficulty in understanding
when to use the command and how it will affect our implemented RACF security
(using secondary authids) that we have defined for DB2. Currently, our 3 DB2
DBA's have SYSADM authority in both RACF and DB2 internal security. It is
the DBA's responsibility to create all DB2 objects. The high level
qualifiers for tables and indexes are simply labels, either TST (test) or
PRD (prod).
Now, we are preparing to implement PEOPLESOFT which suggests that the set
current sqlid command will be issued to set the sqlid to the owner id. If
the sqlid gets set to the owner id, won't all the implicit privileges that
an owner has be given to the user? What affects does this have with RACF
security? I would really appreciate any information on how other people have
implemented security with PEOPLESOFT. The more I read, the more confused
I'm getting!!! Thanks in advance