Dropping quoted users – why sometimes Oracle can’t bear to say goodbye

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 :

CREATE USER “MR SPOCK” IDENTIFIED BY x;
CREATE USER “MILES O'BRIEN” IDENTIFIED BY x;

And you can drop them in the same way, can’t you ?

DROP USER “MR SPOCK”;

User dropped

Logical 😉

SQL> drop user "MILES O'BRIEN"; 
drop user "MILES O'BRIEN" 
* 
ERROR at line 1: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-00933: SQL command not properly ended 
ORA-06512: at line 7 

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 :

SELECT owner, trigger_name, trigger_type 
FROM dba_triggers 
WHERE base_object_type LIKE 'DATABASE%' 
/

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.

create or replace trigger sdo_drop_user
after drop on DATABASE
declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
     ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' ';
       EXECUTE IMMEDIATE stmt;
        ...
    end if;
end;

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.

First things first, let’s speed poor old Mr O’Brien on his way :

SQL> ALTER TRIGGER mdsys.sdo_drop_user disable; 

Trigger altered. 

SQL> ALTER TRIGGER mdsys.sdo_drop_user_before disable; 

Trigger altered. 

SQL> DROP USER "MILES O'BRIEN"; 

User dropped. 

SQL> 

Note that we have to disable both of these triggers.

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 !