audit

audit

- 사용자의 행동을 감시하거나 데이터베이스에 관한 통계자료를 얻는 목적으로 사용됨.
- 누가 어떤 테이블을 언제 사용하고, 언제 어떤 작업을 하는지를 기록할 수 있음.
- 레코드에 대한 auditing 은 불가능.
- auditing 걸게되면 시스템 부하를 줄 수 있음.




*종류
1) STATEMENT AUDITING : 명령의 종류에 따라서 auditing 설정
ex) audit table by scott by access whenever successful;
=> scott 유저가 내리는 명령중 테이블에 관련된 명령어(create table, drop table 등..) 이 성공한 경우 기록함

2) PRIVILEGE AUDITING : 사용되는 PRIVILEGE 에 따른 auditing 설정
ex) audit create table by scott by session 
=> scott 유저가 create table 권한이 필요한 명령(create table)을 내린 경우 접속된 세션에 대해 한번만 기록

3) OBJECT AUDITING : 적용되는 OBJECT 에 따른 AUDITING 설정
ex) AUDIT ALL ON SCOTT.EMP;
=>scott.emp 테이블에 대한 모든 명령(select, delete, drop 등...)에 관한 사항이 기록됨.




*audit 해제
- NOAUDIT ALL ON SCOTT.EMP;
- NOAUDIT create table by scott;
-

    select * from  DBA_STMT_AUDIT_OPTS;
    select * from  DBA_PRIV_AUDIT_OPTS;
    
    PRIVILEGE 를 확인하고, 
  NOAUDIT [PRIVILEGE 명, PRIVILEGE 명] WHENEVER SUCCESSFUL; -- SUCCESS 부분 감시해제
  NOAUDIT [PRIVILEGE 명, PRIVILEGE 명] WHENEVER NOT SUCCESSFUL; -- FAILURE 부분 감시해제
  
  
설정된 audit 기능 모두 해제




*설정
- init<SID>.ora 화일에 audit_trail 파라미터를 설정하고 db를 shutdown/startup 
- AUDIT_TRAIL=DB     설정 되었다면
     SYS.AUD$ 테이블에 쌓이게 됨

USER NAME : 오라클 유저 이름 
SESSION ID : 오라클 세션 ID
TERMINAL ID : 유닉스 시스템 상의 터미날 ID
OBJECT NAME : 테이블, 뷰 등의 이름
ACTION ID : 명령 코드
DATE : 명령이 실행된 날짜,시간
COMPLETION CODE : 0 이면 성공, 실패하면 에러 코드
ACTION ID : 사용된 명령을 나타내는데 이것은 숫자로 기록이 되어 있으므로
AUDIT_ACTIONS : 딕셔너리를 통하여 실제의 명령으로 변환해 볼 수 있다


-  AUDIT_TRAIL=OS     설정 되었다면
     화일로 생성됨.(AUDIT_FILE_DEST) 파라미터에 지정된 디렉토리에 텍스트 파일로



* DBA_PRIV_AUDIT_OPTS -> 권한 감사 기록
* DBA_STMT_AUDIT_OPTS -> 문장 감사 기록
* DBA_OBJ_AUDIT_OPTS -> 객체 감사 기록
* STMT_AUDIT_OPTION_MAP
* AUDIT_ACTIONS
* ALL_DEF_AUDIT_OPTS
* USER_OBJ_AUDIT_OPTS
* DBA_AUDIT_TRAIL
* USER_AUDIT_TRAIL
* DBA_AUDIT_SESSION
* USER_AUDIT_SESSION
* DBA_AUDIT_STATEMENT
* USER_AUDIT_STATEMENT
* DBA_AUDIT_OBJECT
* USER_AUDIT_OBJECT
* DBA_AUDIT_EXISTS





===================================================================================================================================
테스트



[oracle@DBTEST dbs]$ cat initORCL.ora

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'


SYS@ORCL AS SYSDBA> audit table by scott by access whenever successful;
Audit succeeded.


