获取用户创建和授权

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

作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】

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

可以通过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 过程已成功完成。

本文网址：http://www.ningoo.net/html/2008/how_to_get_oracle_user_privileges.html

 

 

DBA Scripts:获取用户创建语句

作者：eygle | <script src="http://www.eygle.com/English.js" type="text/javascript"></script> English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2006/07/dba_scripts_create_user.html

---

站内相关文章|Related Articles

如何快速的成为一个合格的Oracle DBA?
DBA警世录：职业生涯误操作之误删除篇
DBA Scripts:转换RDBA的文件和数据块地址
DBA Scripts:获得跟踪文件名称的gettrcname.sql
ITPUB年会回顾-阿里巴巴的数据库管理优化体系

今天新开一个分类:Scripts,用来收集和记录一些DBA经常使用的脚本。
这些脚本有的来自网络，有的来自自己编写，记录在这里供大家参考，同时也给自己一个重新熟悉的过程。

很多时候我们在作数据库迁移时需要进行重建用户等工作,这时就需要获得用户信息,本脚本就用于获取用户的创建语句,具体脚本如下,来源Metalink:

SET verify off;
SET termout off;
SET feedback off;
SET echo off;
SET pagesize 0;
SET timeing off;

SET termout on
SELECT 'Creating user build script...' FROM DUAL;
SET termout off;

CREATE TABLE usr_temp( lineno NUMBER,usr_name VARCHAR2(30),text VARCHAR2(80))
/

DECLARE
CURSOR usr_cursor
IS
SELECT username, PASSWORD, default_tablespace, temporary_tablespace,
PROFILE
FROM SYS.dba_users
WHERE username != 'SYS' AND username != 'SYSTEM'
ORDER BY username;

CURSOR qta_cursor (c_usr VARCHAR2)
IS
SELECT tablespace_name, max_bytes
FROM SYS.dba_ts_quotas
WHERE username = c_usr;

lv_username SYS.dba_users.username%TYPE;
lv_password SYS.dba_users.PASSWORD%TYPE;
lv_default_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_temporary_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_profile SYS.dba_users.PROFILE%TYPE;
lv_tablespace_name SYS.dba_ts_quotas.tablespace_name%TYPE;
lv_max_bytes SYS.dba_ts_quotas.max_bytes%TYPE;
lv_string VARCHAR2 (80);
lv_lineno NUMBER := 0;

PROCEDURE write_out (p_line INTEGER, p_name VARCHAR2, p_string VARCHAR2)
IS
BEGIN
INSERT INTO usr_temp
(lineno, usr_name, text
)
VALUES (p_line, p_name, p_string
);
END;
BEGIN
OPEN usr_cursor;

LOOP
FETCH usr_cursor
INTO lv_username, lv_password, lv_default_tablespace,
lv_temporary_tablespace, lv_profile;

EXIT WHEN usr_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE USER ' || LOWER (lv_username));
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;

IF lv_password IS NULL
THEN
lv_string := 'IDENTIFIED EXTERNALLY';
ELSE
lv_string := ('IDENTIFIED BY VALUES ''' || lv_password || '''');
END IF;

write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'DEFAULT TABLESPACE ' || lv_default_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TEMPORARY TABLESPACE ' || lv_temporary_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;

OPEN qta_cursor (lv_username);

LOOP
FETCH qta_cursor
INTO lv_tablespace_name, lv_max_bytes;

EXIT WHEN qta_cursor%NOTFOUND;
lv_lineno := lv_lineno + 1;

IF lv_max_bytes IS NULL
THEN
lv_string := 'QUOTA UNLIMITED ON ' || lv_tablespace_name;
ELSE
lv_string :=
'QUOTA ' || lv_max_bytes || ' ON ' || lv_tablespace_name;
END IF;

write_out (lv_lineno, lv_username, lv_string);
END LOOP;

CLOSE qta_cursor;

lv_string := ('PROFILE ' || lv_profile || ';');
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := ' ';
write_out (lv_lineno, lv_username, lv_string);
END LOOP;

CLOSE usr_cursor;
END;
/

SPOOL create_users.sql
SET heading off
SET recsep off
COL test format a80 word_wrap

SELECT text
FROM usr_temp
ORDER BY usr_name, lineno;

SPOOL off;

DROP TABLE usr_temp;
EXIT

运行该脚本后会产生一个create_users.sql的输出文件,输出内容参考范例如下:

CREATE USER dbsnmp IDENTIFIED BY VALUES 'E066D214D5421CCC' DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; CREATE USER eygle IDENTIFIED BY VALUES 'B726E09FE21F8E83' DEFAULT TABLESPACE EYGLE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; CREATE USER outln IDENTIFIED BY VALUES '4A3BA55E08595C81' DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; CREATE USER perfstat IDENTIFIED BY VALUES 'AC98877DE1297365' DEFAULT TABLESPACE PERFSTAT TEMPORARY TABLESPACE TEMP QUOTA -1 ON PERFSTAT PROFILE DEFAULT; CREATE USER test IDENTIFIED BY VALUES '7A0F2B316C212D67' DEFAULT TABLESPACE TEST TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; CREATE USER wmsys IDENTIFIED BY VALUES '7C9BA362F8314299' DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

 

 

历史上的今天...
      >> 2008-07-15文章:

              《深入浅出Oracle》一书修订进度

              SPFILE参数修改错误的解决办法

      >> 2007-07-15文章:

              摄影习作-花之灵

------
这篇 【 DBA Scripts:获取用户创建语句】来自 www.eygle.com | CSDN技术网摘| del.icio.us| 365Key

By eygle on 2006-07-15 13:09 | Comments (0) | Posted to Scripts | Edit |Pageviews: <script src="http://www.eygle.com/cntall.php" language="Javascript" type="text/javascript"></script> 981