| Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
|
|
View PDF |
| Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
|
|
View PDF |
This appendix contains these topics:
Nearly all users should find it easiest to use the Replication Manager Setup Wizard when configuring multimaster replication security. However, in certain cases you might 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 master site with the appropriate privileges to configure and maintain the replication environment and to propagate and apply replicated changes. You must also define links for users at each master site.
In addition to the end users who access replicated objects, there are three special categories of "users" in a replication environment:
Replication administrators, who are responsible for configuring and maintaining a replication environment.
Propagators, who are responsible for propagating deferred transactions.
Receivers at remote sites, who are responsible for applying these transactions.
Typically, a single user acts as administrator, propagator, and receiver. However, you can have separate users perform each of these functions. You can choose to have a single, global replication administrator or, if your replication groups do not span schema boundaries, you might prefer to have separate replication administrators for different schemas. Note, however, that you can have only one registered propagator for each database.
Table A-1 describes the necessary privileges that must be assigned to these specialized accounts. Most privileges needed by these users are granted to them through calls to the replication management API. You also must grant certain privileges directly, such as the privileges required to connect to the database and manage database objects.
In addition to the different types of users, you also need to determine which type of security model you will implement: trusted or untrusted. With a trusted security model, the receiver has access to all local master groups. Because the receiver performs database activities at the local master site on behalf of the propagator at the remote site, the propagator also has access to all master groups at the receiver's site. Remember that a single receiver is used for all incoming transactions.
For example, consider the scenario in Figure A-1. Even though only Master Groups A and C exist at Master Site B, the propagator has access to Master Groups A, B, C, and D at Master Site A because the trusted security model has been used. While this greatly increases the flexibility of database administration, due to the mobility of remote database administration, 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, Oracle automatically grants the appropriate privileges for objects as they are added to or removed from a replication environment.
Untrusted security assigns only the privileges to the receiver that are required to work with specified master groups. The propagator, therefore, can only access the specified master groups that are local to the receiver. Figure A-2 illustrates an untrusted security model. Because Master Site B contains only Master Groups A and C, the receiver at Master Site A has been granted privileges for Master Groups A and C only, thereby limiting the propagator's access at Master Site A.
Typically, master sites are considered trusted and therefore the trusted security model is used. If, however, your remote master sites are untrusted, then you might want to use the untrusted model and assign your receiver limited privileges. A site might be considered untrusted, for example, if a consulting shop performs work for multiple customers. Use the appropriate API call listed for the receiver in Table A-1 to assign the different users the appropriate privileges.
Table A-1 Required User Accounts
| User | Privileges |
|---|---|
| global replication administrator | DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA |
| schema-level replication administrator | DBMS_REPCAT_ADMIN.GRANT_ADMIN_SCHEMA |
| propagator | DBMS_DEFER_SYS.REGISTER_PROPAGATOR |
| receiver | See "REGISTER_USER_REPGROUP Procedure" for details.
Trusted: DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP privilege => 'receiver' list_of_gnames => NULL, ... Untrusted: DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP
privilege => 'receiver'
list_of_gnames => 'mastergroupname',
...
|
After you have created these accounts and assigned the appropriate privileges, create the following private database links, including username and password between each site:
From the local replication administrator to the remote replication administrator.
From the local propagator to the remote receiver.
Assuming you have designated a single user account to act as replication administrator, propagator, and receiver, you must create N(N-1) links, where N is the number of master sites in your replication environment.
After creating these links, you must call DBMS_DEFER_SYS.SCHEDULE_PUSH and DBMS_DEFER_SYS.SCHEDULE_PURGE, at each location, to define how frequently you want to propagate your deferred transaction queue to each remote location, and how frequently you wish to purge this queue. You must call DBMS_DEFER_SYS.SCHEDULE_PUSH multiple times at each site, once for each remote location.
A sample script for setting up multimaster replication between hq.world and sales.world is shown as follows:
/*--- Create global replication administrator at HQ ---*/
CONNECT SYSTEM/MANAGER@hq.world
CREATE USER repadmin IDENTIFIED BY repadmin;
EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');
/*--- Create global replication administrator at Sales ---*/
CONNECT SYSTEM/MANAGER@sales.world
CREATE USER repadmin IDENTIFIED BY repadmin;
EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');
/*--- Create single user to act as both propagator and receiver at HQ ---*/
CONNECT SYSTEM/MANAGER@hq.world
CREATE USER prop_rec IDENTIFIED BY prop_rec;
/*--- Grant privileges necessary to act as propagator ---*/
EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'prop_rec');
/*--- Grant privileges necessary to act as receiver ---*/
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(
username => 'prop_rec',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/
/*--- Create single user to act as both propagator and receiver at Sales ---*/
CONNECT SYSTEM/MANAGER@sales.world
CREATE USER prop_rec IDENTIFIED BY prop_rec;
/*--- Grant privileges necessary to act as propagator ---*/execute
EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'prop_rec');
/*--- Grant privileges necessary to act as receiver ---*/
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(
username => 'prop_rec',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/
/*--- Create public link from HQ to Sales with necessary USING clause ---*/
CONNECT SYSTEM/MANAGER@hq.world
CREATE PUBLIC DATABASE LINK sales.world USING 'sales.world';
/*--- Create private repadmin to repadmin link ---*/
CONNECT repadmin/repadmin@hq.world
CREATE DATABASE LINK sales.world CONNECT TO repadmin IDENTIFIED BY repadmin;
/*--- Schedule replication from HQ to Sales ---*/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'sales.world',
interval => 'sysdate + 1/24',
next_date => sysdate,
stop_on_error => FALSE,
parallelism => 1);
END;
/
/*--- Schedule purge of def tran queue at HQ ---*/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => sysdate,
interval => 'sysdate + 1',
delay_seconds => 0,
rollback_segment => '');
END;
/
/*--- Create link from propagator to receiver for scheduled push ---*/
CONNECT prop_rec/prop_rec@hq.world
CREATE DATABASE LINK sales.world CONNECT TO prop_rec IDENTIFIED BY prop_rec;
/*--- Create public link from Sales to HQ with necessary USING clause ---*/
CONNECT system/manager@sales.world
CREATE PUBLIC DATABASE LINK hq.world USING 'hq.world';
/*--- Create private repadmin to repadmin link ---*/
CONNECT repadmin/repadmin@sales.world
CREATE DATABASE LINK hq.world CONNECT TO repadmin IDENTIFIED BY repadmin;
/*--- Schedule replication from Sales to HQ ---*/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'hq.world',
interval => 'sysdate + 1/24',
next_date => sysdate,
stop_on_error => FALSE,
parallelism => 1);
END;
/
/*--- Schedule purge of def tran queue at Sales ---*/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => sysdate,
interval => 'sysdate + 1',
delay_seconds => 0,
rollback_segment =>'');
END;
/
/*--- Create link from propagator to receiver for scheduled push ---*/
CONNECT prop_rec/prop_rec@sales.world
CREATE DATABASE LINK hq.world connect TO prop_rec IDENTIFIED BY prop_rec;
Nearly all users should find it easiest to use the Replication Manager Setup Wizard when configuring materialized view replication security. However, for certain specialized cases, you might 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 materialized view 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 or master materialized view site. You might need to create additional users, or assign additional privileges to users at the associated master site or master materialized view site.
In addition to end users who will be accessing replicated objects, there are three special categories of "users" at a materialized view 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 materialized views from the associated master tables or master materialized views.
Typically, a single user performs each of these functions. However, there might be situations where you need different users performing these functions. For example, materialized views can be created by a materialized view site administrator and refreshed by another end user.
Table A-2 describes the privileges needed to create and maintain a materialized view site.
Table A-2 Required Materialized View Site User Accounts
| User | Privileges |
|---|---|
| Materialized view site replication administrator | DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA |
| Propagator | DBMS_DEFER_SYS.REGISTER_PROPAGATOR |
| Refresher | CREATE ANY MATERIALIZED VIEW ALTER ANY MATERIALIZED VIEW |
In addition to creating the appropriate users at the materialized view site, you might need to create additional users at the associated master site or master materialized view site, as well. Table A-3 on describes the privileges need by master site or master materialized view site users to support a new materialized view site.
In addition to the different users at the master site or master materialized view 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 materialized view administrator have access to all local replication groups. The receiver and proxy materialized view administrator perform database activities at the local master site or master materialized view site on behalf of the propagator and materialized view administrator, respectively, at the remote materialized view site. Therefore, the propagator and materialized view administrator at the remote materialized view site also have access to all replication groups at the master site or master materialized view site. Remember that a single receiver is used for all incoming transactions.
For example, consider the scenario in Figure A-3. Even though Materialized View Groups A and C exist at the materialized view site (based on Master Groups A and C at the Master Site), the propagator and materialized view 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, Oracle automatically grants the appropriate privileges for objects as they are added to or removed from a replication environment.
Figure A-3 Trusted Security: Materialized View Replication
Untrusted security assigns only the privileges to the proxy materialized view administrator and receiver that are required to work with specified replication groups. The propagator and materialized view administrator, therefore, can only access these specified replication groups at the Master Site. Figure A-4 illustrates an untrusted security model with materialized view replication. Because the Materialized View Site contains Materialized View Groups A and C, access to only Master Groups A and C are required. Using untrusted security does not allow the propagator or the materialized view administrator at the Materialized View Site to access Master Groups B and D at the Master Site.
Figure A-4 Untrusted Security: Materialized View Replication
Typically, materialized view 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 materialized view site and it is recommended that you use the untrusted security model with materialized view sites.
One reason you might choose to use a trusted security model is if your materialized view site is considered a master site in every way (security, constant network connectivity, resources) but is a materialized view only because of data subsetting requirements. Remember that row and column subsetting are not supported in a multimaster configuration.
Use the appropriate API calls listed for the proxy materialized view administrator and receiver in Table A-3 to assign the different users the appropriate privileges.
Table A-3 Required Master Site or Master Materialized View Site User Accounts
| User | Privileges |
|---|---|
| proxy materialized view site administrator | See "REGISTER_USER_REPGROUP Procedure" for details.
Trusted: DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP privilege => 'proxy_snapadmin' list_of_gnames => NULL, ... Untrusted: DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP
privilege => 'proxy_snapadmin'
list_of_gnames => 'mastergroupname',
...
|
| receiver | See "REGISTER_USER_REPGROUP Procedure" for details.
Trusted:
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP
privilege => 'receiver'
list_of_gnames => NULL,
...
Untrusted: DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP
privilege => 'receiver'
list_of_gnames => 'mastergroupname',
...
|
| proxy refresher | Trusted:
Grant Untrusted: Grant |
After creating the accounts at both the materialized view and associated master sites or master materialized view sites, you need to create the following private database links, including username and password, from the materialized view site to the master site or master materialized view site:
From the materialized view replication administrator to the proxy materialized view replication administrator.
From the propagator to the receiver.
From the refresher to the proxy refresher.
From the materialized view owner to the master site or master materialized view site for refreshes.
Assuming you have designated a single user account to act as materialized view administrator, propagator, and refresher, you must create one link for each materialized view site for those functions. You do not need a link from the master site or master materialized view site to the materialized view site.
After creating these links, you must call DBMS_DEFER_SYS.SCHEDULE_PUSH and DBMS_DEFER_SYS.SCHEDULE_PURGE at the materialized view site to define how frequently you want to propagate your deferred transaction queue to the associated master site or master materialized view site, and how frequently you wish to purge this queue. You must also call DBMS_REFRESH.REFRESH at the materialized view site to schedule how frequently to pull changes from the associated master site or master materialized view site.
