9 DBMS_XSTREAM_AUTH

The DBMS_XSTREAM_AUTH package provides subprograms for granting privileges to and revoking privileges from XStream administrators.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

This chapter contains the following topic:

See Also:

"Granting Privileges for the XStream Administrator"

Using DBMS_XSTREAM_AUTH

This section contains topics which relate to using the DBMS_XSTREAM_AUTH package.

Overview

This package provides subprograms for granting privileges to XStream administrators and revoking privileges from XStream administrators.

See Also:

"Granting Privileges for the XStream Administrator"

Security Model

Security on this package can be controlled in either of the following ways:

  • Granting EXECUTE on this package to selected users or roles.

  • Granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

To ensure that the user who runs the subprograms in this package has the necessary privileges, connect as an administrative user who can create users, grant privileges, and create tablespaces when using this package.

Summary of DBMS_XSTREAM_AUTH Subprograms

Table 9-1 DBMS_XSTREAM_AUTH Package Subprograms

Subprogram Description

GRANT_ADMIN_PRIVILEGE Procedure

Either grants the privileges needed by a user to be an XStream administrator directly, or generates a script that grants these privileges

GRANT_REMOTE_ADMIN_ACCESS Procedure

Enables a remote XStream administrator to perform administrative actions at the local database by connecting to the grantee using a database link

REVOKE_ADMIN_PRIVILEGE Procedure

Either revokes XStream administrator privileges from a user directly, or generates a script that revokes these privileges

REVOKE_REMOTE_ADMIN_ACCESS Procedure

Disables a remote XStream administrator from performing administrative actions by connecting to the grantee using a database link

Note:

All subprograms commit unless specified otherwise.

GRANT_ADMIN_PRIVILEGE Procedure

This procedure either grants the privileges needed by a user to be an XStream administrator directly, or generates a script that grants these privileges.

See Also:

"Granting Privileges for the XStream Administrator"

Syntax

DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
   grantee                    IN  VARCHAR2,
   privilege_type             IN  VARCHAR2  DEFAULT '*',
   grant_select_privileges    IN  BOOLEAN   DEFAULT FALSE,
   do_grants                  IN  BOOLEAN   DEFAULT TRUE,
   file_name                  IN  VARCHAR2  DEFAULT NULL,
   directory_name             IN  VARCHAR2  DEFAULT NULL
   grant_optional_privileges  IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 9-2 GRANT_ADMIN_PRIVILEGE Procedure Parameters

Parameter Description

grantee

The user to whom privileges are granted

privilege_type

Specify one of the following values:

  • CAPTURE

    Specifying CAPTURE grants the minimum privileges required by the user to administer capture processes.

  • APPLY

    Specifying APPLY grants the minimum privileges required by the user to administer outbound servers, inbound servers, and apply processes.

  • *

    Specifying * grants the minimum privileges required by the user to administer capture processes, outbound servers, inbound servers, and apply processes.

grant_select_privileges

If TRUE, then the procedure grants a set of privileges, including SELECT_CATALOG_ROLE, to the user.

If FALSE, then the procedure does not grant the set of privileges to the user.

SELECT_CATALOG_ROLE enables the user to select from the data dictionary. Set this parameter to TRUE for the XStream trusted user model. Set this parameter to FALSE for the XStream untrusted user model.

do_grants

If TRUE, then the procedure grants the privileges to the specified grantee directly, and adds the grantee to the DBA_XSTREAM_ADMINISTRATOR data dictionary view with YES for both the LOCAL_PRIVILEGES column and the ACCESS_FROM_REMOTE column. If the user already has an entry in this data dictionary view, then the procedure does not make another entry, and no error is raised. If TRUE and any of the grant statements fails, then the procedure raises an error.

If FALSE, then the procedure does not grant the privileges to the specified grantee directly, and does not add the grantee to the DBA_XSTREAM_ADMINISTRATOR data dictionary view.

You specify FALSE when the procedure is generating a file that you will run later. If you specify FALSE and either the file_name or directory_name parameter is NULL, then the procedure raises an error.

file_name

The name of the file generated by the procedure. The file contains all of the statements that grant the privileges. If a file with the specified file name exists in the specified directory name, then the grant statements are appended to the existing file.

If NULL, then the procedure does not generate a file.

directory_name

The directory into which the generated file is placed. The specified directory must be a directory object created using the SQL statement CREATE DIRECTORY. If you specify a directory, then the user who invokes the procedure must have the WRITE privilege on the directory object.

If the file_name parameter is NULL, then this parameter is ignored, and the procedure does not generate a file.

If NULL and the file_name parameter is non-NULL, then the procedure raises an error.

grant_optional_privileges

A comma-separated list of optional privileges to grant to the grantee, such as the DV_XSTREAM_ADMIN and DV_GOLDENGATE_ADMIN privileges

Usage Notes

The user who runs the procedure must be an administrative user who can grant privileges to other users.

