advertisements
_____________________________________________________________________________________________________________________
This is an Oracle EM alert which says the database has n unsuccessful login attempts in x minutes.
In order to identify the login details which are getting failed you should have enable the database auding especially enable the “connect audit”.
How to verify the auding is enable or not?
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- --------------
audit_trail string DB
How to enable database auditing if it is not enabled already?
The value for the audit_trail parameter shows as NONE in case the auding is not enabled.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- --------------
audit_trail string NONE
You can change the value using the following command.
SQL> alter system set audit_trail='DB' scope=spfile;
System altered.
And bounce the database.
Now verify the parameter value.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- --------------
audit_trail string DB
Enable the Connect Audit.
SQL> audit connect;
Audit succeeded.
Here onwards the failed logins will be audited sys.aud$ table. All errors with error code “ORA-01017: invalid username/password; logon denied” will get audited with RETURNCODE 1017 into sys.aud$ table. You can use following query with a DBA privileged user to get the failed login attempts from dba_audit_session table also instead of sys.aud$.
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a40
select OS_USERNAME, USERNAME,USERHOST,TIMESTAMP,RETURNCODE from dba_audit_session where rownum<10 and returncode=1017;
Anytime you can stop the auding with following command.
SQL> noaudit connect;
Noaudit succeeded.
Try it out. J
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment