There is often a need to restrict what a user is able to see within underlying tables within an Oracle Database. This can be achieved many was via views, and logic within them. However Oracle has offered row level security for many years, since around v8. Below is a simple example of how to apply row level security via a policy applied to a table. This policy essentially uses a function, the logic within the function applies a where clause thats invisible to the user which may restrict that users access to the data within the table.
Create 2 users
create user np identified by np;
create user np2 identified by np2;
Create a table on which to apply security, in this case its a dimension table for Stores
create table np.dim_stores
(id integer,
store_name varchar2(200)
)
Seed with sample data
insert into np.dim_stores values (1,’Ringwood Store’);
insert into np.dim_stores values (2,’Poole Store’);
Create a Lookup table, which maps users to properties just a simple way to map people to property data
create table np.tbl_store_security
( username varchar2(200),
store_id varchar2(1000)
)
Populate security table with list of users able to see certain data….
insert into np.tbl_store_security values (‘NP’,’1′);
insert into np.tbl_store_security values (‘NP’,’2′);
Create a Function that checks who is able to see which properties
this function will simply return a WHERE statement to restrict a user when they perform a select on a table that has a
row level security policy applied.
create or replace
function FNC_STORE_SECURITY (schema varchar2, tab varchar2) return varchar2
as
v_result varchar2(2000);
security_record np.tbl_store_security%rowtype;
cursor get_store_ids is
select
username,
listagg (store_id, ‘,’)
WITHIN GROUP
(ORDER BY username) store_ids
FROM
np.tbl_store_security
where username in (sys_context(‘userenv’,’session_user’))
GROUP BY
username;
begin
if sys_context(‘userenv’,’session_user’) <> ‘DAS_DW’ then
open get_store_ids;
fetch get_Store_ids into security_record;
v_result := ‘id in (‘ || nvl(security_record.store_id,-0) || ‘)’;
close get_store_ids;
else
v_result :=’id < 0 or id > 0 or id is not null’;
end if;
return v_result;
end;
Test output from function
select np.fnc_store_security(‘NP’,”) from dual;
Add a policy to the copy of the table, i.e. apply the logic in the function above
begin
dbms_rls.add_policy
(object_schema=>’NP’,
object_name=>’DIM_STORES’,
policy_name=>’DIM_STORE_POLICY’,
function_schema=>’NP’,
policy_function=>’FNC_STORE_SECURITY’
);
end;
/
Login as user NP/NP , then try selecting from the table :-
Should see that it returns the 2 rows you allowed the user to see via the security lookup table
select * from np.dim_store
Login as user NP2, should see the same select statement returns 0 rows as the user has not been configured to see any records.
select * from np.dim_store
Remove Policy from table
begin
dbms_rls.drop_policy
(object_schema=>’NP’,
object_name=>’DIM_STORE’,
policy_name=>’DIM_STORE_POLICY’);
end;
/
You can review which objects have policies applied via this :-
select * from dba_policies
where object_name = ‘DIM_STORES’