systems_privileges_roles_grants.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Generate GRANT statements of ROLES and SYSTEM PRIVILEGES granted to a particular user.
-- Call Syntax  : @systems_privileges_roles_grants.sql
-- Last Modified: 30/06/2016
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET ECHO OFF VERIFY OFF LINESIZE 220 PAGES 49999 TRIMOUT ON HEADING OFF TRIMSPOOL OFF
COLUMN system_privileges FORMAT a100
COLUMN granted_roles FORMAT a100
SPOOL systems_privileges_roles_grants.lst
ACCEPT username PROMPT 'Enter User name : '
PROMPT System privileges granted to a user/role i.e. &&username:
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT 'GRANT ' ||LOWER(PRIVILEGE) ||' TO ' ||LOWER(grantee) ||decode(
    admin_option,
    'YES',
    'WITH ADMIN OPTION;',
    ';'
  ) system_privileges
FROM dba_sys_privs
WHERE grantee=UPPER('&username')
ORDER BY
  grantee,
  PRIVILEGE;
PROMPT Roles granted to a user/role i.e. &&username:
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT 'GRANT ' ||LOWER(granted_role) ||' TO ' ||LOWER(grantee) ||decode(
    admin_option,
    'YES',
    'WITH ADMIN OPTION;',
    ';'
  ) granted_roles
FROM SYS.dba_role_privs
WHERE grantee=UPPER('&username')
ORDER BY
  grantee,
  granted_role;
SPOOL OFF