Resource Manager Privilege Management Bug

When working with Oracle Database Resource Manager recently I ran into what seemed like unexpected behaviour and turned out to be bug 13101791 as documented in MOS ID 13101791.8. The note states workaround as “none”, but that depends on what you view as an acceptable workaround.

The bug description starts:

The Resource Manager consumer group is not changed to the right one when it is granted via a role.

That gives a high level view of the issue, but there are some subtleties that seem worth noting.

Demonstration

  • SESSION 1 is SYS and is used to grant privileges and manage the Resource Manager configuration
  • SESSION 2 is a test user named MARTIN and is used to verify the Resource Manager behaviour by logging in and checking the assigned consumer group

Validation of the current privileges for switching consumer group

SESSION 1> select * from dba_rsrc_consumer_group_privs;

GRANTEE      GRANTED_GROUP                  GRA INI
------------ ------------------------------ --- ---
PUBLIC       DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM       SYS_GROUP                      NO  YES
PUBLIC       LOW_GROUP                      NO  NO

3 rows selected.

Validation of the current privileges for switching consumer group

SESSION 1> select * from dba_rsrc_group_mappings;

ATTRIBUTE         VALUE        CONSUMER_GROUP   STATUS
----------------- ------------ ---------------- -------
SERVICE_NAME      DBRM_SRV1    GOLD_GROUP
ORACLE_USER       SYS          SYS_GROUP
ORACLE_USER       SYSTEM       SYS_GROUP
ORACLE_FUNCTION   BACKUP       BATCH_GROUP
ORACLE_FUNCTION   COPY         BATCH_GROUP
ORACLE_FUNCTION   DATALOAD     ETL_GROUP

6 rows selected.

Connection as user MARTIN to service DBRM_SRV1 and verification of consumer group

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     OTHER_GROUPS              dbrm_srv1

Nothing unexpected here – MARTIN does not have permission to switch to GOLD_GROUP, so he can’t.

Granting MARTIN permission to switch to GOLD_GROUP directly

SESSION 1> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'martin',consumer_group => 'GOLD_GROUP',grant_option => FALSE)

PL/SQL procedure successfully completed.

Querying V$SESSION for MARTIN (no reconnection)

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     GOLD_GROUP                dbrm_srv1

Revoking from MARTIN permission to switch to GOLD_GROUP directly

SESSION 1> exec dbms_resource_manager_privs.revoke_switch_consumer_group(revokee_name => 'martin',consumer_group => 'GOLD_GROUP')

PL/SQL procedure successfully completed.

Querying V$SESSION for MARTIN (no reconnection)

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     OTHER_GROUPS              dbrm_srv1

Again, not too surprising here – Granting and revoking the privilege to be in GOLD_GROUP to user MARTIN results in him moving both into and out of the GOLD_GROUP.

Creating a ROLE named APP_ROLE and granting that to MARTIN

SESSION 1> create role app_role;

Role created.

SESSION 1> grant app_role to martin;

Grant succeeded.

Enable the newly granted APP_ROLE for user MARTIN in current session

SESSION 2> set role all;

Role set.

SESSION 2> select role from session_roles;

ROLE
-----------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
APP_ROLE

Granting APP_ROLE permission to switch to GOLD_GROUP directly

SESSION 1> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'app_role',consumer_group => 'GOLD_GROUP',grant_option => FALSE)

PL/SQL procedure successfully completed.

Querying V$SESSION for MARTIN (no reconnection)

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     OTHER_GROUPS              dbrm_srv1

Validation of the current privileges for switching consumer group

SESSION 1> select * from dba_rsrc_consumer_group_privs;

GRANTEE      GRANTED_GROUP                  GRA INI
------------ ------------------------------ --- ---
PUBLIC       DEFAULT_CONSUMER_GROUP         YES YES
APP_ROLE     GOLD_GROUP                     NO  NO
SYSTEM       SYS_GROUP                      NO  YES
PUBLIC       LOW_GROUP                      NO  NO

4 rows selected.

Testing switching consumer group manually using DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP

SESSION 2> var old_group varchar2(10)
SESSION 2> exec dbms_session.switch_current_consumer_group('GOLD_GROUP',:old_group,FALSE)

PL/SQL procedure successfully completed.

Querying V$SESSION for MARTIN (no reconnection)

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     GOLD_GROUP                dbrm_srv1

So the consumer group was successfully switched

Revoking from APP_ROLE permission to switch to GOLD_GROUP directly

SESSION 1> exec dbms_resource_manager_privs.revoke_switch_consumer_group(revokee_name => 'app_role',consumer_group => 'GOLD_GROUP')

PL/SQL procedure successfully completed.

Querying V$SESSION for MARTIN (no reconnection)

SESSION 2> select username, resource_consumer_group, service_name from v$session where sid = sys_context('userenv','sid');

USERNAME   RESOURCE_CONSUMER_GROUP   SERVICE_NAME
---------- ------------------------- ---------------
MARTIN     OTHER_GROUPS              dbrm_srv1

Summary

When the privilege to switch consumer group is granted to a user and a session is connected to a service that maps to the same consumer group the session is automatically switched to the consumer group. However, when the privilege to switch consumer group is granted to a role the same behaviour is not observed and the session is only switched to the consumer group when specified to do so manually.

Conclusions

MOS 13101791.8 states that the bug is fixed in 12.1.0.1 and 11.2.0.4. I haven’t tested to verify this. I expect the vast majority of organisations are not yet running these versions and so it seems worth discussing workarounds. The two options that occur to me are:

  1. Granting the privilege to switch at a user level – Depending on the number of database users, the rate of new users and the method with which they are provisioned this could be more or less painful.
  2. Use a logon trigger to call dbms_session.switch_current_consumer_group to switch to the desired consumer group – I’m less keen on this option, but the appropriateness over workaround (1) would depend on the specifics of the environment.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>