oracle列級許可權控制

       客戶有個需求，一張150多個欄位的表，客戶要求只將部分欄位給掃描公司的人看，這個需求用檢視就可以很容易實現，客戶又要求，這些欄位，掃描公司只可以修改其中的個別欄位，我之前還真沒遇到這樣在列級別做許可權控制的需求，做了個實驗，感覺很有意思，記錄下測試過程。

1.   建立測試表並插入點測試資料：

SQL> create table test( id number,table_name varchar2(50),owner varchar2(50),TABLESPACE_NAME varchar2(50));

Table created.

SQL> insert into test select rownum,table_name,owner,TABLESPACE_NAME from dba_tables;

5490 rows created.

SQL> commit;

Commit complete.

2.         建立測試使用者並賦予基本許可權：

SQL> CONN / AS SYSDBA

Connected.

SQL> create user stream identified by stream default tablespace users;

User created.

SQL> grant connect,resource to stream;

Grant succeeded.

3.         賦予測試使用者列級許可權：

SQL> conn auth/auth

Connected.

SQL> grant update (id) on test to stream;

Grant succeeded.

SQL> grant insert (table_name) on test to stream;

Grant succeeded.

SQL>

SQL> grant select on test to stream;

Grant succeeded.

4.         查詢列級許可權設定資訊：

SQL> select GRANTEE,OWNER,TABLE_NAME,COLUMN_NAME,GRANTOR,PRIVILEGE,GRANTABLE from user_col_privs;

GRANTEE   OWNER   TABLE_NAME COLUMN_NAME   GRANTOR   PRIVILEGE   GRA

-------   -----   ---------- -----------   -------   ---------   ---

STREAM    AUTH    TEST       TABLE_NAME    AUTH      INSERT      NO

AUT       AUTH    TEST       ID            AUTH      INSERT      NO

AUT       AUTH    TEST       ID            AUTH      UPDATE      NO

STREAM    AUTH    TEST       ID            AUTH      UPDATE      NO

5.   登陸測試使用者驗證SELECT許可權：

SQL> conn stream/stream

Connected.

SQL> select * from(select * from auth.test order by 1) where rownum<=10;

        ID TABLE_NAME           OWNER      TABLESPACE

---------- -------------------- ---------- ----------

         1 ICOL$                SYS        SYSTEM

         2 IND$                 SYS        SYSTEM

         3 COL$                 SYS        SYSTEM

         4 CLU$                 SYS        SYSTEM

         5 TAB$                 SYS        SYSTEM

         6 LOB$                 SYS        SYSTEM

         7 COLTYPE$             SYS        SYSTEM

         8 SUBCOLTYPE$          SYS        SYSTEM

         9 NTAB$                SYS        SYSTEM

        10 REFCON$              SYS        SYSTEM

10 rows selected.

6.   驗證列級UPDATE許可權控制：

SQL> update auth.test set wner='STREAM'  where id =1;

update auth.test set wner='STREAM'  where id =1

            *

ERROR at line 1:

ORA-01031: insufficient privileges

       可見，不允許修改測試表的OWNER欄位的值，報ORA-01031：許可權不足，由於上文賦予了測試使用者對修改測試表ID欄位的修改許可權，修改ID欄位是可以的。

SQL> update auth.test set id=10 where id=1;

1 row updated.

SQL> rollback;

Rollback complete.

7.   驗證列級INSERT許可權控制：

SQL> insert into auth.test values(1,'stream','stream','users');  

insert into auth.test values(1,'stream','stream','users')

                 *

ERROR at line 1:

ORA-01031: insufficient privileges

       可見，整行插入是不被允許的，也是許可權不夠，由於上文賦予了測試使用者對修改測試表TABLE_NAME欄位的插入許可權，所以插入TABLE_NAME欄位是可以的，但是前提是其他欄位沒有NOT NULL約束。

SQL> insert into auth.test(table_name) values ('stream');

1 row created.

SQL> rollback;

Rollback complete.