Oracle fine-grained access control (FGAC)

Environment:

Sys @ ORCL> select * from v $ version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

Sys @ ORCL>! Uname-
Linux localhost. localdomain 2.6.18-308. el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

The experiment process is as follows:

Scott @ ORCL> create table tvpd (name varchar2 (20), salary number (8, 2), department_id number (5 ));

Table created.

Scott @ ORCL> insert into tvpd values ('zhang san', 10 );

1 row created.

Scott @ ORCL> insert into tvpd values ('lily', 20 );

1 row created.

Scott @ ORCL> commit;

Commit complete.


Sys @ ORCL> grant connect to zhangsan identified by zhangsan;

Grant succeeded.

Sys @ ORCL> grant select on scott. tvpd to zhangsan;

Grant succeeded.

Sys @ ORCL> grant connect to lisi identified by lisi;

Grant succeeded.

Sys @ ORCL> grant select on scott. tvpd to lisi;

Grant succeeded.

Sys @ ORCL> conn zhangsan/zhangsan
Connected.
Zhangsan @ ORCL> select * from scott. tvpd;

Name salary DEPARTMENT_ID
-------------------------------------------
Zhang San 5000 10
Lili 250 20

Zhangsan @ ORCL> conn scott/tiger
Connected.
Scott @ ORCL> create or replace function func_vpd
(Owner varchar2, objname varchar2)
Return varchar2
Is
V_where_clause varchar2 (2000 );
Begin
V_where_clause: = 'name = initcap (sys_context (''userenv'', ''session _ user ''))';
Return v_where_clause;
End; 2 3 4 5 6 7 8 9
10/

Function created.

Scott @ ORCL> conn/as sysdba
Connected.
Sys @ ORCL> select * from dba_policies where object_owner = 'Scott ';

No rows selected

Sys @ ORCL> BEGIN
Dbms_rls.add_policy (object_schema => 'Scott ',
Object_name => 'tvpd ',
Policy_name => 'Scott _ policy123 ',
Function_schema => 'Scott ',
Policy_function => 'func_vpd ',
Statement_types => 'select ',
Sec_relevant_cols => 'salary ');
END; 2 3 4 5 6 7 8 9
10/

PL/SQL procedure successfully completed.

Sys @ ORCL> select * from dba_policies where object_owner = 'Scott ';

OBJECT_OWNER OBJECT_NAME POLICY_GROUP
------------------------------------------------------------------------------------------
POLICY_NAME PF_OWNER PACKAGE
------------------------------------------------------------------------------------------
Function sel ins upd del idx chk ena sta POLICY_TYPE LON
---------------------------------------------------------------------------------
Scott tvpd SYS_DEFAULT
SCOTT_POLICY123 SCOTT
FUNC_VPD YES NO DYNAMIC NO
Scott @ ORCL> conn zhangsan/zhangsan
Connected.
Zhangsan @ ORCL> select * from scott. tvpd;

No rows selected

Zhangsan @ ORCL> select name from scott. tvpd;

NAME
--------------------
Zhang San
Li Si

In this test, we only refined the control of column salary. If we do not check the salary, we can still see all of it, as shown above.

However, note that sys is still not affected because it has the permission "exempt access policy", which is of the same nature as sysdba.

Zhangsan @ ORCL> conn/as sysdba
Connected.
Sys @ ORCL> grant exempt access policy to zhangsan;

Grant succeeded.

Sys @ ORCL> conn zhangsan/zhangsan
Connected.
Zhangsan @ ORCL> select * from scott. tvpd;

Name salary DEPARTMENT_ID
-------------------------------------------
Zhang San 5000 10
Lili 250 20

At the same time, if a table protected by the policy is dropped and does not enter recyclebin, flashback... to before drop cannot be used.

Zhangsan @ ORCL> conn scott/tiger
Connected.
Scott @ ORCL> show recyclebin
Scott @ ORCL> drop table tvpd;

Table dropped.

Scott @ ORCL> show recyclebin
Scott @ ORCL> flashback table tvpd to before drop;
Flashback table tvpd to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN