SCRIPT FOR CAPTURING ROLE GRANTS
===========================================


REM
REM SCRIPT FOR CAPTURING ROLE GRANTS
REM
REM This script must be run by a user with the DBA role.
REM
REM This script is intended to run with Oracle7.
REM
REM Running this script will in turn create a script of all the grants
REM of roles to users and other roles. This created script, grant_roles.sql,
REM must be run by a user with the DBA role.
REM
REM Since role grants are not dependant on the schema that issued the grant,
REM the grant_roles.sql script will not issue the grant of a role by the
REM original grantor. All grants will be issued by the user specified when
REM running this script.
REM
REM NOTE: Grants made to 'SYS' are not captured.
REM
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM

set verify off
set feedback off
set termout off
set echo off;
set pagesize 0

set termout on
select 'Creating role grant script...' from dual;
set termout off

spool grant_roles.sql

select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_role_privs
where grantee != 'SYS'
order by grantee
/

spool off

exit

REM ---------------------------------------------------------------------------