■ Audit(감사)
1. 정의
1) audit 은 선택한 user database 작업을 모니터하는 것
- 의심가는 데이터베이스 작업을 조사
- 특정 데이터베이스 작업에 대한 정보를 수집할 경우
2) SQL문 감사, 권한 감사, 테이블에 대한 SELECT문, DML 감사
2. 조회 방법
SQL> show parameter audit_trail
NAME TYPE VALUE
------------- -------- ---------
audit_trail string DB : 활성화
1) DB : 활성화
2) NONE : 비활성화
3) OS : audit 결과를 OS 에 파일로 생성
# 확인
SELECT * FROM v$parameter WHERE name ='audit_trail';
3. 변경방법
ALTER SYSTEM SET audit_trail = NONE SCOPE = SPFILE;
ALTER SYSTEM SET audit_trail = db SCOPE = SPFILE;# 확인(aud$ : 감사테이블)
SELECT * FROM sys.aud$;
| 컬럼명 | user name | session id | terminal id | object name | action id | date | completioncode |
| 해석 | 오라클 유저명 |
오라클 session id |
리눅스/유닉스 시스템 상의 터미널 id |
테이블, 뷰 등의 이름 | 명령 코드 | 명령이 실행된 날짜, 시간 | 0 : 성공 실패시 에러코드 |
# 옵션 지우는 방법
noaudit table;
truncate table sys.aud$;
4. 옵션 방법
1) 문장 감사 : SQL문을 선택적으로 감사
# 감사 옵션 활성화
audit table;
# 감사 옵션 비활성화
noaudit table;- create table, drop table, truncate table 수행 시 감사.(단, alter table은 제외)
select * from dba_stmt_audit_opts where audit_option='TABLE';| 컬럼명 | 해석 |
| user_name | 감사 옵션을 활성화 또는 비활성화한 사용자 또는 역할의 이름 |
| proxt_name | 사용자 대신 감사를 설정한 경우, 프록시 사용자의 이름. 프록시 사용자는 다른 사용자를 대신하여 일부 권한을 가지는 사용자 |
| audit_option | 감사 옵션의 이름 또는 유형. ex) SELECT, INSERT, UPDATE, DELETE 등 |
| success | 성공한 문장 실행 감사의 상태 'BY ACCESS' : 각 문장의 실행에 대한 성공. 한 세션에서 위 명령문이 들어올 때 마다 감사로그정보로 남기겠다. 'BY SESSION' : 세션 동안의 모든 성공 |
| failure | 실패한 문장 실행 감사의 상태 'BY ACCESS' : 각 문장의 실행에 대한 실패. 실패가 되더라도 그 오류도 감사로그정보로 남기겠다. 'BY SESSION' : 세션 동안의 모든 실 |
# 순서대로 진행할 것
| 순서 | SESSION | 실행 코드 |
| 1 | SAWON02 | CREATE TABLE sawon02.emp AS SELECT * FROM hr.employees; |
| 2 | SYS | SELECT * FROM sys.aud$; => 로그정보가 보여야하는데 안보인다면? SQL> noaudit table; -- 감사옵션 취소 select * from dba_stmt_audit_opts where audit_option='TABLE'; -- 비활성화하면 정보없음 truncate table sys.aud$; |
| 3 | SAWON02 | select * from tab; drop table emp purge; |
| 4 | SYS | select * from aud$; |
| 5 | SAWON02 | CREATE TABLE sawon02.emp_copy AS SELECT * FROM hr.employees; |
| 6 | SYS | select * from aud$; |
| 7 | SAWON02 | create table sawon02.emp as select * from hr.employees; |
| 8 | SYS | select * from aud$; |
| 9 | SAWON02 | drop table sawon02.emp purge; |
| 10 | SYS | select * from aud$; |
| 11 | SAWON02 | alter table emp_copy rename to emp; |
| 12 | SYS | select * from aud$; ==> alter는 감사에 걸리지 않는다. select * from dba_audit_object; -- 실제 dictionary 테이블(sys.aud$)을 보기 편하게 만든 테이블 select username, owner, obj_name, action_name, decode(returncode, '0', 'success', returncode) sess, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day from dba_audit_object; -- 0 이면 sql문 성공한 것임 그럼 success표현 |
| 13 | SAWON02 | create table sawon02.emp as select * from hr.employees; => 오류발생 |
| 14 | SYS | select username, owner, obj_name, action_name, decode(returncode, '0', 'success', returncode) sess, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day from dba_audit_object; (출력화면) username owner obj_name action_name sess day -------- ------ -------- ------------ ------- ------------------- SAWON02 SAWON02 EMP CREATE TABLE 955 2023-12-26 14:28:33 SAWON02 SAWON02 EMP CREATE TABLE success 2023-12-26 14:17:14 |
| 15 | SAWON02 | truncate table emp; |
| 16 | SYS | select username, owner, obj_name, action_name, decode(returncode, '0', 'success', returncode) sess, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day from dba_audit_object; (출력화면) username owner obj_name action_name sess day -------- ------ -------- ------------ ------- ------------------- SAWON02 SAWON02 EMP TRUNCATE TABLE success 2023-12-26 14:32:44 drop table hr.dept cascade CONSTRAINTS purge; drop table hr.emp cascade CONSTRAINTS purge; |
test 해보자!
< hr session >
| 순서 | session / 해석 | 코드 |
| 1 | < hr session > test table 생성 |
create table hr.dept as select department_id dept_id, department_name dept_name from hr.departments; create table hr.emp as select employee_id id, last_name name, salary sal, department_id dept_id from hr.employees; |
| 2 | < hr session > 제약조건 추가 |
alter table hr.dept add constraint deptid_pk primary key(dept_id); alter table hr.emp add constraint empid_pk primary key(id); alter table hr.emp add constraint emp_deptid_pk foreign key(dept_id) references hr.dept(dept_id); #) 확인 select * from user_constraints where table_name in ('EMP', 'DEPT'); |
| 3 | < hr session > 권한 sawon01, sawon02 부여 : SELECT + DML 권한 |
grant select, insert, update, delete on hr.emp to sawon01, sawon02; grant select, insert, update, delete on hr.dept to sawon01, sawon02; #) 확인 select * from user_tab_privs where grantee in ('SAWON01', 'SAWON02'); |
| 4 | <SYS SESSION> 테이블에 대한 감사 정책 |
# 스키마(schema) object audit - 특정한 유저가 갖고 있는 object = 스키마 - 특정한 유저가 소유한 테이블에 대해서 감사 설정 audit select, insert, update, delete on hr.emp; audit select, insert, update, delete on hr.dept; select owner, object_name, object_type, sel, ins, upd, del from dba_obj_audit_opts; |
| 5 | <SAWON01 SESSION> 다른 session에서 확인 |
select sal from hr.emp where id = 100; |
| 6 | <SYS SESSION> user session에서 어떤 쿼리문을 수행했는지 확인 |
SELECT * FROM sys.aud$; select username, owner, obj_name, action_name, decode(returncode, '0', 'success', returncode) sess, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day from dba_audit_object; |
| 7 | <SAWON01 SESSION> 다른 쿼리문도 수행 |
INSERT INTO hr.emp(id, name, sal, dept_id) VALUES (300, 'james', 1000, 10); UPDATE hr.emp SET sal = 1000 WHERE id = 101; DELETE FROM hr.emp WHERE id = 200; rollback; |
| 8 | <SAWON02 SESSION> 또다른 session 에서 쿼리문 실행 |
SELECT sal FROM hr.emp WHERE id = 300; INSERT INTO hr.emp(id, name, sal, dept_id) VALUES (400, 'scott', 1000, 500); DELETE FROM HR.dept WHERE dept_id = 20; rollback; |
| 9 | <SYS SESSION> # 감사 설정 취소, 스키마 객체 감사 취소 |
noaudit select, insert, update, delete on hr.emp; noaudit select, insert, update, delete on hr.dept; # 감사 취소 확인 SELECT owner, object_name, object_type, sel, ins, upd, del FROM dba_obj_audit_opts; |
| 10 | <SYS SESSION> # 감사 로그 삭제 및 확인 |
truncate table sys.aud$; -- 감사 로그 삭제 SELECT * FROM sys.aud$; -- 삭제 확인 SELECT owner, object_name, object_type, sel, ins, upd, del FROM dba_obj_audit_opts; |
| 11 | <hr session> 권한 확인 후 권한 회수 |
select * from user_tab_privs where grantee in ('SAWON01', 'SAWON02'); revoke all on hr.emp from sawon01, sawon02; revoke all on hr.dept from sawon01, sawon02; #) 확인 select * from user_tab_privs where grantee in ('SAWON01', 'SAWON02'); |
| 12 | <SYS SESSION > 권한 부여 |
grant select any table to sawon01, sawon02; #) 확인 select * from dba_sys_privs where grantee in ('SAWON01', 'SAWON02'); |
■ 권한(privilege) audit
- 시스템 권한을 선택적으로 감사
audit select any table by sawon01, sawon02 by session;
select * from dba_stmt_audit_opts where audit_option = 'SELECT ANY TABLE';
<SAWON01 SESSION>
select * from hr.emp;
select * from hr.employees;
select * from hr.dept;
select * from hr.departments;
select * from hr.locations;
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
from dba_audit_object;
※ sql developer 에서 출력이 되지 않을 경우,
1. putty 창에서 sawon01/oracle 로 로그인
2. sawon01 에서 select문 조회
3. as sysdba 로 로그인
4. select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
from dba_audit_object; 쿼리문 조회
5. sql developer 에서도 동일한 쿼리문 조회
■ audit_trail=db_extended 유저가 실행한 SQL 텍스트 및 변수 값 감사
SQL> show user
USER is "SYS"
SQL> show parameter audit_trail
SQL> alter system set audit_trail=db_extended
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 436208676 bytes
Database Buffers 268435456 bytes
Redo Buffers 5419008 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail
<SYS SESSION>
# 문장 감사(정보가 나타나지 않아서)
truncate table sys.aud$;
audit table;
select * from dba_stmt_audit_opts where audit_option='TABLE';
<SAWON02 SESSION>
CREATE TABLE sawon02.emp
AS
SELECT *
FROM hr.employees;
<SYS SESSION>
SELECT * FROM sys.aud$;
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, -- sql 쿼리문
sql_bind --변수
from dba_audit_object;
<SYS SESSION>
SELECT * FROM sys.aud$;
=> 로그정보가 보여야하는데 안보인다면?
SQL> noaudit table; -- 감사옵션 취소
select * from dba_stmt_audit_opts where audit_option='TABLE'; -- 비활성화하면 정보없음
truncate table sys.aud$;
<SAWON02 SESSION>
select * from tab;
drop table emp purge;
<SYS SESSION>
select * from aud$;
<SAWON02 SESSION>
CREATE TABLE sawon02.emp_copy
AS
SELECT * FROM hr.employees;
<SYS SESSION>
select * from aud$;
<SAWON02 SESSION>
create table sawon02.emp
as
select * from hr.employees;
<SYS SESSION>
select * from aud$;
<SAWON02 SESSION>
drop table sawon02.emp purge;
# 감사 옵션 취소
<SYS SESSION>
noaudit table;
select * from dba_stmt_audit_opts where audit_option='TABLE';
truncate table sys.aud$; -- 공간관리, timestamp 에 보고
# 객체 감사
<SYS SESSION>
grant select on hr.employees to sawon01, sawon02;
audit select on hr.employees;
select owner, object_name, object_type, sel, ins, upd, del from dba_obj_audit_opts;
<SAWON01 SESSION> : putty 창에
SQL> var v_id number
SQL> execute :v_id := 100
PL/SQL procedure successfully completed.
SQL> print :v_id
V_ID
----------
100
SQL> select salary from hr.employees where employee_id = :v_id;
SALARY
----------
24000
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day, sql_text, sql_bind
from dba_audit_object;
<SAWON02 SESSION> : putty 창에
SQL> var v_dept_id number
SQL> execute :v_dept_id := 20
SQL> print :v_dept_id
SQL> select salary from hr.employees where department_id = :v_dept_id;
SALARY
----------
13000
6000
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day, sql_text, sql_bind
from dba_audit_object;
<SAWON02 SESSION> : putty창에서
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- -------------------
EMP TABLE
SQL> drop table emp
2 purge;
Table dropped.
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day, sql_text, sql_bind
from dba_audit_object;# 지우기
noaudit select on hr.employees;
select owner, object_name, object_type, sel, ins, upd, del from dba_obj_audit_opts;
truncate table sys.aud$;
# 권한 감사
<SYS SESSION> : 시스템권한 < 객체권한 강함
audit select any table by sawon01, sawon02 by session; -- 객체권한이 있기 때문에
select * from dba_stmt_audit_opts where audit_option = 'SELECT ANY TABLE';
<SAWON01 SESSION>
select * from hr.locations;
<SAWON02 SESSION>
select * from hr.departments;
select * from user_tab_privs;
select * from role_tab_privs;
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day, sql_text, sql_bind
from dba_audit_object;-- 객체권한이 있기 때문에 sawon02은 hr.departments 확인X
'Data Base > Linux' 카테고리의 다른 글
| 231227 Linux_DBA 감사, TRIGGER (1) | 2023.12.27 |
|---|---|
| 231227 Linux_권한, sys.aud$ 이관작업, SQL문 감사, 객체 감사, 시스템 권한 감사, Find Grained Auditing (1) | 2023.12.27 |
| 231222 Linux_password file 생성, SYSDBA ROLE 인증체크 (1) | 2023.12.22 |
| 231222 Linux_RESOURCE 관리, PROFILE 관리 (0) | 2023.12.22 |
| 231222 Linux_ password 관리 (0) | 2023.12.22 |