The purpsoe of this workshop is to demonstrate the use of RLS ( row level security, aka virtual private database (VPD), find-grained access )
Scenario - 1
Descriptsion:
Use RLS to modify the following query:
select * from emp;
transparently to the query:
select * from emp where sal <= 1500;
Steps:
1. Create a function authorized_emps
Logon as SCOTT
Script: f_authorized_emps.sql
2. set up an RLS policy
Logon as SCOTT
Script: add_policy.sql
3. Test
a. select statement
select * from emp;
Notes: only 7 rows returned not 14 rows
b. update statement
scott@TEST10GR> update emp set sal=1600;
7 rows updated.
c. delete statement
scott@TEST10GR> delete from emp;
0 rows deleted.
Notes: becuase as a result of (b) all the row have sal > 1500, so in (c)
we see 0 rows deleted. If we are not aware of RLS, this can be confuse.
d. issue: rollback
4. cleaup
script: cleanup_scnr1.sql
Scenario - 2: Using update_check in a policy
Steps:
1. Create a function authorized_emps
Logon as SCOTT
Script: f_authorized_emps.sql
2. Set up an RLS policy
Logon as SCOTT
Script: add_policy_uc.sql
3. Test with update
scott@TEST10GR> update emp set sal=1600;
update emp set sal=1600
*
ERROR at line 1:
ORA-28115: policy with check option violation
Notes:
The ORA-28115 error is raised because the policy now prevents any
updates to the value of columns in a row that will cause a change
in the visibility of those rows with the specified predicate.
4. Clean up
Script: cleanup_scnr1.sql (note: cleanup_scnr2.sql not exist)
Scenario - 3: Problems with static policys
Assume that the predicate depends on a changing value
Steps:
1. Setup the test
Script: setup_scnr3.sql
2. Set up an RLS policy
Logon as SCOTT
Script: add_policy_scnr3.sql
3. Test with dynamic policy (by default)
(a) Issue following statement once:
select count(*) from emp;
returen 14
(b) check trigger_fire
scott@TEST10GR> select * from trigger_fire;
VAL
----------
3
Notes: Because the policy function was called twice: once during
the parse phase and once during the execution phase,
the value was incremented by 2 from 1.
(c) select count for 5 times:
scott@TEST10GR> select count(*) from emp;
COUNT(*)
----------
14
scott@TEST10GR> select count(*) from emp;
COUNT(*)
----------
0
scott@TEST10GR> select count(*) from emp;
COUNT(*)
----------
0
scott@TEST10GR> select count(*) from emp;
COUNT(*)
----------
0
scott@TEST10GR> select count(*) from emp;
COUNT(*)
----------
7
(d) check trigger_fire
scott@TEST10GR> select * from trigger_fire;
VAL
----------
13
Notes: it can be seen for each selec count statement, the function gets
called twice
4. Test with static policy
(a) Run script: reset_scnr3.sql
(b) Run following statment multiple times:
select count(*) from emp;
Notes: the return number always the same.
(c) check trigger_fire
scott@TEST10GR> select * from trigger_fire;
VAL
----------
2
Notes: By declaring a policy as static, we have effectively instructed
the policy function to execute only once, and the policy to reuse
the predicate originally created, even though the predicate might
have changed in the course of time. This behavior might produce
unexpected results in your application, so you should use static
policies with great caution.
Scenario - 4 : Dynamic policy
-----------------------------
Description: A user can only see the infomation in his own department
in the emp table
Implement:
Create a special schema named RLSOWNER, which creates all the
RLS policies and owns all the policy functions. Only this user,
and no others, is granted the EXEMPT ACCESS POLICY system privilege.
Test steps:
1. Setup
scripts setup_scnr4.sql
2. test
script: test_scnr4.sql
Logon as differenct users, issuem the same statement:
select ename, sal, deptno from emp;
Output:
--- Logon as Martin
Connected.
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
BLAKE 2850 30
TURNER 1500 30
JAMES 950 30
6 rows selected.
--- Logon as Ford
Connected.
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 840 20
JONES 2975 20
SCOTT 3000 20
ADAMS 1100 20
FORD 3000 20
--- Logon as Miller
Connected.
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING 5000 10
MILLER 1300 10
--- Logon as Noexist
Connected.
select ename, sal, deptno from emp
*
ERROR at line 1:
ORA-28112: failed to execute policy function
Notes: if a user is not exist in the scott.emp table we saw ORA-28112 error
and we can find a trace file under user_dump_dest. The policy function
may be modified to give a warning and prevent the trace file in this case
Solution: we can create a role called EMP_SELECT, we grant select on
emp to this role, then only grant this role to those users in the emp table.
3. Cleanup
script: cleanup_scnr4.sql
Appendix A - Scripts
***** Script: add_policy.sql **************************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT'
);
END;
/
***** Script: add_policy_uc.sql ***************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
);
END;
/
***** Script: add_policy_scnr3.sql ************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
static_policy => FALSE
);
END;
/
***** Script: cleanup_scnr1.sql ***************************************
BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY') ;
END;
/
drop function authorized_emps
/
***** Script: cleanup_scnr3.sql ****************************************
BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY') ;
END;
/
drop function authorized_emps
/
drop table trigger_fire
/
**** Script: cleanup_scnr4.sql ****************************************
connect / as sysdba
drop user rlsowner cascade;
drop user martin cascade;
drop user miller cascade;
drop user ford cascade;
drop user noexist cascade;
drop public synonym emp;
***** Script: f_authorized_emps.sql ***********************************
CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
RETURN 'SAL <= 1500';
END;
/
***** Script: reset_scnr3.sql *****************************************
BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY');
END;
/
BEGIN
DBMS_RLS.add_policy (
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE,
static_policy => TRUE
);
END;
/
-- Reset the table TRIGGER_FIRE
UPDATE trigger_fire SET val = 1;
COMMIT;
***** Script: setup_scnr3.sql *****************************************
create table trigger_fire(val number);
insert into trigger_fire values(1);
commit;
CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
l_return_val VARCHAR2 (2000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
l_return_val := 'SAL <= ' TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) * 100;
UPDATE trigger_fire SET val = val + 1;
COMMIT;
RETURN l_return_val;
END;
/
**** Script: setup_scnr4.sql ***************************************
connect / as sysdba
create user rlsowner identified by rlsowner;
grant connect, resource to rlsowner;
grant exempt access policy to rlsowner;
grant execute on dbms_rls to rlsowner;
REM create users appeared in the emp table for testing purpose
create user martin identified by martin;
create user miller identified by miller;
create user ford identified by ford;
create user noexist identified by noexist;
grant connect to martin;
grant connect to miller;
grant connect to ford;
grant connect to noexist;
create public synonym emp for scott.emp;
grant select on emp to public;
connect rlsowner/rlsowner
CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
l_deptno NUMBER;
l_return_val VARCHAR2 (2000);
BEGIN
IF (p_schema_name = USER)
THEN
l_return_val := NULL;
ELSE
SELECT deptno
INTO l_deptno
FROM emp
WHERE ename = USER;
l_return_val := 'DEPTNO = ' l_deptno;
END IF;
RETURN l_return_val;
END;
/
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'RLSOWNER',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
);
END;
/
exit;
**** Script: test_scnr4.sql ****************************************
Prompt --- Logon as Martin
connect martin/martin
select ename, sal, deptno from emp;
Prompt --- Logon as Ford
connect ford/ford
select ename, sal, deptno from emp;
Prompt --- Logon as Miller
connect miller/miller
select ename, sal, deptno from emp;
Prompt --- Logon as Noexist
connect noexist/noexist
select ename, sal, deptno from emp;
