在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中需要在目标库当中创建相同的用户和相关权限，如果在一个开发文档规范的项目组中，相关的用户和权限信息可以轻易的获取到，但是万一相关的信息没有被记录文档，，这个时候就需要从数据库中获取相关的语句了。
以下语句可以获取到相关的所需的创建语句。
-- 获取创建用户
set pagesize 100;
set linesize 150;
set serveroutput on size 1000000;
undefine user_name;
declare
v_sql varchar2(1000);
v_username varchar2(100):='&&user_name';
begin
v_username:=upper(v_username);
for x in (select username,
password,
default_tablespace,
temporary_tablespace,
profile,
account_status
from sys.dba_users
where username =v_username
order by username) loop
v_username := x.username;
v_sql := 'create user ' || x.username ||
' identified by values ''' || x.password || '''' ||
' default tablespace ' || x.default_tablespace ||
' temporary tablespace ' || x.temporary_tablespace ||
' profile ' || x.profile || ';';
dbms_output.put_line(v_sql);
for x in (select granted_role, grantee
from dba_role_privs
where grantee = upper(v_username)) loop
v_sql := 'grant ' || x.granted_role || ' to ' || x.grantee || ';';
dbms_output.put_line(v_sql);
end loop;
for x in (select privilege, grantee
from dba_sys_privs
where grantee = upper(v_username)) loop
v_sql := 'grant ' || x.privilege || ' to ' || x.grantee || ';';
dbms_output.put_line(v_sql);
end loop;
for x in (select privilege, owner, table_name, grantee
from dba_tab_privs
where grantee = upper(v_username) order by owner) loop
if x.owner !='sys' then
dbms_output.put_line('--注意使用其他用户登录执行授权');
end if;
v_sql := 'grant ' || x.privilege || ' on ' || x.owner || '.' ||
x.table_name || ' to ' || x.grantee || ';';
dbms_output.put_line(v_sql);
end loop;
for x in (select user_name, ts_name, maxblocks, blocksize
from ku$_tsquota_view
where user_name = upper(v_username)) loop
v_sql := 'alter user ' || x.user_name || ' quota ' ||
x.maxblocks * x.blocksize || ' on ' || x.ts_name || ';';
dbms_output.put_line(v_sql);
end loop;
end loop;
end;
/
执行示例如下：
sql> @1
enter value for user_name: test
old 3: v_username varchar2(100):='&&user_name';
new 3: v_username varchar2(100):='test';
create user test identified by values '7a0f2b316c212d67' default tablespace users temporary tablespace temp profile default;
grant resource to test;
grant dba to test;
grant connect to test;
grant unlimited tablespace to test;
grant write on sys.impdp_dir to test;
grant read on sys.impdp_dir to test;
--注意使用其他用户登录执行授权
grant select on test2.sms_vote_web to test;
-the end-