Oracle : RLS - Row Level Security

Implementing Oracle Row Level Security with Examples.

Permissions recquired for the Database User :-

CONNECT SYS@DEV11I AS SYSDBA
GRANT EXECUTE ON DBMS_RLS TO JSECURITY;

If you want to exempt Row level security for one user
--grant EXEMPT ACCESS POLICY to dwhDATA;

Adding Security to a Table :-

BEGIN
DBMS_RLS.ADD_POLICY
(OBJECT_SCHEMA => 'dwhDM',
OBJECT_NAME => 'AR_INVOICES_TBL',
POLICY_NAME => 'RLS_AR_ITEM',
FUNCTION_SCHEMA => 'JSECURITY',
POLICY_FUNCTION => 'PRODUCT_SECURITY',
STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
UPDATE_CHECK => TRUE);
END;
/

Policy Function :-

CREATE or replace FUNCTION PRODUCT_security (obj_schema VARCHAR2, obj_name VARCHAR2)
RETURN VARCHAR2 authid CURRENT_USER AS

rls_val VARCHAR2(1000);
vcolumn varchar2(50);
vError varchar2(50) := 1 ;

Cursor c is select rls_Value from js_rls_user_access where rls_name = 'PRODUCT' and user_name = USER;

BEGIN

vError := 2 ;

begin
SELECT column_name INTO vCOLUMN FROM js_rls_tab_columns where table_name = obj_name and rls_name = 'PRODUCT';
exception
when no_data_found then
vcolumn := null;
end;

vError := 3 ;

if vColumn like '%ITEM%' THEN
rls_val := '( 0 ';
FOR rec IN c LOOP
rls_val := rls_val||' ,'||REC.RLS_VALUE ;
END LOOP;

return vCOLUMN||' in '||rls_val||')';
ELSE
return vCOLUMN||' = 0';
end if;

end;