如何获得Oracle用户创建和授权语句

有时候,我们需要在不同的库中复制用户定义,比如需要在一个测试库中创建和产品库中同名的用户,并且拥有同样的权限。或者在同一个库中创建一个不同名的用户,但是和另外一个用户拥有同样的权限等。换句话说,就是需要获得某个用户的创建和授权语句。

可以通过SQL从一些数据字典中查询到授权信息,生成授权语句:

undefine user_name
set pagesize 1000
select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' to '||tt.grantee||';'
from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';'
from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));

 

另外,通过Oracle提供的dbms_metadata包,可以获得更加详细准确的创建用户以及授权的DDL语句,注意在9i中dbms_output.put_line中限制一行不能超过255个字符,所以如果某些授权语句超长,可能无法打印出来,折衷的办法可能,先将结果插入的临时表然后select出来,或者将一行截断循环打印,或者干脆使用前面的SQL语句直接查数字字典表就没有这个限制了:

set serveroutput on size 1000000
set verify off
undefine user_name
declare
 v_name varchar2(30) := upper('&user_name');
 no_grant exception;
 pragma exception_init( no_grant, -31608 );
begin
 dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
 dbms_output.enable(1000000);
 dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name));
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No system privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No role privs granted');
 end;
 begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No object privs granted');
 end;
 begin
  dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name));
 exception
   when no_grant then dbms_output.put_line('-- No tablespace quota specified');
 end;
 dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name ));
exception
 when others then
  if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists');
  else raise;
  end if;
end;
/

运行结果如下:

输入 user_name 的值:  NinGoo

   CREATE USER "NINGOO" IDENTIFIED BY VALUES
'S:76033D49338E38166B0C090A4447B3D58A70B16C2001A39D7AA844B25616;DABAE35759'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
  GRANT UNLIMITED TABLESPACE TO "NINGOO";
   GRANT "CONNECT" TO "NINGOO";
   GRANT "RESOURCE" TO "NINGOO";
   GRANT "DBA" TO "NINGOO";
  GRANT "PLUSTRACE" TO "NINGOO";
  GRANT SELECT ON "SYS"."V_$PROCESS" TO "NINGOO";
  GRANT SELECT ON "SYS"."V_$SESSION" TO "NINGOO";

  DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR :=
'ALTER USER "NINGOO" QUOTA 1048576000 ON "USERS"';
  EXECUTE IMMEDIATE
SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR
:= 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME =
''USERS'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO
TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END
IF;
    ELSE
      RAISE;
    END IF;
END;
/

   ALTER USER "NINGOO" DEFAULT ROLE ALL;

PL/SQL 过程已成功完成。

Leave a Reply