SCRIPT FOR CREATING ROLES
====================================


REM
REM SCRIPT FOR CREATING ROLES
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 to build all the roles
REM in the database. This created file, create_roles.sql, can be run
REM by any user with the DBA role or with the 'CREATE ROLE' system privilege.
REM
REM Since it is not possible to create a role under a specific schema, it is
REM essential that the original creator be granted 'ADMIN' option to the role.
REM Therefore, such grants will be made at the end of the create_roles.sql
REM script. Since it is not possible to distinguish the creator from someone
REM who was simply granted 'WITH ADMIN OPTION', all grants will be spooled.
REM In addition, the user who creates the role is automatically granted
REM 'ADMIN' option on the role, therefore, if this script is run a second
REM time, this user will also be granted 'ADMIN' on all the roles. You must
REM explicitly revoke 'ADMIN OPTION' from this user to prevent this from
REM happening.
REM
REM NOTE: This script will not capture the create or grant on the Oracle
REM predefined roles, CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, or
REM IMP_FULL_DATABASE.
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 build script...' from dual;
set termout off

spool create_roles.sql

select 'CREATE ROLE ' || lower(role) || ' NOT IDENTIFIED;'
from sys.dba_roles
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE')
and password_required='NO'
/
select 'CREATE ROLE ' || lower(role) || ' IDENTIFIED BY VALUES ' ||
'''' || password || '''' || ';'
from sys.dba_roles, sys.user$
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE')
and password_required='YES' and
dba_roles.role=user$.name
and user$.type=0
/

select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
' WITH ADMIN OPTION;'
from sys.dba_role_privs
where admin_option='YES'
and granted_role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE')
order by grantee
/
spool off

exit

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