View All oracle user permissions and obtain the authorized DDL

1. View All permissions (system permissions, object permissions, and roles) of a specified user in three views: dba_sys_privs, dba_tab_privs, and dba_role_privs.

Script: query_user_privs. SQL

2. Obtain the DDL statements granted by the specified user in two ways:

1) Use the query_user_privs. SQL script in 1 and add keywords such as grant to after select to make the query output result a complete DDL statement.

Script: get_ddl_privs_dic. SQL

2) use the packages provided by oracle:

Dbms_metadata.get_ddl ('user', '& uname') obtains the DDL statement for creating a USER.

Dbms_metadata.get_granted_ddl ('System _ GRANT ',' & uname') gets the DDL statement that the specified user grants SYSTEM permissions.

Dbms_metadata.get_granted_ddl ('Role _ GRANT ',' & uname') gets the DDL statement assigned to the ROLE by the specified user.

Dbms_metadata.get_granted_ddl ('object _ GRANT, '& uname') gets the DDL statement that the specified user grants the OBJECT permission.

Script: get_ddl_privs_pac. SQL

3. Script

3.1 query_user_privs. SQL

 

set echo off  set verify off  set pagesize 999  set linesize 200  col type format a20  SELECT *    FROM (SELECT a.username, 'ROLE' AS TYPE,                  b.granted_role || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted            FROM sys.dba_users a, sys.dba_role_privs b           WHERE a.username = b.grantee          UNION          SELECT a.username, 'SysPrivs' AS TYPE,                  b.privilege || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted            FROM sys.dba_users a, sys.dba_sys_privs b           WHERE a.username = b.grantee          UNION          SELECT a.username,                 'ObjPrivs' AS TYPE,                 b.owner || '.' || b.table_name || ' - ' || b.privilege ||                  DECODE (grantable, 'YES', ' (With Grant Option)', NULL) what_granted            FROM sys.dba_users a, sys.dba_tab_privs b           WHERE a.username = b.grantee          ORDER BY 1)   WHERE username = upper('&input_username');  

 

3.2 get_ddl_privs_dic. SQL

 

clear screen    accept uname prompt 'Enter User Name : '  accept outfile prompt  ' Output filename : '    col username noprint  col lne newline    set heading off pagesize 0 verify off feedback off linesize 180    spool &&outfile..gen    prompt   SELECT username, 'CREATE USER '||username||' '||         DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',                'IDENTIFIED BY VALUES '''||password||''' ') lne,         'DEFAULT TABLESPACE '||default_tablespace lne,         'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne    FROM DBA_USERS   WHERE USERNAME LIKE UPPER('%&&uname%')      OR UPPER('&&uname') IS NULL   ORDER BY USERNAME;    SELECT username, 'ALTER USER '||username||' QUOTA '||         DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')         ||' ON '||tablespace_name||';' lne    FROM DBA_TS_QUOTAS   WHERE USERNAME LIKE UPPER('%&&uname%')      OR UPPER('&&uname') IS NULL   ORDER BY USERNAME;    col grantee noprint    select grantee, granted_role granted_priv,         'GRANT '||granted_role||' to '||grantee||         DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')    from dba_role_privs   where grantee like upper('%&&uname%')           UNION  select grantee, privilege granted_priv,         'GRANT '||privilege||' to '||grantee||         DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')    from dba_sys_privs   where grantee like upper('%&&uname%')   order by 1, 2;    spool off  

 

3.3 get_ddl_privs_pac. SQL

 

clear screen     accept uname prompt 'Enter User Name : '  accept outfile prompt  ' Output filename : '     spool &&outfile..gen     SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180     SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;  SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') from dual;  SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname') from dual;  SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&uname') from dual;     spool off