Oracle7 Server Distributed Systems Volume II: Replicated Data

Before Creating a Replicated Environment

After you have determined which objects you want to replicate, you must ensure that you have the privileges necessary to create these objects at each site. Additionally, once you have determined which sites will make up your replicated environment, you must ensure that these sites can communicate with one another by creating the necessary database links.

There are three categories of users in a replicated environment:

replication administrators Users who are responsible for configuring and maintaining a replicated environment.
symmetric replication facility Certain replication activities are run as SYS and must execute on remote nodes.
end users Users querying and updating replicated objects.

Replication Administrators

A replication administrator is responsible for configuring the replicated environment. You can create one replication administrator to administer all replicated objects at a site, or you can have separate administrators for the replicated objects in each schema. All symmetric replication procedures should be run while you are connected as a replication administrator.

Note: The packages DBMS_REPCAT_ADMIN and DBMS_REPCAT_AUTH are owned by SYS. Access to these packages should not be granted widely.

Granting Privileges

To create a replication administrator for a single schema, call DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP, as shown in the following example:

DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP(userid => 'acctng');

In this example, the ACCTNG user now has the privileges needed to administer any replicated objects in the ACCTNG schema at the current site. The ACCTNG user must already exist, before you call GRANT_ADMIN_REPGROUP. You can now GRANT or REVOKE privileges for this user as needed. This procedure is most useful if your replicated object group does not span schemas.

Additional Information: The parameter for the GRANT_ADMIN_REPGROUP procedure is described in Table 12 - 177, and the exceptions are listed in Table 12 - 178.

To create a replication administrator for all replicated groups at your current site (also known as a global replication administrator), complete the following steps:

1. Create the replication administrator account, as shown in the following example:

	CREATE USER repadmin IDENTIFIED BY repadminpassword;

2. Grant the replication administrator the necessary privileges to administer all replicated objects at the current site by calling DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP as shown in the following example:

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP(userid => 'repadmin');

3. You can now GRANT additional privileges to the replication administrator as necessary. Additional Information: The parameter for the GRANT_ADMIN_ANY_REPGROUP procedure is described in Table 12 - 175, and the exceptions are listed in Table 12 - 176.

Revoking Privileges Call the REVOKE_ADMIN_REPGROUP or REVOKE_ADMIN_ANY_REPGROUP procedures in the DBMS_REPCAT_ADMIN package to revoke the privileges and roles granted above.

Attention: Calling the REVOKE_privs procedures will revoke all privileges granted with the associated GRANT_privs procedures, even if identical privileges were granted through additional channels.

Additional Information: The parameter for the REVOKE_ADMIN_REPGROUP procedure is described in Table 12 - 181, and the exceptions are listed in Table 12 - 182. The parameter for the REVOKE_ADMIN_ANY_REPGROUP procedure is described in Table 12 - 179, and the exceptions are listed in Table 12 - 180.

Creating Database Links

To create the necessary links to perform replication administration, you must complete the following steps at each master site in the replicated environment:

Connect to the site as the replication administrator.

Ensure that the initialization parameter GLOBAL_NAMES is set to TRUE.

Create a private database link to every other master site in the replicated environment. That is, if the schema is replicated on N masters, you must create N-1 links. Each database link must include the fully qualified database name of the remote master site and the username and password of the replication administrator at that site.

In the following example, assume that you have three master sites whose global database names are sales.widgetek.com, inventory.widgetek.com, and hq.widgetek.com. Now assume that you have created user REPADMIN at each site with password SECRET, and that this user will act as the replication administrator for this replicated environment. At the SALES site, you would need to create the database links as shown below:

CONNECT repadmin/secret

CREATE PUBLIC DATABASE LINK inventory.widgetek.com 
    USING 'inventory.widgetek.com';

CREATE DATABASE LINK inventory.widgetek.com CONNECT TO repadmin
    IDENTIFIED BY secret;

CREATE PUBLIC DATABASE LINK hq.widgetek.com
    USING 'hq.widgetek.com';

CREATE DATABASE LINK hq.widgetek.com CONNECT TO repadmin
    IDENTIFIED BY secret;

Using a public link to provide the database specification of the remote database eliminates the need to include a USING clause in each of your private database links. To determine the global database name of a remote database, you can query the GLOBAL_NAME data dictionary view.

