Check all privileges of User in Oracle

Check all privileges of User 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.