Specifically, the procedure grants the following privileges to the specified user:

  • The RESTRICTED SESSION system privilege

  • EXECUTE on the following packages:

    • DBMS_APPLY_ADM

    • DBMS_AQ

    • DBMS_AQADM

    • DBMS_AQIN

    • DBMS_AQELM

    • DBMS_CAPTURE_ADM

    • DBMS_FLASHBACK

    • DBMS_LOCK

    • DBMS_PROPAGATION_ADM

    • DBMS_RULE_ADM

    • DBMS_STREAMS_ADM

    • DBMS_STREAMS_ADVISOR_ADM

    • DBMS_STREAMS_HANDLER_ADM

    • DBMS_STREAMS_MESSAGING

    • DBMS_TRANSFORM

    • DBMS_XSTREAM_ADM

  • Privileges to enqueue messages into and dequeue messages from any queue

  • Privileges to manage any queue

  • Privileges to create, alter, and execute any of the following types of objects in the user's own schema and in other schemas:

    • Evaluation contexts

    • Rule sets

    • Rules

    In addition, the grantee can grant these privileges to other users.

  • SELECT_CATALOG_ROLE

  • SELECT privilege on data dictionary views related to XStream and Oracle Streams

  • The ability to allow a remote XStream administrator to perform administrative actions through a database link by connecting to the grantee

    This ability is enabled by running the GRANT_REMOTE_ADMIN_ACCESS procedure in this package.

    Note:

    • To view all of the statements run by the procedure in detail, you can use the procedure to generate a script and then view the script in a text editor.

    • This procedure grants only the privileges necessary to configure and administer an XStream environment. You can grant additional privileges to the grantee if necessary.

    See Also:

GRANT_REMOTE_ADMIN_ACCESS Procedure

This procedure enables a remote XStream administrator to perform administrative actions at the local database by connecting to the grantee using a database link.

Syntax

DBMS_XSTREAM_AUTH.GRANT_REMOTE_ADMIN_ACCESS(
   grantee  IN  VARCHAR2);

Parameters

Table 9-3 GRANT_REMOTE_ADMIN_ACCESS Procedure Parameter

Parameter Description

grantee

The user who allows remote access. The procedure adds the grantee to the DBA_XSTREAM_ADMINISTRATOR data dictionary view with YES for the ACCESS_FROM_REMOTE column. If the user already has an entry in this data dictionary view, then the procedure does not make another entry. Instead, it updates the ACCESS_FROM_REMOTE column to YES.

Usage Notes

Typically, you run the procedure and specify a grantee at a local source database if a downstream capture process captures changes originating at the local source database. The XStream administrator at a downstream capture database administers the source database using this connection.

Note:

The GRANT_ADMIN_PRIVILEGE procedure in this package runs this procedure.

See Also:

"GRANT_ADMIN_PRIVILEGE Procedure"

REVOKE_ADMIN_PRIVILEGE Procedure

This procedure either revokes XStream administrator privileges from a user directly, or generates a script that revokes these privileges.

Syntax

DBMS_XSTREAM_AUTH.REVOKE_ADMIN_PRIVILEGE(
   grantee            IN  VARCHAR2,  
   revoke_privileges  IN  BOOLEAN   DEFAULT TRUE,
   file_name          IN  VARCHAR2  DEFAULT NULL,
   directory_name     IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 9-4 REVOKE_ADMIN_PRIVILEGE Procedure Parameters

Parameter Description

grantee

The user from whom privileges are revoked

revoke_privileges

If TRUE, then the procedure revokes the privileges from the specified user directly, and removes the user from the DBA_XSTREAM_ADMINISTRATOR data dictionary view. If the user does not have a record in this data dictionary view, then the procedure does not remove a record from the view, and no error is raised. If TRUE and any of the revoke statements fails, then the procedure raises an error. A revoke statement fails if the user is not granted the privilege that is being revoked.

If FALSE, then the procedure does not revoke the privileges from the specified user directly, and does not remove the user from the DBA_XSTREAM_ADMINISTRATOR data dictionary view.

You specify FALSE when the procedure is generating a file that you will edit and then run. If you specify FALSE and either the file_name or directory_name parameter is NULL, then the procedure does not raise an error.

file_name

The name of the file generated by this procedure. The file contains all of the statements that revoke the privileges. If a file with the specified file name exists in the specified directory name, then the revoke statements are appended to the existing file.

If NULL, then the procedure does not generate a file.

directory_name

The directory into which the generated file is placed. The specified directory must be a directory object created using the SQL statement CREATE DIRECTORY. If you specify a directory, then the user who invokes the procedure must have the WRITE privilege on the directory object.

If the file_name parameter is NULL, then this parameter is ignored, and the procedure does not generate a file.

If NULL and the file_name parameter is non-NULL, then the procedure raises an error.

Usage Notes

The user who runs this procedure must be an administrative user who can revoke privileges from other users. Specifically, this procedure revokes the privileges granted by running the GRANT_ADMIN_PRIVILEGE procedure in this package.

Note:

To view all of the statements run by this procedure in detail, you can use the procedure to generate a script and then view the script in a text editor.

See Also:

REVOKE_REMOTE_ADMIN_ACCESS Procedure

This procedure disables a remote XStream administrator from performing administrative actions by connecting to the grantee using a database link.

Note:

The REVOKE_ADMIN_PRIVILEGE procedure in this package runs this procedure.

See Also:

"REVOKE_ADMIN_PRIVILEGE Procedure"

Syntax

DBMS_XSTREAM_AUTH.REVOKE_REMOTE_ADMIN_ACCESS(
   grantee  IN  VARCHAR2);

Parameters

Table 9-5 REVOKE_REMOTE_ADMIN_ACCESS Procedure Parameter

Parameter Description

grantee

The user for whom access from a remote XStream administrator is disabled.

If a row for the grantee exists in the DBA_XSTREAM_ADMINISTRATOR data dictionary view, then the procedure updates the ACCESS_FROM_REMOTE column for the grantee to NO. If, after this update, both the LOCAL_PRIVILEGES column and the ACCESS_FROM_REMOTE column are NO for the grantee, then the procedure removes the grantee from the view.

If no row for the grantee exists in the DBA_XSTREAM_ADMINISTRATOR data dictionary view, then the procedure does not update the view and does not raise an error.