Implement read lock with VPD by yangtingkun

Source: http://yangtingkun.itpub.net/post/468/462908

看到yangtingkun提到用VPD实现读锁，感觉很有意思，尝试了下--

 

-- create test table
create table test_read_lock(name varchar2(10));

-- create policy function
create or replace function read_lock_policy(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
return varchar2
as
  l_obj_id pls_integer;
  l_lock_return pls_integer;
begin
  select object_id into l_obj_id
  from user_objects
  where object_name = 'TEST_READ_LOCK';
  
  l_lock_return := dbms_lock.request(l_obj_id, 6, 60, true);
  
  return l_lock_return || ' in (0, 4)';
end;


-- Add policy
exec dbms_rls.drop_policy(user, 'TEST_READ_LOCK', 'my_test_policy');
exec dbms_rls.ADD_POLICY(user, 'TEST_READ_LOCK', 'my_test_policy', user, 'READ_LOCK_POLICY');


-- Test
-- Session 1
select * from TEST_READ_LOCK;

-- Session 2
select * from TEST_READ_LOCK;
hang until sesion 1 commit or rollback