有时候,我们需要在不同的库中复制用户定义,比如需要在一个测试库中创建和产品库中同名的用户,并且拥有同样的权限。或者在同一个库中创建一个不同名的用户,但是和另外一个用户拥有同样的权限等。换句话说,就是需要获得某个用户的创建和授权语句。
可以通过SQL从一些数据字典中查询到授权信息,生成授权语句:
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 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;
/
运行结果如下:
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 过程已成功完成。