Title: Generating CREATE USER DDL Statements
Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada.
These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated
are as follows:
1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL)
2. CREATE role (GENROLE.SQL)
3. GRANT [role|priv] TO user (GRANTPRIV.SQL)
All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The
exception is the prompt for the output filename.
Source/Text/Comments
REM
REM PROGRAM-ID : GENUSER.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 26-AUG-1998
REM
clear screen
PROMPT GENUSER.SQL Generates CREATE USER commands
PROMPT
PROMPT Includes ALTER USER...QUOTA x ON tabspace commands
PROMPT
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off
spool &&outfile..gen
prompt genuser.log
prompt set term on echo off
prompt prompt Creating User Accounts...
prompt set term off echo on
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
prompt set term on echo off
prompt prompt Granting Tablespace Quotas...
prompt set term off echo on
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON TABLESPACE '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
spool off
PROMPT
PROMPT File &&outfile..GEN generated. Please review before using
PROMPT
EXIT
=============================================================
REM
REM PROGRAM-ID : GENROLE.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 6-APR-1996
REM
set term on echo off linesize 132 pagesize 0 heading off
set verify off
clear screen
prompt GENROLE.SQL V1.0 Generate CREATE ROLE statements
prompt
prompt
accept rname prompt ' Grant Role : '
accept outfile prompt ' Output filename : '
set feedback off pagesize 0 heading off
col lne newline
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Role = &&rname
prompt spool &&outfile..log
prompt set term on echo off feedback on
select 'CREATE ROLE '||role||';' lne
from dba_roles
where role like UPPER('%&&rname%')
and role not in ('CONNECT', 'RESOURCE', 'DBA',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY ROLE;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
=====================================================
REM
REM PROGRAM-ID : GRANTPRIVS.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 26-AUG-1998
REM
clear screen
set term on echo off linesize 132 pagesize 0 heading off
set verify off
prompt GRANTPRIVS.SQL Generate Existing GRANT role/priv statements
prompt
prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts
prompt
accept rname prompt ' Grant Priv : '
accept towner prompt ' To User : '
accept outfile prompt ' Output filename : '
set feedback off verify off
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Priv = &&rname
prompt prompt . . User = &&towner
prompt spool &&outfile..log
prompt set term on echo on feedback on
col grantee noprint
col granted_priv noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where (granted_role like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where (privilege like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
order by 1, 2;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
Comment