【DB筆試面試544】在Oracle中，用戶的許可權分為哪幾類？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，用戶的許可權分為哪幾類？
♣
答案部分
許可權是指執行特定類型SQL命令或訪問其他用戶對象的權利，包括系統許可權（System Privilege）、對象許可權（Object Privilege）、角色許可權（Role Privilege）、列許可權。
（一）系統許可權
系統許可權是指執行特定類型SQL命令的權利。它用於控制用戶可以執行的一個或是一組資料庫操作。通過查詢系統表SYSTEM_PRIVILEGE_MAP可以獲取所有系統許可權，查詢視圖DBA_SYS_PRIVS可以獲取每個用戶擁有的系統許可權。以下示例顯示了SCOTT用戶具有的系統許可權：
SYS@lhrdb> SELECT * FROM DBA_SYS_PRIVS D WHERE D.GRANTEE='SCOTT'; GRANTEE PRIVILEGE ADMIN_OPTION ----------- ----------------------- --------------- SCOTT UNLIMITED TABLESPACE NO
一般情況，授予系統許可權是由DBA完成的，如果用其他用戶來授予系統許可權，那麼要求該用戶必須具有GRANT ANY PRIVILEGE的系統許可權。在授予系統許可權時，可以帶有WITH ADMIN OPTION選項，這樣，被授予許可權的用戶或角色還可以將該系統許可權授予其他的用戶或角色。
（二）對象許可權
對象許可權指訪問其它用戶（SCHEMA）對象的權利，用戶可以直接訪問自己用戶的對象，但是如果要訪問別的用戶的對象，那麼必須具有該對象的相應許可權。常用的對象許可權有：ALTER、DELETE、SELECT、INSERT、UPDATE等。通過數據欄位視圖DBA_TAB_PRIVS可以查詢用戶或角色所具有的對象許可權。下列示例列舉出了所有的對象許可權的種類：
SYS@lhrdb> SELECT DISTINCT PRIVILEGE FROM DBA_TAB_PRIVS; PRIVILEGE ---------------------------------------- EXECUTE FLASHBACK DEQUEUE ON COMMIT REFRESH ALTER DELETE UPDATE DEBUG QUERY REWRITE SELECT READ USE WRITE INSERT INDEX REFERENCES MERGE VIEW 17 rows selected.
可以單獨賦權，也可以多個許可權用逗號隔開：
GRANT SELECT ON EMP TO LHR; GRANT UPDATE ON EMP TO LHR; GRANT DELETE ON EMP TO LHR; GRANT UPDATE,DELETE,INSERT ON EMP TO LHR; 也可以使用ALL來賦權： GRANT ALL ON EMP TO LHR; 需要注意的是，系統許可權和對象許可權不能放在一個GRANT語句中進行授權，分開單獨授權即可，否則會報錯： SYS@oradg11g > GRANT CREATE TABLE, SELECT ON oe.Orders TO lhr; GRANT CREATE TABLE, SELECT ON oe.Orders TO lhr * ERROR at line 1: ORA-00990: missing or invalid privilege SYS@oradg11g > GRANT SELECT ON oe.Orders to lhr; Grant succeeded. SYS@oradg11g > GRANT CREATE TABLE to lhr; Grant succeeded.
（三）列許可權
可以基於列進行賦權，只不過只能賦予INSERT、REFERENCES和UPDATE的許可權，舉例如下：
GRANT UPDATE (ENAME,SAL) ON EMP TO LHR;
基於列的許可權可以查詢DBA_COL_PRIVS視圖。
（四）角色許可權
角色即用戶許可權的集合，可以對用戶直接賦予某一個角色，這樣，該用戶就擁有了角色的所有許可權。如果想查詢角色所擁有的許可權，那麼可以通過視圖DBA_SYS_PRIVS來查詢；如果想查詢某個用戶擁有哪些角色，那麼可以通過視圖DBA_ROLE_PRIVS來查詢。Oracle預定義角色請參考：【3.1.2 Oracle有哪些預定義角色？】。
角色許可權需要注意默認角色（Default Role）的問題。一個用戶一旦被賦予某個角色之後，其默認角色為YES，即角色許可權處於激活狀態，該角色擁有的許可權是生效的；若默認角色為NO，則代表目標用戶被賦予了某個角色，但是該角色擁有的許可權並沒有生效。可以使用如下SQL語句讓角色生效：
ALTER USER LHRTEST DEFAULT ROLE CONNECT;--設置LHRTEST用戶的默認角色為CONNECT，讓該用戶其它角色失效 SET ROLE RESOURCE; --讓當前用戶的RESOURCE角色生效 ALTER USER LHRTEST DEFAULT ROLE ALL;--所有角色生效
有關許可權角色的更多內容可以參考：http://blog.itpub.net/26736162/viewspace-2140769/。
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
