Select Any Table Privilege With Exception?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Select Any Table Privilege With Exception?

  1. #1
    Join Date
    Jun 2001
    Posts
    103
    Hi All ,
    I have a problem with oracle system privilege. Suppose that I have a user with 'select any table' privilege which I granted this privilege via a role . I want to limit and protect this user , cannot select some specific tables in other users . I cannot revoke 'select any table' privilege because it causes very problems in my database . So I should revoke select on that specific tables from that user .
    How can I do that ?
    Is it possible ?
    My oracle server is 8i(8.1.6 EE).
    Please help me .

    Best Regards .


  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    U create a role and grant all the priveleges on the tables that u want to give to that role and grant that role to the user.The user will be able to select only from the tables which he has got the privelege to and revoke the SELECT ANY TABLE system privelege.


    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  3. #3
    Join Date
    Jun 2001
    Posts
    103
    Dear rohitsn ,
    As I said before , unfortunately I can't revoke 'select any table' privilege from role whitch granted to users . It may cause some problems in my applicationd for other users .
    I know that your solution is a good way , but it's very time consuming to me to change my way . So I want to find another way to revoke select on some specific table from that user who have that privilege .

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Considder using row level security feature on those tables that you don't want your user to select from. Basically you for those few tables you need to add a predicate like "WHERE 1=2" to be added to any query isued by this particular user.

    He will still be able to select from any table, but will get no rows from those special tables.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello Jurij


    When a user fires a query to the database how do i programtically add that predicate to his query every time he issues a select statement.

    regards
    Hrishy

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You wouldn't (normaly) do it programatically, you would define this functionality on system level and then let RDBMS to take care of it. For setting it up, look at Oracle manuals (check Application Developer's Guide-Fundamentals, chapter 11 -> Fine-Grained Access Control). For excelent detailed description of how to implement it, check the following article:

    http://osi.oracle.com/~tkyte/article2/index.html
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width