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 ?
FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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.
0
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
0
sdstuberCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
0
sdstuberCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FutureDBA-Author Commented:
as always, you're very helpful.

I ended up using

:P0_UNAME := apex_authentication.get_login_username_cookie;

from

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/sec_authentication.htm#BABBCIEE

but your answer set me in the right direction. thanks
0
sdstuberCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.