Let’s take a closer look at the database link. In this simple example, no mention is made of the user ID that is used to establish the connection on the remote database. Because Scott is the user connecting to SQL*Plus, Scott will be the user ID when the remote connection is established to the London database. Therefore, Scott must have SELECT privileges against the employee table in London in order for the query to work properly. Scott’s privileges on the initiating Oracle have no bearing on the success of the query.

---

Note:  If you are using the Oracle Names facility, you must be sure that your database service names are the same as the global_databases_names and the DOMAIN init.ora parameter.

---

In cases where SELECT security is not an issue, you can enhance the database link syntax to include a remote connect description:

CREATE DATABASE LINK LONDON USING 'LONDON'
CONNECT TO scott1 IDENTIFIED BY tiger1;

This way, all users who specify the LONDON database link will connect as scott1 and will have whatever privileges that scott1 has on the London system.

Once you establish a communications pathway to the remote database, it is often desirable to implement location transparency. In relational databases such as Oracle, you can obtain location transparency by creating database links to the remote database and then assigning a global synonym to the remote tables. The database link specifies a link name and an SQL*Net service name. You can create database links with a location suffix that is associated with a host name (in this example, london_unix).

You can use database links to allow applications to point to other databases without altering the application code. For data warehousing applications, you can replicate a table on another machine and establish links to enable the application to point transparently to the new box containing the replicated table.

To see the links for a database, query the Oracle dictionary:

SELECT DISTINCT db_link FROM dba_db_links;

Keep in mind that SQL*Net bypasses all operating system connections when it connects to a database. All user accounts that are identified externally (that is, without an Oracle password) will not be allowed in SQL*Net transactions unless the init.ora parameter is changed. The IDENTIFIED EXTERNALLY clause (OPS$) in Oracle version 6 allowed the operating system to manage passwords, but because SQL*Net bypasses the operating system, impostor accounts could be created from other platforms. The result? Security was bypassed. Consequently, Oracle now recommends that IDENTIFIED EXTERNALLY accounts be forbidden for distributed connections.

It is interesting to note that Oracle will allow you to create accounts with an OPS$ prefix. Therefore, the operating system can manage its passwords, while you also have passwords within Oracle. For example, assume the following user definition:

CREATE USER ops$scott IDENTIFIED BY tiger;

Assuming that Scott has logged onto the operating system, Scott could enter SQL*Plus either with or without a password:

sqlplus /
sqlplus scott/tiger

This ability to connect directly to Oracle presents a confounding issue with password management. Because two sets of passwords exist—one in the operating system and another in Oracle—you may need a third-party tool to keep the passwords synchronized. (See Figure 8.5.)

Figure 8.5  Centralized password management tool architecture.

Understanding The SQL*Net Listener

To see what the Oracle listener is doing, Oracle provides a series of listener commands, which include:

•  LSNRCTL RELOAD—Refreshes the listener

•  LSNRCTL START—Starts the listener

•  LSNRCTL STOP—Stops the listener

•  LSNRCTL STATUS—Shows the status of the listener

Listing 8.3 shows the output of the LSNRCTL status command.

Listing 8.3 Output of the LSNRCTL status command.

[oracle]ram2: lsnrctl stat

LSNRCTL for HPUX: Version 2.0.15.0.0 - Production on 16-SEP-94 15:38:00

Copyright (a)  Oracle Corporation 1993.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=ram2)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for HPUX: Version 2.0.15.0.0 -
                          Production
Start Date                29-AUG-94 13:50:16
Uptime                    18 days 1 hr. 47 min. 45 sec
Trace Level               off
Security                  OFF
Listener Parameter File   /etc/listener.ora
Listener Log File         /usr/oracle/network/log/listener.log
Services Summary...
  dev7db                has 1 service handlers
  ram2db                has 1 service handlers
The command completed successfully

lsnrctl services     - lists all servers and dispatchers

[oracle]seagull: lsnrctl services

LSNRCTL for HPUX: Version 2.0.15.0.0 - Production on 16-SEP-94 15:36:47

Copyright (a)  Oracle Corporation 1993. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=seagull)(PORT=1521))
Services Summary...
  tdb000                has 4 service handlers
    DISPATCHER established:1 refused:0 current:2 max:55 state:ready
      D001 (machine: seagull, pid: 4146)
      (ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1323))
    DISPATCHER established:1 refused:0 current:2 max:55 state:ready
      D000 (machine: seagull, pid: 4145)
      (ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1321))
    DISPATCHER established:0 refused:0 current:1 max:55 state:ready
      D002 (machine: seagull, pid: 4147)
      (ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1325))
    DEDICATED SERVER established:0 refused:0
The command completed successfully