http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2888]
PostgreSQL [103]
기타정보 [150]
운영체제 [577]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: DB2

 회원가입

sql trace / db2 login fail log
최길호 [LIST]   2019-03-22 15:03:50, 조회 : 845

web AP 접근 성공 [ db2inst1 ]
PC AP 접근 에러/insert 없이 완료 [ 어느계정인지, 왜 안되는지 모름 ] 원인 분석 필요.
login trace 결과 db2inst1 다른 계정은 없음.
sql trace 결과 해당 테이블에 insert 없고 select,update 있음.
row count 해서 해당 테이블만 추척 필요.


OS 로그인 실패 확인
====================================
AIX login fail log
who -a /etc/security/failedlogin
출처: https://forum.ivorde.com/aix-5-3-how-to-check-for-failed-login-attempts-t19735.html


실행 SQL을 확인 [ SQL trace ]
====================================
#connect to db
db2 connect to sample

#1-Create event monitor- make sure you've got write permissions to stated folder
db2 "CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE '/tmp'"

#2 Turn on event monitor
db2 SET EVENT MONITOR stmon STATE = 1

#3 (Do stuff to the database that you want to monitor)

#4 Turn off event monitor
db2 SET EVENT MONITOR stmon STATE 0

#5 Translate event monitor into readable stuff
db2evmon -path /tmp > /tmp/filtered.out

#6 Read the events
grep Text /tmp/filtered.out
egrep "Text|sqlcode" /tmp/filtered.out | more
egrep "Text|sqlcode" /tmp/filtered.out | grep -v SYS | grep -v QUERY

#7 Delete event monitor
db2 DROP EVENT MONITOR stmon

출처: https://www.dba-db2.com/2010/01/trace-sql-statements-in-db2-database.html


DB2 Login Fail Audit
====================================
$ db2audit start

AUD0000I  Operation succeeded.
$ db2 "create audit policy fl categories validate status both error type normal"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
$ db2 connect to sample
   Database Connection Information
Database server        = DB2/AIX64 9.7.3
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE

$ db2 "create audit policy fl categories validate status both error type normal"
DB20000I  The SQL command completed successfully.
$ db2 "audit database using policy fl"
DB20000I  The SQL command completed successfully.
$ db2 "connect to sample user jackvamvas using evilpw"
SQL30082N  Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID").  SQLSTATE=08001
$ db2audit flush

AUD0000I  Operation succeeded.
$ db2audit archive database mydb to /tmp

Node     AUD      Archived or Interim Log File                      
         Message                                                    
-------- -------- ---------------------------------------------------
       0 AUD0000I                                                    

AUD0000I  Operation succeeded.
$ db2audit archive database sample to /tmp

Node     AUD      Archived or Interim Log File                      
         Message                                                    
-------- -------- ---------------------------------------------------
       0 AUD0000I db2audit.db.SAMPLE.log.0.20190324151654  
$ db2audit extract delasc to /tmp from files /tmp/db2audit.db.SAMPLE.log.0.20190324151654

AUD0000I  Operation succeeded.
$ ls -alrt /tmp/          
-rw-------    1 db2inst1 db2iadm1       8442 Mar 24 15:16 db2audit.db.SAMPLE.log.0.20190324151654
-rw-rw-rw-    1 db2inst1 db2iadm1        366 Mar 24 15:18 validate.del
$ more /tmp/validate.del
"2019-03-24-15.14.11.122638","VALIDATE","AUTHENTICATION",2,-30082,"SAMPLE","jackvamvas",,"db2inst1",0,0, [ Login Fail -30082 ]
"*LOCAL.db2inst1.190324061423","db2bp","SERVER",,,,,"IBMOSauthserver",,,,,,,,,
"2019-03-24-15.16.28.170964","VALIDATE","AUTHENTICATION",2,-30082,"SAMPLE","jackvamvas",,"db2inst1",0,0,
"*LOCAL.db2inst1.190324061628","db2bp","SERVER",,,,,"IBMOSauthserver",,,,,,,,,
"2019-03-24-15.25.07.707127","VALIDATE","AUTHENTICATION",2,0,"SAMPLE","db2inst1","DB2INST1","db2inst1",0 [ Login Successful ]
,0,"*LOCAL.db2inst1.190324062508","DB2HMON","SERVER",,,,,"IBMOSauthserver",,,,,,,,,

$ db2 drop audit policy fl
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0478N  DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "AUDIT
POLICY" cannot be processed because there is an object "SAMPLE", of type
"DATABASE", which depends on it.  SQLSTATE=42893

$ db2 AUDIT DATABASE REMOVE POLICY
DB20000I  The SQL command completed successfully.
$ db2 drop audit policy fl
DB20000I  The SQL command completed successfully.
$ db2audit stop
AUD0000I  Operation succeeded.

출처: https://www.dba-db2.com/2012/07/db2-audit-failed-logons.html

3.235.78.122


  LIST

제목 작성자 작성일 조회
Install&Config  SQL30082N 이유 "42"("ROOT CAPABILITY REQUIRED")(으)로 인해 보안 처리에 실패했습니다.    최길호 2022/04/28 33
Troubleshoot  [jcc][t4][10205][11235][4.11.77] 널(NULL) password은(는) 지원되지 않습니다. ERRORCODE=-4461, SQLSTATE=42815    최길호 2020/10/24 496
Admin  DB2 상태 확인 status    최길호 2019/05/06 688
Admin  trigger test    최길호 2019/03/30 668
Admin  auto commit on/off    최길호 2019/03/30 932
Troubleshoot  DB21034E SQL0104N An unexpected token ";"/db2set DB2OPTIONS=-t/    최길호 2019/03/30 3743
Admin  dba_objects dba_tables    최길호 2019/03/26 685
Troubleshoot  sql trace / db2 login fail log    최길호 2019/03/22 845
Troubleshoot  SQL0332N Character conversion from the source code page "970" to the target code page "819" is not supported.    최길호 2019/02/22 825
Admin  db2 list application [ db2 session 정보 확인 ]    최길호 2019/02/09 692
Backup&Recovery  test    최길호 2019/02/05 1012
Troubleshoot  SQL6031N Error in the db2nodes.cfg file at line number "1". Reason code "10". [ hostname 변경 ]    최길호 2019/02/03 1193
Backup&Recovery  SQL1116N SQL2413N [ db2dart test /CHST /WHAT DBBP OFF ]    최길호 2014/04/22 4102
Troubleshoot  SQL0204N "DB2.TAB" is an undefined name. SQLSTATE=42704    최길호 2014/04/15 2908
Admin  db2 catalog tcpip node [ listener.ora, tnsnames.ora ]    최길호 2014/04/15 2078
Admin  test memo    최길호 2014/04/14 2374

    목록보기   다음페이지 1 [2][3][4]
       

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
06/29
[ORACLE]
DDE: Problem Key 'ORA 600 [133....
by 최길호
06/25
[ORACLE]
Replication may not be valid i....
by 최길호
06/22
[운영체제]
CentOS7 홈페이지 Migration 메모.
by 최길호