script Creazione Utente

vi create_user.sql

set long 9999

set linesize 400

set pagesize 0

set trimspool on

set verify off

set serveroutput on size 100000

col usrddl format a400

col sysgrant format a400

col rolgrant format a400

col objgrant format a400

col spoolname new_value outname

connect / as sysdba

accept username prompt 'Utente: '

select 'create_user_&username' spoolname from dual ;

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

spool &&outname..sql

select 'spool '||'&&outname'||'.log' from dual ;

select 'REM CREATE USER' from dual ;

select dbms_metadata.get_ddl('USER',upper('&username')) usrddl from dual ;

select 'REM SYSTEM PRIVS' from dual ;

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper('&username')) sysgrant from dual ;

select 'REM ROLE PRIVS' from dual ;

select dbms_metadata.get_granted_ddl('ROLE_GRANT',upper('&username')) rolgrant from dual ;

select 'REM OBJECT PRIVS' from dual ;

select dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper('&username')) objgrant from dual ;

select 'spool off' from dual ;

spool off

exit;

Si esegue così:

# sqlplus / as sysdba

SQL> @create_user.sql

Utente:

compare un prompt in cui viene chiesto di inserire il nome dello schema da creare