Recreate Oracle User account.
Since the release of Oracle 10g and 9i, most would think that the need for scripts to recreate individual database components are unnecessary and they would be correct if you are already at Oracle 9i or 10g. Oracle 9i provides the user of the DBMS_METADATA package, which give the DBA the ability to generate the DDL for most any Oracle structure. Oracle 10g has enhanced the abilities of DBMS_METADATA and given the add value of ORACLE expdmp and impdmp, which do not require the existence of such objects as a user to transport data between databases. However, there still exist tons of Oracle 8i and earlier versions, which do not contain either of the abilities above and from which we still have to recreate individual oracle objects. This article is part of a series that covers the recreation of Oracle objects by producing the DDL from which to recreate them.
In this article we will cover the recreate of an Oracle User account in its most simple form. NOTE: We will not cover the recreation of a user defined profile or other objects an oracle user account may depend on. For that information, please see my related article Recreation of User defined Oracle Profiles.
The below script regenerates a user account:
set serveroutput on
spool c:\oradata\create_user_account.sql
declare
curr varchar2(30):= ‘TEST_USER’;
v_ext varchar2(3);
begin
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(’create tablespace ‘||user.default_tablespace);
for dat_file in(select * from dba_data_files where
tablespace_name=user.default_tablespace)
loop
if dat_file.autoextensible=’YES’
then
v_ext:=’ON’;
else
v_ext:=’OFF’;
end if;
dbms_output.put_line(’datafile ‘||””||dat_file.file_name||””||’ size
‘||floor(dat_file.bytes/1024/1024)||’m');
dbms_output.put_line(’autoextend ‘||v_ext);
dbms_output.put_line(’maxsize ‘||floor(dat_file.maxbytes/1024/1024)||’m');
end loop;
dbms_output.put_line(’/');
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;
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;
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;
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
The output generated by above script to recreate the TEST_USER account is listed below.
MYDB >
MYDB > set serveroutput on
MYDB > spool c:\oradata\create_user_account.sql
MYDB >
MYDB >
MYDB > declare
2
3 curr varchar2(30):= ‘TEST_USER’;
4 v_ext varchar2(3);
5
6
7 begin
8
9
10 for user in(select * from dba_users where username = curr)
11 loop
12
13
14 dbms_output.put_line(’create tablespace ‘||user.default_tablespace);
15
16 for dat_file in(select * from dba_data_files where tablespace_name=user.def
ault_tablespace)
17 loop
18
19 if dat_file.autoextensible=’YES’
20 then
21 v_ext:=’ON’;
22 else
23 v_ext:=’OFF’;
24 end if;
25
26
27 dbms_output.put_line(’datafile ‘||””||dat_file.file_name||””||’ size ‘|
|floor(dat_file.bytes/1024/1024)||’m');
28 dbms_output.put_line(’autoextend ‘||v_ext);
29 dbms_output.put_line(’maxsize ‘||floor(dat_file.maxbytes/1024/1024)||’m');
30 end loop;
31 dbms_output.put_line(’/');
32
33 dbms_output.put_line(’create user ‘||user.username||’ identified by ‘||user
.username||’;');
34
35
36 dbms_output.put_line(’alter user ‘||user.username||’ default tablespace ‘||
user.default_tablespace||’;');
37
38 dbms_output.put_line(’alter user ‘||user.username||’ temporary tablespace ‘
||user.temporary_tablespace||’;');
39
40 dbms_output.put_line(’alter user ‘||user.username||’ profile ‘||user.profil
e||’;');
41
42 if user.account_status<>’OPEN’
43 then
44 dbms_output.put_line(’alter user ‘||user.username||’ account lock;’);
45 end if;
46
47 end loop;
48
49
50 for role in(select * from dba_role_privs where grantee=curr)
51 loop
52
53
54 if role.admin_option = ‘YES’
55 then
56 dbms_output.put_line(’grant ‘||role.granted_role||’ to ‘||role.grantee||’ w
ith admin option’||’;');
57 else
58 dbms_output.put_line(’grant ‘||role.granted_role||’ to ‘||role.grantee||’;’
);
59 end if;
60
61
62 end loop;
63
64 for sys_priv in(select * from dba_sys_privs where grantee=curr)
65 loop
66
67 if sys_priv.admin_option = ‘YES’
68 then
69 dbms_output.put_line(’grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’ with admin option’||’;');
70 else
71 dbms_output.put_line(’grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee
||’;');
72 end if;
73
74 end loop;
75
76 for tab_priv in(select * from dba_tab_privs where grantee=curr)
77 loop
78
79 if tab_priv.grantable = ‘YES’
80 then
81 dbms_output.put_line(’grant ‘||tab_priv.privilege||’ on ‘||tab_priv.owner||
‘.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with grant option;’);
82 else
83 dbms_output.put_line(’grant ‘||tab_priv.privilege||’ on ‘||tab_priv.owner||
‘.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;');
84 end if;
85
86 end loop;
87 end;
88 /
create tablespace FOMIS_PROBLEM_TRACKING_BE2
datafile ‘C:\ORACLE\PRODUCT\10.1.0\ORADATA\MYDB\FOMIS_PROBLEM_TRACKING_BE2′
size 304m
autoextend ON
maxsize 4096m
/
create user TEST_USER identified by TEST_USER;
alter user TEST_USER default tablespace TEST_USER_T;
alter user TEST_USER temporary tablespace TEMP;
alter user TEST_USER profile DEFAULT;
grant CONNECT to TEST_USER;
grant CREATE JOB to TEST_USER;
grant CREATE TYPE to TEST_USER;
grant CREATE VIEW to TEST_USER;
grant CREATE TABLE to TEST_USER;
grant ALTER SESSION to TEST_USER;
grant CREATE CLUSTER to TEST_USER;
grant CREATE LIBRARY to TEST_USER;
grant CREATE SESSION to TEST_USER;
grant CREATE SYNONYM to TEST_USER;
grant CREATE TRIGGER to TEST_USER;
grant CREATE OPERATOR to TEST_USER;
grant CREATE SEQUENCE to TEST_USER;
grant CREATE DIMENSION to TEST_USER;
grant CREATE INDEXTYPE to TEST_USER;
grant CREATE PROCEDURE to TEST_USER;
grant CREATE ANY CONTEXT to TEST_USER;
grant CREATE DATABASE LINK to TEST_USER;
grant UNLIMITED TABLESPACE to TEST_USER;
grant CREATE MATERIALIZED VIEW to TEST_USER;
grant EXECUTE on SYS.DBMS_RLS to TEST_USER;
grant EXECUTE on CTXSYS.CTX_DDL to TEST_USER;
grant EXECUTE on CTXSYS.CTX_DOC to TEST_USER;
PL/SQL procedure successfully completed.
MYDB >
MYDB >
This completes the creation of SQL to regenerate a specific user account.
LARRY J. Catt, OCP 9i, 10g