And that is what we have VPD (Virtual Private Database) for, a feature that developers like to reinvent.
Using Oracle Virtual Private Database to Control Data Access
Set up environment:
create user app identified by app default tablespace users
quota unlimited on users account lock;
grant create table to app;
create user u1 identified by u1;
create user u2 identified by u2;
create user u3 identified by u3;
grant create session to u1, u2, u3;
CREATE TABLE app.tblss_user(
user_id INTEGER NOT NULL,
user_name VARCHAR2(15),
reg_date DATE
);
CREATE TABLE app.job (
job_id INTEGER NOT NULL,
job_name VARCHAR(25),
job_salary INTEGER NOT NULL,
fkuser_id INTEGER NOT NULL);
grant select, update on app.job to u1, u2, u3;
insert into app.tblss_user values (1, 'U1', sysdate - 400);
insert into app.tblss_user values (2, 'U2', sysdate);
commit;
insert into app.job values(1, 'JOB1', 1000, 1);
insert into app.job values(2, 'JOB2', 1200, 1);
insert into app.job values(3, 'JOB3', 800, 2);
commit;
Our data:
SQL> select * from app.tblss_user;
USER_ID USER_NAME REG_DATE
---------- --------------- ---------
1 U1 24-JAN-17
2 U2 28-FEB-18
SQL> select * from app.job;
JOB_ID JOB_NAME JOB_SALARY FKUSER_ID
---------- ------------------------- ---------- ----------
1 JOB1 1000 1
2 JOB2 1200 1
3 JOB3 800 2
Now create the security functions providing the predicates:
create or replace function app.job_select_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
l_user_id number;
begin
if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
begin
select user_id into l_user_id from tblss_user
where user_name = sys_context('userenv', 'session_user');
exception when no_data_found then return '1=2';
end;
return 'fkuser_id = ' || l_user_id;
end;
/
grant execute on app.job_select_security to u1, u2, u3;
create or replace function app.job_update_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
l_user_id number;
l_reg_date date;
begin
if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
begin
select user_id, reg_date into l_user_id, l_reg_date from tblss_user
where user_name = sys_context('userenv', 'session_user');
exception when no_data_found then return '1=2';
end;
if l_reg_date > sysdate - 365 then return '1=2'; end if;
return 'fkuser_id = ' || l_user_id;
end;
/
grant execute on app.job_update_security to u1, u2, u3;
Create the policies using the above functions:
BEGIN
DBMS_RLS.add_policy('APP', 'JOB', 'JOB_SELECT_POLICY',
'APP', 'job_select_security',
'SELECT', TRUE);
end;
/
begin
DBMS_RLS.add_policy('APP', 'JOB', 'JOB_UPDATE_POLICY',
'APP', 'job_update_security',
'UPDATE', TRUE);
END;
/
And now test. Selecting:
SQL> conn u1/u1
Connected.
SQL> select * from app.job;
JOB_ID JOB_NAME JOB_SALARY FKUSER_ID
---------- ------------------------- ---------- ----------
1 JOB1 1000 1
2 JOB2 1200 1
SQL> conn u2/u2
Connected.
SQL>
SQL> select * from app.job;
JOB_ID JOB_NAME JOB_SALARY FKUSER_ID
---------- ------------------------- ---------- ----------
3 JOB3 800 2
SQL> conn u3/u3
Connected.
SQL> select * from app.job;
no rows selected
Updating:
SQL> conn u1/u1
Connected.
SQL> update app.job set job_salary=1400;
2 rows updated.
SQL> select * from app.job;
JOB_ID JOB_NAME JOB_SALARY FKUSER_ID
---------- ------------------------- ---------- ----------
1 JOB1 1400 1
2 JOB2 1400 1
SQL> conn u2/u2
Connected.
SQL> update app.job set job_salary=1400;
0 rows updated.
SQL> select * from app.job;
JOB_ID JOB_NAME JOB_SALARY FKUSER_ID
---------- ------------------------- ---------- ----------
3 JOB3 800 2
SQL> conn u3/u3
Connected.
SQL> update app.job set job_salary=1400;
0 rows updated.