CODEWITHSUNDEEP
oracle-database
eXamScripts
eXamScripts

Reputation: 447

database user having grants to same table in 2 schemas

I tried the following - and was expecting an error but it seems to work
User1 is given select grants to table XYZ which is present in two schemas
test1.XYZ and test2.XYZ

Now this user - User1 is given 'SELECT' grants to the table XYZ present in both schemas - test1 and test2

I was expecting that the following select would give an exception of something like 'ambiguous' etc etc - but it worked: ( logged in as User1 ) 

select * from XYZ;

It seemed to get the details from XYZ table in schema test1.
Kind of confused why this would work.

Upvotes: 0

Views: 378

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Assuming that does not exist a table with that name in your schema, it probably depends on synonyms; for example, create tables with same name in different schemas

SQL> conn test1/test1@main
Connected.
SQL> create table xyz(id number);

Table created.

SQL> grant select on xyz to user1;

Grant succeeded.

SQL> conn test2/test2@yourDB
Connected.
SQL> create table xyz(id number);

Table created.

SQL> grant select on xyz to user1;

Grant succeeded.

This way your user will have no doubt, but will not see any of the tables:

SQL> conn user1/user1@yourDB
Connected.
SQL> select * from xyz;
select * from xyz
              *
ERROR at line 1:
ORA-00942: table or view does not exist

If you add the schema, you can query:

SQL> select * from test1.xyz;

no rows selected

SQL> select * from test2.xyz;

no rows selected

Now you create a public synonym for one of your tables:

SQL> conn test1/test1@yourDB
Connected.
SQL> create public synonym xyz for xyz;

Synonym created.

This way your user will see only the table with the synonym:

SQL> conn user1/user1@yourDB
Connected.
SQL> select * from xyz;

no rows selected

Even after creating synonym, you can access tables by writing 'schema.table'.

Upvotes: 0

Related Questions