CODEWITHSUNDEEP
oracle11goracle-apex-5.1
blank
blank

Reputation: 21

Oracle Apex 5.1 custom authorization scheme that only the user can view the data that is inserted on a table

How can i create a authorization scheme on oracle apex 5.1 that work like a facebook

example user1 and user2 same table

user1 log in with his/her account and inserted data and can view the data user2 logged in to his/her account cannot view the data that user1 inserted

vice-versa

Upvotes: 1

Views: 670

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

APEX Authorization Schemes control access to APEX components such as pages, regions, items etc. but not to rows of your data.

The proper Oracle solution to this requirement would be Virtual Private Database (VPD) which enforces your rules across all queries the restrict which rows a user can see. However to use this requires Oracle Enterprise Edition.

The simple method that will work in any edition of Oracle is:

  1. Each table needs a column stamped with the ID of the user that created it e.g. CREATED_BY. You could use a database trigger to ensure it is always populated with the APEX username. (NB The VPD method would require this column too.)

  2. In each report query you add the filter e.g.:

    and e.created_by = :APP_USER

Of course there is always the risk you will forget to add the filter somewhere. A better solution that avoids that would be to create views that apply the filter e.g.

create view apex_employees as
  select * from employees
  where created_by = (select v('APP_USER') from dual);

Then use schemas and grants to ensure that APEX can only see the view, not the table. Now there is no need to add the filters to each report query. This solution is sometimes called "poor-man's VPD".

Upvotes: 2

Related Questions