Weaponising DBMS_SCHEDULER

It’s been a while since I talked about oracle so probably about time I did.
I like to rock up into the office pretty late, usually around 10ish. By this time all the early morning panics, finger pointing exercises and general bluster have died down which leaves me and a handful of other grown ups to get on with their day.

Imagine my surprise then when I get a call from my boss asking when I’m going to be in the office as our entire production database had just ground to a halt. It didn’t help that I was actually outside enjoying my last cigarette before I gave them up completely.

The symptoms were weird to say the least – Server CPU flat out at 100%, Disk activity so high the poor things were rattling their way out of their cabinets, database just not responding, or at best it would take 20 minutes to log in and strangely of all zero network activity

Cue the managing classes, MBA’s and others with a single figure IQ who were convinced we’d been infected by a virus. “Not so” I said and by judicious use of the unix kill and windows taskkill command plus occasional use of a handy cricket bat managed to prove it.

The aftermath and subsequent investigation was tremendous fun. Here’s what happened

A developer, who will henceforth be known as “Arrogant Knobhead” came up with this gem of pl/sql which was embedded into a dbms_scheduler job which ran every 15 minutes

Obviously I’m paraphrasing a little here – I don’t want anyone to copy this rubbish

DECLARE
CURSOR C1 IS SELECT MY_VALUE FROM STUFF_TO_DO_TABLE WHERE STATUS=’N’;
BEGIN
FOR C1REC IN C1 LOOP
dbms_job.submit(what=>””do_stuff””);
END LOOP;
END;

So every 15 minutes it would wake up, scan a table and for every record it found and would launch a background job to process it. Pretty elegant but with one major drawback – no off switch or safety catch. What would you call a piece of software that replicates itself and eats up all your system resources. Yep – a virus

I quite liked the idea and said so. What I didn’t like was “Arrogant Knobhead”‘s answers which were

Your database your problem. If you can’t keep up with my genius it’s your problem

No I won’t change the code. It’s mine and there’s nothing wrong with it

You’re just a DBA, I’m way more important. Why should I listen to you?

Now at this point nobody liked “Arrogant Knobhead”, the individual in question even managed to piss off some of our old timers who finally woke up and said “Whut?” or even “Wow man”. One of them even remembered what century they were in which is a first for my outfit

Being evil I decided to get my own back in my own unique fashion. Here’s how I did it

1. In oracle 11g and 12c in order to access network resources you need to create an ACL. I did that, enabling an unused database to talk to our corporate email server

2. I created a stored procedure dbms_revenge. This grabs the email address of our CEO and senior board members and sends them a randomly selected insult

3. Turn “Arrogant Knobhead”‘s code against the perpetrator. I changed the code slightly to do this:

DECLARE
CURSOR C1 IS SELECT MY_VALUE FROM STUFF_TO_DO_TABLE WHERE STATUS=’N’;
BEGIN
FOR C1REC IN C1 LOOP
dbms_revenge;
END LOOP;
END;

So every 15 minutes it would send a rather humorous but also insulting email to one of our senior managers, CEO or board members. Here’s a few examples

FROM:Arrogant Knobhead
TO: Big Boss
SUBJECT: I like snorting Elderberries and love voting Democrat  from your bottom?

FROM:Arrogant Knobhead
TO: CEO
SUBJECT: Please give it to me from Russia right now you mathematical sex-weasel

FROM:Arrogant Knobhead
TO: CIO
SUBJECT: I love your delivery and want to have mutant frogs with your 26Hp lawnmower

FROM: Arrogant Knobhead
TO: CTO
SUBJECT: Yeah Baby, can I rub you in chip fat and eat your free pizza if it isn’t delivered in 30 minutes?

To be honest it was a bit of a work of genius. I used utl_htp to grab random words and phrases from a selection of the worst ad-slinging websites such as Channel 4, Pornhub, Amazon and others

You get the idea. Arrogant Knobhead has been asked to resign and I’m now involved in an internal investigation over who can send emails and how. Strangely, somebody else will be held to blame. Me, innocent, wouldn’t hurt a fly old me – NEVER!

And here’s why

DBMS_SCHEDULER falls through the gaps – it isn’t classed as either DDL or DML so is very difficult to track unless you enable oracle auditing

DBMS_JOB sucks. Sucks a lot but is still supported even in 12c for backward compatibility. It’s so old and so bad it doesn’t even understand basic DST changes. Anyone still using it deserves a good kicking

Sending an email or reading a url from oracle is child’s play. I would expect any DBA to be able to do the same. Again, difficult to track without auditing or a modicum of paranoia

And Finally….

Please don’t do this, please don’t copy it unless you like being unemployed. I used to give code snippets that were deliberately borken but having found some of my funnier ideas out there “in the wild” I no longer even do that

The DBA