Skip Headers
Oracle® Database Advanced Replication Management API Reference
11g Release 2 (11.2)

Part Number E10707-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

A Security Options

This appendix describes security options for multimaster and materialized view replication environments.

This appendix contains these topics:

Security Setup for Multimaster Replication

Nearly all users should find it easiest to use the configuration wizards in the Advanced Replication interface in Oracle Enterprise Manager 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:

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.

Trusted Compared with Untrusted Security

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.

Figure A-1 Trusted Security: Multimaster Replication

Description of Figure A-1 follows
Description of "Figure A-1 Trusted Security: Multimaster Replication"

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.

Figure A-2 Untrusted Security: Multimaster Replication

Description of Figure A-2 follows
Description of "Figure A-2 Untrusted Security: Multimaster Replication"

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 user name 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 want 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.example.com and sales.example.com is shown as follows:

/*--- Create global replication administrator at HQ ---*/
CONNECT system@hq.example.com
ACCEPT password PROMPT 'Enter password for user: ' HIDE
CREATE USER repadmin IDENTIFIED BY &password;
EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');

/*--- Create global replication administrator at Sales ---*/
CONNECT system@sales.example.com
CREATE USER repadmin IDENTIFIED BY &password;
EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');

/*--- Create single user to act as both propagator and receiver at HQ ---*/
CONNECT system@hq.example.com
CREATE USER prop_rec IDENTIFIED BY &password;
/*--- 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@sales.example.com
CREATE USER prop_rec IDENTIFIED BY &password;
/*--- 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@hq.example.com
CREATE PUBLIC DATABASE LINK sales.example.com USING 'sales.example.com';

/*--- Create private repadmin to repadmin link ---*/
CONNECT repadmin@hq.example.com
CREATE DATABASE LINK sales.example.com CONNECT TO repadmin 
 IDENTIFIED BY &password;

/*--- Schedule replication from HQ to Sales ---*/
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH(
     destination => 'sales.example.com',
     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.example.com
CREATE DATABASE LINK sales.example.com CONNECT TO prop_rec 
 IDENTIFIED BY &password;

/*--- Create public link from Sales to HQ with necessary USING clause ---*/
CONNECT system@sales.example.com
CREATE PUBLIC DATABASE LINK hq.example.com USING 'hq.example.com';

/*--- Create private repadmin to repadmin link ---*/
CONNECT repadmin@sales.example.com
CREATE DATABASE LINK hq.example.com CONNECT TO repadmin IDENTIFIED BY &password;

/*--- Schedule replication from Sales to HQ ---*/
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH(
     destination => 'hq.example.com',
     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.example.com
CREATE DATABASE LINK hq.example.com connect TO prop_rec IDENTIFIED BY &password;

Security Setup for Materialized View Replication

Nearly all users should find it easiest to use the configuration wizards in the Advanced Replication interface in Oracle Enterprise Manager 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:

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.

Trusted Compared with Untrusted Security

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

Description of Figure A-3 follows
Description of "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

Description of Figure A-4 follows
Description of "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 CREATE SESSION Grant SELECT ANY TABLE

Untrusted:

Grant CREATE SESSION Grant SELECT on necessary master tables or master materialized views and materialized view logs


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 user name 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 want 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.