Oracle數據庫中有列權限(Column Privileges)嗎? 相信不少老司機都會對這個權限感到陌生. 無它,只是用得少而已,而且Oracle關于列權限的控制有點奇怪.甚至有點奇葩,沒有詆毀的意思,而是就事論事. 下面請見淺析, 僅代表個人觀點.

首先,這個權限并不像你想象的那樣.可以控制列的查詢(只對某些列授予查詢權限給其它用戶), 這個是不存在的.而像SQL Server數據庫是有這項功能的.

SQL Server可以給用戶授予相關列的查詢權限（SELECT）, 更多關于SQL Server列權限的介紹,可以查看我的這篇博客SQL Server 關于列的權限控制

GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON  [HumanResources].[Employee] TO [UserA]

但是Oracle數據庫沒有這個功能. 如下所示

SQL> SHOW USER;
USER is "TEST"
SQL> GRANT SELECT(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1;
GRANT SELECT(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1
            *
ERROR at line 1:
ORA-00969: missing ON keyword

SQL> 

其實并不是我的SQL語法有問題,而是實際上(到目前為止),Oracle就沒有這個功能,官方文檔關于列權限(Column Privileges)的闡述如下所示

column

Specify the table or view column on which privileges are to be granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, then the grantee has the specified privilege on all columns in the table or view.

For information on existing column object grants, query the USER_, ALL_, or DBA_COL_PRIVS data dictionary view.

Oracle數據庫的列權限(Column Privileges)只能對列授予INSERT,UPDATE,REFERENCES這三種權限.也就是說不能對表/視圖的相關列進行SELECT授權. 下面簡單演示一下

用戶TEST授予用戶TEST1對表T2的列(OBJECT_ID, OBJECT_NAME)更新的權限

SQL> 
SQL> SHOW USER;
USER is "TEST"
SQL> GRANT UPDATE(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1;

Grant succeeded.

SQL> 

用戶TEST授予用戶TEST1對表T2的列(OBJECT_ID, OBJECT_NAME)插入的的權限

SQL> SHOW USER;
USER is "TEST"
SQL> GRANT INSERT(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1;

Grant succeeded.

SQL> 

那么要如何回收這些授予的權限呢?

SQL> SHOW USER;
USER is "TEST"
SQL> REVOKE UPDATE(OBJECT_ID, OBJECT_NAME) ON TEST.T2 FROM TEST1;
REVOKE UPDATE(OBJECT_ID, OBJECT_NAME) ON TEST.T2 FROM TEST1
             *
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by
column

關于錯誤ORA-01750的解釋如下所示:

$ oerr ora 01750
01750, 00000, "UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column"
// *Cause:     Although it was possible to GRANT update privileges on a
//             column-by-column basis, it was only possible to REVOKE them for
//             an entire table.
// *Action:    Do not identify specific columns. To revoke update
//             privileges for certain columns, use REVOKE for the entire
//             table and GRANT the user privileges for specific columns.

上面解釋得非常清楚了. 也就是說關于列權限(UPDATE),只能對整個表進行回收.

SQL> SHOW USER;
USER is "SYS"
SQL> REVOKE UPDATE ON TEST.T2 FROM  TEST1;

Revoke succeeded.
SQL> REVOKE INSERT ON TEST.T2 FROM TEST1;

Revoke succeeded.

SQL> 

總結

關于Oracle數據庫中有列權限(Column Privileges),沒有授予指定列的查詢權限功能, 其實這個才是列權限最有價值的功能, 沒有這個功能,那么只能通過視圖來間接實現這個功能了. 另外, 列權限的權限回收也是透漏著別扭. 讓人有點懵.