Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    121

    Question Unanswered: Granting Privileges

    Hi
    I'm relatively new to Oracle security etc... I want to grant just query only privileges to user_A on specific tables. Thus far, I granted user_A the SELECT privileges on these tables, as well as the CONNECT privilege. However, when user_A attempts to query these tables, he gets the following error message... "table or view does not exist." Is ther additional privileges that he requires?


    thanks
    TM


  2. #2
    Join Date
    Apr 2004
    Posts
    246
    did you make synonyms, or prefix the table with the schema owner in the queries which fail?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Aug 2002
    Posts
    121

    Talking

    Thanks shoblock.. I did and it works fine now thanks.


  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not privileges. But when USER_A runs "SELECT * FROM tablex" Oracle by default will look for a table called tablex that is owned by USER_A. The table name needs to be prefixed with the owner: "SELECT * FROM user_b.tablex". However, you can avoid the need for the owner prefix in a few different ways:

    1) USER_A can run "ALTER SESSION SET CURRENT_SCHEMA = user_b;"

    Now Oracle will look in the USER_B schema rather than in USER_A's schema.

    2) USER_A can create a private synonym for each USER_B table:

    CREATE SYNONYM tablex FOR user_b.tablex;

    3) A DBA can create a public synonym for each USER_B table:

    CREATE PUBLIC SYNONYM tablex FOR user_b.tablex;

    This will work for all users, not just USER_A.

Posting Permissions

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