failed login attempts 설정 후 실패한 세션 확인

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 12.2.0.1

방법 : 

기본상태에서는 따로 로깅이 되지 않음

하지만 audit를 걸어두면 아래 쿼리로 확인 가능

Question:  I'm doing an audit and I need to be able to track all failed login (logon) attempts.  Because the user never gets logged-on to Oracle, how can you track failed sign on attempts to Oracle?

Answer:  Yes, it is difficult to audit failed sign-on attempts because the user never gets connected to Oracle, and a logon trigger would not be useful because it requires a valid login, not just an attempt.  For complete directions on auditing Oracle failed sign-on attempts, see my book "Oracle Privacy Security Auditing".

1 - 파라미터 및 audit 경로 확인:

audit_trail=true

audit_file_dest='/u01/app/oracle/mysid/mydir/'

2 - SYSDBA로 failed logion attempts의 감사를 활성화:

SQL> audit create session whenever not successful;

3 - dba_audit_trail 에서 실패한 로그인 시도 확인:

select 

   os_username,

   username,

   terminal,

   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')

from

   dba_audit_trail;

OS_USERNAME     USERNAME        TERMINAL        TO_CHAR(TIMESTAMP,'

--------------- --------------- --------------- -------------------

fred         SCOTT              app93           05-16-2009 16:21:13

참조 : 

참조 : http://www.dba-oracle.com/t_tracking_counting_failed_logon_signon_attempts.htm

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3056.htm#i1619732