ORACLE VPD AND FGA

原創 轉載請註明出處

VPD 虛擬專用資料庫，主要用來控制某個使用者對特定列的一個訪問許可權，比如我是李三，我在檢視員工資訊表的時候，只要檢視了薪水列，就只顯示我李三的這一行，因為其他員工的薪水對我是保密了。使用包dbms_rls來管理列：只要使用者檢視了薪水列只返回這個使用者相關的列。

 1、表結構如下： SQL> select * from pp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17    6000.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

2、建立函式

 create or replace function emp_pf
 (owner varchar2,objname varchar2)
 return varchar2
 is
where_clause varchar2(2000);
begin
 where_clause:='ename=sys_context(''userenv'',''session_user'')';
 return where_clause;
 end;

Function created 這個函式主要返回是當前會話的使用者名稱，用於判斷他一旦訪問了薪水列，哪一列他能夠檢視。

3、建立策略

SQL> begin
  2    dbms_rls.add_policy(object_name => 'pp',policy_name => 'TEST',function_schema => 'pp',policy_function => 'emp_pf',sec_relevant_cols => 'SAL',sec_relevant_cols_opt => dbms_rls.all_rows);
  3  end;
  4  /

4、登陸SMITH使用者檢視，可以看到這裡的SAL列除了SMITH使用者自己的SAL是有值的其他都是NULL，當然我這裡使用了dbms_rls.all_rows顯示所有列，策略列其他值放回為NULL。也可以不使用那樣的話只會返回1列就是SAL有值的那一列。

SQL> select * from pp.pp;
 
EMPNO ENAME      JOB         MGR HIREDATE           SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- ---------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17        6000               20
 7499 ALLEN      SALESMAN   7698 1981-2-20                 300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22                 500.00     30
 7566 JONES      MANAGER    7839 1981-4-2                             20
 7654 MARTIN     SALESMAN   7698 1981-9-28                1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1                             30
 7782 CLARK      MANAGER    7839 1981-6-9                             10
 7788 SCOTT      ANALYST    7566 1987-4-19                            20
 7839 KING       PRESIDENT       1981-11-17                           10
 7844 TURNER     SALESMAN   7698 1981-9-8                    0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23                            20
 7900 JAMES      CLERK      7698 1981-12-3                            30
 7902 FORD       ANALYST    7566 1981-12-3                            20
 7934 MILLER     CLERK      7782 1982-1-23                            10

5、刪除

SQL> execute dbms_rls.drop_policy(object_name => 'pp',policy_name=> 'TEST');

ORACLE 10G中VPD的策略因為要消耗較多的資源，所以引入了SGA快取，所以有了5中不同型別的策略

1、動態

2、靜態

3、共享-靜態

4、上下文敏感

5、共享-上下文敏感

本人也不太熟悉，所以這裡提一下。需要的朋友自己研究下。

FGA 細粒度審計，ORACLE其實提供了審計功能，但是為了支援更加複雜的審計情況引入了FGA，比如我只想審計誰修改了員工資訊表中的薪水列，就可以考慮使用FGA來實現。使用包dbms_fga來管理列，我想審計PP表中對薪水列有關的且薪水列大於5000的SELECT UPDATE會話資訊

1、檢視錶結構

SQL> select * from pp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17    6000.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

2、建立策略

execute dbms_fga.add_policy(policy_name => 'FGA_TEST',object_name => 'PP',audit_column=> 'SAL',statement_types=> 'SELECT,UPDATE',audit_condition => 'SAL>5000');

3、檢視結果

SQL> select count(*) from pp where sal>5000;

COUNT(*)

----------

0

SQL> select count(*) from dba_fga_audit_trail;

COUNT(*)

----------

0

沒有涉及到大於5000的現在進行UPDATE，SELECT

SQL> update pp set SAL=6000 where ENAME='SMITH';

1 row updated

SQL> commit;

Commit complete

SQL> select * from pp where ENAME='SMITH';

 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 6000.00 20

再次檢視結果

SQL> select count(*) from dba_fga_audit_trail;

COUNT(*)

----------

2

SQL> select SESSION_ID,DB_USER,OS_USER,SQL_TEXT  from dba_fga_audit_trail;
 
SESSION_ID DB_USER                        OS_USER                                                                          SQL_TEXT
---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
     68491 PP                             gaopeng                                                                         
                                                                                                                           update pp set SAL=6000 where ENAME='SMITH'
 
     68491 PP                             gaopeng                                                                         
                                                                                                                           select * from pp where ENAME='SMITH'
                                                                       

不錯吧。其實有時候還是很有用的，當然這兩個包的選項都很多，我沒學習完。這裡只是給出了一些列子。