Replication Administration Usage Notes

When you call a procedure in the DBMS_REPCAT package at a master site, the symmetric replication facility attempts to perform any deferred replication administration for any replicated schema that is passed as an argument to the DBMS_REPCAT procedure.

All calls to procedures in the DBMS_REPCAT package for a given replicated object group should be performed serially. That is, only one person should be adding to, or altering a replicated environment at a time, or your environment may become out of sync. This includes activities that you may have set up to be performed by a background process.

To be safe, you should disable any DBMS_REPCAT.* jobs in the local job queue whenever you are administering a replicated environment.

You can use the USER_JOBS view to determine which jobs in the queue are associated with a given replicated object group. Then call DBMS_JOB.BROKEN to disable these jobs temporarily.

After completing your administrative activities, you can call this procedure again to re-enable the jobs.

If your object group spans schemas you will probably find it easiest to designate one person as the replication administrator for all replicated objects. If your object groups do not span schemas, you can designate one person as the replication administrator for each schema containing replicated objects. This person would be responsible for all calls made using the DBMS_REPCAT package to administer an object group at all master sites in the replicated environment. Different users can administer different schemas.

Because the DBMS_REPCAT package does not provide any additional access control, EXECUTE privileges on this package should not be granted widely.

A user granted EXECUTE privileges on DBMS_REPCAT does not gain any privileges on non-replication catalog views, but can modify the replication catalog views and disrupt a replicated environment. If you desire more flexibility, you should create a cover package for DBMS_REPCAT that provides the appropriate level of access control, and then grant wider access only on this cover package.

Note that granting EXECUTE on DBMS_REPCAT to a user does not give that user any greater privileges on the replicated objects. You must call the appropriate DBMS_REPCAT_ADMIN procedure to ensure that your replication administrator has the necessary privileges on all objects at all sites to perform the necessary DDL and DML operations that are supported by the procedures in DBMS_REPCAT.

Symmetric Replication Facility

Certain replication activities are run as SYS and require access to remote nodes. By creating a surrogate replication administrator at the remote site, you preclude the need for a SYS to SYS database link between master sites. The surrogate replication administrator performs actions on behalf of the symmetric replication facility at the remote site.

Granting Privileges

To create a surrogate replication administrator at your current site, complete the following steps:

1. Create the surrogate replication administrator account, as shown in the following example:

	CREATE USER surrogate IDENTIFIED BY surrogate_password;

You will probably find it easiest to create the same user as the surrogate replication administrator at all master sites in your replicated environment.

2. Grant the surrogate replication administrator the necessary privileges to act on behalf of the symmetric replication facility for all replicated object groups at the current site by calling DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT, as shown in the following example:

	DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid => 'surrogate');

3. You can now GRANT additional privileges to the surrogate replication administrator as necessary. Additional Information: The parameter for the GRANT_SURROGATE_REPCAT procedure is described in Table 12 - 183, and the exceptions are listed in Table 12 - 184.

Revoking Privileges Call the REVOKE_SURROGATE_REPCAT procedure in the DBMS_REPCAT_AUTH package to revoke the privileges and roles granted above.

Additional Information: The parameter for the REVOKE_SURROGATE_REPCAT procedure is described in Table 12 - 185, and the exceptions are listed in Table 12 - 186.

Creating Database Links

You need to create the links used by the symmetric replication facility in a similar manner to those created for each replication administrator. The replication facility, however, requires special privileges at each site to perform required operations. Therefore, the user that you specify in the CONNECT TO clause must have these privileges. This special user is called the surrogate replication administrator.

To create the necessary links for the symmetric replication facility, you must complete the following steps at each master site:

Connect to the local site as SYSOPER or SYSDBA.

Create a private database link to every other remote master site in the replicated environment. That is, if the schema is replicated to N masters, you must create N-1 links. Each database link must include the fully qualified database name of the remote master site and the username and password of the surrogate replication administrator at that site. For example, to create a link to the HQ master site from the previous example, you could issue the following statement:

	CREATE DATABASE LINK hq.widgetek.com CONNECT TO surrogate
	    IDENTIFIED BY surrogate_password USING 'hq.widgetek.com';

