Database에 접근 시도 실패한 로그인 확인하는 방법

Audit Trail 설정으로 기록 남기는 방법

DAC가 없거나, 필요에 의해서 DB에 접근 시도한 기록을 보는 방법입니다.

Aduit_trail을 적용하기 위해서는 DB를 재구동 해야하니 주의 하시기 바랍니다.

SQL> show parameter audit;

audit_trail = db 인지 확인

설정값이 DB가 아니라면

SQL> alter system set audit_trail=db scope=spfile;

적용하고 DB를 재구동합니다.

DB가 맞다면 접근 시도에 대핸 실패한 로그인 기록 하기를 적용합니다.

SQL> audit create session whenever not successful;

set line 500
set pages 1000
col os_username for a20
col username for a20
col terminal for a20
col time for a26
col action_name for a18
col comment_text for a101

select
os_username,
username,
terminal,
to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') "Time",
action_name,
returncode,
comment_text
from
dba_audit_trail
where username = 'APP_USER';
--and returncode > 0;

조건절에 조회 하고자 하는 유저로 변경해서 원하는 유저의 기록을 확인할 수 있습니다.  맨 마지막줄 주석 해제하여 조건절을 설정해주면 실패한 기록만 확인하는 것도 가능합니다.

audit 설정을 해서 DB 내부에 인증기록을 하게되면 I/O가 과다 발생할 수 있으며, 그에 따라 부하가 걸릴수도 있습니다. Audit 설정 할때는 항상 주의를 해야하며, 보안을 위해서는DB 자체적으로 설정하기보다는 DAC 솔루션을 따로 적용하는 편이 부하관리에서 유리합니다.

audit 설정 해제 하려면 다음과 같이 입력합니다.

SQL> noaudit create session whenever not successful;

Audit 설정 없이 Trigger를 사용해 기록을 남기는 방법

단순히 login, logoff 기록을 알고자 할 때는 audit을 사용하지 않고도 단순히 트리거를 생성해서 접속 로그를 만들수 있습니다.

다음의 sample source code에 있는 trigger는 system user로 compile되며, 그 후 각 user가 database에 logon/logoff할 때, 특정 directory/file에 시각, client IP, oracle username, logon/off를 기록합니다.

파라미터 파일의 수정

$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

# utl_file_dir parameter에 Oracle server가 write permission이 있는 directory를 설정
*.utl_file_dir=/home/oracle/log

Login on/off Trigger 작성

$ vi login_onoff_trigger.sql

create or replace trigger logon_trigger after logon on <database>
declare
hFile utl_file.file_type;
begin
hFile := utl_file.fopen('/home/oracle/log', 'connection.log', 'a');
utl_file.putf(hFile, '%s %s %s LOGON', to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'SESSION_USER'));
utl_file.fclose(hFile);
exception
when others then
if utl_file.is_open(hFile) then
utl_file.fclose(hFile);
end if;
end;
/
show errors

create or replace trigger logout_trigger before logoff on <database>
declare
hFile utl_file.file_type;
begin
hFile := utl_file.fopen('/home/oracle/log', 'connection.log', 'a');
utl_file.putf(hFile, '%s %s %s LOGOFF', to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'SESSION_USER'));
utl_file.fclose(hFile);
exception
when others then
if utl_file.is_open(hFile) then
utl_file.fclose(hFile);
end if;
end;
/
show errors

SQL> @ login_onoff_trigger.sql

트리거 생성 후에 각 클라이언트에서 <database>로 로그인, 로그아웃 해보면 로그로 기록이 남습니다.

$ cat /home/oracle/log/connection.log

2014-03-24 11:24:27 SYS LOGON
2014-03-24 11:25:29 SYS LOGOFF
2014-03-24 11:27:43 SYS LOGON
2014-03-24 11:28:10 NRIS-TEST-PC 121.126.160.117 LOGOFF
2014-03-24 11:28:15 NRIS-TEST-PC 121.126.160.117 LOGON

로컬에서 로그인 하면 ip가 남지 않고, 클라이언트에서 접속시 해당 PC명과 ip주소가 남습니다.