-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Author : Sameer S Choudhari -- Description : Generate GRANT statements of objects privileges for perticular user -- Call Syntax : @objects_privileges_grants.sql -- Last Modified: 30/06/2016 -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SET ECHO OFF VERIFY OFF LINESIZE 220 PAGES 49999 TRIMOUT ON HEADING OFF TRIMSPOOL OFF COLUMN objects_privileges FORMAT a100 SPOOL objects_privileges_grants.lst ACCEPT username PROMPT 'Enter User or Role name : ' prompt SYSTEM PRIVILEGES granted TO A USER/ROLE i.e. &&username: prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT 'GRANT ' ||LOWER(PRIVILEGE) ||' ON ' ||LOWER(OWNER) ||'.' ||LOWER(table_name) ||' TO ' ||LOWER(grantee) ||';' objects_privileges FROM dba_tab_privs WHERE grantee=UPPER('&username') ORDER BY grantee, OWNER, table_name, PRIVILEGE; SPOOL OFF