Forum Stats

  • 3,853,736 Users
  • 2,264,259 Discussions
  • 7,905,437 Comments

Discussions

How to restrict user to certain tables

660227
660227 Member Posts: 2
edited Sep 17, 2008 8:57AM in General Database Discussions
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 ?
Tagged:
«1

Answers

  • 521217
    521217 Member Posts: 845
    I think Virtual Private Database can be a solution
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    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
    Fco Munoz Alvarez Member Posts: 926 Bronze Badge
    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
    NavneetU Member Posts: 1,724 Gold Badge
    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....
    Aman.... Member Posts: 22,919 Gold Crown
    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
    NavneetU Member Posts: 1,724 Gold Badge
    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....
    Aman.... Member Posts: 22,919 Gold Crown
    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
    NavneetU Member Posts: 1,724 Gold Badge
    Thanks Aman to make me correct.

    Regads,
    Navneet
  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown
    Your welcome,infact I asked you to correct me ;-). I am still not sure that my assertion is correct or not.
    Regards
    Aman....
  • NavneetU
    NavneetU Member Posts: 1,724 Gold Badge
    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
This discussion has been closed.