获取用户和权限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-创建表，提示ORA-00959: 表空间 "MC_DATA" 不存在Oracle控制文件基本维护相关资讯 SQL

SQL 新手指南（09/10/2015 10:57:53）
SQL导入txt以及SQL中的时间格式操（01/25/2015 11:35:04）
SQL 事务及实例演示（08/12/2014 10:36:37）

SQL 中的正则函数（06/28/2015 15:59:48）
SQL 集合（笔记）（01/15/2015 20:19:06）
Oracle shell调用SQL操作DB （02/03/2014 10:48:18）

本文评论查看全部评论（0）