Search Results revoke_admin_privilege

Overview

SYS.DBMS_GOLDENGATE_AUTH is an Oracle-supplied administrative package that provisions and removes the database privileges required by users who act as Oracle GoldenGate (OGG) administrators, particularly when OGG is integrated with the XStream Out capture and apply infrastructure. In the Oracle EBS 12.1.1 and 12.2.2 environments the package is owned by SYS and is classified as OTHER within the ETRM repository. Its central purpose is to consolidate the many discrete GRANT and REVOKE statements — SELECT_CATALOG_ROLE membership, XStream administrative roles, and a minimal privilege set — into a single callable API, removing the need for administrators to hand-assemble privilege scripts. Because EBS leverages XStream-based replication for reporting, data warehousing, or downstream integrations, the package materially simplifies the setup of a compliant GoldenGate administrator account and reduces configuration errors during instance deployment.

Key Procedures and Functions

• GRANT_ADMIN_PRIVILEGE — Grants the privilege set required for the named grantee to act as an OGG administrator for XStream Out integration. It accepts the grantee user, a privilege type (CAPTURE, APPLY, or both, defaulting to *), a boolean controlling whether SELECT_CATALOG_ROLE is granted, a boolean controlling whether the statements are actually executed versus merely scripted, optional file and directory names for script output via UTL_FILE, a comma-separated list of optional privilege codes (XBADMIN, DV_XSTREAM_ADMIN, DV_GOLDENGATE_ADMIN), and a container argument defaulting to CURRENT. The procedure raises an error if do_grants is FALSE while no file_name is supplied. When do_grants is TRUE, each statement is appended to the output script only after it executes successfully.
• REVOKE_ADMIN_PRIVILEGE — Performs the inverse operation, withdrawing the OGG administrative privileges previously granted to a user. By symmetry with the grant routine, it operates on the same privilege categories so that a decommissioned or reassigned GoldenGate administrator can be cleanly stripped of elevated rights without manual enumeration of individual grants.

Tables Accessed

The ETRM metadata records no directly referenced tables for this package, and it is not accessed through APPS synonyms. Internally, DBMS_GOLDENGATE_AUTH relies on Oracle data dictionary views to resolve user identity, role membership, and privilege state; the documented API surface does not expose DML against application tables. Any output produced by GRANT_ADMIN_PRIVILEGE is written to a filesystem location through the UTL_FILE package rather than to a database table.

Usage Notes

Typical invocation occurs outside of EBS forms and concurrent programs. A DBA or applications DBA connects as SYSDBA or as a sufficiently privileged account and calls DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE using an anonymous PL/SQL block or a SQL*Plus script. Before scripted execution, a directory must be created with CREATE DIRECTORY; the procedure opens the target file in append mode. The package is not referenced by any other package in ETRM, and no EBS concurrent program is documented as calling it. Common scenarios include initial setup of XStream Out capture for an EBS reporting instance, granting a temporary administrator for a migration project, and using the do_grants = FALSE mode to generate a reviewable privilege script for change-controlled environments. Because the package is version-dependent, privilege requirements should be re-verified after each database upgrade.