You can’t beat the system (trigger)

I don’t mind when people get things wrong. In our over complicated, politicised, dumbed down world it’s only natural to get confused, click on the wrong button or otherwise cock up

Hell, the DBA does it all the time. How else would you explain his rather unnatural obsession with goats?

There are however people who refuse to learn, no matter how hard you hit them. Certain individuals who should never be allowed anywhere near critical information. In other words Sales and Marketing. The thickest of the thick, the most arrogant and insulting scumbags ever invented since the first amoeba decided to split up with itself

So how do you keep them out?
Here’s the DBA’s answer, it’s called a system trigger. As usual you can’t just copy and paste the code, and if you do prepare to be unemployed for a very long time

The first thing to do is make a list of the people and oracle accounts you don’t like e.g.

CREATE TABLE HATE_LIST (ORACLE_USERNAME VARCHAR2(80));
INSERT INTO HATE_LIST VALUES(‘etc…..
COMMIT;

So you now have a list of the oracle accounts of people who have pissed you off. No big deal. Here’s the clever bit

CREATE OR REPLACE TRIGGER sys.I_hate_you AFTER logon ON DATABASE
DECLARE
p_session_user varchar2(64);
p_hated_user number := 0;
BEGIN
SELECT UPPER(SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’)) INTO p_session_user FROM DUAL;
SELECT COUNT(*) INTO p_hated_user from HATE_LIST WHERE
UPPER(ORACLE_USERNAME)=p_session_user;

IF P_hated_user != 0 THEN
RAISE_APPLICATION_ERROR(-20003,’DBA Error – Please try again’);
END IF;

END;
/

The beauty of this is that you can arbitrarily change the final IF clause. Instead of raising an exception what about trying DBMS_LOCK.SLEEP(60*24) which will leave the session hanging for a day? Why not go further, have it call DBMS_SYSTEM.KILL_SESSION to randomly disconnect one of their friends? The possibilities are endless, just have some popcorn ready for when the ultimate fights break out

You can also make this more reliable by adding the dreaded “WHEN OTHERS THEN NULL” exception. Something everyone should avoid but on this occasion a bit forgivable as you’re really only targeting people you don’t like

System triggers are a valuable tool in the DBA weapon locker and sadly are really under used. They can for example be used for good to implement your own application or site based auditing or security, use them on database starttup to populate frequently used in-memory data. All good

I choose to use them for evil but then this is the darkside after all