Row Level Security Part 1 November 15, 2012
Posted by mwidlake in database design, security.Tags: security, SQL, system development
9 comments
I’ve been working a little on Row Level Security (RLS) recently and wanted to mention a few things, so first some groundwork.
If you want to limit the rows certain users can see, you might think to use views or you might think to use RLS (part of VPD – Virtual Private Database). You can also (from V10 I think) limit which columns users can see. An example is probably the best way to show this. I’m doing this on Oracle 11.2.0.3.
I have two users, MDW and MDW_OFFSHORE. MDW has DBA-type privileges and MDW_OFFSHORE has connect, resource and one or two other simple privs. I will now demonstrate creating and populating a simple table under MDW, adding RLS to it and how it alters what MDW_OFFSHORE sees.
MDW> -- create a table and show row level and column level rls
MDW> --
MDW> drop table TEST1 purge;
MDW> --
MDW> create table test1
2 (id number not null
3 ,cp_name varchar2(30) not null
4 ,other_name varchar2(30) not null
5 ,MASK_FL number
6 )
7 /
MDW> insert into test1 VALUES (1,'ERIC','ERIC THE RED',1);
MDW> insert into test1 VALUES (2,'BROWN','BOB THE BROWN',2);
MDW> insert into test1 VALUES (3,'GREEN','GARY THE GREEN',1);
MDW> insert into test1 VALUES (4,'BLUE','BILL THE BLUE',3);
MDW> insert into test1 VALUES (5,'BLACK','DAVE THE BLACK',4);
MDW> insert into test1 VALUES (6,'PURPLE','PETE THE PURPLE',5);
MDW> insert into test1 VALUES (7,'RED','ROGER THE RED',6);
MDW> --
MDW> COMMIT;
MDW> SELECT * FROM TEST1;
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
MDW> grant all on test1 to mdw_onshore;
MDW> grant select on test1 to mdw_offshore;
MDW> --
--
--This is where I create a function to generate the RLS rules
--
MDW> CREATE OR REPLACE FUNCTION hide_rows (
2 v_schema IN VARCHAR2
3 ,v_objname IN VARCHAR2)
4 RETURN VARCHAR2 AS
5 con VARCHAR2 (200);
6 BEGIN
7 -- note the use of backets to make it an atomic test
8 con := '(user='''||v_schema||''' or MASK_FL > 3)';
9 RETURN (con);
10 END hide_rows;
11 /
MDW> --
--
-- Now add the actual RLS policy to the table TEST1 using the function hide_rows
MDW> BEGIN
2 sys.DBMS_RLS.ADD_POLICY (
3 object_schema => 'MDW'
4 ,object_name => 'TEST1'
5 ,policy_name => 'hide_rows_policy'
6 ,policy_function => 'hide_rows'
7 ,function_schema => 'MDW'
8 );
9 END;
10 /
MDW> --
That’s it. In effect you create a function, in this hide_rows, that creates a WHERE predicate (more on that later) and associate it with the table by creating a policy hide_ rows_policy.
Now let’s see what impact it has:
-- as the main user:
--
MDW> select * from test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
MDW>
-- now as the offshore user:
mdw_offshore> select * from mdw.test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
3 rows selected.
As you can see, mdw_offshore is seeing only a subset of rows in the table. This subset is determined by the function hide_rows. This function returns a WHERE PREDICATE that is added to any query run against the table. It is actually very simply to see what that predicate is, you just call the function passing in the table owner and name:
MDW> select hide_rows(v_schema => user,v_objname=>’TEST1′) from dual;
HIDE_ROWS(V_SCHEMA=>USER,V_OBJNAME=>’TEST1′)
——————————————————————————-
(user=’MDW’ or MASK_FL > 3)
Note that my refernce to v_owner in the function comes out as the text MDW – a way to ensure the predicate does not filter data looked at by the table owner. This implies of course that yes, you can stop the table owner from seeing data!
By default, this extra WHERE PREDICATE is applied to any SELECT, INSERT, UPDATE or DELETE statement run against this table. The function has to accept the v_schema and v_objname parameters and output a varchar2 string. At the time of being called as part of a RLS policy, oracle passes in the owner and name of the object being masked (which is usually a table and sometimes a view).
There are a few things I think it is important to keep in mind in respect of this extra Where Predicate:-
- You have very little control over what other where predicates will be applied to DML against this table, so keep it self-contained – which is why I bound it in brackets.
- It has to be syntactically correct but no check is made for this when you create the function – it is simply a string being outputted, as far as Oracle is concerned, at the time of creation.
- It can be any valid Where Predicate you like, so long as it does not reference other rows in the table with the policy is applied to
- This could be fired a lot. The performance impact could be considerable, which is why CONTEXTs are often used (as they are memory resident and can be configured by eg logon triggers) but I have skipped over that. Go look at the manuals.
I mentioned that the masking of data could be applied to just a column rather than whole rows. So let’s demonstrate that. I can do this by simply dropping and reapplying the same function in a different way:
MDW> BEGIN
-- first drop the policy, simply by stating the object owner, name and policy
2 sys.dbms_rls.drop_policy(
3 object_schema => 'MDW'
4 ,object_name => 'TEST1'
5 ,policy_name => 'hide_rows_policy'
6 );
-- and recreate - note the two new paramters
7 sys.DBMS_RLS.ADD_POLICY (
8 object_schema => 'MDW'
9 ,object_name => 'TEST1'
10 ,policy_name => 'hide_rows_policy'
11 ,policy_function => 'hide_rows'
12 ,function_schema => 'MDW'
13 ,sec_relevant_cols => 'OTHER_NAME,MASK_FL'
14 ,sec_relevant_cols_opt => sys.dbms_rls.ALL_ROWS
15 );
16 END;
17 /
PL/SQL procedure successfully completed.
--
-- as the main user
MDW> select * from test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
--
-- now as the offshore user:
mdw_offshore> select * from mdw.test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC
2 BROWN
3 GREEN
4 BLUE
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
--
-- but what can look a little odd is that if mdw_offshore describes the table..
mdw_offshore> desc mdw.test1
Name Null? Type
----------------------------------------------------- -------- --------------
ID NOT NULL NUMBER
CP_NAME NOT NULL VARCHAR2(30)
OTHER_NAME NOT NULL VARCHAR2(30)
MASK_FL NUMBER
--
-- they see that OTHER_NAME is NOT NULL but...
mdw_offshore> select count(*) from mdw.test1 where other_name is null;
COUNT(*)
----------
4
That’s rather nice, I think. The same function can be used and rather than hide the rows it masks the stated columns. The sec_relevant_cols => ‘OTHER_NAME,MASK_FL’ parameter states the columns to apply the function on and the sec_relevant_cols_opt => sys.dbms_rls.ALL_ROWS parameter means “show a row even if a column or columns are hidden”. Without it, you only see rows if you do not reference the masked columns (even if indirectly via eg “select (*)”) – see this nice article by Tim Hall if you want an example of that.
With column masking the string from the function is not actually used as a Where Predicate but as a Boolean TRUE/FALSE test. If the test results in TRUE, you see the column(s), otherwise they are hidden.
As you can see from the example, this column-level RLS can result in some odd looking (though intentional) results. A DESC of the table shows the column OTHER_NAME is mandatory but records are found with it set to null. There can also be the situation where a user can insert a value for the masked column, but then cannot see it when they select the data back, though other users can see the populated column.
A limitation of column-level masking is that you can only mask columns with NULL and not, for example, a string of “MASKED”. Thus if a column you are masking is not manadatory, how would you distinguish between a true NULL and a masked NULL? {A nod of thanks goes to Pete Finnigan for confirming this “NULL only” limitation.}
One of the appeals of RLS over eg views is that it is hard to circumvent, as it is applied at the level of the SQL. Another is that the same function can be applied to many tables and might also be applied to both column and row masking, if designed well.
That will almost do for an introduction to the use of RLS to mask rows and columns. However I’ll finish with just a few important points that follow on from this being a security feature.
- I demonstrated that I could see what the function responsible for this security actually returns. If you do not protect unauthorised execution of that function, that could be a security hole.
- I masked the column that is used to control if the OTHER_NAME column is masked. Would you need to also do this?
- Data that is missing is data that could be of interest.
- Anyone who can alter that function can undo your security.
- Anyone who can view DBA_SOURCE (eg has SELECT ANY DICTIONARY) can view the function and see how it works.
- As you can appreciate from the above, apply Security securely is a right pain in the proverbial.
Martin Widlake's Yet Another Oracle Blog 