Hello everyone.
I've been working through the RAS hr demo found here: https://docs.oracle.com/database/121/DBFSG/security_hr_demo_files.htm#DBFSG823
and, as an experiment, I've changed this:
realms(2) := xs$realm_constraint_type(
realm => 'department_id = 60',
acl_list => xs$name_list('it_acl'));
to this:
realms(2) := xs$realm_constraint_type(
realm => 'department_id in (select department_id from hr.employees '||
'where email = xs_sys_context(''xs$session'',''username''))',
acl_list => xs$name_list('it_acl'));
because I wanted to see if I could avoid having to hardcode 'department_id = 60' into the realm constraint.
Unfortunately this change seems to break something. This is the only change I've made to the hr RAS demo. I can still login as daustin & smavris, however if they attempt to query the hr.employees table the database complains (ORA-28113).
Is it possible to implement a realm constraint that uses subqueries in this manner? If not, is there a feature of RAS that can be used to filter rows based on the users current context?
Thanks.
ps: if I create smavris and daustin without constraints, executing the following statement returns a department_id list as expected:
select department_id from hr.employees where department_id in (select department_id from hr.employees where email = xs_sys_context(''xs$session'',''username''));
========================
EDIT
========================
solved(?) using functions:
create or replace function getdeptid(username in hr.employees.email%TYPE) return hr.employees.department_id%TYPE
is
deptid hr.employees.department_id%TYPE;
begin
select department_id into deptid from hr.employees where email = username;
return deptid;
exception
when no_data_found then
return 0;
end;
realms(2) := xs$realm_constraint_type(
--realm => 'department_id = 60',
realm => 'department_id = getdeptid(xs_sys_context(''xs$session'',''username''))',
acl_list => xs$name_list('dept_acl'));