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