Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 15.1 DBMS_REPCAT_AUTH: Setting Up Administrative AccountsChapter 15
Advanced Replication
Next: 15.3 DBMS_REPCAT: Replication Environment Administration
 

15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts

Along with DBMS_REPCAT_AUTH, use the DBMS_REPCAT_ADMIN package to create administrator accounts for replication. This section explains how to do so.

15.2.1 Getting Started with DBMS_REPCAT_ADMIN

The DBMS_REPCAT_ADMIN package is created when the Oracle database is installed. The dbmsrepc.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped sql script prvtrepc.sql creates the public synonym DBMS_REPCAT_ADMIN. No EXECUTE privileges are granted on DBMS_REPCAT_ADMIN; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

15.2.1.1 DBMS_REPCAT_ADMIN programs

Table 15.2 lists the programs in DBMS_REPCAT_ADMIN


Table 15.2: DBMS_REPCAT_ADMIN Program

Name

Description

Use in SQL?

GRANT_ADMIN_ANY_REPGROUP

Grants privileges required to administer any replication group at the current site

No

GRANT_ADMIN_ANY_REPSCHEMA

(Oracle8)

Grants privileges required to administer any replication schema at the current site

No

GRANT_ADMIN_REPGROUP

Grants privileges required to administer the replication group for which the user is the schema owner

No

GRANT_ADMIN_REPSCHEMA

(Oracle8)

Grants privileges required to administer the replication schema for which the user is the schema owner

No

REVOKE_ADMIN_ANY_REPGROUP

Revokes privileges required to administer all replication groups

No

REVOKE_ADMIN_ANY_REPSCHEMA

(Oracle8)

Revokes privileges required to administer all replication schemas

No

REVOKE_ADMIN_REPGROUP

Revokes privileges required to administer the replication group for which the user is the schema owner

No

REVOKE_ADMIN_REPSCHEMA

(Oracle8)

Revokes privileges required to administer the replication schema for which the user is the schema owner.

No

15.2.1.2 DBMS_REPCAT_ADMIN exceptions

The DBMS_REPCAT_ADMIN package may raise exception ORA-1917 if the specified user does not exist.

NOTE: Oracle8 documents only the REPGROUP procedures, although the REPSCHEMA procedures also exist. The functionality is identical.

15.2.2 Creating and Dropping Replication Administrator Accounts

Advanced replication also requires an account to maintain the environment. The replication administrator account (usually REPADMIN) performs tasks such as quiescing the environment, adding and removing master sites, and creating replication groups. For example, you'll use DBMS_REPCAT's ADMIN.GRANT_ADMIN_ANY_REPGROUP procedure to set up the replication administrator account to maintain all replication groups in your environment. You can also configure an account to control exactly one schema in a replication group with the GRANT_ADMIN_REPGROUP procedure. The recipient of this grant will be able to perform administrative tasks on objects it owns within a replication group; the account will not be able to administer objects it does not own. Because of this restriction, it usually makes sense to create administrative accounts for a specific group only if it is a single schema replication group and the administrative account is the schema account.

In most cases, the DBA opts for using a single replication administrator account over creating administrative accounts for every replication group.

15.2.2.1 The DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP procedure

The GRANT_ADMIN_REPGROUP procedure grants the privileges required to administer a replication group for which the user is the schema owner. The specification is,

PROCEDURE DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP
   (userid IN VARCHAR2);

where userid is the Oracle userid for whom you are granting privileges.

15.2.2.1.1 Exceptions

DBMS_REPCAT_ADMIN.GRANT_ADMIN.REPGROUP may raise exception ORA-1917 if the specified user does not exist.

15.2.2.1.2 Example

This call configures the SPROCKET account to administer its objects in a replication group:

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP('SPROCKET');
END;

This approach is most viable when the name of the replication group is the same as the name of the schema, and when all objects in the replication group belong to that schema.

15.2.2.2 The DBMS_REPCAT_ADMIN.REVOKE_ADMIN_REPGROUP procedure

The REVOKE_ADMIN_REPGROUP procedure revokes the privileges required to administer the replication group for which the user is the schema owner. The specification is:

PROCEDURE DBMS_REPCAT_ADMIN.REVOKE_ADMIN_REPGROUP
   (userid IN VARCHAR2);

where userid is the Oracle userid for whom you are revoking privileges.

15.2.2.2.1 Exceptions

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_REPGROUP may raise exception ORA-1917 if the specified user does not exist.

15.2.2.3 The DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP procedure

The GRANT_ADMIN_ANY_REPGROUP procedure grants the privileges required to administer any replication group at the current site. The specification is,

PROCEDURE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP
   (userid IN VARCHAR2);

where userid is the Oracle userid for whom you are granting privileges.

15.2.2.3.1 Exceptions

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP may raise exception ORA-1917 if the specified user does not exist.

15.2.2.3.2 Example

The following call supplies the REPADMIN account with privileges to perform maintenance operations on all replication groups at the site:

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP('REPADMIN');
END;

NOTE: Be sure to set up a replication administrator account at every master site of a multimaster replication environment. In addition, administration will be easiest if you use the same account name in all locations.

15.2.2.4 The DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_REPGROUP procedure

The REVOKE_ADMIN_ANY_REPGROUP procedure revokes the privileges required to administer any replication group at the current site. The specification is,

PROCEDURE DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_REPGROUP
   (userid IN VARCHAR2);

where userid is the Oracle userid for whom you are revoking privileges.

15.2.2.4.1 Exceptions

DBMS_REPCAT_ADMIN.REVOKE_ANY_REPGROUP may raise exception ORA-1917 if the specified user does not exist.

15.2.2.4.2 Example

This call revokes replication administrator privileges from the REPADMIN account:

BEGIN
    DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_REPGROUP('REPADMIN');
END;


Previous: 15.1 DBMS_REPCAT_AUTH: Setting Up Administrative AccountsOracle Built-in PackagesNext: 15.3 DBMS_REPCAT: Replication Environment Administration
15.1 DBMS_REPCAT_AUTH: Setting Up Administrative AccountsBook Index15.3 DBMS_REPCAT: Replication Environment Administration

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.