Here, we will use the package DBMS_RESOURCE_MANAGER_PRIVS, which contains four procedures, namely:

DESC DBMS_RESOURCE_MANAGER_PRIVS;

1- GRANT_SWITCH_CONSUMER_GROUP: This procedure is used to grant the user the privilege to switch to a Consumer Group, after specifying the user and the group in the procedure.

The default situation is that the user cannot switch to a specific group unless granted permission for that. Now, let’s assume we want to grant the user OBAY the privilege to switch to the NEW_GROUP Consumer Group.

DECLARE

GRANTEE_NAME VARCHAR2(200);

CONSUMER_GROUP VARCHAR2(200);

GRANT_OPTION BOOLEAN;

BEGIN

GRANTEE_NAME := 'OBAY';

CONSUMER_GROUP := 'NEW_GROUP';

GRANT_OPTION := FALSE;

SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONS UMER_GROUP ( GRANTEE_NAME, CONSUMER_GROUP, GRANT_OPTION );

COMMIT;

END;

Now, the user OBAY can join the NEW_GROUP Consumer Group.

However, he cannot join a group for which he does not have the privilege to join.

2 - GRANT_SYSTEM_PRIVILEGE:

This procedure is used to grant Resource Manager administration privileges to users, where the user and the privilege we want to grant them are specified during the execution of the procedure.

DECLARE

GRANTEE_NAME VARCHAR2(200);

PRIVILEGE_NAME VARCHAR2(200);

ADMIN_OPTION BOOLEAN;

BEGIN

GRANTEE_NAME := 'OBAY';

PRIVILEGE_NAME := 'ADMINISTER_RESOURCE_MANAGER';

ADMIN_OPTION := FALSE;

SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE ( GRANTEE_NAME, PRIVILEGE_NAME, ADMIN_OPTION );

COMMIT;

END;

We granted Resource Manager administration privileges to the user OBAY.

3 - REVOKE_SWITCH_CONSUMER_GROUP: Through this procedure, we can revoke the privilege to join a specific group from the user who has this privilege, which was granted through the procedure GRANT_SWITCH_CONSUMER_GROUP.

DECLARE

REVOKEE_NAME VARCHAR2(200);

CONSUMER_GROUP VARCHAR2(200);

BEGIN

REVOKEE_NAME := 'OBAY';

CONSUMER_GROUP := 'NEW_GROUP';

SYS.DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSU MER_GROUP ( REVOKEE_NAME, CONSUMER_GROUP );

COMMIT;

END; 

4 - REVOKE_SYSTEM_PRIVILEGE: This is a procedure to withdraw Resource Manager administration privileges from users.

Assuming we want to revoke Resource Manager administration privileges from the user OBAY, to whom we granted this privilege previously.

DECLARE

REVOKEE_NAME VARCHAR2(200);

PRIVILEGE_NAME VARCHAR2(200);

BEGIN

REVOKEE_NAME := 'OBAY';

PRIVILEGE_NAME := 'ADMINISTER_RESOURCE_MANAGER';

SYS.DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SYSTEM_PRIVI LEGE ( REVOKEE_NAME, PRIVILEGE_NAME );

COMMIT;

END;