내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux 6.8, 7.2 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 12.2.0.1
방법 : 오라클 audit (감사) 정리 및 테스트
AUDITING 기능은 사용자의 행동을 감시하거나 데이타베이스에 관한 통계자료를 얻는 목적으로 사용됨
AUDITING 기능을 사용함으로써 누가 어떤 테이블을 언제 사용하고, 언제 어떤 작업을 하는지를 기록할 수 있음
하지만 레코드에 대한 AUDITING 은 불가능하며 AUDITING 을 걸게 되면 시스템에 부하를 줄 수도 있기 때문에
자주 사용되는 기능은 아니지만 대부분 국가 정보보안 기본지침에 따라서 설정함
그리고 AUDITING 에서 제공되지 않는 기능은 사용자가 트리거를 만들어서 구현을 해야함
AUDIT의 활성화 방법
- initSID.ora 파일에 AUDIT_TRAIL 파라미터를 추가하고 DB 재기동
- spfile 일 경우 alter system 명령으로 AUDIT_TRAIL 파라미터를 변경후 DB 재기동
audit 로그파일 삭제(정리) 에대한 내용은 아래 게시물 참조
오라클 audit 로그 정리 방법( https://positivemh.tistory.com/220 )
AUDIT_TRAIL 파라미터의 값 적용방법
기존파라미터 확인
1 2 3 4 5 6 7 8 9 | SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin /ORCL11/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB |
현재 Audit_trail이 DB임
Audit_trail OS로 변경
1 2 3 | SQL> alter system set audit_trail=OS scope=spfile; System altered. |
db 재기동
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1553305600 bytes Fixed Size 2253544 bytes Variable Size 1174408472 bytes Database Buffers 369098752 bytes Redo Buffers 7544832 bytes Database mounted. Database opened. |
확인
1 2 3 4 5 6 7 8 9 | SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin /ORCL11/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string OS |
audit_trail 이 OS로 변경됨
OS 파일 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> !ls -al /oracle/app/oracle/admin/ORCL11/adump total 132 drwxr-x---. 2 oracle dba 36864 Mar 17 07:43 . drwxr-x---. 6 oracle dba 4096 Feb 18 03:07 .. -rw-r--r--. 1 oracle dba 57 Feb 4 06:28 afiedt.buf -rw-r-----. 1 oracle dba 837 Mar 16 21:29 ORCL11_ora_23888_20200316212936331911143795.aud -rw-r-----. 1 oracle dba 1034 Mar 16 21:32 ORCL11_ora_23906_20200316213154724802143795.aud -rw-r-----. 1 oracle dba 793 Mar 16 21:32 ORCL11_ora_23907_20200316213217029639143795.aud -rw-r-----. 1 oracle dba 827 Mar 16 21:32 ORCL11_ora_23907_20200316213218880807143795.aud -rw-r-----. 1 oracle dba 833 Mar 16 21:32 ORCL11_ora_23942_20200316213218932431143795.aud -rw-r-----. 1 oracle dba 1034 Mar 16 21:32 ORCL11_ora_23947_20200316213223051670143795.aud |
AUDIT_TRAIL 파라미터의 값 설명
1. none
audit을 비활성화함
initSID.ora 파일에 AUDIT_TRAIL 매개 변수가 설정되지 않았거나
데이터베이스 구성 지원 이외의 방법을 사용하여 데이터베이스를 작성한 경우이 값이 기본값임
Database Configuration Assistant(DBCA)를 사용하여 데이터베이스를 작성한 경우 기본값은 db임
2. os
audit 결과를 OS에 파일로 생성을 함
AUDIT_FILE_DEST파라미터에 지정된 디렉토리에 텍스트 파일로 생성됨
3. db(true)
OS에 저장되는 audit trail 결과를 제외하고 audit 결과를 데이터베이스 audit trail(SYS.AUD$ 테이블)으로 보냄
관리 효율성을 위해 일반 데이터베이스에 이 설정을 사용하라고 docs 에 나와있음
DB대신 TRUE로 지정을 해도됨
AUDIT_TRAIL이 db로 설정된 상태에서 데이터베이스가 읽기 전용 모드로 시작된 경우 Oracle Database는 내부적으로 AUDIT_TRAIL을 os로 설정함, 자세한 내용은 alert log를 확인해야함
SYS.AUD$ 테이블에서 얻을 수 있는 정보는 매우 많은데 대표적인 것으로 다음을 들 수 있음
- USER NAME : 오라클 유저 이름
- SESSION ID : 오라클 세션 ID
- TERMINAL ID : 유닉스 시스템 상의 터미날 ID
- OBJECT NAME : 테이블, 뷰 등의 이름
- ACTION ID : 명령 코드
- DATE : 명령이 실행된 날짜,시간
- COMPLETION CODE : 0 이면 성공, 실패하면 에러 코드
AUDIT_TRAIL을 db로 설정 했을 때
11g Audit 정보는 SYS.AUD$, FGA는 SYS.FGA_LOG$ 테이블에 저장됨 (Tablespace : SYSTEM)
1 2 3 4 5 6 7 8 9 10 | SQL> select tablespace_name, segment_name from dba_segments where owner = 'SYS' and segment_name in ('AUD$','FGA_LOG$') / TABLESPACE_NAME SEGMENT_NAME ----------------- --------------------------- SYSTEM FGA_LOG$ SYSTEM AUD$ |
11g에서 AUDSYS.AUD$UNIFIED, SYS.AUD$, SYS.FGA_LOG$ 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> select count(*) from audsys.AUD$UNIFIED; select count(*) from audsys.AUD$UNIFIED * ERROR at line 1: ORA-00942: table or view does not exist SQL> select count(*) from sys.AUD$; COUNT(*) ---------- 29 SQL> select count(*) from sys.FGA_LOG$; COUNT(*) ---------- 0 |
12c Unified Audit 정보는 AUSSYS.AUD$UNIFIED 에 저장됨 (Tablespace : SYSAUX)
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> select tablespace_name, segment_name from dba_segments where owner = 'AUDSYS' and segment_name='AUD$UNIFIED' / TABLESPACE_NAME SEGMENT_NAME ----------------- --------------------------- SYSAUX AUD$UNIFIED SYSAUX AUD$UNIFIED SYSAUX AUD$UNIFIED |
12c에서 AUDSYS.AUD$UNIFIED, SYS.AUD$, SYS.FGA_LOG$ 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> select count(*) from audsys.AUD$UNIFIED COUNT(*) ---------- 10 SQL> select count(*) from sys.AUD$; COUNT(*) ---------- 0 SQL> select count(*) from sys.FGA_LOG$; COUNT(*) ---------- 0 |
4. db, extended
AUDIT_TRAIL = db의 모든 조치를 수행하고 사용 가능한 경우 SYS.AUD$ 테이블의 SQL 바인드 및 SQL 텍스트 CLOB 유형 컬럼을 채움, 이 두 열은이 매개 변수가 지정된 경우에만 채워짐
AUDIT_TRAIL을 db, extended로 설정된 상태에서 데이터베이스가 읽기 전용 모드로 시작된 경우 Oracle Database는 내부적으로 AUDIT_TRAIL을 os로 설정함, 자세한 내용은 alert log를 확인해야함
5. xml
audit trail 결과를 OS에 XML 형식으로 저장함, Sql문과 Sql 바인드 정보를 제외한 AuditRecord 노드의 모든 요소를 운영 체제 XML 감사 파일에 기록함
6. XML, extended
AUDIT_TRAIL = xml의 모든 조치를 수행하고 audit trail에 Sql문과 Sql 바인드 정보를 포함함
SQL AUDIT 문을 사용하여이 매개 변수 설정에 관계없이 감사 옵션을 설정할 수 있음
=> db로 설정하면 datafile 사이즈가 증가해서 datafile 용량관리를 잘 해줘야하고
os로 설정하면 datafile 사이즈는 증가하지 않지만 os에 파일이 저장되어서 filesystem 용량관리를 잘 해줘야함
Oracle Unified Auditing for Oracle 12c
Oracle 12c에는 새로운 데이터베이스 감사 기반이 도입됨
Oracle Unified Auditing(통합 감사)은 데이터베이스의 기본 감사 기능을 변경함
Oracle의 이전 릴리스에서는 각 개별 구성 요소에 대해 별도의 감사 추적이있었음
Oracle Unified Auditing(통합 감사)은 모든 감사를 단일 저장소 및 뷰로 통합함
이는 두 가지 단순화를 제공함 이제 audit 데이터를 단일 위치에서 찾을 수 있으며
모든 감사 데이터는 단일 형식임
- Standard database auditing
- SYS operations auditing (AUDIT_SYS_OPERATIONS)
- Fine Grained Audit (FGA)
- Data Pump
- Oracle RMAN
- Oracle Label Security (OLS)
- Database Vault (DV)
- Real Application Security (RAS)
- SQL*Loader Direct Load
Unified Auditing(통합 감사)는 Oracle Enterprise Edition과 함께 제공됨 추가 라이센스가 필요하지 않음
기본적으로 설치되지만 기본적으로 완전히 활성화되지는 않음
12c 이전 audit에서 전환 할 수있는 두 가지 작동 모드가 있음
Mixed 모드 – 기본 12c 옵션. 모든 12c 이전 로그 및 감사 기능 및 구성은 이전과 동일하게 작동함
새로운 통합 감사 기능도 사용할 수 있음, 로그 데이터는 기존 위치와 새로운 뷰 SYS.UNIFIED_AUDIT_TRAIL에서 모두 사용할 수 있음 또한 Syslog를 사용할 때 로그 데이터는 일반 텍스트로 계속 기록됨
FULL 모드 또는 PURE 모드 – 데이터베이스를 중지하고 Oracle 커널을 다시 연결해야만 사용할 수 있음
활성화되면 12c 이전의 로그 및 audit 구성이 무시되고 독점 파일 형식 인 Oracle SecureFiles를 사용하여 감사 데이터가 저장됨
이 때문에 Syslog는 지원되지 않음, 모든 audit 데이터는 SYS.UNIFIED_AUDIT_TRAIL 뷰에서 찾을 수 있음
그림 1 – Oracle 12c 이전 감사
그림 2 – Oracle 12c 통합 감사 – Mixed 모드
그림 3 – Oracle 12c 통합 감사 – PURE 모드
그림 4 – Oracle 12c 통합 감사
audit_sys_operations 파라미터
audit_sys_operations 파라미터가 TRUE 인 경우 sys 유저가 실행한 로그는
AUDIT_FILE_DEST파라미터에 지정된 디렉토리에 텍스트 파일로 생성됨
파라미터 확인
1 2 3 4 5 6 7 8 | SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin/ORCL12/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 |
audit_trail이 DB 이고 audit_sys_operations 이 TRUE 인 경우 아래와 같은 명령을 입력하면
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin/ORCL12/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 SQL> create user IMSI identified by imsi account unlock; User created. SQL> grant dba to imsi; Grant succeeded. |
OS의 audit 파일에는 아래와 같이 기록됨
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | $ cd /oracle/app/oracle/admin/ORCL12/adump $ vi ORCL12_ora_1206_20200202142034301496143795.aud Audit file /oracle/app/oracle/admin/ORCL12/adump/ORCL12_ora_1206_20200202142034301496143795.aud Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125 ORACLE_HOME: /oracle/app/oracle/product/12.2.0.1/db_1 System name: Linux Node name: ORACLE12 Release: 4.14.35-1818.3.3.el7uek.x86_64 Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018 Machine: x86_64 Instance name: ORCL12 Redo thread mounted by this instance: 1 Oracle process number: 53 Unix process pid: 1206, image: oracle@ORACLE12 (TNS V1-V3) . . . Sun Feb 2 14:18:32 2020 +09:00 LENGTH : '540' ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3621335312' SESSIONID:[10] '4294967295' USERHOST:[8] 'ORACLE12' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' Sun Feb 2 14:18:52 2020 +09:00 LENGTH : '298' ACTION :[47] 'create user IMSI identified by * account unlock' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3621335312' SESSIONID:[10] '4294967295' USERHOST:[8] 'ORACLE12' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '51' Sun Feb 2 14:19:02 2020 +09:00 LENGTH : '268' ACTION :[17] 'grant dba to imsi' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3621335312' SESSIONID:[10] '4294967295' USERHOST:[8] 'ORACLE12' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '17' |
21번째 줄에 나온 로그는
show parameter audit 를 했을 때 내부적으로 실행한 쿼리문을 보여줌
그리고 35번째 줄에는 유저 생성구문이, 50번째 줄에는 grant 구문이 나와있음
21번째 줄에 나온 쿼리문인데 바인드변수 부분에 내부적으로 '%audit%'로 처리한 것 처럼 보임
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> var NMBIND_SHOW_OBJ varchar2(10); exec :NMBIND_SHOW_OBJ := '%audit%'; SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',6,'big integer', 'unknown') TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM / NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/app/oracle/admin/ORCL12/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 |
show parameter audit와 동일하게 결과가 나옴
audit 기본 tablespace 변경
audit 결과가 11g 는 SYSTEM tablespace, 12c는 SYSAUX tablespace 에 저장되는데 이렇게
기본 설정으로 두면 해당 시스템 tablespace Full로 DB서비스 장애가 발생할 가능성이 높기 때문에,
audit 테이블은 별도의 tablespace에 저장하는 것이 좋음
기존 audit 정보가 쌓이는 tablespace 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> select segment_name, tablespace_name, blocks,bytes/1024/1024 "mb" from dba_segments where segment_name in ('AUD$','FGA_LOG$','AUD$UNIFIED') / SEGMENT_NA TABLESPACE BLOCKS Mb ---------- ---------- ---------- ---------- AUD$ SYSTEM 8 .0625 FGA_LOG$ SYSTEM 8 .0625 AUD$UNIFIED SYSAUX 8 .0625 |
audit 데이터 저장용 신규 테이블스페이스 생성
1 2 3 | SQL> create tablespace SYSAUD datafile '/oracle/app/oracle/oradata/ORCL12/sysaud01.dbf' size 1024m; Tablespace created. |
audit 로그 저장 테이블 및 스키마 이동
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SQL> begin DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUD') ; end; / PL/SQL procedure successfully completed. SQL> begin DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUD') ; end; / PL/SQL procedure successfully completed. SQL> -- 12c only begin DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUD') ; end; / PL/SQL procedure successfully completed. |
변경 후 확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> select segment_name, tablespace_name, blocks,bytes/1024/1024 "mb" from dba_segments where segment_name in ('AUD$','FGA_LOG$','AUD$UNIFIED') / SEGMENT_NA TABLESPACE BLOCKS Mb ---------- ---------- ---------- ---------- AUD$ SYSAUD 8 .0625 FGA_LOG$ SYSAUD 8 .0625 AUD$UNIFIED SYSAUX 8 .0625 |
AUD$, FGA_LOG$는 SYSAUD로 변경이 되었는데 AUD$UNIFIED는 왜 변경이 안되는지 모르겠어서 찾아봄
https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65429 에 나와있는 내용을 보면
When AUDIT_TRAIL_TYPE is AUDIT_TRAIL_UNIFIED, this procedure sets the tablespace for newer audit records in the unified audit trail but does not move the older audit records. Thus, it is not resource intensive for unified audit trail.
AUDIT_TRAIL_TYPE이 AUDIT_TRAIL_UNIFIED 인 경우 이 프로시저는 통합 감사 추적에서 최신 감사 레코드의 테이블 공간을 설정하지만 이전 감사 레코드는 이동하지 않습니다. 따라서 통합 감사 추적에는 리소스를 많이 사용하지 않습니다. -> ?
이 내용을 보면 아마도 AUD$UNIFIED가 저장되는 tablespace를 변경해도 기존에 발생한 audit 로그는
계속 기존 tablespace(SYSAUX)에 있고 이후 발생한 audit 로그만 새로운 tablespace(SYSAUD)에 저장되는것 같음
하지만 imsi 유저로 테스트를 해본 결과
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | SQL> conn imsi/imsi Connected. SQL> create table dept( deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) ); Table created. SQL> insert into dept values(20, 'RESEARCH', 'DALLAS'); 1 row created. SQL> commit; Commit complete. SQL> drop table dept; Table dropped. SQL> flashback table dept to before drop; Flashback complete. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SQL> drop table dept purge; Table dropped. |
imsi 라는 계정으로 일련의 작업을 한 뒤 다시 조회해 보았지만
dba_segments 를 조회한 결과는 동일하였음
하지만 dba_audit_mgmt_config_params 뷰를 보니 정상적으로 tablespace가 변경은 된것 같음
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> select * from dba_audit_mgmt_config_params; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ ------------------------------ -------------------- DB AUDIT TABLESPACE SYSAUD STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUD FGA AUDIT TRAIL DB AUDIT TABLESPACE SYSAUD UNIFIED AUDIT TRAIL AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL 14 rows selected. |
dba_segments 뷰를 조금 자세히 보았음
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL> select SEGMENT_NAME, SEGMENT_SUBTYPE, SEGMENT_TYPE, BYTES/1024/1024, TABLESPACE_NAME from dba_segments where OWNER = 'AUDSYS' / SEGMENT_NAME SEGMENT_SU SEGMENT_TYPE BYTES/1024/1024 TABLESPACE ------------------------------ ---------- ------------------ --------------- ---------- AUD$UNIFIED ASSM TABLE PARTITION .0625 SYSAUX AUD$UNIFIED ASSM TABLE PARTITION .0625 SYSAUX AUD$UNIFIED ASSM TABLE PARTITION .0625 SYSAUX SYS_IL0000017941C00097$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00031$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00030$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00097$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00031$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00030$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00097$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00031$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_IL0000017941C00030$$ ASSM INDEX PARTITION .0625 SYSAUX SYS_LOB0000017941C00030$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00031$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00097$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00031$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00030$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00030$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00097$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00031$$ SECUREFILE LOB PARTITION .125 SYSAUX SYS_LOB0000017941C00097$$ SECUREFILE LOB PARTITION .125 SYSAUX 21 rows selected. |
파이션 테이블로 저장이 되고있음
이 파티션 테이블을 삭제하고 다시 imsi 유저로 작업을 해봄
AUD$UNIFIED 삭제
1 2 3 4 5 6 | SQL> truncate table AUDSYS.AUD$UNIFIED; truncate table AUDSYS.AUD$UNIFIED * ERROR at line 1: ORA-46385: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED". |
실패함 해당 audit 테이블은 DML 또는 DDL을 수행할 수 없다고함
삭제하려면 아래 명령을 사용해야함
남아있는 audit 확인
1 2 3 4 5 6 7 8 | SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name; UNIFIED_AUDIT_POLICIES ACTION_NAME COUNT(*) ---------------------- ---------- ---------- ORA_SECURECONFIG UNDROP OBJECT 1 |
모두 삭제
1 2 3 4 5 6 7 8 9 | SQL> begin DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP=>false); end; / PL/SQL procedure successfully completed. |
다시 확인
1 2 3 4 5 6 7 8 9 | SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name; UNIFIED_AUDIT_POLICIES ACTION_NAME COUNT(*) ---------------------- ---------- ---------- EXECUTE 1 |
모두 지워지고 EXCUTE 한 기록만 남아있음
다시 imsi 유저로 작업 후 확인해 보았지만 이 쿼리 결과는 동일하게 나옴
1 2 3 4 5 6 7 8 9 10 11 | SQL> select segment_name, tablespace_name, blocks,bytes/1024/1024 "mb" from dba_segments where segment_name in ('AUD$','FGA_LOG$','AUD$UNIFIED') / SEGMENT_NA TABLESPACE BLOCKS Mb ---------- ---------- ---------- ---------- AUD$ SYSAUD 8 .0625 FGA_LOG$ SYSAUD 8 .0625 AUD$UNIFIED SYSAUX 8 .0625 |
truncate 방법을 찾음
db를 upgrade mode로 startup 한뒤에 truncate 명령을 사용하면 에러가 발생하지 않는다고함
db 종료
1 2 3 4 | SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. |
upgrade mode로 startup
1 2 3 4 5 6 7 8 9 10 | SQL> startup upgrade; ORACLE instance started. Total System Global Area 1660944384 bytes Fixed Size 8621376 bytes Variable Size 989856448 bytes Database Buffers 654311424 bytes Redo Buffers 8155136 bytes Database mounted. Database opened. |
AUDSYS.AUD$UNIFIED 테이블 truncate 시도
1 2 3 | SQL> truncate table AUDSYS.AUD$UNIFIED; Table truncated. |
다시 재기동 후 확인
1 2 3 4 5 6 7 8 | SQL> shutdown immediate SQL> startup SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name; no rows selected |
다시 imsi 유저로 작업 후 확인해 보았지만 이 쿼리 결과는 동일하게 나옴
1 2 3 4 5 6 7 8 9 10 11 | SQL> select segment_name, tablespace_name, blocks,bytes/1024/1024 "mb" from dba_segments where segment_name in ('AUD$','FGA_LOG$','AUD$UNIFIED') / SEGMENT_NA TABLESPACE BLOCKS Mb ---------- ---------- ---------- ---------- AUD$ SYSAUD 8 .0625 FGA_LOG$ SYSAUD 8 .0625 AUD$UNIFIED SYSAUX 8 .0625 |
=> 오라클 커뮤니티에서 답을 찾음
JimFx 2018. 6. 21 오후 9:50 (Mark D Powell에 대한 응답)
The audit uses the default build tablespace, SYSAUX, until the partition interval is hit. Then any new partitions are created in the tablespace defined dbms_audit_mgmt.set_audit_trail_location
audit는 파티션 간격에 도달 할 때까지 기본 빌드 테이블 스페이스 SYSAUX를 사용합니다. 그런 다음 테이블 스페이스 정의 dbms_audit_mgmt.set_audit_trail_location에 새 파티션이 작성됩니다.
=> AUD$UNIFIED 해당 테이블이 파티션 테이블인데 월별로 자동으로 파티션됨
그래서 dbms_audit_mgmt.set_audit_trail_location로 저장되는 tablespace를 변경해도 바로 변경이 안되고
다음에 할당되는(만들어지는) 파티션부터 새로운 tablespace에 저장된다고함
시간이 날때 OS 시간을 1달뒤로 변경한 뒤 다시한번 imsi 유저로 작업한뒤 audit를 확인해봐야겠음
audit 관련 뷰
감사 정보 획득
- ALL_DEF_AUDIT_OPTS (Default audit options)
- DBA_STMT_AUDIT_OPTS (Statement auditing options)
- DBA_PRIV_AUDIT_OPTS (Privilege auditing options)
- DBA_OBJ_AUDIT_OPTS (Schema object auditing options)
감사가 수행된 결과
- DBA_AUDIT_TRAIL (All audit trail entries)
- DBA_AUDIT_EXISTS (Records for AUDIT EXISTS/NOT EXISTS)
- DBA_AUDIT_OBJECT (Records concerning schema objects)
참조 :
http://rastalion.me/archives/619
http://blog.daum.net/_blog/BlogTypeView.do?blogid=07wRi&articleno=15858542&_bloghome_menu=recenttext
https://makebob.tistory.com/458
http://www.gurubee.net/lecture/1861
https://secmaster.tistory.com/23
https://aozjffl.tistory.com/412
https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams017.htm#REFRN10006
Doc ID 2229667.1
https://www.integrigy.com/oracle-security-blog/what-oracle-release-12c-unified-auditing
https://blog.dbi-services.com/purging-unified-audit-trail-in-12cr2/
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 파라미터 뷰, 파일 관련 정리 spfile, v$parameter, v$parameter2, v$spparameter 비교 (0) | 2020.02.11 |
|---|---|
| 오라클 control file 경로 변경 및 이중화 (0) | 2020.02.11 |
| 오라클 undo tablespace 재생성 (0) | 2020.02.01 |
| 오라클 메모리 관련 정리 (6) | 2020.01.28 |
| 오라클 10g에서 sga 메모리 및 프로세스 변경 방법 (2) | 2020.01.28 |