Home » RDBMS Server » Security » Virtual Private Database update policy on complex views (Oracle 11g R2)
Virtual Private Database update policy on complex views [message #593902] Wed, 21 August 2013 15:19 Go to next message
mvhegde83
Messages: 3
Registered: August 2013
Location: Stuttgart, Germany
Junior Member
Hello All,

1)I have created a complex view, and created an instead of trigger on this view

example:

create or replace trigger tr_x instead of before update on test_view_name for each row
-------

2) I have created an update policy on this view

DBMS_RLS.ADD_POLICY (
object_schema := schema_name,
object_name := name of the view,
policy_name := ploicy name,
function_schema := func schema name,
policy_function := pkg_test.fn_get_where,
statement_types := 'UPDATE',
update_check := TRUE,
policy_type := dbms_rls.dynamic);

3) function pkg_test.fn_get_where, which is used in the policy function always return 1 = 2, so that update should fail.

4) Now I will issue an update statement on the view test_view_name

update test_view_name set test_col = 1;

but still it updates the records, though update policy returns the where cluse 1 = 2

same where clause for select policy works perfectlr fine.

kindly help me to resolve this issue

[Updated on: Wed, 21 August 2013 15:23]

Report message to a moderator

Re: Virtual Private Database update policy on complex views [message #593925 is a reply to message #593902] Thu, 22 August 2013 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post your Oracle version with 4 decimals.
Can you post a complete test case we can reproduce to see if we see the same thing in our environments.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Virtual Private Database update policy on complex views [message #593949 is a reply to message #593925] Thu, 22 August 2013 04:09 Go to previous messageGo to next message
mvhegde83
Messages: 3
Registered: August 2013
Location: Stuttgart, Germany
Junior Member
Version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
create table employees_vpd
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)  CONSTRAINT     emp_last_name_nn  NOT NULL
    , salary         NUMBER(8,2)
    , department_id  NUMBER(4)
    );        
create table departments_vpd
   ( d_name         VARCHAR2(100)
   , department_id  NUMBER(4))
;      
create table vpd_test(text_msg varchar2(100))
;
insert into departments_vpd values('HR', 10)
;
insert into employees_vpd values(100,'Jim','Clarke',5000,101)
;
create or replace view emp_dept as 
select first_name, last_name, sum(salary) salary, d_name
  from employees_vpd e, departments_vpd d
 where e.department_id = d.department_id
 group by first_name, last_name, d_name
 order by last_name
 ; 
create or replace trigger tr_emp_dept_bur
instead of update on emp_dept 
for each row
declare
begin
  insert into vpd_test values('in update');
end; 
/
create or replace package pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)
    return varchar2;
end pkg_vpd_test;
/
create or replace package body pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)  
   return varchar2 is
  begin
    return '(1 = 2)';
  end;
end pkg_vpd_test;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_S',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'SELECT',
                      update_check    => TRUE);
end;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_U',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'UPDATE',
                      update_check    => TRUE);
end;
/
update emp_dept set first_name = 'Tom' --it is updating one row though policy returns 1 = 2
;
--if I test the predicte using the below query, predicate is getting generated fine, but it is not taking the effect 
select sql_text, predicate, policy, object_name, s.sql_id, last_load_time 
  from v$sqlarea s, v$vpd_policy v
 where hash_value = sql_hash
 order by s.last_load_time desc 
 ; 
select * from vpd_test --test table inside the instead of trigger
;
select * from emp_dept --it works fine, it is not returning any data
; 




[Edit MC: add code tags, do it yourself next time]

[Updated on: Thu, 22 August 2013 04:22]

Report message to a moderator

Re: Virtual Private Database update policy on complex views [message #593951 is a reply to message #593949] Thu, 22 August 2013 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Great! For the moment I have not the time to test it but now anyone who reads this topic can do it.

Regards
Michel
Re: Virtual Private Database update policy on complex views [message #593962 is a reply to message #593949] Thu, 22 August 2013 05:05 Go to previous message
mvhegde83
Messages: 3
Registered: August 2013
Location: Stuttgart, Germany
Junior Member
--small correction when inserting the data
Version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
create table employees_vpd
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)  CONSTRAINT     emp_last_name_nn  NOT NULL
    , salary         NUMBER(8,2)
    , department_id  NUMBER(4)
    );        
create table departments_vpd
   ( d_name         VARCHAR2(100)
   , department_id  NUMBER(4))
;      
create table vpd_test(text_msg varchar2(100))
;
insert into departments_vpd values('HR', 10)
;
insert into employees_vpd values(100,'Jim','Clarke',5000,10)
;
create or replace view emp_dept as 
select first_name, last_name, sum(salary) salary, d_name
  from employees_vpd e, departments_vpd d
 where e.department_id = d.department_id
 group by first_name, last_name, d_name
 order by last_name
 ; 
create or replace trigger tr_emp_dept_bur
instead of update on emp_dept 
for each row
declare
begin
  insert into vpd_test values('in update');
end; 
/
create or replace package pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)
    return varchar2;
end pkg_vpd_test;
/
create or replace package body pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)  
   return varchar2 is
  begin
    return '(1 = 2)';
  end;
end pkg_vpd_test;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_S',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'SELECT',
                      update_check    => TRUE);
end;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_U',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'UPDATE',
                      update_check    => TRUE);
end;
/
update emp_dept set first_name = 'Tom' --it is updating one row though policy returns 1 = 2
;
--if I test the predicte using the below query, predicate is getting generated fine, but it is not taking the effect 
select sql_text, predicate, policy, object_name, s.sql_id, last_load_time 
  from v$sqlarea s, v$vpd_policy v
 where hash_value = sql_hash
 order by s.last_load_time desc 
 ; 
select * from vpd_test --test table inside the instead of trigger
;
select * from emp_dept --it works fine, it is not returning any data
; 
Previous Topic: user_history$ view
Next Topic: ORA-01720: grant option does not exist for '%%'
Goto Forum:
  


Current Time: Fri Aug 22 17:01:50 CDT 2014

Total time taken to generate the page: 0.71132 seconds