I would like to grant the right of update to a manager on a table custumer but only on the customers of which it is manager.
For example I have a table Custumer and I created a user "Jean" with the command CREATE USER, so I would like that "Jean" can modify all the customers of which it is manager.
I found this example of RLS but It's too late to edit to meet your simple USER requirements. This example caters for users with many andvarious ROLES that may be inherited from an Organizational Tree. But I hope you get the idea?
BEGIN
DBMS_RLS.DROP_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_READ');
DBMS_RLS.DROP_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_WRITE');
DBMS_RLS.DROP_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_REGISTER');
END;
CREATE OR REPLACE PACKAGE chs_security_policies AS
ROLE_CRUM CONSTANT VARCHAR2(5) := 'CRUM';
ROLE_CRUO CONSTANT VARCHAR2(5) := 'CRUO';
ROLE_HOU CONSTANT VARCHAR2(5) := 'HOU';
ROLE_DCO CONSTANT VARCHAR2(5) := 'DCO';
ROLE_SA CONSTANT VARCHAR2(5) := 'SA';
ROLE_CA CONSTANT VARCHAR2(5) := 'CA';
CHS_ORG_TREE CONSTANT CHAR(5) := 'CHS01';
READ_ACCESS CONSTANT INTEGER := 63;
WRITE_ACCESS CONSTANT INTEGER := 42;
INSERT_ACCESS CONSTANT INTEGER := 64;
ORG_CASCADE CONSTANT INTEGER := 2060;
CASE_SPECIFIC CONSTANT INTEGER := 48;
QUARANTINED_ACCESS CONSTANT INTEGER := 128;
FINALISED_ACCESS CONSTANT INTEGER := 256;
READ_ONLY CONSTANT INTEGER := 21;
READ_CASE_ORG_UNITS CONSTANT INTEGER := 16;
READ_GROUP_ORG_UNITS CONSTANT INTEGER := 4;
READ_WORLD_ORG_UNITS CONSTANT INTEGER := 1;
FUNCTION case_file_read (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION case_file_write (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION case_file_register (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION case_file_filter (i_schema IN VARCHAR2, i_object IN VARCHAR2, i_access_requested INTEGER)
RETURN VARCHAR2;
FUNCTION check_case_access (i_case_file_id IN INTEGER, i_mgr_org_unit IN INTEGER, i_dco_person_id in varchar2)
RETURN INTEGER
DETERMINISTIC;
FUNCTION default_priv_mask (i_person_id in varchar2)
RETURN INTEGER
DETERMINISTIC;
FUNCTION decode_role (i_case_file_id IN INTEGER, i_mgr_org_unit IN INTEGER, i_dco_person_id in varchar2)
RETURN VARCHAR2
DETERMINISTIC;
FUNCTION check_proxy_access (i_case_file_id IN case_file.case_file_id%type
, i_dco_person_id IN case_file.dco_person_id%type
, i_mgr_org_unit IN case_file.mgr_org_unit%type
, i_proxy_person_id IN case_file.dco_person_id%type)
RETURN INTEGER
DETERMINISTIC;
END chs_security_policies;
CREATE OR REPLACE PACKAGE BODY chs_security_policies AS
FUNCTION case_file_read (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN case_file_filter(i_schema, i_object, READ_ACCESS);
END case_file_read;
FUNCTION case_file_write (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN case_file_filter(i_schema, i_object, WRITE_ACCESS);
END case_file_write;
FUNCTION case_file_register (i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN case_file_filter(i_schema, i_object, INSERT_ACCESS);
END case_file_register;
FUNCTION case_file_filter (i_schema IN VARCHAR2, i_object IN VARCHAR2, i_access_requested INTEGER)
RETURN VARCHAR2
IS
o_filter_predicate VARCHAR2(200) := '';
BEGIN
CASE lower(i_object)
WHEN 'case_file' THEN
o_filter_predicate :=
'BITAND(chs_security_policies.check_case_access(case_file_id, mgr_org_unit, dco_person_id), '||i_access_requested||') <> 0 ';
WHEN 'case_file_h' THEN
o_filter_predicate :=
'BITAND(chs_security_policies.check_case_access(case_file_id, mgr_org_unit, dco_person_id), '||i_access_requested||') <> 0 ';
END CASE;
RETURN o_filter_predicate;
END case_file_filter;
FUNCTION check_proxy_access (i_case_file_id IN case_file.case_file_id%type
, i_dco_person_id IN case_file.dco_person_id%type
, i_mgr_org_unit IN case_file.mgr_org_unit%type
, i_proxy_person_id IN case_file.dco_person_id%type)
RETURN INTEGER
DETERMINISTIC
IS
o_has_access INTEGER := 0;
v_prev_person varchar2(8) := SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');
BEGIN
dbms_session.set_identifier(i_proxy_person_id);
IF BITAND(check_case_access(i_case_file_id, i_mgr_org_unit, i_dco_person_id), READ_ACCESS) <> 0
THEN
o_has_access := 1;
END IF;
dbms_session.set_identifier(v_prev_person);
RETURN o_has_access;
END check_proxy_access;
FUNCTION check_case_access(i_case_file_id in INTEGER, i_mgr_org_unit in INTEGER, i_dco_person_id in varchar2)
RETURN INTEGER
DETERMINISTIC
IS
o_access_granted INTEGER := 0;
v_curr_person varchar2(8) := SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');
v_single_priv INTEGER := 0;
v_profile_org_unit INTEGER := 0;
v_quarantine_ok varchar2(1);
v_finalise_ok varchar2(1);
v_is_participant varchar2(1);
v_local_role access_role.role_code%type;
cursor user_privs is
select
d.priv_id,
b.org_unit_id,
c.role_code
from
access_user a
,access_profile b
,access_role c
,access_role_priv d
,access_priv e
where
a.person_id = v_curr_person
and a.active_flag = 'Y'
and b.person_id = a.person_id
and c.role_code = b.role_code
and d.role_code = b.role_code
and e.priv_id = d.priv_id
;
BEGIN
IF v_curr_person is null
THEN
RETURN 0;
END IF;
select
case
when exists
(select
*
from
complainant a
where
a.case_file_id = i_case_file_id
and a.person_id = v_curr_person
and a.active_flag = 'Y'
)
then 'Y'
else 'N'
end
into
v_is_participant
from
dual
;
IF v_is_participant = 'Y'
THEN
RETURN 0;
END IF;
select
case
when exists
(select
*
from
respondent a
where
a.case_file_id = i_case_file_id
and a.person_id = v_curr_person
and a.person_type <> 'U'
and a.active_flag = 'Y'
)
then 'Y'
else 'N'
end
into
v_is_participant
from
dual
;
IF v_is_participant = 'Y'
THEN
RETURN 0;
END IF;
v_quarantine_ok := 'N';
v_finalise_ok := 'N';
open user_privs;
loop
fetch user_privs into v_single_priv,
v_profile_org_unit,
v_local_role;
exit when user_privs%notfound;
IF v_single_priv = QUARANTINED_ACCESS
THEN
v_quarantine_ok := 'Y';
continue;
END IF;
IF v_single_priv = FINALISED_ACCESS
THEN
v_finalise_ok := 'Y';
continue;
END IF;
IF bitand(v_single_priv, ORG_CASCADE) <> 0
THEN
select
case
when i_mgr_org_unit in (
select
a.org_unit_child
from
org_rel a
connect by nocycle
a.org_rel_type_code = CHS_ORG_TREE and
prior
a.org_unit_child = a.org_unit_parent
start with
a.org_unit_child = v_profile_org_unit
and a.org_rel_type_code = CHS_ORG_TREE)
then v_single_priv
else 0
end
into
v_single_priv
from
dual
;
END IF;
IF bitand(v_single_priv, CASE_SPECIFIC) <> 0
THEN
IF v_local_role = 'CA'
THEN
select
case
when exists
(select
*
from
case_assistant a
where
a.case_file_id = i_case_file_id
and a.case_assistant_id = v_curr_person
and sysdate between a.access_from and a.access_to
)
then v_single_priv
else 0
end
into
v_single_priv
from
dual
;
IF v_single_priv <> 0
THEN
v_quarantine_ok := 'Y';
END IF;
ELSE
IF v_curr_person <> i_dco_person_id
THEN
v_single_priv := 0;
END IF;
END IF;
END IF;
o_access_granted := (o_access_granted + v_single_priv - bitand(o_access_granted, v_single_priv));
end loop;
close user_privs;
IF o_access_granted = 0 THEN
RETURN o_access_granted;
END IF;
IF (v_quarantine_ok = 'N' AND (i_dco_person_id <> v_curr_person
OR BITAND(o_access_granted,CASE_SPECIFIC) = 0)) THEN
select
case
when exists
(select
*
from
quarantine a
where
a.case_file_id = i_case_file_id
and a.active_flag = 'Y'
)
then 0
else o_access_granted
end
into
o_access_granted
from
dual
;
END IF;
IF o_access_granted = 0 THEN
RETURN o_access_granted;
END IF;
IF v_finalise_ok = 'N' THEN
select
case
when exists
(select
*
from
finalise a
where
a.case_file_id = i_case_file_id
)
then 0
else o_access_granted
end
into
o_access_granted
from
dual
;
END IF;
IF v_finalise_ok = 'Y' THEN
select
case
when exists
(select
*
from
finalise a
where
a.case_file_id = i_case_file_id
)
then BITAND(o_access_granted, READ_ONLY)
else o_access_granted
end
into
o_access_granted
from
dual
;
END IF;
RETURN o_access_granted;
END check_case_access;
FUNCTION decode_role(i_case_file_id IN INTEGER, i_mgr_org_unit IN INTEGER, i_dco_person_id in varchar2)
RETURN VARCHAR2
DETERMINISTIC
IS
v_curr_person varchar2(8) := SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');
v_access_granted INTEGER := 0;
o_role_used access_role.role_code%type;
BEGIN
v_access_granted := check_case_access(i_case_file_id, i_mgr_org_unit, i_dco_person_id);
IF BITAND(v_access_granted, READ_CASE_ORG_UNITS ) <> 0 THEN
IF v_curr_person = i_dco_person_id THEN
o_role_used := ROLE_DCO;
ELSE
o_role_used := ROLE_CA;
END IF;
ELSE
IF BITAND(v_access_granted, READ_GROUP_ORG_UNITS) <> 0 THEN
o_role_used := ROLE_HOU;
ELSE
IF BITAND(v_access_granted, READ_WORLD_ORG_UNITS) <> 0 THEN
select
case
when exists
(select
*
from
access_profile a
where
a.person_id = v_curr_person
and a.role_code = ROLE_CRUM
)
then ROLE_CRUM
else ROLE_CRUO
end
into
o_role_used
from
dual
;
END IF;
END IF;
END IF;
RETURN o_role_used;
END decode_role;
FUNCTION default_priv_mask(i_person_id in varchar2)
RETURN INTEGER
DETERMINISTIC
IS
o_access_available INTEGER := 0;
v_single_priv INTEGER := 0;
cursor user_privs is
select
d.priv_id
from
access_user a
,access_profile b
,access_role c
,access_role_priv d
,access_priv e
where
a.person_id = i_person_id
and a.active_flag = 'Y'
and b.person_id = a.person_id
and c.role_code = b.role_code
and d.role_code = b.role_code
and e.priv_id = d.priv_id
;
BEGIN
open user_privs;
loop
fetch user_privs into v_single_priv;
exit when user_privs%notfound;
o_access_available := (o_access_available + v_single_priv - bitand(o_access_available, v_single_priv));
end loop;
close user_privs;
RETURN o_access_available;
END default_priv_mask;
END chs_security_policies;
/
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_READ',
function_schema => 'CHS',
statement_types => 'SELECT',
policy_function => 'chs_security_policies.case_file_read');
DBMS_RLS.ADD_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_WRITE',
function_schema => 'CHS',
statement_types => 'UPDATE,DELETE',
policy_function => 'chs_security_policies.case_file_write');
DBMS_RLS.ADD_POLICY (object_schema => 'CHS',
object_name => 'CASE_FILE',
policy_name => 'CASE_FILE_REGISTER',
function_schema => 'CHS',
statement_types => 'INSERT',
policy_function => 'chs_security_policies.case_file_register');
END;
