4 Replies Latest reply: Dec 6, 2012 2:26 PM by Panamanian RSS

    Cannot see table

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