ORACLE VPD ROW-LEVEL MARKING..

VPD Virtual Private Database
ORACLE安全方面的控制，例如VIEW ,但是比view控制更灵活。
CREATE VIEW VXX AS SELECT * FROM TXX WHERE XX
比如有个人员工资表，只想让HR的经理看到所有记录，其它部门经理只能看到自己部门的，先不讨论业务逻辑的合理的，目的就是同一张表让不同人看到不同的结果。
下面看我的实验
环境：表都是在HR SCHEMA,HRMAN HR经理，SYSDEPTMAN系统部经理

system@NCME>create user hr identified by hr;
User created.
system@NCME>grant connect,resource to hr;
Grant succeeded.
system@NCME>create user hrman identified by hrman;
User created.
system@NCME>grant connect,resource to hrman;
Grant succeeded.
system@NCME>create user sysdeptman identified by sysdeptman;
User created.
system@NCME>grant connect,resource to sysdeptman;
Grant succeeded.
sys@NCME>select * from hr.dept;
        ID NAME                 DEPTNAME
---------- -------------------- --------------------
         1 bkdcici              HR
         2 rgeankq              HR
         3 zkbstla              HR
         4 oxtzdbq              HR
         5 yctslvq              HR
         6 htbhqff              SYS
         7 beofrsy              SYS
         8 ixbmdyd              SYS
         9 ixtzghd              SYS
        10 sysdeptman           SYS
        11 hrman                HR
11 rows selected.
sys@NCME>select * from hr.emp;
        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131
        11 hrman                     10000 138
11 rows selected.
system@NCME>conn hr/hr
Connected.
hr@NCME>grant select on emp to hrman;
Grant succeeded.
hr@NCME>grant select on emp to sysdeptman;
Grant succeeded.
hr@NCME>grant select on hr.dept to public;
Grant succeeded.

创建APPLICATION CONTEXT
sysdeptman@NCME>conn system/oracle
Connected.
system@NCME>grant create any context,create public synonym to hr;
Grant succeeded.
system@NCME>conn hr/hr
Connected.
hr@NCME>create or replace package context_pkg 
  2  is
  3   procedure set_context;
  4  end;
  5  /
Package created.
hr@NCME>create or replace package body context_pkg
  2  is
  3  procedure set_context
  4  is
  5  v_user varchar2(30);
  6  v_dept varchar2(30);
  7  begin
  8  v_user:=sys_context('USERENV','SESSION_USER');
  9  dbms_session.set_context('VPD_SEC','CUR_USER',v_user);
 10  begin
select deptname into v_dept from dept where upper(name)=v_user;
 12  dbms_session.set_context('VPD_SEC','CUR_DEPT',v_dept);
 13  exception
 14  when NO_DATA_FOUND then
 15  dbms_session.set_context('VPD_SEC','CUR_DEPT','ERR');
 16  end;
 17  end;
 18  end;
 19  /
Package body created.
hr@NCME>exec context_pkg.set_context;
BEGIN context_pkg.set_context; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "HR.CONTEXT_PKG", line 8
ORA-06512: at line 1
hr@NCME>create context VPD_SEC using context_pkg;
Context created.
hr@NCME>exec context_pkg.set_context;
PL/SQL procedure successfully completed.
hr@NCME>COL VA FOR A20
hr@NCME>select sys_context('VPD_SEC','CUR_USER') VA FROM DUAL;
VA
--------------------
HR
hr@NCME>create public synonym CONTEXT_PKG for CONTEXT_PKG;
Synonym created.
hr@NCME>grant execute on context_pkg to public;
Grant succeeded.
hr@NCME>conn hrman/hrman
Connected.
hrman@NCME>exec context_pkg.set_context;
PL/SQL procedure successfully completed.
hrman@NCME>select sys_context('VPD_SEC','CUR_DEPT') VA FROM DUAL;
VA
--------------------
HR

10G语法
DBMS_SESSION.SET_CONTEXT (
   namespace VARCHAR2,
   attribute VARCHAR2,
   value     VARCHAR2,
   username  VARCHAR2,
   client_id VARCHAR2 );
Parameters

namespace				The namespace of the application context to be set, limited to 30 bytes.
attribute 			The attribute of the application context to be set, limited to 30 bytes.
value  					The value of the application context to be set, limited to 4 kilobytes.
username        The database username attribute of the application context.Default: NULL
client_id       The application-specific client_id attribute of the application context (64-byte maximum).default: NULL
NOTE:
can only be called within the package to which it belongs, If you try to execute DBMS_SESSION.SET_CONTEXT ,
you will get an error, as shown here: ORA-01031: insufficient privileges
SET_CONTEXT的NAMSPACE命名空间的值必须和context NAME一致否则也是报错ORA-01031: insufficient privileges
context的创建是USING 指定一个PACKAGE OR PROCEDURE;

