【GRANTS】【SCRIPTS】两种自动化获得Oracle授权语句的脚本

来源：这里教程网时间：2026-03-03 13:17:25 作者：

对于DBA来说，一切可以简化操作的尝试都要被鼓励。这里提供两种快速得到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'));

使用效果如下：

sys@ora10g> undefine user_name sys@ora10g> set pagesize 1000 sys@ora10g> select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text 2 from dba_role_privs tt where tt.grantee=(upper('&&user_name')) 3 union all 4 select 'grant '||tt.privilege||' to '||tt.grantee||';' 5 from dba_sys_privs tt where tt.grantee=(upper('&&user_name')) 6 union all 7 select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';' 8 from dba_tab_privs tt where tt.grantee=(upper('&&user_name')) 9 union all 10 select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';' 11 from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name')); Enter value for user_name: sec old 2: from dba_role_privs tt where tt.grantee=(upper('&&user_name')) new 2: from dba_role_privs tt where tt.grantee=(upper('sec')) old 5: from dba_sys_privs tt where tt.grantee=(upper('&&user_name')) new 5: from dba_sys_privs tt where tt.grantee=(upper('sec')) old 8: from dba_tab_privs tt where tt.grantee=(upper('&&user_name')) new 8: from dba_tab_privs tt where tt.grantee=(upper('sec')) old 11: from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name')) new 11: from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('sec')) SQL_TEXT --------------------------------------- grant DBA to SEC; grant UNLIMITED TABLESPACE to SEC; grant WRITE on SYS.DIR1 to SEC; grant READ on SYS.DIR1 to SEC; grant READ on SYS.dir2 to SEC; grant WRITE on SYS.dir2 to SEC; 6 rows selected.

第二种方法：通过Oracle提供的dbms_metadata包，获得更加详细准确的创建用户以及授权的DDL语句： 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; /

使用过程如下：

sys@ora10g> set serveroutput on size 1000000 sys@ora10g> set verify off sys@ora10g> undefine user_name sys@ora10g> declare 2 v_name varchar2(30) := upper('&user_name'); 3 no_grant exception; 4 pragma exception_init( no_grant, -31608 ); 5 begin 6 dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); 7 dbms_output.enable(1000000); 8 dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name)); 9 begin 10 dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name)); 11 exception 12 when no_grant then dbms_output.put_line('-- No system privs granted'); 13 end; 14 begin 15 dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name)); 16 exception 17 when no_grant then dbms_output.put_line('-- No role privs granted'); 18 end; 19 begin 20 dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name)); 21 exception 22 when no_grant then dbms_output.put_line('-- No object privs granted'); 23 end; 24 begin 25 dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name)); 26 exception 27 when no_grant then dbms_output.put_line('-- No tablespace quota specified'); 28 end; 29 dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name )); 30 exception 31 when others then 32 if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists'); 33 else raise; 34 end if; 35 end; 36 / Enter value for user_name: sec CREATE USER "SEC" IDENTIFIED BY VALUES '9EC74A4FC0A9E227' DEFAULT TABLESPACE "TBS_SEC_D" TEMPORARY TABLESPACE "TEMP"; GRANT UNLIMITED TABLESPACE TO "SEC"; GRANT "DBA" TO "SEC"; GRANT READ ON DIRECTORY "DIR1" TO "SEC" WITH GRANT OPTION; GRANT WRITE ON DIRECTORY "DIR1" TO "SEC" WITH GRANT OPTION; GRANT WRITE ON DIRECTORY "dir2" TO "SEC" WITH GRANT OPTION; GRANT READ ON DIRECTORY "dir2" TO "SEC" WITH GRANT OPTION; -- No tablespace quota specified ALTER USER "SEC" DEFAULT ROLE ALL; PL/SQL procedure successfully completed.