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
Script:Generating CREATE USER DDL Statements
Posted
in
by
Tags:
Leave a Reply