查询Application context
select * from dba_context;


Create Security Policies
hr@NCME>create or replace package sec_hr is
  2  function emp_select(owner varchar2,objname varchar2)
  3  return varchar2;
  4  end;
  5  /
hr@NCME>create or replace package body sec_hr
 is
 function emp_select(owner varchar2,objname varchar2)
 return varchar2
 is
 wherecase varchar2(2000);
 begin
 wherecase :='0=1';
 if(sys_context('VPD_SEC','CUR_USER')='HRMAN') then
  wherecase:=null;
 elsif (sys_context('VPD_SEC','CUR_DEPT')!='ERR') then
  wherecase:='id in(select id from dept where deptname=sys_context(''VPD_SEC'',''CUR_DEPT''))';
 end if;
 return wherecase;
 end;
 end;
 /
Package body created.
hr@NCME>grant execute on sec_hr to public;
Grant succeeded.
hr@NCME>create public synonym sec_hr for sec_hr;
Synonym created.

system@NCME>begin
 dbms_rls.add_policy('HR','EMP','MY_EMP_SELECT_POLICY',
                      'HR','SEC_HR.EMP_SELECT',
                       'SELECT',TRUE,TRUE);
  END;

PL/SQL procedure successfully completed.
system@NCME>conn hr/hr
Connected.
hr@NCME>select * from emp;
no rows selected
hr@NCME>conn hrman/hrman
Connected.
hrman@NCME>exec context_pkg.set_context
PL/SQL procedure successfully completed.

hrman@NCME>select * from hr.emp;
        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131
        11 hrman                     10000 138
11 rows selected.
hrman@NCME>conn sysdeptman/
Enter password: 
Connected.
sysdeptman@NCME>select * from hr.emp;
no rows selected
sysdeptman@NCME>exec context_pkg.set_context
PL/SQL procedure successfully completed.
sysdeptman@NCME>select * from hr.emp;
        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131

ADD_POLICY Procedure
This procedure adds a fine-grained access control policy to a table, view, or synonym.
DBMS_RLS.ADD_POLICY (
   object_schema            IN VARCHAR2 NULL,
   object_name              IN VARCHAR2,
   policy_name              IN VARCHAR2,
   function_schema          IN VARCHAR2 NULL,
   policy_function          IN VARCHAR2,
   statement_types          IN VARCHAR2 NULL,
   update_check             IN BOOLEAN  FALSE,
   enable                   IN BOOLEAN  TRUE,
   static_policy            IN BOOLEAN  FALSE,
   policy_type              IN BINARY_INTEGER NULL,
   long_predicate           IN BOOLEAN  FALSE,
   sec_relevant_cols        IN VARCHAR2,
   sec_relevant_cols_opt    IN BINARY_INTEGER NULL);

如果不想每次设置exec context_pkg.set_context，建立一个数据库级trigger,as sysdba user
sysdeptman@NCME>conn / as sysdba
Connected.
sys@NCME>create or replace trigger tri_hr_sec
  2  after logon on database
  3  begin
  4  hr.context_pkg.set_context;
  5  end;
  6  /
Trigger created.
sys@NCME>conn sysdeptman/sysdeptman
Connected.
sysdeptman@NCME>select * from hr.emp;
        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131
        
查询代理策略
dba|all|user_policies;
如果因策略使用的函数或包补删除，再查询就会出现
ORA-28110: policy function or package HR.SEC_HR has error
删除代理策略
hr@NCME>select * from emp;
no rows selected
hr@NCME>drop package SEC_HR;
Package dropped.
hr@NCME>select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-28110: policy function or package HR.SEC_HR has error

hr@NCME>exec dbms_rls.drop_policy('HR','EMP','MY_EMP_SELECT_POLICY');
PL/SQL procedure successfully completed.
hr@NCME>select * from emp;
        ID NAME                        SAL TEL
---------- -------------------- ---------- -----------
         1 bkdcici                 4656.82 138
         2 rgeankq                 4394.34 138
         3 zkbstla                 3139.03 138
         4 oxtzdbq                 3590.45 138
         5 yctslvq                 3909.43 138
         6 htbhqff                 4234.96 131
         7 beofrsy                 4237.33 131
         8 ixbmdyd                 4265.09 131
         9 ixtzghd                 3533.51 131
        10 sysdeptman                10000 131
        11 hrman                     10000 138
11 rows selected.
复制

note: sys用户不受限制。