141021_데이터베이스보안_Native Auditing

1. 감사 활성화

일단 AUDIT과 관련된 PARAMETER를 확인한다.

SQL > SHOW PARAMETER AUDIT


AUDIT_TRAIL을 DB_EXTENDED로하고
SCOPE를 spfile로 한다.

SQL > ALTER system SET audit_trail = db_extended scope=spfile --Server Parameter FIle


sysdba로 로그인 한 후에 DB를 재부팅한다.
SQL > CONN sys as sysdba
SQL > SHUTDOWN IMMEDIATE
SQL > STARTUP


변경 내용 확인

(http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000)

2. 감사 실행
audit 명령어 Ref
(http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF01107)

AUDIT 명령어 구성
- operation
- schema_object
EX) AUDIT SELECT ON hr.employees
- NETWORK

- OPTION
- BY SESSION
감사 대상 작업이 반복 실행될 때 감사 기록을 1번 생성(세션마다 한 번씩만 하겠다는 말인듯)
- BY ACCESS
감사 대상 작업이 반복될때 마다 감사 기록을 생성
- WHENEVER [not] SUCCESSFUL


EX)
- AUDIT SELECT ON hr.employees;

- AUDIT SELECT table, UPDATE, table;

- AUDIT role WHENEVER SUCCESSFUL

3. 작업

따로 USER 만들어서 할라구 했는데
CREATE GLOBAL TEMPORARY TALBE에 대한 권한이 뭔지 모르겠어서 그냥 SYSTEM으로 했다.

CREATE GLOBAL TEMPORARY TABLE test_tab (id number);

AUDIT INSERT, UPDATE, DELETE ON test_tab BY ACCESS WHENEVER SUCCESSFUL;


테이블에 데이터를 조금 넣어준다.
BEGIN
FOR i In 1..1000 LOOP
INSERT INTO test_tab VALUES (i);
END LOOP;
END;
/

확인해준다.
SELECT * FROM test_tab;

UPDATE작업을 해서 감사가 제대로 되는지 확인한다.
UPDATE test_tab
SET id = 0
WHERE id BETWEEN 1 AND 10;

4. 감사 결과 조회

SELECT * FROM SYS.AUD$;

5. 생각 해볼점

1. Trigger와 비교해서 Native Audit(Standard Audit)의 장점
트리거를 생성하지 않아도 되며, 감사 명령을 통해 보다 많은 정보를 얻을 수 있다.

2. 감사의 목적
감사와 데이터베이스의 성능은 반비례한다.
무분별한 감사는 데이터베이스의 성능의 감소를 초래할 수 있다.
그래서 감사의 목적을 명확히 해서 데이터베이스의 성능에 영향을 덜 주어야 할 것이다.

2-1) 감사의 범위
예) AUDIT SELECT table, UPDATE table
- 모든 SELECT와 UPDATE에 감사결과가 쌓이게 된다. 엄청난 감사 결과가 쌓일 것이고
- SELECT와 UPDATE 작업마다 감사를 기록하게 되므로 데이터베이스의 성능에 영향을 줄 수 있다.

2-2) 감사를 할때 일반적인 감사 수행 후 감사 결과를 조회한다. 필요한 부분만 선별적으로 감사를 하자
DBMS_FGA(; Fine Grained Auditing) 패키지 내의 ADD_POLICY를 실행

예) OE 스키마에 CUSTOMERS 테이블에 신용한도 (CREDIT_LIMIT)를 검색,수정하는 경우에 대해 감사를 수행

정책을 만든다.
BEGIN
dbms_fga.add_policy(
object_schema => 'oe',
object_name => 'customers',
policy_name => 'upd_credit_limit',
audit_column => 'credit_limit',
statement_types => 'select, update'
);
END;
/

조회와 수정을 해준다.
1.
SELECT * FROM customers WHERE customer_id = 250;

2.
UPDATE customers SET account_mgr_id = 146 WHERE credit_limit < 1000;

3.
SELECT customer_id, cust_first_name
  FROM customers
  WHERE gender = 'F' AND EXTRACT(YEAR FROM date_of_birth ) >= 1980;

4.       
SELECT customer_id, cust_first_name, credit_limit
  FROM oe.customers
  WHERE account_mgr_id = 146;

조회한다.
SELECT * FROM SYS.FGA_LOG$;

3의 경우에는 감사가 되지 않았다.
credit_limit
에 대한 SELECT가 아니기 때문이다.

정책 삭제하는 법
begin
dbms_fga.drop_policy (
object_schema   => 'oe', 
object_name      => 'customers',
policy_name      => 'upd_credit_limit'
  );  
end; 

3. 감사의 결과의 무결성
DELETE FROM SYS.AUD$;
- 이런 식으로 누군가 삭제하거나 조작이 가능하다.

그래서 감사 결과를 따로 테이블로 만들어서 저장해둔다.
모든 정보를 저장하기보단 필요한 정보만 저장하는 해준다.(CTAS 사용)
CREATE TABLE archive_fgalog
AS
SELECT
dbuid,
osuid,
oshst,
obj$schema,
obj$name,
policyname,
lsqltext,
sqlbind,
ntimestamp#
FROM sys.fga_log$;

조회해주면 된다.
SELECT * FROM archive_fgalog;
`
년/월/일과 분/초는 정확한데 시간이 다르다. 시간에 +9를 해주면 우리나라 시간이 된다.

그래서 매일 매일의 감사 결과를 시간까지 계산해서 저장해주고자 한다면
CREATE TABLE archive_fgalog
AS
SELECT
dbuid,
osuid,
oshst,
obj$schema,
obj$name,
policyname,
lsqltext,
sqlbind,
ntimestamp#
FROM sys.fga_log$;
WHERE  to_date(to_char(ntimestamp# + (9/24), 'YYYY/MM/DD'))  = SYSDATE; 

ntimestamp#에 (9/24)를 더하하는 것이 9시간을 더하는 작업이다.
ntimestamp#에 1을 더하면 하루가 늘어난다.

그리고 9시간 더한 날짜를 YYYY/MM/DD 포멧으로 바꾸고 date 형으로 변환한 것이
SYSDATE,즉 현재 시스템 시간과 같은 것만 조회해서 테이블을 만들겠다는 것이다.