8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: schema_write_access.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/security/schema_write_access.sql -- Author : Tim Hall -- Description : Displays the users with write access to a specified schema. -- Requirements : Access to the DBA views. -- Call Syntax : @schema_write_access.sql (schema-name) -- Last Modified: 05-MAY-2012 -- ----------------------------------------------------------------------------------- set verify off -- Direct grants select distinct grantee from dba_tab_privs where privilege in ('INSERT', 'UPDATE', 'DELETE') and owner = upper('&1') union -- Grants via a role select distinct grantee from dba_role_privs join dba_users on grantee = username where granted_role IN (select distinct role from role_tab_privs where privilege in ('INSERT', 'UPDATE', 'DELETE') and owner = upper('&1') union select distinct role from role_sys_privs where privilege in ('INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE')) union -- Access via ANY sys privileges select distinct grantee from dba_sys_privs join dba_users on grantee = username where privilege in ('INSERT ANY TABLE', 'UPDATE ANY TABLE', 'DELETE ANY TABLE');