[Source] Oracle database column-level permission control

The customer has onedemand, a table with more than 150 fields, the customer asked to only show some of the fields to the people who scanned the company, thisdemandIt can be easily implemented with a view, and the customer requests that these fields, the scanning company can only modify individual fields, I really haven't encountered such a need for permission control at the column level before, I did an experiment, I feel very interesting, record itTestprocess.

1. Create a test table and insert point test data:

　　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. Create a test user and give basic permissions:

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. Grant test users column-level permissions:

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. Query column-level permission setting information:

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. Login test user verifies SELECT permission:

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. Verify column-level UPDATE permission control:

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

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

　　*

ERROR at line 1:

　　ORA-01031: insufficient privileges

It can be seen that the value of the OWNER field of the test table is not allowed to be modified, report ORA-01031: Insufficient permission, since the above gives the test user the permission to modify the ID field of the test table, it is possible to modify the ID field.

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

1 row updated.

SQL> rollback;

Rollback complete.

7. Verify column-level INSERT permission control:

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

Since the above gives the test user the insertion permission to modify the TABLE_NAME field of the test table, it is possible to insert TABLE_NAME field, but only if the other fields do not have NOT SOME constraints.

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

1 row created.

SQL> rollback;

Rollback complete.