程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 查看oracle用戶所有權限,並獲取授權的DDL

查看oracle用戶所有權限,並獲取授權的DDL

編輯:Oracle教程

1. 查看指定用戶擁有的所有權限(系統權限、對象權限、角色),分別是通過dba_sys_privs,dba_tab_privs,dba_role_privs三個視圖來查看。

腳本:query_user_privs.sql

2. 獲得指定用戶所賦權限的DDL語句,有兩種方式:

1).利用1中query_user_privs.sql腳本,在select 後加入grant to等關鍵字,使得查詢輸出的結果為完整的DDL語句。

腳本:get_ddl_privs_dic.sql

2)使用oracle提供的包:

dbms_metadata.get_ddl('USER','&&uname') 獲取創建user的DDL語句

dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') 獲取指定用戶賦予系統權限的DDL語句

dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname‘) 獲取指定用戶賦予角色的DDL語句

dbms_metadata.get_granted_ddl('OBJECT_GRANT,'&&uname') 獲取指定用戶賦予對象權限的DDL語句

腳本:get_ddl_privs_pac.sql

3. 腳本

3.1 query_user_privs.sql

 

set echo off  
set verify off  
set pagesize 999  
set linesize 200  
col type format a20  
SELECT *  
  FROM (SELECT a.username, 'ROLE' AS TYPE,   
               b.granted_role || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted  
          FROM sys.dba_users a, sys.dba_role_privs b  
         WHERE a.username = b.grantee  
        UNION  
        SELECT a.username, 'SysPrivs' AS TYPE,   
               b.privilege || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted  
          FROM sys.dba_users a, sys.dba_sys_privs b  
         WHERE a.username = b.grantee  
        UNION  
        SELECT a.username,  
               'ObjPrivs' AS TYPE,  
               b.owner || '.' || b.table_name || ' - ' || b.privilege ||  
                DECODE (grantable, 'YES', ' (With Grant Option)', NULL) what_granted  
          FROM sys.dba_users a, sys.dba_tab_privs b  
         WHERE a.username = b.grantee  
        ORDER BY 1)  
 WHERE username = upper('&input_username');  

 

3.2 get_ddl_privs_dic.sql

 

clear screen  
  
accept uname prompt 'Enter User Name : '  
accept outfile prompt  ' Output filename : '  
  
col username noprint  
col lne newline  
  
set heading off pagesize 0 verify off feedback off linesize 180  
  
spool &&outfile..gen  
  
prompt   
SELECT username, 'CREATE USER '||username||' '||  
       DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',  
              'IDENTIFIED BY VALUES '''||password||''' ') lne,  
       'DEFAULT TABLESPACE '||default_tablespace lne,  
       'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne  
  FROM DBA_USERS  
 WHERE USERNAME LIKE UPPER('%&&uname%')  
    OR UPPER('&&uname') IS NULL  
 ORDER BY USERNAME;  
  
SELECT username, 'ALTER USER '||username||' QUOTA '||  
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')  
       ||' ON '||tablespace_name||';' lne  
  FROM DBA_TS_QUOTAS  
 WHERE USERNAME LIKE UPPER('%&&uname%')  
    OR UPPER('&&uname') IS NULL  
 ORDER BY USERNAME;  
  
col grantee noprint  
  
select grantee, granted_role granted_priv,  
       'GRANT '||granted_role||' to '||grantee||  
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')  
  from dba_role_privs  
 where grantee like upper('%&&uname%')  
         UNION  
select grantee, privilege granted_priv,  
       'GRANT '||privilege||' to '||grantee||  
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')  
  from dba_sys_privs  
 where grantee like upper('%&&uname%')  
 order by 1, 2;  
  
spool off  

 

3.3 get_ddl_privs_pac.sql

 

clear screen  
   
accept uname prompt 'Enter User Name : '  
accept outfile prompt  ' Output filename : '  
   
spool &&outfile..gen  
   
SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180  
   
SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;  
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') from dual;  
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname') from dual;  
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&uname') from dual;  
   
spool off  

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved