Killing for Fun

I forget why, but rocking up to our office the other day I found that someone had stolen my parking space and had plonked a brand new Audi in it.

Now working in a town centre, even if it is far from the bright lights of civilisation , these things aren’t easy to come by
But being a nice guy I didn’t really make a big deal of it, but when it went on for over a week I made some not so subtle enquiries of our security guards as to who it was. They eventually told me – his name was Eric and he was a “consulting architect”, whatever the hell that means. His job apparently was to report to our Finance director and recommend “efficiencies” in the IT department. So in other words, the spawn of Satan

This also went a long way to explaining the helpdesk call to set him up with unrestricted DBA access to world+dog that I had been ignoring for several days. Given these two pieces of evidence my plan was complete

First of all, create him an oracle account that could look but not touch. This is a lot easier than you might think, yes it leaves a lot to be desired but it does have the advantage of being quick and dirty

CREATE USER ERIC IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE USERS;
GRANT CREATE SESSION TO ERIC;
GRANT SELECT ANY TABLE TO ERIC;
GRANT SELECT_CATALOG_ROLE TO ERIC;

Second, implement my patent user destroyer. As usual the source code below is subtly broken so if you copy/paste it then it won’t work without applying a braincell or two

CREATE OR REPLACE PROCEDURE Hit( V_USERNAME IN VARCHAR2)
as
cursor c1(lv_user varchar2) is
select sid,serial# from v$session where username=upper(lv_user);
v_sql VARCHAR2(255);
begin
for c1rec in c1(P_USERNAME) loop
v_sql := ‘alter system kill session ‘||””||c1rec.sid||’ ‘||c1rec.serial#||””||’ immediate’;
execute immediate v_sql;
end loop;
end;

So now I have a little stored procedure that will kill every database session based on the username.Yes I know it isn’t good code, there are no exception handlers and as is would actually allow you to kill important sessions from the user SYS or anyone else for that matter. However it’s only intended a) for education/humour purposes and b) to be run by people who know what they’re doing

Just for fun, every hour or so, or whenever I had a spare minute for the first day I ran

SQL>exec Hit(‘Eric’);
PLSQL Procedure Completed Successfully

At the end of the first day my helpdesk queue was flooded with complaints about random database disconnections, a coupleĀ  of which I passed on to our poor unsuspecting network guy. The rest I closed based on the fact that nobody else was having any problems

By the end of the second day I had my car park space back