Dtabpriv (dtabpriv.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 comprehensive view of table privileges across the database, showing who has access to which tables and what operations they can perform. Essential for security audits and access control management.

The Script

rem  dtabpriv.sql
rem
ttitle 'TABLE PRIVILEGES'
rem
set linesize 250
rem
col grantee format a20 heading 'GRANTEE'
col owner format a8 heading 'OWNER'
col table_name format a30 heading 'TABLE NAME'
col grantor format a8 heading 'GRANTOR'
col privilege format a9 heading 'PRIVILEGE'
col grantable format a9 heading 'GRANTABLE'
rem
select grantee, owner, table_name,
       grantor, privilege, grantable
  from sys.dba_tab_privs
 where grantee like nvl(upper('&grantee'),'%')
   and owner like nvl(upper('&owner'),'%')
   and table_name like nvl(upper('&table'),'%')
   and grantor like nvl(upper('&grantor'),'%')
/

Parameters

The script prompts for:

• &User - or role receiving the privilege (use % for all)
• &Table - owner to filter (use % for all)
• &Table - name pattern to filter (use % for all)
• &User - who granted the privilege (use % for all)

Usage

-- Basic usage
@dtabpriv.sql

-- When prompted, enter:
-- grantee: User or role receiving the privilege (use % for all)
-- owner: Table owner to filter (use % for all)
-- table: Table name pattern to filter (use % for all)
-- grantor: User who granted the privilege (use % for all)

Sample Output

TABLE PRIVILEGES

GRANTEE              OWNER    TABLE NAME                     GRANTOR  PRIVILEGE GRANTABLE
-------------------- -------- ------------------------------ -------- --------- ---------
APP_USER             HR       EMPLOYEES                      HR       SELECT    NO
APP_USER             HR       EMPLOYEES                      HR       INSERT    NO
APP_USER             HR       EMPLOYEES                      HR       UPDATE    NO
APP_USER             HR       DEPARTMENTS                    HR       SELECT    YES
PUBLIC               HR       COUNTRIES                      HR       SELECT    NO
REPORT_ROLE          HR       EMPLOYEES                      SYS      SELECT    NO
REPORT_ROLE          HR       DEPARTMENTS                    SYS      SELECT    NO
HR_ADMIN             HR       EMPLOYEES                      HR       ALTER     NO
HR_ADMIN             HR       EMPLOYEES                      HR       DELETE    NO
HR_ADMIN             HR       EMPLOYEES                      HR       INDEX     NO
HR_ADMIN             HR       EMPLOYEES                      HR       INSERT    NO
HR_ADMIN             HR       EMPLOYEES                      HR       SELECT    YES
HR_ADMIN             HR       EMPLOYEES                      HR       UPDATE    NO