作者：慈禧太后她妈_151 | 来源：互联网 | 2014-07-13 17:52
在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中需要在目标库当中创建相同的用户和相关权限，如果在一个开发文档规范的项目组中，相关的用户和权限信息可以轻易的获取到，但是万一相...SyntaxHighlighter.all();
在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中
需要在目标库当中创建相同的用户和相关权限，如果在一个开发文档规范的项目组中，
相关的用户和权限信息可以轻易的获取到，但是万一相关的信息没有被记录文档，
这个时候就需要从
数据库中获取相关的语句了。
以下语句可以获取到相关的所需的创建语句。
www.2cto.com
-- 获取创建用户
Set pagesize 100;
Set linesize 150;
set serveroutput on size 1000000;
undefine user_name;
Declare
v_Sql Varchar2(1000);
v_Username Varchar2(100):=&＃39;&&user_name&＃39;;
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; www.2cto.com
v_Sql := &＃39;create user &＃39; || x.Username ||
&＃39; identified by values &＃39;&＃39;&＃39; || x.Password || &＃39;&＃39;&＃39;&＃39; ||
&＃39; default tablespace &＃39; || x.Default_Tablespace ||
&＃39; temporary tablespace &＃39; || x.Temporary_Tablespace ||
&＃39; profile &＃39; || x.Profile || &＃39;;&＃39;;
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 := &＃39;grant &＃39; || x.Granted_Role || &＃39; to &＃39; || x.Grantee || &＃39;;&＃39;;
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 := &＃39;grant &＃39; || x.Privilege || &＃39; to &＃39; || x.Grantee || &＃39;;&＃39;;
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 !=&＃39;SYS&＃39; Then www.2cto.com
Dbms_Output.Put_Line(&＃39;--注意使用其他用户登录执行授权&＃39;);
End If;
v_Sql := &＃39;grant &＃39; || x.Privilege || &＃39; on &＃39; || x.Owner || &＃39;.&＃39; ||
x.Table_Name || &＃39; to &＃39; || x.Grantee || &＃39;;&＃39;;
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 := &＃39;alter user &＃39; || x.User_Name || &＃39; quota &＃39; ||
x.Maxblocks * x.Blocksize || &＃39; on &＃39; || x.Ts_Name || &＃39;;&＃39;;
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):=&＃39;&&user_name&＃39;;
new 3: v_Username Varchar2(100):=&＃39;test&＃39;;
create user TEST identified by values &＃39;7A0F2B316C212D67&＃39; default tablespace
USERS temporary tablespace TEMP profile DEFAULT;
grant RESOURCE to TEST; www.2cto.com
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;
作者 gtlions
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有