How to restrict user to certain tables

660227

Hi All

I have a third party application that I look after and we are needing to open up access to the Oracle 10g database to an outside company. What I would like to do is restrict their user so they can only select,insert,update on a couple of tables.

The main problem with this is that the way the database has been designed for the application is all the tables are owned by one user. All the tables have been granted ALL to public. So if I create a user in this DB then by default they have access to all the tables PUBLIC does !

I don't have many options as I can't change the structure as this would have serious impacts on the application so I have to work with what I've got.

Even with PUBLIC having access to all the tables is there still a way I can restrict a user to certain tables ?

521217

I think Virtual Private Database can be a solution

Pavan Kumar

Hi,

My suggesstion you can implement with Roles.
First thing is that Create a Role
Check what are the required privleges on the tables as per the application and grant those to Role (assing to Role)
Revoke the Privleges given to the Public and assign the New Created Role to the Rest of the Users.
Coming to New User which eer you are required to Give to the Third party. (For this Grant only the Specific Privleges)
on the Specific tables Required.
You might will be solved... !!

There are still alternative Solutions for this.. !!

- Pavan Kumar N

Fco Munoz Alvarez

Hi,

You can create a trigger that each time the user x try to do something to a object (select, insert,update,delete,execute,etc) this trigger will check if the object is included in one table called e.g.: app_privs and if is there check the type of privileges allowed. This will give a little of work to implement but will solve your problem.

Cheers,

Francisco Munoz Alvarez
http://www.oraclenz.com

NavneetU

Hi,
You can use Product_profile table in System user to restrict the particular user for some particular actions like Select ,insert ,update etc.
you have to populate this table with the user information ,table which you dont want to show and action and command you want to restrict.

Regards,
Navneet

Aman....

Good thought Navneet but correct me if I am wrong,this will only put restriction for a user if he/she is using Sql*Plus. Its been a long time since I have used it,last was about 3 years ago so I may be wrong but vaguely I can recall,its only meant for restrictions in sql*plus module.
As per OP,he is going to use an application,there this restriction wont serve him.
Cheers
Aman....

NavneetU

Hi Aman,

THanks for your thoughts on my comments.
Ya i have used this for Sql*plus but never tried it with anyother application. But i think we can give it a try for once.
THanks again.

Regards,
Navneet

Aman....

Navneet,
I am afraid for its use other than sql*plus. A quick search over google gave this as first hit and this proves that we can't use it for any other wayout.
https://netfiles.uiuc.edu/jstrode/www/oraplus/product_profile.html
I am not sure about other wayout of using it.
Regards
Aman....

NavneetU

Thanks Aman to make me correct.

Regads,
Navneet

Aman....

Your welcome,infact I asked you to correct me ;-). I am still not sure that my assertion is correct or not.
Regards
Aman....

NavneetU

Aman,
Going through the link you suggested, it looks if we can only restrict iser activities through sqlplus. but yes i will try it out to do it with some other application if it works.
But in mean time if you find something significant please let me know also. because knowledge is there to share.

Thanks and regards,
navneet