CODEWITHSUNDEEP
oracle-databaseoracle10gdatabase-schemasql-grantsynonym
Rodrick
Rodrick

Reputation: 615

Why I can create a synonym but no give grant select to the same table?

I am trying to grant access to a table from schema1 to schema2 (oracle10g).

I created a synonym on schema2:

CREATE SYNONYM schema2.table FOR schema1.table;

The synonym was created succesfully.

Then I tried to grant select on the same table:

grant select on schema1.table to schema2;

I got:

ORA-00942: table or view does not exist

This doesn't make sense to me. I was able to create the synonym but not the grant. What I am doing wrong?

I am not able to get the table from schema2:

select * from table;
ORA-00942: table or view does not exist

Upvotes: 2

Views: 5480

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

If I have "CREATE ANY SYNONYM| rights, I can create the synonym for the table in schema 1 in schema 2 without needing grants on the underlying objects. If I don't have rights on the schema1 table (GRANT WITH GRANT OPTION) to re-grant it to another user, then I can't also do the grant from this user.

Solution, log in as schema1 and do the grant there and then the synonym will work under schema2, or ensure that the user I AM logged in under has the rights to confer the grant on the schema1 object.

Per your comment:

Log in as schema1 and grant whichever operations you want schema2 to have on your table.

e.g)

SQL> GRANT SELECT, INSERT, UPDATE, DELETE on TABLE to SCHEMA2;

SCHEMA2 will then be able to see the table through its synonym, and be permitted those operations on it.

If SCHEMA2 is going to use this table in a view that it will then be granting select access to other schemas to use, then you need to add "WITH GRANT OPTION" to the initial grant from schema1 or schema2 will not be able to re-grant permissions on to other users.

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132590

You can create synonyms for objects that don't actually exist e.g.

create synonym flub for blib;

...so the fact that you were able to create a synonym does not mean the objects exists.

Upvotes: 2

Related Questions