A Security Options

Trusted Compared with Untrusted Security

In addition to the different types of users, you also must 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 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 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.

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP
privilege => 'receiver'
list_of_gnames => NULL,
...

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;

Trusted Compared with Untrusted Security

In addition to the different users at the master site or master materialized view site, you also must 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 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 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.

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP 
privilege => 'proxy_snapadmin'
list_of_gnames => NULL,
...

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP 
privilege => 'proxy_snapadmin'
list_of_gnames => 'mastergroupname',
...

Trusted:
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP 
privilege => 'receiver'
list_of_gnames => NULL,
...

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP 
privilege => 'receiver'
list_of_gnames => 'mastergroupname',
...

After creating the accounts at both the materialized view and associated master sites or master materialized view sites, you must 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.