Table Privileges (dtabprivs.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

Displays table-level privileges (SELECT, INSERT, UPDATE, DELETE, etc.) granted to specific users or roles in the database.

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'),'%')
/

Usage

-- Basic usage
@dtabprivs.sql

Parameters

The script prompts for:

• &grantee - **: User or role who received the privilege (use % for all)
• &owner - **: Schema that owns the table (use % for all)
• &table - **: Table name (use % for all tables)
• &grantor - **: User who granted the privilege (use % for all)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
```## Sample Output

                                       TABLE PRIVILEGES

GRANTEE OWNER TABLE NAME GRANTOR PRIVILEGE GRANTABLE

---

HR_USER HR EMPLOYEES HR SELECT NO HR_USER HR EMPLOYEES HR INSERT NO HR_USER HR EMPLOYEES HR UPDATE NO HR_MANAGER HR EMPLOYEES SYS SELECT YES HR_MANAGER HR DEPARTMENTS SYS SELECT YES PUBLIC SYS DUAL SYS SELECT NO

## Key Output Columns

- **GRANTEE**: User or role that has been granted the privilege
- **OWNER**: Schema that owns the table
- **TABLE NAME**: Name of the table the privilege applies to
- **GRANTOR**: User who granted the privilege
- **PRIVILEGE**: Type of privilege (SELECT, INSERT, UPDATE, DELETE, etc.)
- **GRANTABLE**: Whether the grantee can grant this privilege to others (YES/NO)

## Common Use Cases

**Security Audit**
```sql
-- Review who has access to sensitive tables
@dtabprivs.sql
-- Enter: [appropriate values]

Access Control

-- Verify proper privilege distribution across users and roles
@dtabprivs.sql

Related Scripts

• db - Basic database information
• dtable - Table storage analysis
• dtabcol - Table column information