获取用户和权限SQL语句

获取用户和权限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-