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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Waseem.

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

Last updated: October 26, 2022 - 3:57 am UTC

Version: 12.1.0.2

Viewed 100K+ 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

  (6 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

Packages

Vic, October 13, 2022 - 11:09 am UTC

If you call a table via dblink its work.

But if you call a package it doesnt work.

For example.

User A:
Create view AAA as select package.function@dblink(parameter) from dual

Grant select on AAA to B

User B:
Select * from AAA

ORA-04504
Chris Saxon
October 13, 2022 - 12:30 pm UTC

What is error ORA-04504? Do you mean ORA-04054 DB link doesn't exist?

If so, have you verified the DB link name? What is the statement that does work?

Data

Vic, October 13, 2022 - 1:57 pm UTC

Thank you for your response.

Yes the error is ORA-04054.

I have a view in a schema A:

CREATE OR REPLACE FORCE VIEW AAA
(SELECT COUNT (C.COLUMN1)
FROM TABLE1@DBLINK1 C)

The dblink DBLINK1 was created in schema A.

With schema A: If I made a SELECT * FROM AAA, it works.

I grant the view to another schema called B:
GRANT SELECT ON AAA TO B

If I made a SELECT * FROM AAA, it works. Like in you example.
---

Now the same but adding a package call in where clause, for example:
CREATE OR REPLACE FORCE VIEW AAA
(SELECT COUNT (C.COLUMN1)
FROM TABLE1@DBLINK1 C
WHERE PACKAGE_NAME_FUNCTION_NAME@DBLINK1 (PARAMETER) = 'N')

With schema A: If I made a SELECT * FROM AAA, it works.

If I made a SELECT * FROM A.AAA, in schema B, it returns:
ORA-04054 database link string does not exist

It is really annoying

Thanks.

Connor McDonald
October 26, 2022 - 3:57 am UTC

Unfortunately that is just the way it is.

I'm generally not a fan of dblinks in view definitions, because if you are building a view, then you may as well build that view on the source system and then access just the view via the dblink.

Where dblink in view the better/only path

Clay, July 06, 2023 - 9:43 pm UTC

I agree with "generally not a fan of dblinks in view definitions, because if you are building a view, then you may as well build that view on the source system and then access just the view via the dblink", however, in our organization, we cannot make database links public so providing access to the link is not option. Our only option is to create the view with the embedded db link and then grant permissions to the view.