Drop Yourself

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.

Advertisements

3 thoughts on “Drop Yourself

  1. Thank You Good Example.
    Perhaps A Good interesting is login as sysdba …. it became sys user 😉

    SQL> connect dba_user/basic_pass@orcl
    Connected.
    SQL> show user;
    USER is “DBA_USER”
    SQL> select user from dual;

    USER
    ——————————
    DBA_USER

    >>>

    SQL> connect sysdba_user/basic_pass@orcl as sysdba
    Connected.
    SQL> show user;
    USER is “SYS”
    SQL> select user from dual;

    USER
    ——————————
    SYS

  2. Hi Surachart,

    Thanks for the comment. I use the following in my glogin.sql so that the user I am connected as is always displayed in the prompt:

    set sqlprompt “_user’@’_Connect_identifier> ”

    I guess the key point with SYSDBA connections is that you connect as SYS, but may have authenticated as someone else, who has the SYSDBA privilege via OS group or database grant.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s