Data Base/Linux

231226 Linux_Audit(감사)

잇꼬 2023. 12. 26. 18:29
728x90
반응형
SMALL

■ 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

728x90
반응형
LIST