안녕하세요 Thinking Man 입니다.
이번 글에서는 DBA로 근무하던 중 실제 DB 운영 중에 발생했던 Audit 이슈에 대해서 다뤄보도록 하겠습니다.
이번 글을 읽게 되면 AUDIT 문과 NOAUDIT 문의 상관 관계에 대해서 알 수 있으며 실제 사례이므로 쉽게 이해되도록 작성하였습니다.
실제 DB 운영 중 Audit 관련 이슈
운영 중인 DB에 특정 유저의 SESSION 접속 빈도가 너무 잦아 데이터가 방대해 지고 해당 유저의 Tablespace 사용량도 증가하고 있는 상황.
해당 유저는 모니터링 솔루션에서 수행되는 유저이고 Session에 대한 Audit 정보를 저장하지 않아도 무관하다고 판단되어
필자는 해당 유저의 SESSION 접속에 대한 감사 기록을 저장하지 않고자 이런저런 시도를 해보았습니다.
현황 파악
먼저 아래의 SQL로 현재 현황에 대해서 파악을 해 보았습니다.
Audit Trail의 전체 데이터 파악
SQL> select count(*) from dba_audit_trail; COUNT(*) ---------- 25766012 SQL> select count(*) from sys.aud$; COUNT(*) ---------- 25766023
아래 SQL은 ACTION 별로 누적된 감사 데이터 확인하기 위한 SQL
SELECT COUNT(*) CNT, ACTION_NAME FROM DBA_AUDIT_TRAIL GROUP BY ACTION_NAME ORDER BY CNT DESC;
다음과 같은 결과가 출력 되었습니다. LOGON과 LOGOFF가 다른 ACTION에 비해서 월등히 많은 감사 데이터가 쌓이고 있었습니다.
| CNT | ACTION_NAME |
| 13680552 | LOGON |
| 11839287 | LOGOFF |
| 237680 | LOGOFF BY CLEANUP |
| 3358 | ALTER TABLE |
| 1497 | CREATE TABLE |
| 1124 | GRANT OBJECT |
| 806 | REVOKE OBJECT |
| 181 | TRUNCATE TABLE |
| 169 | ALTER USER |
| 124 | CREATE FUNCTION |
| 114 | CREATE PACKAGE |
| 112 | ALTER PACKAGE |
| 111 | ALTER FUNCTION |
| 109 | CREATE DATABASE LINK |
| 106 | GRANT ROLE |
| 104 | SYSTEM GRANT |
| 86 | CREATE PACKAGE BODY |
| 74 | ALTER PACKAGE BODY |
| 62 | SELECT |
| 58 | DROP TABLE |
| 57 | ALTER SYSTEM |
| 41 | CREATE USER |
| 37 | DROP DATABASE LINK |
| 32 | DROP USER |
| 27 | ALTER PROFILE |
| 24 | CREATE PUBLIC SYNONYM |
| 22 | ALTER PROCEDURE |
| 22 | CREATE PROCEDURE |
| 13 | SYSTEM NOAUDIT |
| 11 | SYSTEM REVOKE |
| 7 | CREATE ROLE |
| 6 | EXPLAIN |
| 5 | REVOKE ROLE |
| 4 | FLASHBACK |
| 3 | ALTER PUBLIC SYNONYM |
| 2 | ALTER DATABASE |
| 1 | DROP ROLE |
좀 더 상세한 정보를 얻기 위해서 아래의 SQL을 수행
유저별로 LOGON 횟수가 얼마나 많은지 확인하기 위한 SQL
SELECT USERNAME, COUNT(*) CNT
FROM DBA_AUDIT_TRAIL
WHERE ACTION_NAME IN ('LOGON')
GROUP BY USERNAME
ORDER BY CNT DESC;
아래와 같은 결과를 리턴
| UNSERNAME | CNT |
| THINKER | 6212301 |
| THINKINGMAN | 5123914 |
| ADMIN | 1027312 |
| SCOTT | 715902 |
| TEST | 601123 |
| . | . |
| . | . |
| . | . |
특정 유저(THINKER, THINKINGMAN)가 월등히 SESSION 감사 데이터가 많이 쌓이고 있는 상황이였습니다.
Audit 이슈에 대해 조치사항
THINKER, THINKINGMAN 유저의 Session 감사 기능을 끄기 위해서 아래와 같이 noaudit 명령어를 수행하였습니다.
noaudit session by THINKER, THINKINGMAN; noaudit create session by THINKER, THINKINGMAN; noaudit 명령어 수행 시간은 2024-10-31 10:05
noaudit 명령어 수행 후 해당 유저의 Session 데이터가 쌓이는지 여부 확인
SELECT NTIMESTAMP#, SESSIONID, USERID, ACTION#
FROM SYS.AUD$
WHERE NTIMESTAMP# = SYSTIMESTAMP-INTERVAL '10' MINUTE -- 10분 전 데이터 확인
AND USERID IN ('THINKINGMAN', 'THINKER')
AND ACTION# = 100 -- LOGIN ACTION#가 100
ORDER BY NTIMESTAMP# DESC;
| NTIMESTAMP# | SESSIONID | USERID | ACTION# | RETURNCODE |
| 2024-10-31 10:34 | 30061 | THINKINGMAN | 100 | 0 |
| 2024-10-31 10:34 | 30060 | THINKINGMAN | 100 | 0 |
| 2024-10-31 10:34 | 30059 | THINKINGMAN | 100 | 0 |
| 2024-10-31 10:32 | 30058 | THINKER | 100 | 0 |
| 2024-10-31 10:32 | 30057 | THINKER | 100 | 0 |
| 2024-10-31 10:32 | 30056 | THINKER | 100 | 0 |
| 2024-10-31 10:32 | 30055 | THINKER | 100 | 0 |
| 2024-10-31 10:32 | 30054 | THINKER | 100 | 0 |
| 2024-10-31 10:31 | 30053 | THINKER | 100 | 0 |
| 2024-10-31 10:30 | 30052 | THINKER | 100 | 0 |
| 2024-10-31 10:30 | 30051 | THINKER | 100 | 0 |
| 2024-10-31 10:30 | 30050 | THINKER | 100 | 0 |
| 2024-10-31 10:27 | 30049 | THINKER | 100 | 0 |
| 2024-10-31 10:27 | 30048 | THINKER | 100 | 0 |
noaudit 명령어 이후에도 위와 같이 Session 감사 데이터가 저장되고 있었습니다.
참고사항
현재 DB 버전은 아래와 같이 11.2.0.4
SQL> SELECT banner FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
현재 audit_trail 파라미터 값은 DB로 설정
SQL> show parameter audit
| NAME | TYPE | VALUE |
| audit_file_dest | string | /app/ora11g/admin/ABCDE/adump |
| audit_sys_operations | boolean | FALSE |
| audit_syslog_level | string | |
| <strong>audit_trail | string | DB |
해결방안
결론부터 말하자면 CREATE SESSION은 사용자 레벨이 아니라 데이터베이스 레벨에서 감사가 수행 중이기 때문에 감사가 사용자 레벨에서 명시적으로 수행된 경우에만 NOAUDIT이 가능하다는 사실을 알게되었습니다.
현재 NOAUDIT 을 수행하고자 하는 DB에서는 아래의 명령어로 Session 감사를 수행한 적이 없기 때문에 noaudit 명령어가 유효하지 않습니다
audit session by THINKER, THINKINGMAN; audit create session by THINKER, THINKINGMAN;
현재 DB에서 수행되고 있는 감사를 확인하기 위해서는 아래의 SQL을 수행하여 확인 가능합니다.
SQL> select user_name,proxy_name,audit_option,success,failure
from DBA_STMT_AUDIT_OPTS order by user_name,audit_option;
| USER_NAME | PROXY_NAME | AUDIT_OPTION | SUCCESS | FAILURE |
| ALTER ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| ALTER ANY TABLE | BY ACCESS | BY ACCESS | ||
| ALTER DATABASE | BY ACCESS | BY ACCESS | ||
| ALTER PROFILE | BY ACCESS | BY ACCESS | ||
| ALTER SYSTEM | BY ACCESS | BY ACCESS | ||
| ALTER USER | BY ACCESS | BY ACCESS | ||
| CREATE ANY JOB | BY ACCESS | BY ACCESS | ||
| CREATE ANY LIBRARY | BY ACCESS | BY ACCESS | ||
| CREATE ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| CREATE ANY TABLE | BY ACCESS | BY ACCESS | ||
| CREATE EXTERNAL JOB | BY ACCESS | BY ACCESS | ||
| CREATE PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | ||
| CREATE SESSION | BY ACCESS | BY ACCESS | ||
| CREATE USER | BY ACCESS | BY ACCESS | ||
| DATABASE LINK | BY ACCESS | BY ACCESS | ||
| DIRECTORY | BY ACCESS | BY ACCESS | ||
| DROP ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| DROP ANY TABLE | BY ACCESS | BY ACCESS | ||
| DROP PROFILE | BY ACCESS | BY ACCESS | ||
| DROP USER | BY ACCESS | BY ACCESS | ||
| EXEMPT ACCESS POLICY | BY ACCESS | BY ACCESS | ||
| GRANT ANY OBJECT PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY ROLE | BY ACCESS | BY ACCESS | ||
| PROFILE | BY ACCESS | BY ACCESS | ||
| PUBLIC SYNONYM | BY ACCESS | BY ACCESS | ||
| ROLE | BY ACCESS | BY ACCESS | ||
| SYSTEM AUDIT | BY ACCESS | BY ACCESS | ||
| SYSTEM GRANT | BY ACCESS | BY ACCESS |
SQL> select user_name,proxy_name,privilege,success,failure
from DBA_PRIV_AUDIT_OPTS order by user_name,privilege;
| USER_NAME | PROXY_NAME | PRIVILEGE | SUCCESS | FAILURE |
| ALTER ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| ALTER ANY TABLE | BY ACCESS | BY ACCESS | ||
| ALTER DATABASE | BY ACCESS | BY ACCESS | ||
| ALTER PROFILE | BY ACCESS | BY ACCESS | ||
| ALTER SYSTEM | BY ACCESS | BY ACCESS | ||
| ALTER USER | BY ACCESS | BY ACCESS | ||
| AUDIT SYSTEM | BY ACCESS | BY ACCESS | ||
| CREATE ANY JOB | BY ACCESS | BY ACCESS | ||
| CREATE ANY LIBRARY | BY ACCESS | BY ACCESS | ||
| CREATE ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| CREATE ANY TABLE | BY ACCESS | BY ACCESS | ||
| CREATE EXTERNAL JOB | BY ACCESS | BY ACCESS | ||
| CREATE PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | ||
| CREATE SESSION | BY ACCESS | BY ACCESS | ||
| CREATE USER | BY ACCESS | BY ACCESS | ||
| DROP ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| DROP ANY TABLE | BY ACCESS | BY ACCESS | ||
| DROP PROFILE | BY ACCESS | BY ACCESS | ||
| DROP USER | BY ACCESS | BY ACCESS | ||
| EXEMPT ACCESS POLICY | BY ACCESS | BY ACCESS | ||
| GRANT ANY OBJECT PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY ROLE | BY ACCESS | BY ACCESS |
SQL> select owner,object_name,object_type,alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,exe,cre,rea,wri,fbk 2 from DBA_OBJ_AUDIT_OPTS order by owner,object_type,object_name; no rows selected
확인 결과 특정 Owner에 감사 중인 내용은 없으며 데이터베이스 레벨에서 “CREATE SESSION” 을 수행 중인 것을 확인할 수 있습니다.
즉 현재 DB에서는 데이터베이스 레벨의 Session 감사는 noaudit으로 중지할 수 있으며 이 경우에는 모든 세션에 대한 감사가 중지되기 때문에 적합하지 않다고 판단하였습니다.
만약 데이터베이스 레벨의 세션 감사를 중지하고 THINKINGMAN과 THINKER 계정을 제외한 모든 계정에 대한 세션 감사를 사용자 레벨에서 지정한다면 원치 않는 두 유저의 세션 감사 데이터는 쌓지 않을 수 있습니다.
noaudit create session; -- 현재 DB에서 이 명령어는 유효
같이 읽으면 좋은 글
> 예제로 살펴보는 Audit 설정 및 Audit Trail View 확인
> Audit Trail Purge 4편 (기타 Purge 작업 2)
> Audit Trail Purge 3편 (기타 Purge 작업)