CODEWITHSUNDEEP
databaseoracle-databaseprivilegessynonym
swathi manda
swathi manda

Reputation: 131

Does creating a synonym automatically grant select access on underlying table in oracle

I have two users - schema1 and schema 2 in our oracle database.

I created a table and synonym in schema1 as follows

Create table test ( user varchar2(25)) ;

Create or replace synonym schema2.test for schema1.test

I observed a difference in behavior while I was trying to query test table from schema2 between our development and production environments.

select * from test
in our development environment did not throw any error.

But when I ran same command in production ,I got insufficient privileages error for schema2. I had to explicitly give select grant on test table to schema2 in production.

Why this difference could have arised between dev and prod environments?

Does creating a synonym automatically grant select access to an underlying table in oracle?

Upvotes: 0

Views: 995

Answers (1)

Alex Poole
Alex Poole

Reputation: 191295

No, it doesn't. From the documentation:

However synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

Assuming privileges on the table haven't been granted without you realising or remembering, directly or via a role (which you could check easily of course), it sounds like schema2 in your development environment has 'any' privileges - such as select any table - granted as a shortcut, probably for convenience. That could be directly, or via a role.

Again from the documentation

The READ ANY TABLE or SELECT ANY TABLE system privilege also allows you to select data from any table, materialized view, analytic view, or hierarchy, or the base table of any materialized view, analytic view, or hierarchy.

Those sorts of privileges shouldn't be granted lightly, so it wouldn't be surprising for them not to be there in production. If they must be there in development - and someone must have agreed that - then an intermediate test or model environment which has the same restrictions as production would be useful to identify discrepancies, such as missing grants, before you try to deploy to production.

Upvotes: 4

Related Questions