I thought this might be an interesting approach to share with those DBAs who have been tasked with preventing certain applications from accessing the databases they maintain.
Here's the scenario: a legacy helper application that should have been uninstalled from - what was then - an indeterminate number of our remote sites started reverting server data to a state that was not compliant with the latest software release of our primary application. Of course, chaos ensued shortly thereafter when some of the compliant sites began automatically syncing those errant data changes to their local caches through a separate background replication process. We were facing rampant data corruption and imminent systemic collapse while the system analysts feverishly tracked down which machines to target for immediate rebuilds. In the meantime, I had to prevent this "rogue program" from wreaking further havoc on our server data and remote sites.
I chose to use the Oracle Resource Manager for a few reasons. First of all, it didn't require any production downtime and only very little scripting to implement the changes immediately. Secondly, the changes required would not affect any compliant sites connecting to the same database accounts whether they were connected now or in the future, but any current or future sessions established by the rogue program through those same accounts would be killed instantly by ORM. Thirdly, I wanted to pursue an Oracle built-in solution rather than a custom solution such as a database logon trigger with application context detection that may have required thorough testing, an obstacle in a critical production situation like this.
The script below is a sanitized version of what was implemented and assumes that Oracle Resource Manager has never been configured on the affected server instance. Given this perceived advantage, I decided to use the built-in Oracle resource plan DEFAULT_PLAN which is already configured for use with the built-in consumer groups DEFAULT_CONSUMER_GROUP, SYS_GROUP, and OTHER_GROUPS. Moreover, the Oracle accounts used by the applications were assigned initially to DEFAULT_CONSUMER_GROUP upon login, so any sessions connecting through those accounts would follow the established plan directives of that consumer group configured under the DEFAULT_PLAN. Any concerns regarding the built-in resource plan and consumer groups should be reviewed prior to implementation.
/* execute all under a dba-enabled account */
BEGIN
-- clear the plan schema scratchpad
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
-- initialize the plan schema scratchpad
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- create a consumer group to assign unwanted sessions for immediate termination
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
consumer_group => 'KILL_GROUP',
comment => 'The group assigned to unwanted sessions for immediate termination.');
-- validate any pending plans, groups, and directives
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- commit any pending plans, groups, and directives
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
BEGIN
-- clear the plan schema scratchpad
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
-- initialize the plan schema scratchpad
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- add a plan directive to immediately move sessions assigned to the KILL_GROUP
-- consumer group to the built-in KILL_SESSION consumer group;
-- note that KILL_SESSION is a special built-in “switch group” instructing
-- ORM to terminate the session immediately and cannot be assigned consumer
-- group mappings directly
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
plan => 'DEFAULT_PLAN',
comment => 'Switch conditions for KILL_GROUP',
group_or_subplan => 'KILL_GROUP',
switch_group => 'KILL_SESSION',
max_idle_time => 0,
switch_time => 0);
-- automatically assign the Rogue Program to the KILL_GROUP for
-- immediate termination;
-- use the value in the PROGRAM field from the V$SESSION view for the second
-- parameter below, not the actual OS process name
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (
attribute => 'CLIENT_PROGRAM',
value => 'Rogue Program',
consumer_group => 'KILL_GROUP');
-- allow the affected Oracle account “APP” to be assigned to the KILL_GROUP;
-- if any other accounts are accessed by the Rogue Program, those accounts will
-- also need to be granted switching to the KILL_GROUP
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
grantee_name => 'APP',
consumer_group => 'KILL_GROUP',
grant_option => FALSE);
-- validate any pending plans, groups, and directives
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- commit any pending plans, groups, and directives
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
-- enable Oracle Resource Manager and use the DEFAULT_PLAN
ALTER SYSTEM SET resource_manager_plan='DEFAULT_PLAN' scope=BOTH;
