Dprivall (dprivall.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a unified view of all privileges granted to users and roles, including system privileges, role grants, and object privileges. Essential for comprehensive security audits and access reviews.

The Script

col privilege format a100
col grantee   format a30

define grantee    = &grantee


select grantee, privilege
  from sys.dba_sys_privs
 where grantee like nvl(upper('&grantee'),'%')
UNION ALL
select grantee, granted_role privilege
  from sys.dba_role_privs
 where grantee like nvl(upper('&grantee'),'%')
UNION ALL
select grantee, privilege
  from
  (select grantee, 'grantor = '||grantor||', owner = '|| owner ||', table_name = '||table_name||', privilege = '||privilege privilege
  from sys.dba_tab_privs
 where grantee like nvl(upper('&grantee'),'%')
)
order by grantee, privilege
/

undefine grantee

Parameters

The script prompts for:

• &User - or role to analyze (use % for all)

Usage

-- Basic usage
@dprivall.sql

-- When prompted, enter:
-- grantee: User or role to analyze (use % for all)

Sample Output

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------------------------------------------------------------------
HR_ADMIN                       CREATE SESSION
HR_ADMIN                       CREATE TABLE
HR_ADMIN                       CREATE VIEW
HR_ADMIN                       HR_ROLE
HR_ADMIN                       RESOURCE
HR_ADMIN                       SELECT ANY DICTIONARY
HR_ADMIN                       grantor = HR, owner = HR, table_name = EMPLOYEES, privilege = DELETE
HR_ADMIN                       grantor = HR, owner = HR, table_name = EMPLOYEES, privilege = INSERT
HR_ADMIN                       grantor = HR, owner = HR, table_name = EMPLOYEES, privilege = SELECT
HR_ADMIN                       grantor = HR, owner = HR, table_name = EMPLOYEES, privilege = UPDATE
HR_ADMIN                       grantor = HR, owner = HR, table_name = DEPARTMENTS, privilege = SELECT
HR_ADMIN                       grantor = SYS, owner = SYS, table_name = DBA_USERS, privilege = SELECT