1

While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink.

However, this was only true for databases that had no value for DB_DOMAIN. If the local database's DB_DOMAIN was qualified with "foo.com", then a user could not query the remote DB and required a dblink.

I could not find any documentation that explains why this happens and am a very new DBA. What is it about Oracle that allows this to happen? Is the only way to prevent this to qualify every database's DB_DOMAIN with "foo.com"?

Example of accessing information from remote DB without a dblink below:

From DB: FooB

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:05:21 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string

From DB:FooA

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:08:25 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
db_domain                            string
SQL> select sysdate from dual@FooB;

SYSDATE
---------
26-FEB-15


SQL> select db_link from all_db_links;

no rows selected
2

What you described here is absolutely normal and intended. This feature is called a global database link:

http://docs.oracle.com/cd/E24693_01/server.11203/e17120/ds_concepts002.htm#i1007888

It is a "side effect" when for example Oracle Internet Directory is in use, I have seen this at a few companies who have a lot of databases.

More and better explanation can be found in the support note: What are Global Database Link and How do you Disable them? (Doc ID 1632329.1)

You are able to make a distributed connection using a Database link, However you have not created the database link used.

You expect an ORA-2019 since the Database link Object itself does not exist but somehow the connection works. How is this possible?

This is because of a default feature Called GLOBAL DATABASE LINKS. The feature is due to LDAP usage.

Global Database links were introduced in earlier versions of Oracle when the Oracle Names Server was in use. You could create a Database Link that was stored in the Oracle Names server and accessed by those who accessed the Oracle Names Server. Once Oracle Names was deprecated the feature was moved to the replacement LDAP Naming.

When a Database link is called the database link name will be checked against the PRIVATE link then PUBLIC links then GLOBAL links. If the Database link name matches the entry listed in the ldap server the actual Database link object is not required. The DBLINK will use the current user to connect therefore it only works if the user has access to both instances and has the same user id and same password on both.

This is default behavior when LDAP naming is used.

Currently the only way to disable the feature is to stop using LDAP naming.

1

I now see that Balasz has provided a solution, too. My solution works if the name of the database is FooB, and I see now that that isn't the case here. So Balas will be right. But I leave this post because it seems not to be widely known that this works.


From Administrator's Guide, 31 Distributed Database Concepts,

Global Name as a Loopback Database Link You can use the global name of a database as a loopback database link without explicitly creating a database link. When the database link in a SQL statement matches the global name of the current database, the database link is effectively ignored.

For example, assume the global name of a database is db1.example.com. You can run the following SQL statement on this database:

SELECT * FROM hr.employees@db1.example.com;

In this case, the @db1.example.com portion of the SQL statement is effectively ignored.


If a directory service is in use (ldap server) global database link can be defined network wide outside of the database.

No user owns the global database link. The global database link exists in a directory service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.