I am new to people soft Env, at the installation we need to run dbowner.sql which under PS_HOME/script/nt when i open that script i found create PS user grant dba to PS and revoke role DBA from PS i don't understand logic behind that can some one help me in this issue.
The PS schema is used to hold the PSDBOWNER which maps the name of the peoplesoft databasse to the schema in the database that holds it.
This table allows peoplesoft to manage multiple peoplesoft databases within a single oracle database.
Thats why we keep this PSDBOWNER table under a separate schema called PS.
GRANT CONNECT, RESOURCE, DBA TO PS IDENTIFIED BY 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;
REVOKE CONNECT, RESOURCE, DBA FROM PS;
The scripts actually grant connect and DBA privilage to PS user id inorder to connect and create a table in database.
when we try to connect to peoplesoft using 4-tier or 3-tier it actually connects to db using your connect id, and then it issues a select query againts PSDBOWNER in order to get the ownerid. Thats why it creates the synonym to PSDBOWNER TABLE inorder to grant access to public.
Then once everything is done, it revokes the permission from PS user id.
thanks for such a nice explanation.Here my question is like this insted of ps connect through connectid(people), cant we directly connect through operator id(PS) what is necessary to revoke all privileges from operator id(PS)("REVOKE CONNECT, RESOURCE, DBA FROM PS;") is their any logic behind that or the architecture is design like that,please help i am bit confused regarding the logic behind that.
To answer your first question we don’t connect using people when we run the script, we actually connect as "sysdba" and then run the dbowner script. so the sysdba has the authority to grant dba privilage to PS so same way the sysdba revokes the dba privilage from PS. Any userid can’t revoke his own access/privilege. So PS user id can’t revoke its own privilege.
The people id which I talked about comes into picture when we connect to 4-tier (PeopleSoft URL) or using application designer. So when you try to connect to 4-tier or application designer using PS, VP1 or any other PeopleSoft id, by default PeopleSoft will first try
* To connect the db using connect id
* Then it verifies in psoprdefn whether we have issued a valid PeopleSoft user id and password
* Then it picks up the symbolic id corresponding to that particular user id from psoprdefn
* using the symbolic id it gets the accessid from psaccessprfl table
* Then it connects the db using your accessid.
This is just an overview of how PeopleSoft authenticate us to go inside.
Thanks for explanation What logic behind revoking all privileges from PS(operator id),correct me if am wrong as flow goes in this way in two-tier architecture
---> Go to configuration manager to get people password based on that
---> The peopleid which select privileges on the psoperdef,psaccesprfl which will check the login details of PS based that fetch symbolicid and password
---> Onces it get the ownerid password it disconnect connectid(people) session try to connect useing accessid(ownerid) this how it goes right i understand that point
My question insted of going in this passion PS-->people-->ownerid why can't user PS can directly access peoplesoft database is their any security reason for that
Yes this is how PeopleSoft works. More over all the PeopleSoft user like (VP1, etc..) are just PeopleSoft user. They are not database level users. we use PS schema to create the psdbowner table so PS is an exception case and PS user will be available in database also. But the PS user can access just psdbowner and public objects. It can’t access all the objects available in db even though it is a database level user.
All our PeopleSoft objects sit under one schema which is owned by our access id. So thatsy PeopleSoft connect to the db through access id. If you want to access the PeopleSoft using PeopleSoft user id (VP1 , PS, etc) then we need to create all those users in database and we need to give access to the entire schema for all those users in order to access PeopleSoft. If we do that then there is no point of security. Any PeopleSoft user can access/delete any table.
So in order to implement security, PeopleSoft use just one user id called access id to connect to db.