Hi Experts,
We're implementing RLS to hide sensistive data from consumers of our Data Warehouse.
At the beginning we have decided to have 3 policy types:
1: user can see very limited set of data
2: user can see everything from 1 and restricted data
3: user can see everything from 1,2 and highly restricted data
This must be done for all tables in given schema ADWP_DWH. All tables on this schema have column POLICY_TYPE with assigned value for each row.
Then we have done implementation on RLS admin user for a single table only as follows:
1) Creation of the table that contains user and policy type:
CREATE TABLE RLS_USERS (
USER_NAME VARCHAR2(30),
POLICY_TYPE number(1)
);
2) Creation of context:
CREATE OR REPLACE CONTEXT customers_ctx USING customers_ctx_pkg;
3) create package for setting context:
CREATE OR REPLACE PACKAGE customers_ctx_pkg IS
PROCEDURE set_policy_custom_type;
END;
/
CREATE OR REPLACE PACKAGE BODY customers_ctx_pkg IS
PROCEDURE set_policy_custom_type
AS
policy_type number(1);
BEGIN
SELECT POLICY_TYPE INTO POLICY_TYPE FROM RLS_USERS
WHERE USER_NAME = SYS_CONTEXT('USERENV', 'SESSION_USER');
DBMS_SESSION.SET_CONTEXT('customers_ctx', 'policy_type', policy_type);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END set_policy_custom_type;
END;
/
4) Create logon trigger
CREATE TRIGGER set_ctx_trig2 AFTER LOGON ON DATABASE
BEGIN
RLS_OWNER.customers_ctx_pkg.set_policy_custom_type;
END
/
5) Create function for policy
CREATE OR REPLACE FUNCTION get_customers(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
AS
custs_pred VARCHAR2 (400);
BEGIN
custs_pred := 'policy_type = SYS_CONTEXT(''customers_ctx'', ''policy_type'')
OR policy_type = DECODE(SYS_CONTEXT(''customers_ctx'', ''policy_type''), ''3'', ''2'', ''1'')
OR policy_type = ''1''
';
RETURN custs_pred;
END;
/
6) Add policy:
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'adwp_dwh',
object_name => 'customers',
policy_name => 'customer_policy',
function_schema => 'rls_admin',
policy_function => 'get_customers',
statement_types => 'select');
END;
Questions:
1. Is there any way to do policy for all tables on given schema with same predicate? Above example is only for one table and I would not like to repeat it for almost 600 tables.
2. In step 5) I used pretty complex predicate to select for type access: 1 -> policy type 1, for type access: 2 -> policy type 1, 2, for type access: 3 -> policy type: 1,2, 3. Is there any way for simplification? I read that for optimal performance predicates should be as simple as possible.
3. Do you have experience with this mechanism for tables with ~900 000 000 rows? Does it affect performance for the queries?
Thanks for your reply.
Regards,
Bolo