Home » RDBMS Server » Security » Who locked oracle user (oracle 12c Windows 2012)
Who locked oracle user [message #673085] Mon, 05 November 2018 06:47 Go to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi Everyone,

I want to know who locked the account.

Our parameter setup is:-

audit_file_dest                      string                           C:\ORACLE\ORA12C\HOME\DBS\ADMIN\ADUMP
audit_sys_operations                 boolean                          TRUE
audit_trail                          string                           DB


And I am using the below query:-

select
    os_username,
   username,
   userhost,terminal client,
   action_name,
   returncode,timestamp
from
      dba_audit_session
      where
      returncode='1017'
      and
      username=='<username>'
      order by timestamp desc

But it is not showing the latest details of the account lock. Am I missing anything.?

Also, what is the difference of using the below query:-

select terminal,userhost,spare1,ntimestamp# from aud$
where
returncode='1017'
and
userid='<username>'
order by ntimestamp# desc


Regards
M.Krish
Re: Who locked oracle user [message #673087 is a reply to message #673085] Mon, 05 November 2018 07:22 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Are you auditing ALTER USER statements? It isn't done by default. Also:
username=='<username>'
will fail.
Re: Who locked oracle user [message #673089 is a reply to message #673085] Mon, 05 November 2018 08:02 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Who says anybody locked it? Perhaps it locked itself from too many attempts with an invalid password.

issue the following in a sqlplus session.

select username,
       account_status,
       lock_date,
       expiry_date,
       profile
from dba_users
where username='<USER IN QUESTION IN CAPS'
;

Show us both the entire query and entire result by using copy and paste. Do not attach a screen shot. I refuse to open attachments for the same reason many sites block them to begin with. Show us the entire query and result as one continuous copy and paste. Enclose it in 'code' tags. For example:

SQL> select username,
  2  account_status,
  3  lock_date,
  4  expiry_date,
  5  profile
  6  from dba_users
  7  where username='SCOTT';

USERNAME		       ACCOUNT_STATUS			LOCK_DATE
------------------------------ -------------------------------- ---------
EXPIRY_DA PROFILE
--------- ------------------------------
SCOTT			       OPEN
18-APR-19 DEFAULT




And read this article: https://edstevensdba.wordpress.com/2012/01/16/exploring-password-lifetime-and-grace-period/
Re: Who locked oracle user [message #673090 is a reply to message #673085] Mon, 05 November 2018 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Error 1017 is "invalid username/password; logon denied" so, following your post, you think the account was locked because of "FAILED_LOGIN_ATTEMPTS" parameter value on the profile of the account.
In this case, the status of the account should be "LOCKED(TIMED)"

2/ If the status of the account is "LOCKED" then the account has been locked using "ALTER USER"

3/ In both cases, if you want the operation to be audited you must activate the audit for them: "AUDIT SESSION" and/or "AUDIT ALTER USER"



Re: Who locked oracle user [message #673766 is a reply to message #673090] Sun, 09 December 2018 22:27 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi Michel,

Sorry for the delay reply.

Yes, the lock was made manually by some of the user. (The status is "LOCKED")

Our setup is having policy of password change in every quarter.

After changing, some apps user using the old password, then It get locked.

But couldn't find out who is that.


If we have enabled this "AUDIT SESSION" and/or "AUDIT ALTER USER",
then how to query, who got locked.


Is there any other ways, like listener log file.


Regards
krish
Re: Who locked oracle user [message #673767 is a reply to message #673766] Mon, 10 December 2018 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

listener.log gives you who remotely (try to) log not who has locked an account.

Quote:
If we have enabled this "AUDIT SESSION" and/or "AUDIT ALTER USER",

Did you set "audit_trail"? And with which value?
You need to set it in "extended" mode if you want to specifically get who lock.
Or use unified auditing

Re: Who locked oracle user [message #673798 is a reply to message #673767] Mon, 10 December 2018 07:47 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Michel Cadot wrote on Mon, 10 December 2018 00:01

listener.log gives you who remotely (try to) log not who has locked an account.

Quote:
If we have enabled this "AUDIT SESSION" and/or "AUDIT ALTER USER",
Did you set "audit_trail"? And with which value?
You need to set it in "extended" mode if you want to specifically get who lock.
Or use unified auditing

And while it should be so blindingly obvious as to not need to be pointed out, history suggests otherwise: Setting up auditing will only capture events going forward. It will not magically audit and report past events. If it was not properly set up prior to an account being locked, setting it now will no reveal anything about who/how an account got locked yesterday -- or last month.
Re: Who locked oracle user [message #673812 is a reply to message #673798] Mon, 10 December 2018 23:02 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi Michel,

Yes,we have set the parameter "audit_trail" with the value of DB.

so, we should set it as EXTENDED or UNIFIED AUDITING.

Otherwise no chance to get the account locked info?

Am I right?

Regards
krish
Re: Who locked oracle user [message #673815 is a reply to message #673812] Tue, 11 December 2018 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66721
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are many ways to get it but no (simple) one to get it what has already be done in the past as Ed specified.

Re: Who locked oracle user [message #673823 is a reply to message #673812] Tue, 11 December 2018 06:45 Go to previous message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
muthukrish104 wrote on Mon, 10 December 2018 23:02
Hi Michel,

Yes,we have set the parameter "audit_trail" with the value of DB.

so, we should set it as EXTENDED or UNIFIED AUDITING.

Otherwise no chance to get the account locked info?

Am I right?

Regards
krish
Setting the audit_trail parameter is necessary but not sufficient. That enables auditing, but you must also use the AUDIT command to specify what you want to audit. See https://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG1025

And as I pointed up yesterday, setting auditing is NOT retroactive. Auditing causes information to be captured so that it can be reported. If you were not auditing at the time of the incident, then you were not capturing and so cannot report.

[Updated on: Tue, 11 December 2018 06:45]

Report message to a moderator

Previous Topic: DBA_USER privilege
Next Topic: standby database error ORA-28374
Goto Forum:
  


Current Time: Wed Dec 11 00:43:31 CST 2019