Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tanul.

Asked: October 18, 2016 - 7:31 pm UTC

Last updated: October 19, 2016 - 2:26 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I've given grant to one user on a table but user unable to access the table though I'm getting output as grant succeeded. The scenario is
1. There is one database A on remote location.
2. A is trying to access some table on database B(place on other location) using DBLink(User@DBLINK).
3. Inside database B, we've the user and provided select grants to this user on a table placed in some other schema C on the same database(B).

Query is:- grant select on C.Table to User;

When we're trying to access C.Table from database A, we get no table exist.

Could yo u please suggest?

and Connor said...

Works fine for me - here's a demo.

--
-- database 1
--
SQL> create user B identified by B;

User created.

SQL> grant create session to B;

Grant succeeded.

SQL> create user C identified by C;

User created.

SQL> alter user C quota 100m on users;

User altered.

SQL> create table c.demo as select * from all_objects;

Table created.

SQL> grant select on c.demo to b;

Grant succeeded.

--
-- database 2
--
SQL> create user A identified by A;

User created.

SQL> grant create session, create database link to a;

Grant succeeded.

SQL> connect A/A
Connected.

SQL> create database link remote connect to B identified by B using 'db1';

Database link created.

SQL> select count(*) from c.demo@remote;

  COUNT(*)
----------
     96893

1 row selected.

SQL>


Compare this with your setup script and look for differences.

Is this answer out of date? If it is, please let us know via a Comment