Generate Revoke Privileges Statements (revoke_privs.gen)

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 generator script creates REVOKE statements to remove object privileges from users or roles. It can filter by object owner, object name, and grantee.

The Script

rem  revoke_privs.gen
rem
select 'revoke ' || privilege || ' on ' || owner || '.' || table_name ||
       ' from ' || grantee || ';'
  from sys.dba_tab_privs
 where owner like upper('&owner')
   and table_name like upper('&object_name')
   and grantee like upper('&grantee')
 order by owner, table_name, grantee, privilege
/

Usage

-- Basic usage
@revoke_privs.gen

-- When prompted, enter:
-- owner: Object owner pattern (use % for wildcard)
-- object_name: Object name pattern (use % for wildcard)
-- grantee: User or role pattern (use % for wildcard)

Parameters

The script prompts for:

• &owner - Object owner pattern (use % for wildcard)
• &object_name - Object name pattern (use % for wildcard)
• &grantee - User or role pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TAB_PRIVS
-- Plus appropriate privileges to revoke

Sample Output

revoke SELECT on HR.EMPLOYEES from PUBLIC;
revoke INSERT on HR.EMPLOYEES from APP_USER;
revoke UPDATE on HR.EMPLOYEES from APP_USER;
revoke DELETE on HR.EMPLOYEES from APP_USER;

Key Features

• Flexible Filtering: Filter by owner, object, and grantee
• All Privilege Types: Handles SELECT, INSERT, UPDATE, DELETE, etc.
• Ordered Output: Sorted for logical execution order
• Pattern Matching: Use wildcards for bulk operations

Common Use Cases

Revoke All Privileges from User

@revoke_privs.gen
-- Enter: % for owner, % for object_name, OLD_USER for grantee

Revoke PUBLIC Access

@revoke_privs.gen
-- Enter: HR for owner, % for object_name, PUBLIC for grantee

Related Scripts

• Grant Select Generator - Grant privileges
• Table Privileges Analysis - View current privileges