Script:Generating CREATE USER DDL Statements

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