Skip to Main Content
  • Questions
  • Grant Access on DB_Link to other User

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Waseem.

Asked: July 11, 2016 - 5:10 pm UTC

Last updated: January 19, 2022 - 6:36 pm UTC

Version: 12.1.0.2

Viewed 50K+ times! This question is

You Asked

I need to give access to other user on current DB_LINK, can you provide command ?

Current Link: sittest
Grant to User: Z9XBIQ1_DW

Regards
Chaudhry

and Connor said...

Database links are public (everyone) or private (only one).

Let's say you had this:

user A: has a db link to a remote database
user B: you want to allow access via A to object X in remote database

Then in user A, you could do (for example):

create view REMOTE_X as select * from X@dblink;

and then grant access to REMOTE_X to B.

SQL> grant connect to demo identified by demo;

Grant succeeded.

SQL> create view VVV as select * from tab@db11;

View created.

SQL> grant select on VVV to demo;

Grant succeeded.

SQL> conn demo/demo
Connected.

SQL> select * from mcdonac.vvv;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ACCOUNT_EXTRACT                TABLE
ANY_OTHER_TABLE                TABLE
BLAH                           TABLE
...


Rating

  (3 ratings)

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

Comments

thansk Tom

JUSTIN RAJU, October 11, 2018 - 9:36 pm UTC

Thanks Tom (connar) for sharing the information it did help.

how can i check how many user are accessing this Public DB link.
Connor McDonald
October 12, 2018 - 2:55 am UTC

If you run AUDIT on database links, you get records based on create/alter/drop of links. But you can also audit SELECT statements - then the audit records for the SELECT will contain information about the db link used, eg

SQL> set lines 1000
SQL> column userid format a15
SQL> column ntimestamp# format a30
SQL> column sqltext format a40
SQL> column comment$text format a200
SQL> select userid, ntimestamp#, sqltext, comment$text from aud$ where comment$text like '%DBLINK%';

USERID          NTIMESTAMP#                    SQLTEXT                                  COMMENT$TEXT
--------------- ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MARK            26-OCT-15 12.49.55.219992 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 12.51.34.476959 PM   SELECT 0 FROM "GIGI" "A1"                DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 12.51.59.086081 PM   SELECT 0 FROM "GIGI" "A1"                DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.00.37.517809 PM                                            Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=52438)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.00.37.527570 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.01.36.310674 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.05.07.564925 PM                                            Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=52725)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.05.07.595509 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.08.54.204852 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.09.34.166246 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.09.59.877822 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.10.29.447086 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.10.38.853625 PM                                            DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.10.46.192234 PM                                            Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=53072)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.10.46.199130 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.10.57.918255 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.11.09.381775 PM                                            DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.11.31.318063 PM                                            Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=53119)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 01.11.31.323628 PM   SELECT "A1"."COL1" FROM "GIGI" "A1"      DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK            26-OCT-15 03.50.24.076062 PM                                            DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)



(Taken from MOS NOte 2071002.1)

Rachel Barker, May 07, 2021 - 2:04 pm UTC

I have been debating the easiest way to do this. As always you have a great answer. Thank you Connor!
Connor McDonald
May 10, 2021 - 7:51 am UTC

glad we could help

Flo, January 19, 2022 - 1:49 pm UTC

What if object X in the remote DB is a synonym to another user's table? It doesn't seem to work. Is it possible to do that?
Chris Saxon
January 19, 2022 - 6:36 pm UTC

You can use synonyms over db links - you still need access to the underlying objects though