Tuesday, January 22, 2013

Minimum RDMS Privileges of the PSADMIN Role

 

|--------------------+------------------------------------+------------| 
| | | |
| Privilege | Comments | Removable? |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| ALTER SESSION | This is done during some SQR | No |
| | processes and is also necessary to | |
| | enable SQL tracing during | |
| | performance tuning | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| ALTER TABLESPACE | PeopleSoft does not alter the | Yes |
| | tablespace itself, this is a job | |
| | for the DBA instead. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| ALTER ROLLBACK | The rollback segments should be | Yes |
| SEGMENT | completely maintained by the DBA. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE CLUSTER | PeopleTools cannot do this | Yes |
| | directly and is therefore not a | |
| | function of PeopleSoft, but could | |
| | be an option for performance | |
| | tuning that the DBA may wish to | |
| | take | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE DATABASE | Private database links are used | Yes |
| LINK | during the upgrade compare | |
| | process. Although, perhaps only | |
| | the DBA should be creating | |
| | database links. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE PUBLIC | Public database links are not used | Yes |
| DATABASE LINK | by PeopleSoft | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE PUBLIC | Public synonyms are created during | No |
| SYNONYM | the upgrade compare process | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE SEQUENCE | Sequences are not used by | Yes |
| | PeopleSoft | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE SNAPSHOT | PeopleSoft does not use snapshots | Yes |
| | by default, but they can be used | |
| | to maintain generated tables. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE SESSION | This allows connection to the | No |
| | database and is essential | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE SYNONYM | Private synonyms are created for | No |
| | PSOPRDEFN and PSLOCK for each user | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE TABLE | Data Designer created tables | No |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE VIEW | Data Designer creates views | No |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE PROCEDURE | Not used directly by PeopleSoft. | Yes |
| | Useful in conjunction with | |
| | triggers. Before Oracle 7.3 would | |
| | be needed in conjunction with | |
| | triggers as triggers are not | |
| | compiled, whereas packaged | |
| | procedures are. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE TRIGGER | PeopleSoft does not use triggers | Yes |
| | by default. However this is an | |
| | option for additional auditing. | |
| | In which case CREATE PROCEDURE | |
| | would be an advantage too. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE TABLESPACE | PeopleSoft does not alter the | Yes |
| | tablespace itself, this is a job | |
| | for the DBA instead. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE USER | A new database user is created | No |
| | when a new PeopleSoft user is | |
| | created | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| CREATE ROLLBACK | The rollback segments should be | Yes |
| SEGMENT | completely maintained by the DBA. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| DROP PUBLIC | Public database links are not used | Yes |
| DATABASE LINK | by PeopleSoft | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| DROP PUBLIC | Public synonyms may be dropped | No |
| SYNONYM | during the upgrade compare process | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| DROP ROLLBACK | The rollback segments should be | Yes |
| SEGMENT | completely maintained by the DBA. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| DROP TABLESPACE | PeopleSoft does not alter the | Yes |
| | tablespace itself, this is a job | |
| | for the DBA instead. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| DROP USER | A database user is dropped when a | No |
| | new PeopleSoft user is removed | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| MANAGE TABLESPACE | This allows tablespaces to be | Yes |
| | taken on and off line during a | |
| | backup. This is a job for the DBA | |
| | instead | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| RESOURCE | This is equivalent to CREATE | Yes |
| | CLUSTER, CREATE PROCEDURE, | |
| | CREATE_SEQUENCE, CREATE TABLE, and | |
| | CREATE TRIGGER that are all dealt | |
| | with explicitly elsewhere. It is | |
| | used for compatibility with | |
| | previous versions of Oracle7. | |
| | Thus is privilege is not required | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| EXP_FULL_DATABASE | Full exports could be performed by | Yes |
| | the DBA rather than the PeopleSoft | |
| | system administrator. This also | |
| | implies SELECT ANY TABLE. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| IMP_FULL_DATABASE | Full imports could be performed by | Yes |
| | the DBA rather than the PeopleSoft | |
| | system administrator This also | |
| | implies BECOME USER. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| GRANT ANY ROLE | This privilege is required in | Yes |
| | order to grant the PSUSER role to | |
| | the new database user when | |
| | creating a PeopleSoft user in | |
| | Operator Security. However, if | |
| | the PSUSER role is granted to | |
| | SYSADM with the ADMIN OPTION then | |
| | SYSADM can still grant PSUSER and | |
| | GRANT ANY ROLE is no longer | |
| | required. | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| ALTER USER | This allows the user, amongst | No |
| | other things, to change passwords. | |
| | It is therefore required | |
| | | |
|--------------------+------------------------------------+------------|
| | | |
| BECOME USER | This is required by and implicit | Yes |
| | in IMP_FULL_USER, but is not | |
| | otherwise required by PeopleTools | |
| | | |
|--------------------+------------------------------------+------------|
Source: Oracle Doc ID: 1140413.1