Oraclue

Oracle internals, debugging and undocumented features

Dropping account by itself while still connected?

I will connect as myself and then drop myself from the same session:

-sh-3.2$ sqlplus miladin/xxxxx

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jan 13 17:23:53 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

SQL> drop user miladin;

User dropped.

How come??

We are talking about ASM instance .That’s why.

Demo:

ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/oracle/product/11.1.0.6/asm is /oracle
-sh-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jan 13 17:23:11 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

SQL> create user miladin identified by miladin123;

User created.

SQL> grant sysasm to miladin;

Grant succeeded.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options
-sh-3.2$ sqlplus miladin/miladin123 as sysasm  (  note   AS SYSASM )

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jan 13 17:23:53 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

SQL> drop user miladin;

User dropped.

SQL> show user;
USER is “SYS”

This is still showing user SYS.And even if you look v$session view username will be still SYS.

From audit log:

ACTION : ‘CONNECT’
DATABASE USER: ‘miladin’
PRIVILEGE : SYSASM
CLIENT USER: oracle
CLIENT TERMINAL: pts/0

It shows database user miladin.

Let’s exit and try to login again:
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

now login again:
-sh-3.2$ sqlplus miladin/miladin123 as sysasm

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jan 13 17:24:43 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Lging failed as expected.
Of course on regular instance this will not work:

SQL> conn miladin/miladin_123
Connected.
SQL> show user
USER is “MILADIN”
SQL> drop user miladin cascade;
drop user miladin cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

According to Oracle  the sysdba and sysasm roles will separate into OS groups.Oracle 12g I guess..

Advertisements

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

%d bloggers like this: