Oracle 数据库角色

在PeopleSoft数据库安装的过程中，会创建两个数据库角色PSUSER和PSADMIN，如下面的脚本所示：

以下内容截取自psroles.sql：

REMARK -- These are the minimum privileges required to run PeopleSoft
REMARK -- applications.  If you plan to run SQL<>Secure, you will need to
REMARK -- grant "execute any procedure" to PSUSER and PSADMIN.

set echo on
spool psroles.log

DROP ROLE PSUSER;
DROP ROLE PSADMIN;

CREATE ROLE PSUSER;
GRANT CREATE SESSION TO PSUSER;

CREATE ROLE PSADMIN;
GRANT 
ANALYZE ANY,
CREATE SESSION, ALTER SESSION,
CREATE TABLESPACE, DROP TABLESPACE,
CREATE ANY TABLE, ALTER ANY TABLE, SELECT ANY TABLE,
INSERT ANY TABLE, UPDATE ANY TABLE, COMMENT ANY TABLE,
DROP ANY TABLE,
CREATE ANY PROCEDURE, ALTER ANY PROCEDURE,
EXECUTE ANY PROCEDURE, DROP ANY PROCEDURE,
CREATE ANY INDEX, DROP ANY INDEX,
CREATE ANY INDEXTYPE, DROP ANY INDEXTYPE,
CREATE ANY SEQUENCE, DROP ANY SEQUENCE,
CREATE PUBLIC SYNONYM, CREATE ANY SYNONYM,
DROP ANY SYNONYM, DROP PUBLIC SYNONYM,
CREATE ANY VIEW, DROP ANY VIEW,
CREATE ANY TRIGGER, ALTER ANY TRIGGER,
ADMINISTER DATABASE TRIGGER, DROP ANY TRIGGER,
CREATE DATABASE LINK,
CREATE PUBLIC DATABASE LINK, 
DROP PUBLIC DATABASE LINK,
CREATE ROLE, DROP ANY ROLE,
CREATE USER
TO PSADMIN WITH ADMIN OPTION;

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE -
    (GRANTEE_NAME => 'PSADMIN', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', -
     ADMIN_OPTION => TRUE);
     
conn / as sysdba;
      
GRANT SELECT ON V_$MYSTAT to PSADMIN;

spool off

在PeopleTools8.51中添加了Oracle Resource Manager权限。

PSUSER 角色

在PeopleTools 7 中引入了PSUSER 角色，该角色赋予给每个PeopleSoft操作者对应的数据库用户。然而，在PeopleTools 8中该角色不再赋予给任何人，而是将CREATE SESSION权限直接赋给CONNECT ID（通常是PEOPLE）：

connect.sql:

CREATE USER people IDENTIFIED BY peop1e DEFAULT TABLESPACE psdefault
TEMPORARY TABLESPACE pstemp;
GRANT CREATE SESSION to people;

PSADMIN 角色
PSADMIN角色赋予给Access ID（通常是SYSADMN）。PeopleSoft将该角色的权限描述为运行PeopleSoft所需要的最小权限。Access ID是一个管理账户，因此在PeopleSoft管理员和DBA之间需要有某种程度的信任与合作。

然而，并不是PSADMIN角色中的所有权限都对PeopleSoft的操作都至关重要。一些DBA甚至想要限制这个角色赋予用户的权限。下表解释了PSADMIN角色中的各种权限的用途：