End Users

End users require no additional privileges beyond those necessary to update the local objects. The ability to apply these changes at the replicated sites is determined by the links used to propagate the changes, and whether the changes are propagated synchronously or asynchronously.

Synchronous Propagation

When you update a table at a site that is propagating changes synchronously, Oracle fires a trigger that calls a package which in turn makes a remote procedure call to the site or sites that are receiving the change synchronously.

To call the remote procedure, Oracle must connect to the remote database using a database link. Because the generated trigger package at the local site has the same owner as the owner of the table being updated, Oracle first looks for a private database link for the table owner. If no such link exists, Oracle next looks for a private database link for the connected user. If Oracle does not find a private database link, it next looks for a public database link to the remote site.

The privilege domain at the remote site is determined by the connection at the remote site. Oracle connects to the remote site using the username and password specified in the CONNECT TO clause of the database link, if one was given; otherwise, Oracle attempts the connection using the username and password for the local user making the update. The user that Oracle ultimately connects as must have the EXECUTE privilege on the generated procedures for the object being updated for the update to complete successfully.

Asynchronous Propagation

The privilege domain of an asynchronously replicated transaction when executed at the remote site is determined by

the value of the EXECUTE_AS_USER argument to the DBMS_DEFER_SYS.EXECUTE procedure

the available database links to the remote site

When the replication administrator schedules the execution of the deferred transaction queue, he or she can require that the deferred transaction be pushed as the user who originally queued the transaction, or as the connected session user.

To push the deferred transaction to the remote location, Oracle must first establish a connection to the remote site. To determine which link to use, Oracle checks the EXECUTE_AS_USER setting. If this parameter is FALSE, the default, Oracle looks for a database link for the user who originated the transaction.

If this parameter is TRUE, Oracle looks for a database link for the connected session user. (Typically, the replication administrator pushes the deferred transaction queue.)

If the available link does not include a username and password, Oracle uses the username and password associated with the current session (regardless of the setting of EXECUTE_AS_USER). If the current session is a background process (such as when you schedule periodic execution of the deferred transaction queue), there is no available username and password, and the connection fails.

Once a connection is established, the privilege domain of the transaction at the remote site is determined by the privilege domain of the connection. For example, if the connection is made using the database link created for the replication administrator, the privilege domain of the replication administrator at the remote site determines whether the transaction can be successfully executed at the remote site. To execute the transaction successfully, this user (the remote replication administrator) must have the EXECUTE privilege on the generated procedures for the object being updated.

If all transactions will be pushed to the remote site as the replication administrator (EXECUTE_AS_USER = TRUE), no additional database links are necessary. To ensure that the replication administrator at the remote site has the necessary privileges to apply these transactions, you must either GRANT the replication administrator at each site the EXECUTE ANY PROCEDURE system privilege, or GRANT the replication administrator the EXECUTE object privilege on each of the generated replication procedures.

Note: When you replicate an object, its associated privileges are not replicated. To replicate the GRANT command to each site, you can use the DBMS_REPCAT.EXECUTE_DDL procedure described .

If transactions will be pushed to the remote site as the originating user (EXECUTE_AS_USER = FALSE), each user should have a private database link to each site participating in the replicated environment. This link should include a username and password. The user specified in the CONNECT TO clause must have the necessary EXECUTE privileges on the generated replication procedures.

For example, if user SCOTT at the New York site has UPDATE privileges on the ACCT.EMP and ACCT.DEPT tables, and you create the following private link while connected as SCOTT

CREATE DATABASE LINK hq.widgetek.com 
    CONNECT TO scott IDENTIFIED BY tiger;

then user SCOTT at the remote HQ site must have EXECUTE privileges on the ACCT.EMP$RP and ACCT.DEPT$RP packages. Because the generated packages are considered to have the same owner as the owner of the table, the table owner automatically has the ability to execute the packages. All other users must either be granted the EXECUTE ANY PROCEDURE privilege at each site, or be granted EXECUTE on each generated package at each remote site for every table they may update.

replication administrators	Users who are responsible for configuring and maintaining a replicated environment.
symmetric replication facility	Certain replication activities are run as SYS and must execute on remote nodes.
end users	Users querying and updating replicated objects.