注册
登录
| 关键词： nbsp Username Tablespace Grantee grant test name Loop user Sql |
在日常维护工作中，会遇到需要将一个库的用户迁移到别的库当中去，在此过程中需要在目标库当中创建相同的用户和相关权限，如果在一个开发文档规范的项目组中，相关的用户和权限信息可以轻易的获取到，但是万一相关的信息没有被记录文档，这个时候就需要从数据库中获取相关的语句了。 以下语句可以获取到相关的所需的创建语句。 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):='&&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; www.2cto.com 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 www.2cto.com 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 tablespaceUSERS 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 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
