Search Results revoke_system_privilege

Overview

DBMS_RESOURCE_MANAGER_PRIVS is a standard Oracle-supplied PL/SQL package owned by the SYS schema and shipped as part of the core database kernel rather than as an E-Business Suite product-specific API. Within the Oracle EBS 12.1.1 and 12.2.2 environments it provides the administrative interface for managing privileges associated with the Database Resource Manager, the facility that allocates CPU and other resources among competing consumer groups. The package allows a privileged administrator to grant or revoke two distinct classes of authority: system-level resource management privileges and the ability to switch a session into a specific consumer group.

In the EBS context, resource manager privileges matter because the application tier and concurrent managers run large numbers of sessions against a single database. DBAs often define consumer groups to separate OLTP user sessions from long-running concurrent program sessions, and the grant and revoke routines exposed by this package are the supported mechanism for controlling which schemas may administer those plans or move their sessions between groups. The package is documented in ETRM as VALID under both release levels, confirming that it is present and compiled in the certified EBS 12.1.1 and 12.2.2 database homes.

Key Procedures and Functions

The package exposes four documented procedures, organized as two grant/revoke pairs.

• GRANT_SYSTEM_PRIVILEGE — Grants a named system-level resource management privilege to a user or role. This authorizes the grantee to administer resource plans, consumer groups, and related Resource Manager objects at the database level.
• REVOKE_SYSTEM_PRIVILEGE — Removes a previously granted system-level privilege from the specified user or role, reversing the effect of GRANT_SYSTEM_PRIVILEGE.
• GRANT_SWITCH_CONSUMER_GROUP — Authorizes a user or role to switch sessions into a designated consumer group. This is the privilege required for a session to migrate into a group other than the one it was initially assigned.
• REVOKE_SWITCH_CONSUMER_GROUP — Withdraws the switch-consumer-group privilege previously granted to a user or role.

All four routines are administrative in nature. They require execution privilege on the package, which is granted to PUBLIC in the standard Oracle data dictionary, and they are executed against the Resource Manager dictionary tables in the SYS schema.

Tables Accessed

The ETRM metadata records no tables accessed through APPS synonyms, which is expected because this is a SYS-owned database package rather than an EBS application API. The package operates on the Resource Manager data dictionary objects owned by SYS, which persist the privilege grants that the four procedures create and remove. Because these are kernel-owned tables, they are not exposed through an APPS synonym and are not intended to be queried or modified directly by EBS application code.

Usage Notes

DBMS_RESOURCE_MANAGER_PRIVS is typically invoked from SQL*Plus or a DBA administration script running as a privileged user, not from EBS forms or concurrent programs. It is normally called once during environment setup, when the DBA configures Resource Manager plans for an EBS database and needs to delegate plan administration or consumer-group switching to particular schemas. The ETRM dependency report shows no other package referencing it, confirming that no standard EBS product depends on it programmatically. Custom code should similarly avoid calling it at runtime; if resource manager privileges must be adjusted, they should be managed as one-time DBA tasks and documented as database configuration rather than application logic.