Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes how to manage and maintain a distributed database system and contains the following topics:
In a distributed database system, each database should have a unique global database name. Global database names uniquely identify a database in the system. A primary administration task in a distributed system is managing the creation and alteration of global database names.
This section contains the following topics:
A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:
These are examples of valid global database names:
The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database's global name. At this point, the database's global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.
The name that you give to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remote database enforces global naming, then you must use the remote database's global database name as the name of the link. For example, if you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use mfg's global database name as the link name.
You can also use service names as part of the database link name. For example, if you use the service names sn1 and sn2 to connect to database hq.acme.com, and hq enforces global naming, then you can create the following link names to hq:
HQ.ACME.COM@SN1HQ.ACME.COM@SN2
|
See Also: "Using Connection Qualifiers to Specify Service Names Within Link Names" for more information about using services names in link names |
To determine whether global naming on a database is enforced on a database, either examine the database's initialization parameter file or query the V$PARAMETER view. For example, to see whether global naming is enforced on mfg, you could start a session on mfg and then create and execute the following globalnames.sql script (sample output included):
COL NAME FORMAT A12 COL VALUE FORMAT A6 SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names' / SQL> @globalnames NAME VALUE ------------ ------ global_names FALSE
Use the data dictionary view GLOBAL_NAME to view the database's global name. For example, issue the following:
SELECT * FROM GLOBAL_NAME; GLOBAL_NAME ------------------------------------------------------------------------------- SALES.AU.ORACLE.COM
Use the ALTER DATABASE statement to change the domain in a database's global name. Note that after the database is created, changing the initialization parameter DB_DOMAIN has no effect on the global database name or on the resolution of database link names.
The following example shows the syntax for the renaming statement, where database is a database name and domain is the network domain:
ALTER DATABASE RENAME GLOBAL_NAME TOdatabase.domain;
Use the following procedure to change the domain in a global database name:
SELECT * FROM GLOBAL_NAME; GLOBAL_NAME ---------------------------------------------------------------------------- SALES.AU.ORACLE.COM
ALTER DATABASE statement. For example, enter:
ALTER DATABASE RENAME GLOBAL_NAME TO sales.us.oracle.com;
GLOBAL_NAME table to check the new name. For example, enter:
SELECT * FROMGLOBAL_NAME;GLOBAL_NAME ----------------------------------------------------------------------------SALES.US.ORACLE.COM
In this scenario, you change the domain part of the global database name of the local database. You also create database links using partially-specified global names to test how Oracle resolves the names. You discover that Oracle resolves the partial names using the domain part of the current global database name of the local database, not the value for the initialization parameter DB_DOMAIN.
SALES.US.ACME.COM and query the GLOBAL_NAME data dictionary view to determine the database's current global name:
CONNECT SYSTEM/password@sales.us.acme.com SELECT * FROM GLOBAL_NAME; GLOBAL_NAME ---------------------------------------------------------------------------- SALES.US.ACME.COM
V$PARAMETER view to determine the current setting for the DB_DOMAIN initialization parameter:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain'; NAME VALUE --------- ----------- db_domain US.ACME.COM
hq, using only a partially-specified global name:
CREATE DATABASE LINK hq USING 'sales';
Oracle expands the global database name for this link by appending the domain part of the global database name of the local database to the name of the database specified in the link.
USER_DB_LINKS to determine which domain name Oracle uses to resolve the partially specified global database name:
SELECT DB_LINK FROM USER_DB_LINKS; DB_LINK ------------------ HQ.US.ACME.COM
This result indicates that the domain part of the global database name of the local database is us.acme.com. Oracle uses this domain in resolving partial database link names when the database link is created.
sales database will move to Japan, you rename the sales database to sales.jp.acme.com:
ALTER DATABASE RENAME GLOBAL_NAME TO sales.jp.acme.com;SELECT * FROM GLOBAL_NAME;GLOBAL_NAME ----------------------------------------------------------------------------SALES.JP.ACME.COM
V$PARAMETER again and discover that the value of DB_DOMAIN is not changed, although you renamed the domain part of the global database name:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain'; NAME VALUE --------- ----------- db_domain US.ACME.COM
This result indicates that the value of the DB_DOMAIN initialization parameter is independent of the ALTER DATABASE RENAME GLOBAL_NAME statement. The ALTER DATABASE statement determines the domain of the global database name, not the DB_DOMAIN initialization parameter (although it is good practice to alter DB_DOMAIN to reflect the new domain name).
supply, and then query USER_DB_LINKS to see how Oracle resolves the domain part of supply's global database name:
CREATE DATABASE LINK supply USING 'supply'; SELECT DB_LINK FROM USER_DB_LINKS; DB_LINK ------------------ HQ.US.ACME.COM SUPPLY.JP.ACME.COM
This result indicates that Oracle resolves the partially specified link name by using the domain jp.acme.com. This domain is used when the link is created because it is the domain part of the global database name of the local database. Oracle does not use the DB_DOMAIN initialization parameter setting when resolving the partial link name.
sales will be in the ASIA.JP.ACME.COM domain, not the JP.ACME.COM domain. Consequently, you rename the global database name as follows:
ALTER DATABASE RENAME GLOBAL_NAME TO sales.asia.jp.acme.com; SELECT * FROM GLOBAL_NAME; GLOBAL_NAME ---------------------------------------------------------------------------- SALES.ASIA.JP.ACME.COM
V$PARAMETER to again check the setting for the parameter DB_DOMAIN:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain'; NAME VALUE ---------- ----------- db_domain US.ACME.COM
The result indicates that the domain setting in the parameter file is exactly the same as it was before you issued either of the ALTER DATABASE RENAME statements.
warehouse database and again query USER_DB_LINKS to determine how Oracle resolves the partially-specified global name:
CREATE DATABASE LINK warehouse USING 'warehouse'; SELECT DB_LINK FROM USER_DB_LINKS; DB_LINK ------------------ HQ.US.ACME.COM SUPPLY.JP.ACME.COM WAREHOUSE.ASIA.JP.ACME.COM
Again, you see that Oracle uses the domain part of the global database name of the local database to expand the partial link name during link creation.
| See Also:
Oracle9i Database Reference for more information about specifying the |
To support application access to the data and schema objects throughout a distributed database system, you must create all necessary database links. This section contains the following topics:
A database link is a pointer in the local database that allows you to access objects on a remote database. To create a private database link, you must have been granted the proper privileges. The following table illustrates which privileges are required on which database for which type of link:
To see which privileges you currently have available, query ROLE_SYS_PRIVS. For example, you could create and execute the following privs.sql script (sample output included):
SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK', 'CREATE PUBLIC DATABASE LINK') / SQL> @privs Database Link Privileges ---------------------------------------- CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK CREATE SESSION
When you create a database link, you must decide who will have access to it. The following sections describe how to create the three basic types of links:
To create a private database link, specify the following (where link_name is the global database name or an arbitrary link name):
CREATE DATABASE LINK link_name ...;
Following are examples of private database links:
| This SQL Statement... | Creates... |
|---|---|
|
|
A private link using the global database name to the remote The link uses the userid/password of the connected user. So if |
|
|
A private fixed user link called |
|
|
A private link called Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links"). Current user links are part of the Oracle Advanced Security option. |
| See Also:
Oracle9i SQL Reference for |
To create a public database link, use the keyword PUBLIC (where link_name is the global database name or an arbitrary link name):
CREATE PUBLIC DATABASE LINK link_name ...;
Following are examples of public database links:
| This SQL Statement... | Creates... |
|---|---|
|
|
A public link to the remote |
|
|
A public link called Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links"). |
|
|
A public fixed user link to the remote |
| See Also:
Oracle9i SQL Reference for |
You must define global database links in the Oracle Names Server. See the Oracle9i Net Services Administrator's Guide to learn how to create global database links.
A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle establishes a database session in the remote database on behalf of the local application request.
When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links.
To create a fixed user database link, you embed the credentials (in this case, a username and password) required to access the remote database in the definition of the link:
CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;
Following are examples of fixed user database links:
When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the fixed user's credentials across the network when an application uses the link to access the remote database.
Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation performed by the application.
For an extended conceptual discussion of the distinction between connected users and current users, see "Users of Database Links".
To create a connected user database link, omit the CONNECT TO clause. The following syntax creates a connected user database link, where dblink is the name of the link and net_service_name is an optional connect string:
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];
For example, to create a connected user database link, use the following syntax:
CREATE DATABASE LINK sales.division3.acme.com USING 'sales';
To create a current user database link, use the CONNECT TO CURRENT_USER clause in the link creation statement. Current user links are only available through the Oracle Advanced Security option.
The following syntax creates a current user database link, where dblink is the name of the link and net_service_name is an optional connect string:
CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER [USING 'net_service_name'];
For example, to create a connected user database link to the sales database, you might use the following syntax:
CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';
|
Note: To use a current user database link, the current user must be a global user on both databases involved in the link. |
| See Also:
Oracle9i SQL Reference for more syntax information about creating database links |
In some situations, you may want to have several database links of the same type (for example, public) that point to the same remote database, yet establish connections to the remote database using different communication pathways. Some cases in which this strategy is useful are:
To facilitate such functionality, Oracle allows you to create a database link with an optional service name in the database link name. When creating a database link, a service name is specified as the trailing portion of the database link name, separated by an @ sign, as in @sales. This string is called a connection qualifier.
For example, assume that remote database hq.acme.com is managed in a Oracle Real Application Clusters environment. The hq database has two instances named hq_1 and hq_2. The local database can contain the following public database links to define pathways to the remote instances of the hq database:
CREATE PUBLIC DATABASE LINK hq.acme.com@hq_1 USING 'string_to_hq_1'; CREATE PUBLIC DATABASE LINK hq.acme.com@hq_2 USING 'string_to_hq_2'; CREATE PUBLIC DATABASE LINK hq.acme.com USING 'string_to_hq';
Notice in the first two examples that a service name is simply a part of the database link name. The text of the service name does not necessarily indicate how a connection is to be established; this information is specified in the service name of the USING clause. Also notice that in the third example, a service name is not specified as part of the link name. In this case, just as when a service name is specified as part of the link name, the instance is determined by the USING string.
To use a service name to specify a particular instance, include the service name at the end of the global object name:
SELECT * FROM scott.emp@hq.acme.com@hq_1
Note that in this example, there are two @ symbols.
Every application that references a remote server using a standard database link establishes a connection between the local database and the remote database. Many users running applications simultaneously can cause a high number of connections between the local and remote databases.
Shared database links enable you to limit the number of network connections required between the local server and the remote server.
This section contains the following topics:
| See Also:
"What Are Shared Database Links?" for a conceptual overview of shared database links |
Look carefully at your application and shared server configuration to determine whether to use shared links. A simple guideline is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of server processes in the local database.
The following table illustrates three possible configurations involving database links:
Shared database links are not useful in all situations. Assume that only one user accesses the remote server. If this user defines a shared database link and 10 shared server processes exist in the local database, then this user can require up to 10 network connections to the remote server. Because the user can use each shared server process, each process can establish a connection to the remote server.
Clearly, a non-shared database link is preferable in this situation because it requires only one network connection. Shared database links lead to more network connections in single-user scenarios, so use shared links only when many users need to use the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema (and therefore the same private database link).
To create a shared database link, use the keyword SHARED in the CREATE DATABASE LINK statement:
CREATE SHARED DATABASE LINK dblink_name [CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER] AUTHENTICATED BY schema_name IDENTIFIED BY password [USING 'service_name'];
The following example creates a fixed user, shared link to database sales, connecting as scott and authenticated as keith:
CREATE SHARED DATABASE LINK link2sales CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY keith IDENTIFIED BY richards USING 'sales';
Whenever you use the keyword SHARED, the clause AUTHENTICATED BY is required. The schema specified in the AUTHENTICATED BY clause is only used for security reasons and can be considered a dummy schema. It is not affected when using shared database links, nor does it affect the users of the shared database link. The AUTHENTICATED BY clause is required to prevent unauthorized clients from masquerading as a database link user and gaining access to privileged information.
| See Also:
Oracle9i SQL Reference for information about the |
You can configure shared database links in the following ways:
In the configuration illustrated in Figure 29-1, a shared server process in the local server owns a dedicated remote server process. The advantage is that a direct network transport exists between the local shared server and the remote dedicated server. A disadvantage is that extra back-end server processes are needed.
The configuration illustrated in Figure 29-2 uses shared server processes on the remote server. This configuration eliminates the need for more dedicated servers, but requires the connection to go through the dispatcher on the remote server. Note that both the local and the remote server must be configured as shared servers.
| See Also:
Oracle9i Net Services Administrator's Guide for information about the shared server option |
This section contains the following topics:
If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link. This situation has the following consequences:
After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manually. For example, close links when:
If you want to close a link, issue the following statement, where linkname refers to the name of the link:
ALTER SESSION CLOSE DATABASE LINK linkname;
Note that this statement only closes the links that are active in your current session.
You can drop a database link just as you can drop a table or view. If the link is private, then it must be in your schema. If the link is public, then you must have the DROP PUBLIC DATABASE LINK system privilege.
The statement syntax is as follows, where dblink is the name of the link:
DROP [PUBLIC] DATABASE LINK dblink;
CONNECT scott/tiger@local_db
USER_DB_LINKS to view the links that you own. For example, enter:
SELECT DB_LINK FROM USER_DB_LINKS;
DB_LINK
-----------------------------------
SALES.US.ORACLE.COM
MKTG.US.ORACLE.COM
2 rows selected.
DROP DATABASE LINK statement. For example, enter:
DROP DATABASE LINK sales.us.oracle.com;
DROP PUBLIC DATABASE LINK privilege. For example, enter:
CONNECT SYSTEM/password@local_db AS SYSDBA
DBA_DB_LINKS to view the public links. For example, enter:
SELECT DB_LINK FROM USER_DB_LINKS WHERE OWNER = 'PUBLIC'; DB_LINK ----------------------------------- DBL1.US.ORACLE.COM SALES.US.ORACLE.COM INST2.US.ORACLE.COM RMAN2.US.ORACLE.COM 4 rows selected.
DROP PUBLIC DATABASE LINK statement. For example, enter:
DROP PUBLIC DATABASE LINK sales.us.oracle.com;
You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.
Note the following considerations for setting this parameter:
OPEN_LINKS to 3 or greater.OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.
| See Also:
Oracle9i Database Reference for more information about |
The data dictionary of each database stores the definitions of all the database links in the database. You can use data dictionary tables and views to gain information about the links. This section contains the following topics:
The following views show the database links that have been defined at the local database and stored in the data dictionary:
These data dictionary views contain the same basic information about database links, with some exceptions:
Any user can query USER_DB_LINKS to determine which database links are available to that user. Only those with additional privileges can use the ALL_DB_LINKS or DBA_DB_LINKS view.
The following script queries the DBA_DB_LINKS view to access link information:
COL OWNER FORMAT a10 COL USERNAME FORMAT A8 HEADING "USER" COL DB_LINK FORMAT A30 COL HOST FORMAT A7 HEADING "SERVICE" SELECT * FROM DBA_DB_LINKS /
Here, the script is invoked and the resulting output is shown:
SQL>@link_script OWNER DB_LINK USER SERVICE CREATED ---------- ------------------------------ -------- ------- ---------- SYS TARGET.US.ACME.COM SYS inst1 23-JUN-99 PUBLIC DBL1.UK.ACME.COM BLAKE ora51 23-JUN-99 PUBLIC RMAN2.US.ACME.COM inst2 23-JUN-99 PUBLIC DEPT.US.ACME.COM inst2 23-JUN-99 JANE DBL.UK.ACME.COM BLAKE ora51 23-JUN-99 SCOTT EMP.US.ACME.COM SCOTT inst2 23-JUN-99 6 rows selected.
Only USER_DB_LINKS contains a column for password information. However, if you are an administrative user (SYS or users who connect AS SYSDBA), then you can view passwords for all links in the database by querying the LINK$ table. If you are not an administrative user, you can be authorized to query the LINK$ table by one of the following methods:
LINK$ tableSELECT ANY DICTIONARY system privilege
| See Also:
"Understanding User Privileges and Roles" for more information about privileges necessary to view objects in the |
You can create and run the following script in SQL*Plus to obtain password information (sample output included):
COL USERID FORMAT A10 COL PASSWORD FORMAT A10 SELECT USERID,PASSWORD FROM SYS.LINK$ WHERE PASSWORD IS NOT NULL / SQL>@linkpwd USERID PASSWORD ---------- ---------- SYS ORACLE BLAKE TYGER SCOTT TIGER 3 rows selected.
It is possible to view AUTHENTICATED BY ... IDENTIFIED BY ... usernames and passwords for all links in the database by querying the LINK$ table. You can create and run the following script in SQL*Plus to obtain password information (sample output included):
COL AUTHUSR FORMAT A10 COL AUTHPWD FORMAT A10 SELECT AUTHUSR AS userid, AUTHPWD AS password FROM SYS.LINK$ WHERE PASSWORD IS NOT NULL / SQL> @authpwd USERID PASSWORD ---------- ---------- ELLIE MAY 1 row selected.
You can also view the link and password information together in a join by creating and executing the following script (sample output included):
COL OWNER FORMAT A8 COL DB_LINK FORMAT A15 COL USERNAME FORMAT A8 HEADING "CON_USER" COL PASSWORD FORMAT A8 HEADING "CON_PWD" COL AUTHUSR FORMAT A8 HEADING "AUTH_USER" COL AUTHPWD FORMAT A8 HEADING "AUTH_PWD" COL HOST FORMAT A7 HEADING "SERVICE" COL CREATED FORMAT A10 SELECT DISTINCT d.OWNER,d.DB_LINK,d.USERNAME,l.PASSWORD, l.AUTHUSR,l.AUTHPWD,d.HOST,d.CREATED FROM DBA_DB_LINKS d, SYS.LINK$ l WHERE PASSWORD IS NOT NULL AND d.USERNAME = l.USERID / SQL> @user_and_pwd OWNER DB_LINK CON_USER CON_PWD AUTH_USE AUTH_PWD SERVICE CREATED -------- --------------- -------- -------- -------- -------- ------- ---------- JANE DBL.ACME.COM BLAKE TYGER ELLIE MAY ora51 23-JUN-99 PUBLIC DBL1.ACME.COM SCOTT TIGER ora51 23-JUN-99 SYS TARGET.ACME.COM SYS ORACLE inst1 23-JUN-99
You may find it useful to determine which database link connections are currently open in your session. Note that if you connect as SYSDBA, you cannot query a view to determine all the links open for all sessions; you can only access the link information in the session within which you are working.
The following views show the database link connections that are currently open in your current session:
These data dictionary views contain the same basic information about database links, with one exception:
For example, you can create and execute the script below to determine which links are open (sample output included):
COL DB_LINK FORMAT A25 COL OWNER_ID FORMAT 99999 HEADING "OWNID" COL LOGGED_ON FORMAT A5 HEADING "LOGON" COL HETEROGENEOUS FORMAT A5 HEADING "HETER" COL PROTOCOL FORMAT A8 COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR" COL IN_TRANSACTION FORMAT A3 HEADING "TXN" COL UPDATE_SENT FORMAT A6 HEADING "UPDATE" COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S" SELECT * FROM V$DBLINK / SQL> @dblink DB_LINK OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE C_P_S ------------------------- ------ ----- ----- -------- ------- --- ------ ------ INST2.ACME.COM 0 YES YES UNKN 0 YES YES 255
After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. The following sections explain how to hide distributed functionality from users:
Local views can provide location transparency for local and remote tables in a distributed database system.
For example, assume that table emp is stored in a local database and table dept is stored in a remote database. To make these tables transparent to users of the system, you can create a view in the local database that joins local and remote data:
CREATE VIEW company AS SELECT a.empno, a.ename, b.dname FROM scott.emp a, jward.dept@hq.acme.com b WHERE a.deptno = b.deptno;
When users access this view, they do not need to know where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example, the following query provides data from both the local and remote database table:
SELECT * FROM company;
The owner of the local view can grant only those object privileges on the local view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management for views that reference local data.
Synonyms are useful in both distributed and nondistributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If you must rename or move the underlying object, you only need to redefine the synonym; applications based on the synonym continue to function normally. Synonyms also simplify SQL statements for users in a distributed database system.
You can create synonyms for the following:
All synonyms are schema objects that are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow single-word access to remote data, hiding the specific object name and the location from users of the synonym.
The syntax to create a synonym is:
CREATE [PUBLIC] synonym_name FOR [schema.]object_name[@database_link_name];
where:
PUBLIC is a keyword specifying that this synonym is available to all users. Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.synonym_name specifies the alternate object name to be referenced by users and applications.schema specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.object_name specifies either a table, view, sequence, materialized view, type, procedure, function or package as appropriate.database_link_name specifies the database link identifying the remote database and schema in which the object specified in object_name is located.A synonym must be a uniquely named object for its schema. If a schema contains a schema object and a public synonym exists with the same name, then Oracle always finds the schema object when the user that owns the schema references that name.
Assume that in every database in a distributed database system, a public synonym is defined for the scott.emp table stored in the hq database:
CREATE PUBLIC SYNONYM emp FOR scott.emp@hq.acme.com;
You can design an employee management application without regard to where the application is used because the location of the table scott.emp@hq.acme.com is hidden by the public synonyms. SQL statements in the application access the table by referencing the public synonym emp.
Furthermore, if you move the emp table from the hq database to the hr database, then you only need to change the public synonyms on the nodes of the system. The employee management application continues to function properly on all nodes.
A synonym is a reference to an actual object. A user who has access to a synonym for a particular schema object must also have privileges on the underlying schema object itself. For example, if the user attempts to access a synonym but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.
Assume scott creates local synonym emp as an alias for remote object scott.emp@sales.acme.com. scott cannot grant object privileges on the synonym to another local user. scott cannot grant local privileges for the synonym because this operation amounts to granting privileges for the remote emp table on the sales database, which is not allowed. This behavior is different from privilege management for synonyms that are aliases for local tables or views.
Therefore, you cannot manage local privileges when synonyms are used for location transparency. Security for the base object is controlled entirely at the remote node. For example, user admin cannot grant object privileges for the EMP_SYN synonym.
Unlike a database link referenced in a view or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the schema in effect at the time the reference to the synonym is parsed. Therefore, to ensure the desired object resolution, it is especially important to specify the underlying object's schema in the definition of a synonym.
PL/SQL program units called procedures can provide location transparency. You have these options:
Procedures or functions (either standalone or in packages) can contain SQL statements that reference remote data. For example, consider the procedure created by the following statement:
CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@hq.acme.com WHERE empno = enum; END;
When a user or application calls the fire_emp procedure, it is not apparent that a remote table is being modified.
A second layer of location transparency is possible when the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For example, the following statement defines a local synonym:
CREATE SYNONYM emp FOR emp@hq.acme.com;
Given this synonym, you can create the fire_emp procedure using the following statement:
CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp WHERE empno = enum; END;
If you rename or move the table emp@hq, then you only need to modify the local synonym that references the table. None of the procedures and applications that call the procedure require modification.
You can use a local procedure to call a remote procedure. The remote procedure can then execute the required DML. For example, assume that scott connects to local_db and creates the following procedure:
CONNECT scott/tiger@local_db CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN EXECUTE term_emp@hq.acme.com; END;
Now, assume that scott connects to the remote database and creates the remote procedure:
CONNECT scott/tiger@hq.acme.com CREATE PROCEDURE term_emp (enum NUMBER) AS BEGIN DELETE FROM emp WHERE empno = enum; END;
When a user or application connected to local_db calls the fire_emp procedure, this procedure in turn calls the remote term_emp procedure on hq.acme.com.
For example, scott connects to the local sales.acme.com database and creates the following procedure:
CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@hq.acme.com WHERE empno = enum; END;
User peggy then connects to the supply.acme.com database and creates the following synonym for the procedure that scott created on the remote sales database:
SQL> CONNECT peggy/hill@supply SQL> CREATE PUBLIC SYNONYM emp FOR scott.fire_emp@sales.acme.com;
A local user on supply can use this synonym to execute the procedure on sales.
Assume a local procedure includes a statement that references a remote table or view. The owner of the local procedure can grant the execute privilege to any user, thereby giving that user the ability to execute the procedure and, indirectly, access remote data.
In general, procedures aid in security. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users.
Oracle allows the following standard DML statements to reference remote tables:
SELECT (queries)INSERTUPDATEDELETESELECT ... FOR UPDATE (not always supported in Heterogeneous Systems)LOCK TABLEQueries including joins, aggregates, subqueries, and SELECT ... FOR UPDATE can reference any number of local and remote tables and views. For example, the following query joins information from two remote tables:
SELECT e.empno, e.ename, d.dname FROM scott.emp@sales.division3.acme.com e, jward.dept@hq.acme.com d WHERE e.deptno = d.deptno;
UPDATE, INSERT, DELETE, and LOCK TABLE statements can reference both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts new rows into the remote table emp in the scott.sales schema by selecting rows from the emp table in the jward schema in the local database:
INSERT INTO scott.emp@sales.division3.acme.com SELECT * FROM jward.emp;
Several restrictions apply to statement transparency.
LONG and LONG RAW columns, sequences, updated tables, and locked tables must be located at the same node.CREATE, ALTER, and DROP) in homogeneous systems except through remote execution of procedures of the DBMS_SQL package, as in this example:
DBMS_SQL.PARSE@link_name(crs, 'drop table emp', v7);
Note that in Heterogeneous Systems, a pass-through facility allows you to execute DDL.
LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.SYSDATE, USER, UID, and USERENV with respect to the local server, no matter where the statement (or portion of a statement) executes.
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the |
This section gives examples of various types of statements involving management of database links:
The following example connects to the local database as jane and creates a public fixed user database link to database sales for scott. The database is accessed through its net service name sldb:
CONNECT jane/doe@local CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO scott IDENTIFIED BY tiger USING 'sldb';
Any user connected to the local database can use the sales.division3.acme.com database link to connect to the remote database. Each user connects to the schema scott in the remote database.
To access the table emp table in scott's remote schema, a user can issue the following SQL query:
SELECT * FROM emp@sales.division3.acme.com;
Note that each application or user session creates a separate connection to the common account on the server. The connection to the remote database remains open for the duration of the application or user session.
The following example connects to the local database as dana and creates a public link to the sales database (using its net service name sldb). The link allows a connection to the remote database as scott and authenticates this user as scott:
CONNECT dana/sculley@local CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY scott IDENTIFIED BY tiger USING 'sldb';
Any user connected to the local shared server can use this database link to connect to the remote sales database through a shared server process. The user can then query tables in the scott schema.
In the above example, each local shared server can establish one connection to the remote server. Whenever a local shared server process needs to access the remote server through the sales.division3.acme.com database link, the local shared server process reuses established network connections.
The following example connects to the local database as larry and creates a public link to the database with the net service name sldb:
CONNECT larry/oracle@local CREATE PUBLIC DATABASE LINK redwood USING 'sldb';
Any user connected to the local database can use the redwood database link. The connected user in the local database who uses the database link determines the remote schema.
If scott is the connected user and uses the database link, then the database link connects to the remote schema scott. If fox is the connected user and uses the database link, then the database link connects to remote schema fox.
The following statement fails for local user fox in the local database when the remote schema fox cannot resolve the emp schema object. That is, if the fox schema in the sales.division3.acme.com does not have emp as a table, view, or (public) synonym, an error will be returned.
CONNECT fox/mulder@local SELECT * FROM emp@redwood;
The following example connects to the local database as neil and creates a shared, public link to the sales database (using its net service name sldb). The user is authenticated by the userid/password of crazy/horse. The following statement creates a public, connected user, shared database link:
CONNECT neil/young@local CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com AUTHENTICATED BY crazy IDENTIFIED BY horse USING 'sldb';
Each user connected to the local server can use this shared database link to connect to the remote database and query the tables in the corresponding remote schema.
Each local, shared server process establishes one connection to the remote server. Whenever a local server process needs to access the remote server through the sales.division3.acme.com database link, the local process reuses established network connections, even if the connected user is a different user.
If this database link is used frequently, eventually every shared server in the local database will have a remote connection. At this point, no more physical connections are needed to the remote server, even if new users use this shared database link.
The following example connects to the local database as the connected user and creates a public link to the sales database (using its net service name sldb). The following statement creates a public current user database link:
CONNECT bart/simpson@localCREATE PUBLIC DATABASE LINK sales.division3.acme.comCONNECT TO CURRENT_USERUSING'sldb';
Assume scott creates local procedure fire_emp that deletes a row from the remote emp table, and grants execute privilege on fire_emp to ford.
CONNECT scott/tiger@local_db CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@sales.division3.acme.com WHERE empno=enum; END; GRANT EXECUTE ON fire_emp TO ford;
Now, assume that ford connects to the local database and runs scott's procedure:
CONNECT ford/fairlane@local_db EXECUTE PROCEDURE scott.fire_emp (enum 10345);
When ford executes the procedure scott.fire_emp, the procedure runs under scott's privileges. Because a current user database link is used, the connection is established to scott's remote schema--not ford's remote schema. Note that scott must be a global user while ford does not have to be a global user.
|
Note: If a connected user database link were used instead, the connection would be to |
You can accomplish the same result by using a fixed user database link to scott's remote schema. With fixed user database links, however, security can be compromised because scott's username and password are available in readable format in the database.
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|