Security Setup for Snapshot Replication

Nearly all users should find it easiest to use the Replication Manager Setup Wizard when configuring snapshot replication security. However, for certain specialized cases, you may need to use the replication management API to perform these setup operations. To configure a replication environment, the database administrator must connect with DBA privileges to grant the necessary privileges to the replication administrator.

First set up user accounts at each snapshot site with the appropriate privileges to configure and maintain the replication environment and to propagate replicated changes. You must also define links for these users to the associated master site. You may need to create additional users, or assign additional privileges to users at the associated master site.

In addition to end users who will be accessing replicated objects, there are three special categories of "users" at a snapshot site:

• Replication administrators, who are responsible for configuring and maintaining a replication environment.

• Propagators, who are responsible for propagating deferred transactions.

• Refreshers, who are responsible for pulling down changes to the snapshots from the associated master tables.

Typically, a single user performs each of these functions. However, there may be situations where you need different users performing these functions. For example, snapshots may be created by a snapshot site administrator and refreshed by another end user.

Table A-2 describes the privileges needed to create and maintain a snapshot site.

Table A-2 Required Snapshot Site User Accounts

In addition to creating the appropriate users at the snapshot site, you may need to create additional users at the associated master site, as well. Table A-5 on describes the privileges need by master site users to support a new snapshot site.

Trusted vs. Untrusted Security

In addition to the different users at the master site, you also need to determine which type of security model you will implement: trusted or untrusted. With a trusted security model, the receiver and proxy snapshot administrator have access to all local master groups. Because the proxy snapshot administrator and receiver perform database activities at the local master site on behalf of the snapshot administrator and propagator, respectively, at the remote snapshot site, the propagator and snapshot administrator also have access to all master groups at the master site. Remember that a single receiver is used for all incoming transactions.

For example, consider the scenario in Figure A-3. Even though Snapshot Groups A and C exist at the snapshot site (based on Master Groups A and C at the Master Site), the propagator and snapshot administrator have access to Master Groups A, B, C, and D at the Master Site because the trusted security model has been used. While this greatly increases the flexibility of database administration, because the DBA can perform administrative functions at any of these remote sites and have these changes propagated to the master sites, it also increases the chances of a malicious user at a remote site viewing or corrupting data at the master site.

Regardless of the security model used, Oracle8i automatically grants the appropriate privileges for objects as they are added to or removed from a replicated environment.

Text description of the illustration rep81067.gif.

Untrusted security assigns only the privileges to the proxy snapshot administrator and receiver that are required to work with specified master groups. The propagator and snapshot administrator, therefore, can only access these specified master groups at the Master Site. Figure A-4 illustrates an untrusted security model with snapshot replication. Because the Snapshot Site contains Snapshot Groups A and C, access to only Master Groups A and C are required. Using untrusted security does not allow the propagator or the snapshot administrator at the Snapshot Site to access Master Groups B and D at the Master Site.

Text description of the illustration rep81068.gif.

Typically, snapshot sites are more vulnerable to security breaches and therefore the untrusted security model is used. There are very few reasons why you would want to use a trusted security model with your snapshot site and it is recommended that you use the untrusted security model with snapshot sites.

One reason you might choose to use a trusted security model is if your snapshot site is considered a master site in every way (security, constant network connectivity, resources) but is a snapshot only because of data partitioning requirements. Remember that horizontal or vertical partitioning are not supported in a multimaster configuration.

Use the appropriate API calls listed for the proxy snapshot administrator and receiver in Table A-5 to assign the different users the appropriate privileges.

Table A-5 Required Master Site User Accounts

privilege => 'proxy_snapadmin'
list_of_gnames => NULL

privilege => 'proxy_snapadmin'
list_of_gnames => 'mastergroupname'

privilege => 'receiver'
list_of_gnames => NULL

privilege => 'receiver'
list_of_gnames => 'mastergroupname'

After creating the accounts at both the snapshot and associated master sites, you need to create the following private database links, including username and password, from the snapshot site to the master:

• From the snapshot replication administrator to the proxy snapshot replication administrator.

• From the propagator to the receiver.

• From the refresher to the proxy refresher.

• From the snapshot owner to the master site for refreshes.

Assuming you have designated a single user account to act as snapshot administrator, propagator, and refresher, you must create one link for each snapshot site for those functions. You do not need a link from the master site to the snapshot site.

After creating these links, you must call DBMS_DEFER_SYS.SCHEDULE_PUSH and DBMS_DEFER_SYS.SCHEDULE_PURGE at the snapshot site to define how frequently you want to propagate your deferred transaction queue to the associated master site, and how frequently you wish to purge this queue. You must also call DMBS_REFRESH. REFRESH at the snapshot site to schedule how frequently to pull changes from the associated master site.