利用DBMS_METADATA包獲取許可權資訊(轉)

Oracle的9i增加了DBMS_METADATA包，從此在需要得到物件的建立指令碼時，不再需要通過查詢多張系統檢視去自己拼湊結果了。只需要呼叫這個包中的GET_DDL過程就可以輕鬆的獲取物件的建立腳步。

不過很多人對DBMS_METADATA包的瞭解僅限與此。當需要獲取使用者的許可權時，往往還是採用通過到資料字典中讀取的方式，其實DBMS_METADATA包本身就支援獲取許可權資訊。

DBMS_METADA他的GET_GRANTED_DDL過程可以用於獲取使用者的授權資訊。Oracle支援的授權相關資訊型別包括：OBJECT_GRANT、SYSTEM_GRANT、ROLE_GRANT、DEFAULT_ROLE、TABLESPACE_QUOTA和PROXY。

由於使用十分簡單，這裡給一個簡單的例子，就不詳細描述了：

SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE YANGTK;

使用者已建立

SQL> GRANT CONNECT TO TEST;

授權成功。

SQL> GRANT SELECT ANY TABLE TO TEST;

授權成功。

SQL> GRANT INSERT, UPDATE ON T TO TEST;

授權成功。

SQL> ALTER USER TEST QUOTA 10M ON YANGTK;

使用者已更改。

SQL> CREATE ROLE R1;

角色已建立

SQL> GRANT R1 TO TEST;

授權成功。

SQL> ALTER USER TEST DEFAULT ROLE R1;

使用者已更改。

SQL> ALTER USER TEST GRANT CONNECT THROUGH YANGTK;

使用者已更改。

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TEST')
----------------------------------------------------------------------

GRANT UPDATE ON "YANGTK"."T" TO "TEST"

GRANT INSERT ON "YANGTK"."T" TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST')
----------------------------------------------------------------------

GRANT SELECT ANY TABLE TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TEST')
----------------------------------------------------------------------

GRANT "CONNECT" TO "TEST"

GRANT "R1" TO "TEST"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" DEFAULT ROLE "R1"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" QUOTA 640 ON "YANGTK"

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('PROXY', 'TEST') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('PROXY','TEST')
----------------------------------------------------------------------

ALTER USER "TEST" GRANT CONNECT THROUGH "YANGTK"

需要注意的是，如果指定查詢的授權不存在，並不是簡單的返回未選定行，而是還會顯示錯誤資訊：

SQL> REVOKE SELECT ANY TABLE FROM TEST;

撤銷成功。

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'TEST') FROM DUAL;
ERROR:
ORA-31608: 找不到型別為 SYSTEM_GRANT 的指定物件
ORA-06512: 在"SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在"SYS.DBMS_METADATA", line 631
ORA-06512: 在"SYS.DBMS_METADATA", line 1339
ORA-06512: 在line 1

未選定行

最後還要說明一點，DBMS_METADA他的GET_GRANTED_DDL不會顯示SYSDBA和SYSOPER許可權。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242364/，如需轉載，請註明出處，否則將追究法律責任。