OraFAQ Forum: SQL & PL/SQL » Account Lock ~ OS user

Home » SQL & PL/SQL » SQL & PL/SQL » Account Lock ~ OS user (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Account Lock ~ OS user [message #648466]

Tue, 23 February 2016 11:14

deepakdot
Messages: 89
Registered: July 2015

Member

Hi

Once of the oracle user is getting locked frequently. Is there a way I can find out which OS user or Machine is locking the oracle user account ?

Deepak

Report message to a moderator

Re: Account Lock ~ OS user [message #648467 is a reply to message #648466]

Tue, 23 February 2016 11:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Put the result of:

show parameter audit
select USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE from DBA_STMT_AUDIT_OPTS order by 1, 2, 3, 4;

Don't forget to previously read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Account Lock ~ OS user [message #648468 is a reply to message #648466]

Tue, 23 February 2016 11:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

deepakdot wrote on Tue, 23 February 2016 09:14

Hi

Once of the oracle user is getting locked frequently. Is there a way I can find out which OS user or Machine is locking the oracle user account ?

Deepak

inspect content of listener.log file for connection request to that Oracle user

Report message to a moderator

Re: Account Lock ~ OS user [message #648609 is a reply to message #648467]

Sat, 27 February 2016 02:48

deepakdot
Messages: 89
Registered: July 2015

Member

I did not see any row in the table DBA_STMT_AUDIT_OPTS, though the user got locked.

Report message to a moderator

Re: Account Lock ~ OS user [message #648610 is a reply to message #648468]

Sat, 27 February 2016 02:55

deepakdot
Messages: 89
Registered: July 2015

Member

I also verified the listener.log informations. It does not captured any information about the OS user/ any system Informations. From the database I am able to Query which oracle user is locked. But I need the OS user / System information from which the oracle user is getting locked.

Report message to a moderator

Re: Account Lock ~ OS user [message #648612 is a reply to message #648609]

Sat, 27 February 2016 03:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

deepakdot wrote on Sat, 27 February 2016 09:48

I did not see any row in the table DBA_STMT_AUDIT_OPTS, though the user got locked.

1/ I posted 2 commands and I ask you to COPY AND PASTE their execution in SQL*Plus.
2/ If you have no rows in DBA_STMT_AUDIT_OPTS then logon are not audited, this was the purpose of my question.

Report message to a moderator

Re: Account Lock ~ OS user [message #648613 is a reply to message #648610]

Sat, 27 February 2016 03:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

deepakdot wrote on Sat, 27 February 2016 09:55

I also verified the listener.log informations. It does not captured any information about the OS user/ any system Informations. From the database I am able to Query which oracle user is locked. But I need the OS user / System information from which the oracle user is getting locked.

What does this mean exactly? There is no line in your listener.log or there are lines but they do not contain the information you want?
COPY AND PASTE one such line if it exists.

Report message to a moderator

Re: Account Lock ~ OS user [message #648619 is a reply to message #648610]

Sat, 27 February 2016 08:07

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

deepakdot wrote on Sat, 27 February 2016 02:55

I also verified the listener.log informations. It does not captured any information about the OS user/ any system Informations.

Frankly, if you were really looking at the listener log (/u01/app/oracle/diag/tnslsnr/<servername>/listener/trace/listener.log) then you simply didn't know what you were looking for.

Here is an entry from a connection attempt on my personal test system:

27-FEB-2016 07:57:02 * (CONNECT_DATA=(SERVICE_NAME=tulsa)(CID=(PROGRAM=C:\app\client\oracle\product\12.1.0\client_1\bin\sqlplus.exe)(HOST=MY-NB-04)(USER=ed))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=59234)) * establish * tulsa * 0

As far as the listener was concerned, the request was successful because the listener was able to spawn a dedicated server process. But after that, the database rejected the request because I had supplied a bad password. The listener didn't care about the rejection because by the time that happened, the listener had done its job and was out of the picture. But notice that it recorded the requestor's host name and user name ((HOST=MY-NB-04)(USER=ed)). You can query dba_users.lock_date and find a listener.log entry that matches that date/time, and there is your guilty party.

Report message to a moderator

Re: Account Lock ~ OS user [message #648679 is a reply to message #648619]

Mon, 29 February 2016 07:14

deepakdot
Messages: 89
Registered: July 2015

Member

Thanks Stevens. Probably I was not able to read this log file earlier. Now I am able to match the log file information with the data available in dba_users. This helps.

Report message to a moderator

Previous Topic:	Not exists with union query-Help
Next Topic:	Select only One Case results

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 09:41:01 CDT 2024