0

I'm trying to create a logon trigger that will restrict users views so that one user can select/ view jobs relating to them and only to them within the JOB table.

Additionally these new users must only have the ability to select the job table whereas users who have a REG_DATE > 1 year for instance will be able to edit job details within the JOB TABLE.

I don't know what the trigger should look like hence the reason i'm posting, also is a trigger ideal for this instance or should look towards using grants?

-----Below are my tables

    CREATE TABLE  tblss_user(
    user_id       INTEGER NOT NULL,
    user_name     VARCHAR(15),
    reg_date      DATE

user_id = pk

    CREATE TABLE job(
    job_id      INTEGER NOT NULL,
    job_name    VARCHAR(25),
    job_salary  INTEGER NOT NULL,
    fkuser_id   INTEGER NOT NULL

jobid = pk

fkuser_id = fk

thanks for your time!

3
  • Are you wanting a solution that basically filters rows out of the query, based on the type of user logged in? So if they do a simply SELECT * FROM table, you want that select to be filtered automatically?
    – Fandango68
    Feb 27, 2018 at 23:17
  • absolutely, just wasn't sure what the sytanx would look like
    – MH91
    Feb 27, 2018 at 23:22
  • Regarding users who have been at the job for a certain period of time like stated in the question, they must have the ability to edit the data so would that mean views will not be applicable for their case? Would I have to allow these users to edit at table level instead?
    – MH91
    Feb 27, 2018 at 23:36

1 Answer 1

1

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.
2
  • I think you missed the policy which associates the VPD function with the table. Feb 28, 2018 at 10:43
  • @Wernfried Domscheit Indeed, thank you, I added them. Feb 28, 2018 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.