获取用户和权限SQL语句

在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中需要在目标库当中创建相同的用户和相关权限，如果在

在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中需要在目标库当中创建相同的用户和相关权限，如果在一个开发文档规范的项目组中，相关的用户和权限信息可以轻易的获取到，但是万一相关的信息没有被记录文档，，这个时候就需要从数据库中获取相关的语句了。
以下语句可以获取到相关的所需的创建语句。

-- 获取创建用户
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-