Oracle – for when it was like that when you got there

Main menu

Post navigation

After messing about with various database objects with unusual usernames, I discovered a little quirk in Oracle XE.
Obviously, you can create usernames containing quotation marks simply by quoting them on creation :

Sneak attack by the Klingons ? Sabotage by the Romulans ? Orion Syndicate after revenge ?
If at this point you’re wondering what on earth I’m babbling about, I can only apologize and say in my defence that Star Trek references are mandatory for geeks of a certain age.

Looking at the errors – particularly ORA-06512, it would appear that some PL/SQL code is running somewhere on a drop user event. Let’s have a look and see if we can find any likely suspects :

This query provides a list of all of the database level DDL triggers.
Note – in the base_object_type column, the value DATABASE is suffixed by a number of space characters for some reason, which is why we need to use LIKE here.

From the results of this query, we can see that MDSYS has a couple of interestingly named triggers – SDO_DROP_USER and SDO_DROP_USER_BEFORE.
MDSYS is the owner of the objects required if your using Oracle Spatial. If you’ve been a good little DBA and performed your post-installation tasks, you’ll probably have locked this account…unless you happen to be using Oracle Spatial.

The point is, that locking the user doesn’t stop their Database Level triggers from firing and it’s these that are causing our problem.
Incidentally, looking at the code for these triggers, it seems that I wasn’t the only one who didn’t appreciate the full implications of SQL Injection.

If you’ve got a “proper” version of Oracle and your security patches are up to date, this should have been fixed long ago. For those of us mucking around with Oracle XE, it’s probably worth thinking about fixing these particular problems.

As I’m not using Spatial, I’m going to go ahead and disable the other MDSYS trigger, SDO_TOPO_DROP_FTBL as well, not least because ( in it’s unpatched, Oracle XE incarnation) it has known vulnerabilities to SQL Injection. Just type the name into a search engine and you’ll see what I mean.

Hmmm, hope Starfleet Personnel aren’t running XE as I get the feeling they’d probably have a use for something called Spatial !