-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Capture the Object,System Privilege & Role GRANT statements for non system generated users and roles. You can user UNION operation to club the results. 
-- Call Syntax  : @grants_statements.sql
-- Last Modified: 17/03/2015
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET LONG 9999999 LONGCHUNKSIZE 9999999 PAGESIZE 0 LINESIZE 300 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON HEAD OFF ECHO OFF

-- Objects privileges
SPOOL grants_statements.SQL
SELECT 'GRANT  ' ||LOWER(PRIVILEGE) ||'  ON ' ||LOWER(OWNER) ||'.' ||LOWER(table_name) ||'  TO ' ||LOWER(grantee) ||'  ;'
FROM dba_tab_privs
WHERE grantee NOT IN (
    'DBSNMP','DIP','ORACLE_OCM','OUTLN','SYS','SYSTEM','APPQOSSYS','ADM_PARALLEL_EXECUTE_TASK','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','DATAPUMP_EXP_FULL_DATABASE'
,'DATAPUMP_IMP_FULL_DATABASE','DBA','DBFS_ROLE','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GLOBAL_AQ_USER_ROLE'
,'HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE','IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','OEM_ADVISOR','OEM_MONITOR','RECOVERY_CATALOG_OWNER'
,'RESOURCE','SCHEDULER_ADMIN','SELECT_CATALOG_ROLE'
  )
ORDER BY grantee;

-- System privileges
SELECT 'GRANT ' ||LOWER(PRIVILEGE) ||'  TO ' ||LOWER(grantee) ||' ;'
FROM dba_sys_privs
WHERE grantee NOT IN (
    'DBSNMP','DIP','ORACLE_OCM','OUTLN','SYS','SYSTEM','APPQOSSYS','ADM_PARALLEL_EXECUTE_TASK','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','DATAPUMP_EXP_FULL_DATABASE'
,'DATAPUMP_IMP_FULL_DATABASE','DBA','DBFS_ROLE','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GLOBAL_AQ_USER_ROLE'
,'HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE','IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','OEM_ADVISOR','OEM_MONITOR','RECOVERY_CATALOG_OWNER'
,'RESOURCE','SCHEDULER_ADMIN','SELECT_CATALOG_ROLE'
  )
ORDER BY grantee;

-- Roles
SELECT 'GRANT ' ||LOWER(granted_role) ||' TO ' ||LOWER(grantee) ||decode(
    admin_option,
    'YES',
    ' WITH ADMIN OPTION;',
    ';'
  )
FROM SYS.dba_role_privs
WHERE grantee NOT IN (
    'DBSNMP','DIP','ORACLE_OCM','OUTLN','SYS','SYSTEM','APPQOSSYS','ADM_PARALLEL_EXECUTE_TASK','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','DATAPUMP_EXP_FULL_DATABASE'
,'DATAPUMP_IMP_FULL_DATABASE','DBA','DBFS_ROLE','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GLOBAL_AQ_USER_ROLE'
,'HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE','IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','OEM_ADVISOR','OEM_MONITOR','RECOVERY_CATALOG_OWNER'
,'RESOURCE','SCHEDULER_ADMIN','SELECT_CATALOG_ROLE'
  )
ORDER BY grantee;

SPOOL OFF