To give user only select privileges on table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: To give user only select privileges on table

  1. #1
    Join Date
    Sep 2000
    Posts
    103
    I 've created a new user called test but I want to see that he has only select privileges on the table.
    My problem is since all the users connect thru the front end and to access the tables in a particular schema,
    I have cretaed public synonym for all the tables and I've granted select,update,insert,delete to public privileges.
    NOw, this user from our other office want to see the data thru oracle so I want to give him access only to view the data.
    Even if I give revoke insert,delete,update on table_name from test,

    it doesnt work.

    Can you please let me know how to handle this? I need to send this userid id as soon as possible.

    Thanks a lot

    pst

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Maybe you have to revoke on the synonym and not the table?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    I just tested this and it seems that when you grant a privelege on a synonym you also grant it on the table, makes sense. So you may want to revoke on the syn as well as the table.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    pst,

    You have to change your grants structure. Granting all those privileges to PUBLIC can not restrict the 'TEST' user from getting all those privileges.

    What you can do is :

    Revoke all privileges from PUBLIC. Do not drop public synonyms.
    Create a ROLE.
    Grant all privileges on the public synonyms to this ROLE.
    Grant this ROLE to all users except this 'TEST' user.
    svk

  5. #5
    Join Date
    Sep 2000
    Posts
    103
    Hi,

    I've created a role now called grants_public_role
    and granted all privileges on the public synonym to this role.
    ie,
    grant select, update, insert, delete on table_name to public;

    And I granted this role to one user for testing. But he doesnt seem to see the tables at all.

    I cant understand where I'm going wrong.

    All help really appreciated.

    pst

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    If you want to all the user to select from all the tables then you have a table called product_user_profile.Just insert values for this table
    the values would be like the product i.e sql*plus.Just check ur documentation u will be able to find the info.
    cheese
    anandkl
    anandkl

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by pst
    Hi,

    I've created a role now called grants_public_role
    and granted all privileges on the public synonym to this role.
    ie,
    grant select, update, insert, delete on table_name to public;

    And I granted this role to one user for testing. But he doesnt seem to see the tables at all.

    I cant understand where I'm going wrong.

    All help really appreciated.

    pst
    Why don't you attach the SQL you used.

    MH
    I remember when this place was cool.

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