20 DBMS_REPCAT_ADMIN

DBMS_REPCAT_ADMIN enables you to create users with the privileges needed by the symmetric replication facility.

This chapter contains this topic:

Summary of DBMS_REPCAT_ADMIN Subprograms

Summary of DBMS_REPCAT_ADMIN Subprograms

Table 20-1 DBMS_REPCAT_ADMIN Package Subprograms

Subprogram	Description
"GRANT_ADMIN_ANY_SCHEMA Procedure"	Grants the necessary privileges to the replication administrator to administer any replication group at the current site.
"GRANT_ADMIN_SCHEMA Procedure"	Grants the necessary privileges to the replication administrator to administer a schema at the current site.
"REGISTER_USER_REPGROUP Procedure"	Assigns proxy materialized view administrator or receiver privileges at the master site or master materialized view site for use with remote sites.
"REVOKE_ADMIN_ANY_SCHEMA Procedure"	Revokes the privileges and roles from the replication administrator that were granted by `GRANT_ADMIN_ANY_SCHEMA`.
"REVOKE_ADMIN_SCHEMA Procedure"	Revokes the privileges and roles from the replication administrator that were granted by `GRANT_ADMIN_SCHEMA`.
"UNREGISTER_USER_REPGROUP Procedure"	Revokes the privileges and roles from the proxy materialized view administrator or receiver that were granted by the `REGISTER_USER_REPGROUP` procedure.

GRANT_ADMIN_ANY_SCHEMA Procedure

This procedure grants the necessary privileges to the replication administrator to administer any replication groups at the current site.

Syntax

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
   username  IN   VARCHAR2);

Parameters

Table 20-2 GRANT_ADMIN_ANY_SCHEMA Procedure Parameters

Parameter	Description
username	Name of the replication administrator to whom you want to grant the necessary privileges and roles to administer any replication groups at the current site.

Exceptions

Table 20-3 GRANT_ADMIN_ANY_SCHEMA Procedure Exceptions

Exception	Description
ORA-01917	User does not exist.

GRANT_ADMIN_SCHEMA Procedure

This procedure grants the necessary privileges to the replication administrator to administer a schema at the current site. This procedure is most useful if your replication group does not span schemas.

The privileges granted by this procedure are more limited than the privileges granted by GRANT_ADMIN_ANY_SCHEMA. However, a replication administrator who is granted privileges with GRANT_ADMIN_SCHEMA still can perform certain administrative activities on replication groups owned by other replication administrators. For example, a replication administrator who is granted privileges with GRANT_ADMIN_SCHEMA can drop replication groups and replication objects owned by other replication administrators.

Note:

To restrict different users to different replicated groups, you can write a wrapper package on top of the DBMS_REPCAT package and grant EXECUTE privilege on the new package, but not on the DBMS_REPCAT package, to each user. The new package performs security checks. For example, the new package can dictate that hr can administer the hr_rg replication group, but no other replication group, and that hr only can administer objects in the hr schema. If the security checks are passed, then the new package calls a subprogram in the DBMS_REPCAT package. If the security checks are not passed, then the new package could log the failure, commit, and raise an exception.

Parameter	Description
username	Name of the replication administrator. This user is then granted the necessary privileges and roles to administer the schema of the same name within a replication group at the current site.

REGISTER_USER_REPGROUP Procedure

This procedure assigns proxy materialized view administrator or receiver privileges at the master site or master materialized view site for use with remote sites. This procedure grants only the necessary privileges to the proxy materialized view administrator or receiver. It does not grant the powerful privileges granted by the GRANT_ADMIN_SCHEMA or GRANT_ADMIN_ANY_SCHEMA procedures.

See Also:

Appendix A, "Security Options" for more information about trusted versus untrusted security models

Syntax

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
   username         IN   VARCHAR2,
   privilege_type   IN   VARCHAR2,
   {list_of_gnames  IN   VARCHAR2 |
   table_of_gnames  IN   DBMS_UTILITY.NAME_ARRAY)};

Note:

This procedure is overloaded. The list_of_gnames and table_of_gnames parameters are mutually exclusive.

Parameters

Table 20-6 REGISTER_USER_REPGROUP Procedure Parameters

