Flowers To India

Flowers To India
Send cakes,flowers to India

Thursday, December 20, 2012

How to track failed login attempts in Oracle

 How to track failed login attempts in Oracle

audit_file_dest string /lapps/ag2/oappsr12/db/tech_st/11.1.0/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED

I run the following query to get more info on who tries to login with incorrect username/password and who locks the user.

select USERID,
userhost,
decode(returncode,01017,'Login Error','Acount Locked') "ISSUE",
spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
order by ntimestamp# desc ;

No comments: