Re: ORA-01940: cannot drop a user that is currently connected

  • From: Adric Norris <landstander668@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 6 Oct 2010 15:53:41 -0500

On Wed, Oct 6, 2010 at 9:46 AM, mayur nagarsheth <mayurpnagarsheth@xxxxxxxxx
> wrote:

> This user account has been locked since 1 month. No active connections, no
> active sessions, no db links, no objects are being owned by this user. I
> revoked all the privileges this (ACOSTA) user had just to be on safer side.
> But nothing works :(
>

Any chance this account is being used as a proxy for another user?  This
will (by design) work even if the proxying account is locked, or lacks the
CREATE SESSION privilege.

I'm not sure how to detect whether or not any existing sessions were
connected via proxy, but you can use the PROXY_USERS view to determine if
any are configured to allow it.

For example:

SYSTEM@mydb> -- create a proxy account, which is both locked and
SYSTEM@mydb> -- lacking the CREATE SESSION privilege
SYSTEM@mydb> create user proxy
  2     identified by proxy
  3     account lock;

User created.

SYSTEM@mydb> -- create an application account
SYSTEM@mydb> create user test
  2     identified by values 'unusable';

User created.

SYSTEM@mydb> grant create session to test;

Grant succeeded.

SYSTEM@mydb> alter user test grant connect through proxy;

User altered.


SQL> -- connect through the proxy account using a separate sqlplus session,
and
SQL> -- just let it sit at the SQL prompt
SQL> connect proxy[test]/proxy
Connected.


-- now back in our original session, attempt to drop the proxy account
-- with the active connection
SYSTEM@mydb> drop user proxy;
drop user proxy
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SYSTEM@mydb> select * from proxy_users;

PROXY                          CLIENT                         AUT FLAGS
------------------------------ ------------------------------ ---
-----------------------------------
PROXY                          TEST                           NO  PROXY MAY
ACTIVATE ALL CLIENT ROLES


>
>
> On Mon, Oct 4, 2010 at 10:20 AM, Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
>
>>
>>
>> On Mon, Oct 4, 2010 at 10:55 AM, mayur nagarsheth <
>> mayurpnagarsheth@xxxxxxxxx> wrote:
>>
>>> There are no jobs existing with these users. Also, it is not on RAC.
>>>
>>>
>>>
>>> That is a strange one.  Not knowing how your database is set up, is it
>> possible that ACOSTA connects periodically?  Could you try locking the
>> account, then do the delete?
>>
>> alter user acosta account lock;
>>
>
>
>
> --
> Thanks n Regards,
> Mayur Nagarsheth
> Cell : (+1)214-364-8271
> http://www.linkedin.com/in/mayurnagarsheth
>
> http://lyle.smu.edu/~mnagarshet/ <http://lyle.smu.edu/%7Emnagarshet/>
>
>


-- 
"I'm too sexy for my code." -Awk Sed Fred

Other related posts: