97
DBMS_STREAMS_AUTH

The DBMS_STREAMS_AUTH package, one of a set of Streams packages, provides interfaces for granting privileges to Streams administrators and revoking privileges from Streams administrators.

See Also: Oracle Streams Concepts and Administration for more information about this package and Streams administrators

This chapter contains the following topic:

• Summary of DBMS_STREAMS_AUTH Subprograms

---

Summary of DBMS_STREAMS_AUTH Subprograms

Table 97-1  DBMS_STREAMS_AUTH Package Subprograms

Subprogram	Description
GRANT_ADMIN_PRIVILEGE Procedure	Either grants the privileges needed by a user to be a Streams administrator directly, or generates a script that can be used to grant these privileges
GRANT_REMOTE_ADMIN_ACCESS Procedure	Enables a remote Streams administrator to perform administrative actions at the local database by connecting to the grantee using a database link
REVOKE_ADMIN_PRIVILEGE Procedure	Either revokes Streams administrator privileges from a user directly, or generates a script that can be used to revoke these privileges
REVOKE_REMOTE_ADMIN_ACCESS Procedure	Disables a remote Streams administrator from performing administrative actions by connecting to the grantee using a database link

---

GRANT_ADMIN_PRIVILEGE Procedure

This procedure either grants the privileges needed by a user to be a Streams administrator directly, or generates a script that can be used to grant these privileges.

Syntax

DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
   grantee           IN  VARCHAR2,
   grant_privileges  IN  BOOLEAN   DEFAULT true,
   file_name         IN  VARCHAR2  DEFAULT NULL,
   directory_name    IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 97-2  GRANT_ADMIN_PRIVILEGE Procedure Parameters

Parameter	Description
grantee	The user to whom privileges are granted
grant_privileges	If true, then the GRANT_ADMIN_PRIVILEGE procedure grants the privileges to the specified grantee directly, and adds the grantee to the DBA_STREAMS_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 this procedure does not make another entry, and no error is raised. If true and any of the grant statements fail, then an error is raised. If false, then the GRANT_ADMIN_PRIVILEGE procedure does not grant the privileges to the specified grantee directly, and does not add the grantee to the DBA_STREAMS_ADMINISTRATOR data dictionary view. You specify false when this 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 an error is raised.
file_name	The name of the file generated by this 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 this 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 this procedure must have WRITE privilege on the directory object. If the file_name parameter is NULL, then this parameter is ignored, and this procedure does not generate a file. If NULL and the file_name parameter is non-NULL, then an error is raised.

Usage Notes

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

Specifically, this 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_PROPAGATION_ADM
  • DBMS_RULE_ADM
  • DBMS_STREAMS_ADM
  • DBMS_STREAMS_MESSAGING
  • DBMS_TRANSFORM
• Privileges to enqueue events into and dequeue events 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 has the ability to grant these privileges to other users.

• SELECT privilege on data dictionary views related to Streams
• The ability to allow a remote Streams 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 this procedure in detail, you can use the procedure to generate a script and then view the script in a text editor. This procedure does not grant any roles to the grantee. This procedure grants only the privileges necessary to configure and administer a Streams environment. You may grant more privileges to the grantee if necessary.

  See Also: "GRANT_REMOTE_ADMIN_ACCESS Procedure" Oracle Streams Concepts and Administration for more information about configuring a Streams administrator

---

GRANT_REMOTE_ADMIN_ACCESS Procedure

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

Syntax

DBMS_STREAMS_AUTH.GRANT_REMOTE_ADMIN_ACCESS(
   grantee  IN  VARCHAR2);

Parameters

Table 97-3  GRANT_REMOTE_ADMIN_ACCESS Procedure Parameter

Parameter	Description
grantee	The user who allows remote access. The procedure adds the grantee to the DBA_STREAMS_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 this procedure does not make another entry. Instead, it updates the ACCESS_FROM_REMOTE column to YES.

Usage Notes

Typically, you run this procedure and specify a grantee at a local source database if a downstream capture process captures changes originating at the local source database. The Streams administrator at a downstream capture database administers the source database using this connection. You may also run this procedure at a database running an apply process so that a remote Streams administrator can set instantiation SCNs at the local database.

Note: The GRANT_ADMIN_PRIVILEGE procedure runs this procedure.

See Also: "GRANT_ADMIN_PRIVILEGE Procedure"

---

REVOKE_ADMIN_PRIVILEGE Procedure

This procedure either revokes Streams administrator privileges from a user directly, or generates a script that can be used to revoke these privileges.

Syntax

DBMS_STREAMS_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 97-4  REVOKE_ADMIN_PRIVILEGE Procedure Parameters

Parameter	Description
grantee	The user from whom privileges are revoked
revoke_privileges	If true, then the REVOKE_ADMIN_PRIVILEGE procedure revokes the privileges from the specified user directly, and removes the user from the DBA_STREAMS_ADMINISTRATOR data dictionary view. If the user does not have a record in this data dictionary view, then this procedure does not remove a record from the view, and no error is raised. If true and any of the revoke statements fail, then an error is raised. A revoke statement will fail if the user is not granted the privilege that is being revoked. If false, then the REVOKE_ADMIN_PRIVILEGE procedure does not revoke the privileges to the specified user directly, and does not remove the user from the DBA_STREAMS_ADMINISTRATOR data dictionary view. You specify false when this 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 an error is raised.
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 this 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 this procedure must have WRITE privilege on the directory object. If the file_name parameter is NULL, then this parameter is ignored, and this procedure does not generate a file. If NULL and the file_name parameter is non-NULL, then an error is raised.

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: "GRANT_ADMIN_PRIVILEGE Procedure"

---

REVOKE_REMOTE_ADMIN_ACCESS Procedure

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

Note: The REVOKE_ADMIN_PRIVILEGE procedure runs this procedure.

See Also: "REVOKE_ADMIN_PRIVILEGE Procedure"

Syntax

DBMS_STREAMS_AUTH.REVOKE_REMOTE_ADMIN_ACCESS(
   grantee  IN  VARCHAR2);

Parameters

Table 97-5 REVOKE_REMOTE_ADMIN_ACCESS Procedure Parameter

Parameter

Description

grantee

The user for whom access from a remote Streams administrator is disabled. If a row for the grantee exists in the DBA_STREAMS_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 this procedure removes the grantee from the view. If no row for the grantee exists in the DBA_STREAMS_ADMINISTRATOR data dictionary view, then the procedure does not update the view and does not raise an error.