Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.