• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

select Results based on Authentication.

I have various users connected to 3 databases. I would like for users to only see results based on what they have entered.

is this possible ?
  • 4
  • 3
1 Solution
This sounds like Virtual Private Databases,  or Row-Level Security.
It is possible and supported using the dbms_rls package.

You can simulate it with views that include a check like "data_owner = USER"  in them.
and your underlying tables include a "data_owner" column.

The view method  is essentially how Oracle's own data dictionary works with the USER_XXX and ALL_XXXX type views.
FutureDBA-Author Commented:
I thought about doing it with the data_owner = 'USER',

my tables do have the equivalent of a data_owner column.

My main thing is, I want to use this with Oracle APEX and Database Authentication, doing it with this method, I would have to make multiple copies of the same app for individual users.

Doesn't seem efficient
no, as long as your apex app only used the views and not the real tables, then the security would be enforced at the database/view level,  not in the APEX code.  

If you must use the tables and not views, then look into the dbms_rls package.  It will allow you to apply the ownership rules on any table, regardless of the method you use to access them.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

FutureDBA-Author Commented:
This is the scenario.

I have a Master / Detail form in apex.

Database level authentication.

7 users..

all users write to the same DB,

I want each individual user to only be able to see his data that he enters or has entered in the past.
create views on the master and detail tables with the "owner = USER" clauses.

change your apex forms to use the views instead of the tables
leave everything else the same

using other authentication schemes, change USER to be some kind of lookup of APEX session state user info.
FutureDBA-Author Commented:
as always, you're very helpful.

I ended up using

:P0_UNAME := apex_authentication.get_login_username_cookie;



but your answer set me in the right direction. thanks
glad I could help,  one little thing though.

I just noticed you had 'USER' in quotes in your reply above. Now I see why you were thinking you'd need an app for each user.

I mean USER - the function, no quotes.  That would return the name of the db user connected, but could, as mentioned above be any other function, including APEX functions to determine which user was authenticated.

In any case, you "can" put your logic into the APEX app itself, but putting it views or vpd policies would be more secure and versatile; because then you wouldn't need to remember to reimplement the rules every where.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now