4 Replies Latest reply on Dec 6, 2012 8:26 PM by Panamanian

    Cannot see table

      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;


      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
          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
            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
              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
                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;