Every process that makes a two-tier
connection to the database identifies itself with a PeopleSoft user or operator
ID. The purpose of this signon processing is to securely validate that the
PeopleSoft operator, authenticated by password, is permitted to access the
PeopleSoft application.
As such, it will be useful to quickly review the database user accounts available in a PeopleSoft database before walking through the actual signon process. a People-Soft database requires only three Oracle database schemas:
•
Owner/Access ID: This schema contains most of the
database objects, and it is as this database user that PeopleSoft processes
access the database.
•
Connect ID: This low-security database user is
used by the signon process until the password is validated.
•
PS: This schema contains a table that describes
which PeopleSoft databases are in the Oracle database.
Owner
ID/Access ID (SYSADM)
Once the signon process is complete, a PeopleSoft process
(such as a two-tier client or an application server process) is connected to
the database via the administrative schema, which is referred to as the “Owner
ID” because it contains nearly all of the database objects. This schema can
access any table or view in the PeopleSoft database. Access to objects in the
database is controlled within the PeopleSoft application, rather than by the
database.
Note The OwnerID is the schema
that contains the PeopleSoft objects, while the AccessID is the database user
with which PeopleSoft connects and references it. In a standard PeopleSoft
installation there is only one AccessID, which is the same as the OwnerID.
By convention, the administrative
schema is usually called SYSADM, although there is nothing to prevent you from
using a different name. The password to this account is the key to the kingdom.
It should be treated with the same respect as the password to the superuser
(root) account on Unix or the passwords to the SYS and SYSTEM accounts on an
Oracle database.
The Owner ID
has certain privileges granted by a role called PSADMIN.
Connect
ID (PEOPLE)
The first connection that each process
makes to the database is via a low-security user account, referred to as the
“Connect ID” and usually named PEOPLE. It is only granted CREATE SESSION
privilege via the PSUSER role and three explicit SELECT privileges:
GRANT SELECT ON PSSTATUS TO PEOPLE;
GRANT SELECT ON PSOPRDEFN TO PEOPLE;
GRANT SELECT ON PSACCESSPRFL TO PEOPLE;
The
Connect ID provides only the bare minimum of access to the PeopleTools tables
in order to permit authentication by the signon process. Later, after
successful validation of the password, the process reconnects as the Access ID.
PS
Schema
The
PS schema is used to hold only the table PSDBOWNER, which maps the name of the
People-Soft database to the schema in the database that holds it. PS.PSDBOWNER
is the only table in a PeopleSoft database that is in a different schema from
the rest of the objects.
The PS schema
and PS.PSDBOWNER table are created during the installation procedure by the dbowner.sql script, All privileges are revoked from the schema after
the table has been created.
An extract from dbowner.sql
GRANT CONNECT,
RESOURCE, DBA TO PS IDENTIFIED BY PS; CONNECT PS/PS; CREATE TABLE PSDBOWNER
(DBNAME VARCHAR2(8) NOT NULL , OWNERID VARCHAR2(8) NOT NULL ) TABLESPACE PSDEFAULT;
CREATE UNIQUE INDEX PS_PSDBOWNER ON PSDBOWNER (DBNAME) TABLESPACE PSDEFAULT;
CREATE PUBLIC SYNONYM PSDBOWNER FOR PSDBOWNER; GRANT SELECT ON PSDBOWNER TO
PUBLIC; CONNECT SYSTEM/MANAGER; REVOKE CONNECT, RESOURCE, DBA FROM PS;
The
PeopleTools signon procedure explicitly references the table and schema as
PS.PSDBOWNER. The “PS” is hard-coded into the process, so you cannot choose a
different name for this schema.
Oracle Database Roles
Two Oracle
database roles, PSUSER and PSADMIN, are created during the installation of the
PeopleSoft database
An extract from PSADMIN.SQL
CREATE ROLE PSUSER; GRANT CREATE SESSION
TO PSUSER;
CREATE ROLE
PSADMIN; GRANT ANALYZE ANY, ALTER SESSION, ALTER TABLESPACE, ALTER ROLLBACK
SEGMENT, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK,
CREATE PUBLIC SYNONYM, CREATE SEQUENCE, CREATE SNAPSHOT,
CREATE SESSION, CREATE SYNONYM, CREATE
TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE TABLESPACE, CREATE
USER, CREATE ROLLBACK SEGMENT, DROP PUBLIC DATABASE LINK, DROP PUBLIC SYNONYM,
DROP ROLLBACK SEGMENT, DROP TABLESPACE, DROP USER, MANAGE TABLESPACE, RESOURCE,
EXP_FULL_DATABASE, IMP_FULL_DATABASE, GRANT ANY ROLE, ALTER USER, BECOME USER
TO PSADMIN WITH ADMIN OPTION;
PSADMIN
Role
The
PSADMIN role is granted to the Access ID (usually SYSADM). PeopleSoft describes
the privileges defined by this role as the minimum for running PeopleSoft. The
Access ID is an administrative account, so there needs to be a degree of trust
and cooperation between the DBA and the PeopleSoft administrator (if they are
not the same person).
