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