denrepo

OracleSecurity & users

All privileges for one user

Prompts for a username and lists the roles, system privileges and object privileges granted directly to it.

Prompts for input

Not yet verified. How scripts are tested

ora-user-privs.sql 
1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 500 TRIMOUT ON TAB OFF VERIFY OFF3COLUMN kind      FORMAT A74COLUMN privilege FORMAT A305COLUMN on_object HEADING "ON" FORMAT A506 7SELECT 'ROLE' AS kind, granted_role AS privilege, NULL AS on_object8FROM dba_role_privs WHERE grantee = UPPER('&&username')9UNION ALL10SELECT 'SYSTEM', privilege, NULL11FROM dba_sys_privs WHERE grantee = UPPER('&&username')12UNION ALL13SELECT 'OBJECT', privilege, owner || '.' || table_name14FROM dba_tab_privs WHERE grantee = UPPER('&&username')15ORDER BY 1, 2, 3;16 17UNDEFINE username

Save it as ora-user-privs.sql and run it with SQL> @ora-user-privs.

Open in denrepo

Helps with

More Oracle scripts: Security & users