This slightly odd little quirk interested me so I thought I’d do a quick post… It is just something that I hadn’t considered before.
Create a DBA user:
SYS@xe> create user dba_user identified by basic_pass; User created. SYS@xe> grant dba to dba_user; Grant succeeded.
Create a SYSDBA user:
SYS@xe> create user sysdba_user identified by basic_pass; User created. SYS@xe> grant sysdba to sysdba_user; Grant succeeded.
Connect as DBA user and try to drop yourself
SYS@xe> connect dba_user/basic_pass@xe Connected. DBA_USER@xe> drop user dba_user; drop user dba_user * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected
As expected, you can’t drop yourself.
Switch to SYSDBA user and drop yourself
DBA_USER@xe> connect sysdba_user/basic_pass@xe as sysdba Connected. SYS@xe> drop user sysdba_user; User dropped. SYS@xe>
The interesting point to me is that at this point you have just dropped yourself as a user and therefore can’t connect anymore (unless you have another way in):
SYS@xe> connect sysdba_user/basic_pass@xe as sysdba ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. SYS@xe>
This would never be possible without a SYSDBA connection as you would hit an ORA-01940 as demonstrated above.
I’m doing a lot of work with privileges at the moment and this seemed worth commenting on.