This discussion is archived
4 Replies Latest reply: Dec 6, 2012 12:26 PM by Panamanian RSS

Cannot see table

Panamanian Newbie
Currently Being Moderated
Table TEST was created by user SYSTEM to USER1.

Create Table USER1.TEST
(
field1 VARCHAR2(10)
);

Then user SYSTEM, created the public synonym:
Create Public Synonym TEST for USER1.TEST;

also.

Grant Select on USER2.TEST for CONNECT;

(USER2 has: CONNECT and RESOURCE privilidges).

USER2 cannot see table TEST. The only way is by putting the USER1.TEST
But is there any other way to proceed without "user.table" = user1.test?
  • 1. Re: Cannot see table
    ZA Journeyer
    Currently Being Moderated
    Grant SELECT rights on user1 table directly to the user2 instead of through role.

    GRANT SELECT on USER1.TEST to USER2;

    This should work!
  • 2. Re: Cannot see table
    MLBrown Journeyer
    Currently Being Moderated
    >
    Grant Select on USER2.TEST for CONNECT;
    >

    You are granting select on the TEST table owned by USER2, but according to the logic above that statement, USER1 owns the table. The grant should be:
    Grant Select on USER1.TEST to CONNECT;
    BUT ... I would never grant privs to one of the system privs like CONNECT, RESOURCE, etc... You should create a role just for your users and grant access to that role instead. You can then grant that role to your different users.
  • 3. Re: Cannot see table
    MLBrown Journeyer
    Currently Being Moderated
    Also... if you didn't want to create another role you could always grant the privs to PUBLIC:
    grant select on user1.test to PUBLIC;
    Just another option...
  • 4. Re: Cannot see table
    Panamanian Newbie
    Currently Being Moderated
    I'm sorry, but none of the options, didnt work. :(
    Nevermind, since I need them very urgently, i 'm just using "USER.TABLE" = select field1 from user1.test;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points