SCOTT@ORCL > create table audit_test(
  2  id number );


SYS@ORCL AS SYSDBA>  select username, timestamp, action_name
    from dba_audit_trail where username ='SCOTT'
    order by timestamp desc ;

USERNAME                       TIMESTAMP    ACTION_NAME
------------------------------ ------------ ----------------------------
SCOTT                          18-APR-16    CREATE TABLE
SCOTT                          18-APR-16    LOGON


SCOTT@ORCL > drop table audit_test;

Table dropped.


SYS@ORCL AS SYSDBA>  select username, timestamp, action_name
    from dba_audit_trail where username ='SCOTT'
    order by timestamp desc ;

USERNAME                       TIMESTAMP    ACTION_NAME
------------------------------ ------------ ----------------------------
SCOTT                          18-APR-16    DROP TABLE
SCOTT                          18-APR-16    LOGON
SCOTT                          18-APR-16    CREATE TABLE



SYS@ORCL AS SYSDBA> NOAUDIT create table by scott;

Noaudit succeeded.








SYS@ORCL AS SYSDBA> audit all privilege by access;

Audit succeeded.







SYS@ORCL AS SYSDBA> conn scott/scott
Connected.
SCOTT@ORCL > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          1350       1234         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1350                    20
      7900 JAMES      CLERK           7698 03-DEC-81          1350                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1350                    10
      7998 PBJ        DBA             7899 18-APR-16          2000        333         20

15 rows selected.

SCOTT@ORCL > 
SCOTT@ORCL > 
SCOTT@ORCL > create table os_audit_t(NO number);

Table created.

SCOTT@ORCL > commit;

Commit complete.




SCOTT@ORCL > insert into os_audit_t values(1);

1 row created.

SCOTT@ORCL > 
SCOTT@ORCL > commit;

Commit complete.

SCOTT@ORCL > 
SCOTT@ORCL > select * from os_audit_t;

        NO
----------
         1

SCOTT@ORCL > 
SCOTT@ORCL > drop table os_audit_t;

Table dropped.













SCOTT@ORCL > create user au_test identified by au_test;
create user au_test identified by au_test
                                  *
ERROR at line 1:
ORA-01031: insufficient privileges


SCOTT@ORCL > select * from dba_audit_trail;
select * from dba_audit_trail
              *
ERROR at line 1:
ORA-00942: table or view does not exist

select username, timestamp, action_name
    from dba_audit_trail where username ='PBJ'
    order by timestamp desc ;

 

SYS@ORCL AS SYSDBA> AUDIT ALL ON SCOTT.EMP;
Audit succeeded.


SCOTT@ORCL > update emp set SAL='1350' where JOB='CLERK';
4 rows updated.



SCOTT@ORCL > insert into emp values('7998','PBJ','DBA','7899',sysdate,'2000','333','20');
1 row created.

SYS@ORCL AS SYSDBA>  select USERNAME, TIMESTAMP,OWNER,OBJ_NAME,ACTION_NAME,SESSIONID
  2  from DBA_AUDIT_TRAIL
  3  where username='SCOTT'
  4  and OBJ_NAME='EMP'
  5  order by timestamp desc
  6  ;

USERNAME                       TIMESTAMP    OWNER                          OBJ_NAME                                                                                                                 ACTION_NAME            SESSIONID
------------------------------ ------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------- ----------
SCOTT                          18-APR-16    SCOTT                          EMP                                                                                                                      SESSION REC              1931453
SCOTT                          18-APR-16    SCOTT                          EMP                                                                                                                      SESSION REC              1931453
SCOTT                          18-APR-16    SCOTT                          EMP                                                                                                                      SESSION REC              1931453
SCOTT                          18-APR-16    SCOTT                          EMP                                                                                                                      SESSION REC              1931453



SYS@ORCL AS SYSDBA> NOAUDIT ALL ON SCOTT.EMP;
Noaudit succeeded.
    



cat $ORACLE_HOME/dbs/initORCL.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='os'