Using Row Level Security
The objective is to make sure that one somebody issue a Query, he/she retrieves rows for which he/she has privilege for.
Normally, this is done using views or application logic.
Since views are created using Standard SQL language, it can be difficult to embed logic if the criteria are a bit complex. Application Logic can enforce filtration is the query is execute through the application itself, but if executed directly through SQL*Plus or any other interface.
Oracle Introduces DBMS_RLS (row level Security) to simplify this task and solve the problem by allowing you to write filtration logic and associate it with a table, effectively, enforcing the filtration on the database level.
Example
I have the following table; each row contains an field called username as
shown below
It is required to set a security policy which allows AMMAR to only view rows with username = AMMAR, same rule applies to SAJDI and SYSTEM
To accomplish this task, we need to create a function (Policy function) and a Procedure (to set the context) in addition to creating a security policy.
Let us start with creating a policy function. The purpose of this function is to return a Where clause that filters required rows. It does not matter what you call the function , but you have to stick with the signature given below (schema_name, and object_name).
The return value is a varchar that actually return a where condition. It does not matter what the name of the return variable is, it only have to be of type VARCHAR2
create or replace function DEPT_PREDICATE( schema_name in varchar2,object_name in varchar2)
return varchar2
is
lv_predicate varchar2(1000):='';
begin
lv_predicate:='USERNAME=SYS_CONTEXT(''test'', ''username'')';
return lv_predicate;
end;
To understand what the function does, just imagine that the SYS_CONTEXT function shall return 'AMMAR'. Therefore, the lv_predicate variable shall be
'USERNAME=AMMAR'
What we are after is then, when a user, executes a query against the EMP table, we want the following to take place automatically
1) A SYS_CONTEXT to automatically return a value which in our case is the username
2) The Query to automatically associate the security function with the EMP table in the Query mode
We shall start with (2). To do the association, you need to issue the following statement from SYSTEM user
BEGIN
dbms_rls.add_policy(
object_schema => 'scott', -- The schema that for which EMP belongs
object_name => 'emp', -- The object you want to associate the sec. function with
policy_name => 'EMP_CL_POLICY', -- a Name for your choice for this policy
function_schema => 'scott', --the schema where the function is created
policy_function => 'dept_predicate ' -- the name of the function
Statement_Types => 'Select');
END;
/
The above statement instructs Oracle to execute the dept_predicate function whenever a SELECT statement is issued against the EMP table and then add the predicate that result from the execution of the dept_predicate function to the WHERE clause of the SELECT statement
Now, we will handle the first (1) above, which will allow us to associate the a NAME with SYS_CONTEXT
Frist, Create a CONTEXT that is associated with a procedure
SQL>CREATE CONTEXT TEST USING set_deptno;
here, we created a context called TEST and is associated with set_detpno.
The set_deptno is a procedure which is responsible for setting a context to a session.
SQL>create or replace PROCEDURE set_deptno(p1 varchar2) AS
BEGIN
DBMS_SESSION.set_context(namespace => 'test',ATTRIBUTE => 'username',
VALUE => p1);
END;
/
When the above procedure is executed from a session, the session shall acquire a context called TEST, with a context value of the parameter P1
(NOTE: you cannot call DBMS_SESSION.set_context as a standalone procedure, it has to be called through the procedure name with which TEST context is created (ie the CREATE CONTEXT associated the TEST context with set_deptno, so you have to call the set_deptno procedure in order to execute the set.context)
Let us demonstrate
SQL> CREATE CONTEXT TEST USING set_deptno;
Context created.
SQL> create or replace PROCEDURE set_deptno(p1 varchar2) AS
2 BEGIN
3 DBMS_SESSION.set_context
4 (namespace => 'test',
5 ATTRIBUTE => 'username',
6 VALUE => p1);
7 END;
8 /
Procedure created.
SQL> create or replace function DEPT_PREDICATE(schema_name in
varchar2,object_name in varchar2)
2 return varchar2
3 is
4 lv_predicate varchar2(1000):='';
5 begin
6 lv_predicate:='USERNAME=SYS_CONTEXT(''test'', ''username'')';
7 return lv_predicate;
8 end;
9 /
Function created.
SQL> BEGIN
2 dbms_rls.add_policy(
3 object_schema => 'scott'
4 ,object_name => 'emp'
5 ,policy_name => 'EMP_CL_POLICY'
6 ,function_schema => 'scott'
7 ,policy_function => 'dept_predicate ');
8 END;
9 /
PL/SQL procedure successfully completed.
1) Set the context to Amman
SQL> begin
2 set_deptno('AMMAR');
3 end;
4 /
PL/SQL procedure successfully completed.
2) test the context setting
SQL> select sys_context('TEST','USERNAME') from dual;
SYS_CONTEXT('TEST','USERNAME')
--------------------------------------------------------------------------------
AMMAR
3) Issue a query against EMP
4) Change the context again
1 begin
2 set_deptno('SAJDI');
3* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select sys_context('TEST','USERNAME') from dual;
SYS_CONTEXT('TEST','USERNAME')
----------------------------------------------------------------------
SAJDI
5) Query Again
