This brief introduction will cover with the fundamentals of Oracle privilege as well as roles, including how to retrieve all privilege for a user, and how to grant necessary roles or privilege to a user.
1. Basic Views of privileges and roles
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_ROLE_PRIVS
ROLE_SYS_PRIVS
SESSION_PRIVS
When granting a privilege to user, the PRIVILEGE will infect immediate; but when granting a role to a user, already connected session have to reconnect or use set role all to refresh the new granted role privileges
2. Script to find all privileges
1 2 3 4 5 6 7 8
select privilege from dba_sys_privs where grantee = upper('&1') union select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee = upper('&1'));
3. Analysis of specific roles
Roles privileges can be retrieved from role_sys_privs view:
Resource role does not have create view privilege, but have unlimited tablespace
[email protected]> select PRIVILEGE, role from role_sys_privs where role = upper('&1'); Enter value for 1: connect old 1: select PRIVILEGE, rolefrom role_sys_privs whererole = upper('&1') new1: select PRIVILEGE, rolefrom role_sys_privs whererole = upper('connect')
PRIVILEGE ROLE --------------------- ---------------------- SETCONTAINERCONNECT CREATESESSIONCONNECT