How to View User Roles and Privileges in Oracle
Script to check all privileges, roles on objects present in Oracle Database to a particular user objects.
TYPE–Type of Privilage or ROLE assigned to User.
grantee –User who have the permission of the object.
PRIV — Define what privilage role or object has
tabnm–Name of the object on which privilege was granted.
colnm–Name of the column (if applicable).
priv–Privilege on the object.
ad–Whether the privilege was granted with the GRANT option. Equal to YES or NO.
Owner- Owner of the object
set line 200 pages 200
column grantee for a7
column priv for a25
column tabnm for a15
column colnm for a15
column owner for a7
set wrap on
select 'ROLE' typ, grantee grantee, granted_role priv, admin_option ad, '--' tabnm, '--' colnm, '--' owner
from dba_role_privs
where grantee='HR'
union
select 'SYSTEM' typ, grantee grantee, privilege priv, admin_option ad, '--' tabnm, '--' colnm,'--' owner
from dba_sys_privs
where grantee='HR'
union
select 'TABLE' typ, grantee grantee, privilege priv, grantable ad, table_name tabnm, '--' colnm, owner owner
from dba_tab_privs
where grantee='HR'
union
select 'COLUMN' typ, grantee grantee, privilege priv, grantable ad, table_name tabnm, column_name colnm, owner owner
from dba_col_privs
where grantee='HR'
order by 1;Output:
TYP GRANTEE PRIV AD TABNM COLNM OWNER ------ ------- ------------------------- --- --------------- --------------- ------- ROLE HR CONNECT NO -- -- -- ROLE HR DBA NO -- -- -- ROLE HR RESOURCE NO -- -- -- SYSTEM HR ALTER SESSION NO -- -- -- SYSTEM HR CREATE DATABASE LINK NO -- -- -- SYSTEM HR CREATE SEQUENCE NO -- -- -- SYSTEM HR CREATE SESSION NO -- -- -- SYSTEM HR CREATE SYNONYM NO -- -- -- SYSTEM HR CREATE VIEW NO -- -- -- SYSTEM HR UNLIMITED TABLESPACE NO -- -- -- TABLE HR EXECUTE NO DBMS_STATS -- SYS
Example: Grant all permission of scott.emp object to user HR. Following line add to output after executing the following command:
Grant all on scott.emp to HR;
TABLE HR ALTER NO EMP -- SCOTT
TABLE HR DEBUG NO EMP -- SCOTT
TABLE HR DELETE NO EMP -- SCOTT
TABLE HR FLASHBACK NO EMP -- SCOTT
TABLE HR INDEX NO EMP -- SCOTT
TABLE HR INSERT NO EMP -- SCOTT
TABLE HR ON COMMIT REFRESH NO EMP -- SCOTT
TABLE HR QUERY REWRITE NO EMP -- SCOTT
TABLE HR REFERENCES NO EMP -- SCOTT
TABLE HR SELECT NO EMP -- SCOTT
TABLE HR UPDATE NO EMP -- SCOTT