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..
