CODEWITHSUNDEEP
sqloracle-databasesecurity
RobertGG
RobertGG

Reputation: 125

Oracle row level security by column value, not by user id

I'm learning Oracle and I'm trying to add Row Level Security to a demo table. I can run successfully this first example:

CREATE OR REPLACE FUNCTION get_emp_pred
    (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
       RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') 
        OR upper(manager) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
    END;

BEGIN
    SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'hector',
        object_name     => 'emp',
        policy_name     => 'emp_vpd1',
        function_schema => 'vpdadmin',
        policy_function => 'get_emp_pred',
        statement_types => 'select'
    );
END;

In this example every user can see the rows where column 'ename' or column 'manager' value matches his own user name. Now my problem is when I try to filter not comparing by a user in a column but for some arbitrary column value like country or salary. For example I want to show rows to Bob where salary > 1000, to Anne where salary < 1000 and show all the rows to other users.

I tried this function and other variants without result:

CREATE OR REPLACE FUNCTION get_emp_pred
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 AS
    l_predicate VARCHAR2(100);
    BEGIN 
        IF 'BOB' = SYS_CONTEXT('USERENV', 'CURRENT_USER') THEN
            l_predicate := 'salary > 1000';
        ELSIF 'ANNE' = SYS_CONTEXT('USERENV', 'CURRENT_USER') THEN
            l_predicate := 'salary < 1000'; 
        ELSE 
            l_predicate := '1=1';
        END IF;
        RETURN l_predicate;
     END;

I suppose you can not evaluate this conditions at runtime, but if I write one policy for every user and predicate it doesn't work neither. For example this function with its policy for Bob, other for Anne, etc:

CREATE OR REPLACE FUNCTION get_emp_pred_bob
    (schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN 
        RETURN '''BOB'' = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') AND salary > 1000';
    END;

If I have only the Bob policy it works for Bob and return 0 rows for other users, but when I add a second policy for Anne I get 0 rows for everybody, maybe because policyBob && policyAnne returns always false.

I want to know if this kind of filtering is possible.

Upvotes: 1

Views: 1630

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Your second get_emp_pred function isn't doing what you expect because it is a (default) definer's rights function. That means that within the function, current_user is the owner, VPDADMIN, not the user performing the query.

From the documentation current_user gives:

The name of the database user whose privileges are currently active. This may change during the duration of a database session ... to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER.

So if you change current_user to session_user in the second function then it should do what you want:

CREATE OR REPLACE FUNCTION get_emp_pred
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 AS
    l_predicate VARCHAR2(100);
    BEGIN 
        IF 'BOB' = SYS_CONTEXT('USERENV', 'SESSION_USER') THEN
            l_predicate := 'salary > 1000';
        ELSIF 'ANNE' = SYS_CONTEXT('USERENV', 'SESSION_USER') THEN
            l_predicate := 'salary < 1000'; 
        ELSE 
            l_predicate := '1=1';
        END IF;
        RETURN l_predicate;
     END;

Your first function doesn't suffer from that because the user is evaluated in the predicate as part of the query, not within the function.

when I add a second policy for Anne I get 0 rows for everybody, maybe because policyBob && policyAnne returns always false.

Yes, both policies are applied, and you end up with mutually exclusive predicates applied to the query, so now rows can match.

You could make this work with a single policy and function that returns a predicate that ORs together all the options; something like:

RETURN q'[(SYS_CONTEXT('USERENV', 'CURRENT_USER') = 'BOB' AND salary > 1000)
       OR (SYS_CONTEXT('USERENV', 'CURRENT_USER') = 'ANNE' AND salary < 1000)
       OR (SYS_CONTEXT('USERENV', 'CURRENT_USER') NOT IN ('BOB', 'ANNE'))'];

But using PL/SQL logic to return different predicates is simpler - as long as it doesn't impact performance. It's possible a static policy with a single fixed complex predicate could perform better.

Upvotes: 1

Related Questions