With the advent of Oracle 10g Import and Export data pump utility, the need to recreate a user account from scratch has been greatly reduced. However, there are still situation when you may need the SQL to recreate a specific user within another Oracle environment or just desire to see the creation statements. The script below will generate the statements to create the user defined in the CURR variable from the current Oracle RDBMS environment.
1. First we will create a user named LJCATT with various roles and privileges.
SQL> create user LJCATT identified by LJCATT;
User created.
SQL> alter user LJCATT default tablespace USERS;
User altered.
SQL> alter user LJCATT temporary tablespace TEMP;
User altered.
SQL> alter user LJCATT profile DEFAULT;
User altered.
SQL> grant DBA to LJCATT;
Grant succeeded.
SQL> grant CREATE TABLE to LJCATT with admin option;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to LJCATT;
Grant succeeded.
SQL> grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
Grant succeeded.
SQL>
2. Now we will execute the following PL/SQL block to regenerate the statements to recreate the user from the data dictionary.
set serveroutput on
spool ./create_user_account.sql
declare
curr varchar2(30):= ‘LJCATT’;
v_ext varchar2(3);
begin
— Create original user definition
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);
if user.account_status<>‘OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;
end loop;
— Grant all roles defined for the user.
for role in(select * from dba_role_privs where grantee=curr)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;
— Grant all system privileges for the user.
for sys_priv in(select * from dba_sys_privs where grantee=curr)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;
— Grant all object privileges for the user.
for tab_priv in(select * from dba_tab_privs where grantee=curr)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/
spool off
Sample Output results:
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
create user LJCATT identified by LJCATT;
alter user LJCATT default tablespace USERS;
alter user LJCATT temporary tablespace TEMP;
alter user LJCATT profile DEFAULT;
grant DBA to LJCATT;
grant CREATE TABLE to LJCATT with admin option;
grant UNLIMITED TABLESPACE to LJCATT;
grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
PL/SQL procedure successfully completed.
SQL>
3. The above SQL statements can now be used against any other Oracle database to recreate the user account with the same rights which exist in this database. NOTE: There exist several dependencies in the above statements, such as: tablespaces, profiles, roles, and object privileges. For example, we need to take care that a default tablespace defined in the alter user statement truly exist in the destination database.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com