objects_privileges_grants.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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