Parameter	Description
username	Name of the user to whom you are giving either proxy materialized view administrator or receiver privileges.
privilege_type	Specifies the privilege type you are assigning. Use the following values for to define your `privilege_type`: `receiver` for receiver privileges `proxy_snapadmin` for proxy materialized view administration privileges
list_of_gnames	Comma-delimited list of replication groups you want a user registered for receiver privileges. There must be no spaces between entries in the list. If you set `list_of_gnames` to `NULL`, then the user is registered for all replication groups, even replication groups that are not yet known when this procedure is called. You must use named notation to set `list_of_gnames` to `NULL`. An invalid replication group in the list causes registration to fail for the entire list.
table_of_gnames	PL/SQL associative array of replication groups you want a user registered for receiver privileges. The PL/SQL associative array must be of type `DBMS_UTILITY`.`NAME_ARRAY`. This table is 1-based (the positions start at 1 and increment by 1). Use the single value `NULL` to register the user for all replication groups. An invalid replication group in the table causes registration to fail for the entire table.

Exceptions

Table 20-7 REGISTER_USER_REPGROUP Procedure Exceptions

Exception	Description
nonmaster	Specified replication group does not exist or the invocation database is not a master site or master materialized view site.
ORA-01917	User does not exist.
typefailure	Incorrect privilege type was specified.

REVOKE_ADMIN_ANY_SCHEMA Procedure

This procedure revokes the privileges and roles from the replication administrator that were granted by GRANT_ADMIN_ANY_SCHEMA.

Note:

Identical privileges and roles that were granted independently of GRANT_ADMIN_ANY_SCHEMA are also revoked.

Syntax

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_ANY_SCHEMA (
   username IN VARCHAR2);

Parameters

Table 20-8 REVOKE_ADMIN_ANY_SCHEMA Procedure Parameters

Parameter	Description
username	Name of the replication administrator whose privileges you want to revoke.

Exceptions

Table 20-9 REVOKE_ADMIN_ANY_SCHEMA Procedure Exceptions

Exception	Description
ORA-01917	User does not exist.

REVOKE_ADMIN_SCHEMA Procedure

This procedure revokes the privileges and roles from the replication administrator that were granted by GRANT_ADMIN_SCHEMA.

Note:

Identical privileges and roles that were granted independently of GRANT_ADMIN_SCHEMA are also revoked.

Syntax

DBMS_REPCAT_ADMIN.REVOKE_ADMIN_SCHEMA (
   username IN VARCHAR2);

Parameters

Table 20-10 REVOKE_ADMIN_SCHEMA Procedure Parameters

Parameter	Description
username	Name of the replication administrator whose privileges you want to revoke.

Exceptions

Table 20-11 REVOKE_ADMIN_SCHEMA Procedure Exceptions

Exception	Description
ORA-01917	User does not exist.

UNREGISTER_USER_REPGROUP Procedure

This procedure revokes the privileges and roles from the proxy materialized view administrator or receiver that were granted by the REGISTER_USER_REPGROUP procedure.

Syntax

DBMS_REPCAT_ADMIN.UNREGISTER_USER_REPGROUP (
   username         IN   VARCHAR2,
   privilege_type   IN   VARCHAR2,
   {list_of_gnames  IN   VARCHAR2 |
   table_of_gnames  IN   DBMS_UTILITY.NAME_ARRAY)};

Note:

This procedure is overloaded. The list_of_gnames and table_of_gnames parameters are mutually exclusive.

Parameters

Table 20-12 UNREGISTER_USER_REPGROUP Procedure Parameters

Parameter	Description
username	Name of the user you are unregistering.
privilege_type	Specifies the privilege type you are revoking. Use the following values for to define your `privilege_type`: `receiver` for receiver privileges `proxy_snapadmin` for proxy materialized view administration privileges
list_of_gnames	Comma-delimited list of replication groups you want a user unregistered for receiver privileges. There must be no spaces between entries in the list. If you set `list_of_gnames` to `NULL`, then the user is unregistered for all replication groups registered. You must use named notation to set `list_of_gnames` to `NULL`. An invalid replication group in the list causes unregistration to fail for the entire list.
table_of_gnames	PL/SQL associative array of replication groups you want a user unregistered for receiver privileges. The PL/SQL associative array must be of type `DBMS_UTILITY`.`NAME_ARRAY`. This table is 1-based (the positions start at 1 and increment by 1). Use the single value `NULL` to unregister the user for all replication groups registered. An invalid replication group in the table causes unregistration to fail for the entire table.

Exceptions

Table 20-13 UNREGISTER_USER_REPGROUP Procedure Exceptions

Exception	Description
nonmaster	Specified replication group does not exist or the invocation database is not a master site or master materialized view site.
ORA-01917	User does not exist.
typefailure	Incorrect privilege type was specified.