Neo-Oracle Home

컨셉 매뉴얼을 잠깐 참조하다가 DDL Log가 있어 확인해본 내용 정리

예전에 audit 말고 ddl을 남기는 방법을 찾다가

ALERT LOG에 DDL 기록을 남기기 위해 enable_ddl_logging 파라미터를 확인해서 적용했던 적이 있는데

결국엔 다시 껐다.

이유는 당시 적용한 db는 주기적인 datapump 작업이 자주 일어나고 있었는데,

datapump 작업시의 임시 테이블이나 뷰 생성과 DROP에 기록도 남아 DB Alert log 를 더럽혔기 때문이다.

12c부터(R1인지 R2인지 정확히 모름) DDL 로그가 전용으로 생겼다

위치1 text file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl_<sid>.log

위치2 xml file

    $ORACLE_BASE/diag/rdbms/<dbname>/<sid>/log/ddl/log.xml

설정:

expdp를 수행해 본다

ddl 로그 파일을 확인해보니

이 내용들이 db alertlog에 남는다면 db alert log가 매우 지저분해질 수 있다.

일반 유저의 DDL은 당연히 남을 것이고

audit는 sys 유저는 별도 설정 안하면 안남는 이슈가 있는데, 과연 이 기능은 남을까?

ddl 로그를 확인해본다.

sys에서 수행한 ddl도 남는다

ddl 전용 로그가 생겨서인지 db alert log에는 ddl 로그가 안남는걸 확인했다.

간편히 사용하고 싶다면 12c이상에선 이 방법도 고려해볼만 하겠다.

단, AUDIT과는 다르게 위 로그에서 알 수 있듯이 단순히 "언제","어떤 SQL" 정보만 남고 어떤 machine에서 어떤 유저가 수행했는지 나오지 않는다.

19-03 추가

11g에선 Oracle Change Management Pack 옵션 라이센스가 필요하고

12c부턴 Oracle Lifecycle Management Pack 옵션 라이센스가 필요한 파라미터이다

이 옵션은 사는 고객사를 본 적이 드물다. 파라미터 변경하지 말도록 하자.

■ 개요

12.1과 12.2 모두 Unified Audit 기능은 off 상태입니다.

활성화하려면 relink가 필요합니다.. (기본값에 대해 다루기에 relink 과정은 생략)

기본설정은 mixed mode로 예전의 방식과 unified audit가 혼용되어 적용되어 있습니다.

■ 12c 기본 정책

11g와 마찬가지로 기본적으로 적용되어 있는 Audit 설정이 존재합니다.

 ▶기본 적용되어 있는 정책

column policy_name format a25
column user_name format a14
column enabled format a7
set line 200
set pages 1000

SQL> SELECT policy_name, enabled_opt, user_name, success, failure FROM audit_unified_enabled_policies;

POLICY_NAME               ENABLED USER_NAME      SUC FAI
------------------------- ------- -------------- --- ---
ORA_SECURECONFIG          BY      ALL USERS      YES YES
ORA_LOGON_FAILURES        BY      ALL USERS      NO  YES

--두가지 정책이 enable 되어 있습니다.
--그리고 ORA_LOGON_FAILURES는 폴리시 자체는 LOGON 이란 액션만 있고 실패에 대한 조건이 없습니다.
--12c부터는 활성화시에 옵션을 사용해 적용됩니다. 아래처럼
   
CREATE AUDIT POLICY ORA_LOGON_FAILURES ACTIONS LOGON;
AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL;

 ▶ 생성되어 있는 정책

SQL> select policy_name from AUDIT_UNIFIED_POLICIES group by policy_name;

POLICY_NAME
-------------------------
ORA_CIS_RECOMMENDATIONS
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_DATABASE_PARAMETER
ORA_RAS_SESSION_MGMT
ORA_ACCOUNT_MGMT
ORA_SECURECONFIG

 ▶ 생성되어 있는 정책 detail

col AUDIT_OPTION for a40
col AUDIT_CONDITION for a10
col ENTITY_NAME for a15
col object_schema for a10
col object_name for a15
select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE, OBJECT_SCHEMA, OBJECT_NAME from AUDIT_UNIFIED_POLICIES
order by 1,2;

 POLICY_NAME               AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCH OBJECT_NAME
------------------------- ---------------------------------------- ------------------ ---------- ---------------
ORA_ACCOUNT_MGMT          ALTER ROLE                               STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          ALTER USER                               STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          CREATE ROLE                              STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          CREATE USER                              STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          DROP ROLE                                STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          DROP USER                                STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          GRANT                                    STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          REVOKE                                   STANDARD ACTION    NONE       NONE
ORA_ACCOUNT_MGMT          SET ROLE                                 STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER ANY TRIGGER                        SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER DATABASE LINK                      STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER PROCEDURE                          STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER PROFILE                            STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER ROLE                               STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   ALTER USER                               STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE ANY TRIGGER                       SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE DATABASE LINK                     STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE PROCEDURE                         STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE PROFILE                           STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE ROLE                              STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE SYNONYM                           STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   CREATE USER                              STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP ANY LIBRARY                         SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP ANY TRIGGER                         SYSTEM PRIVILEGE   NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP DATABASE LINK                       STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP PROCEDURE                           STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP PROFILE                             STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP ROLE                                STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP SYNONYM                             STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   DROP USER                                STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   GRANT                                    STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   REVOKE                                   STANDARD ACTION    NONE       NONE
ORA_CIS_RECOMMENDATIONS   SELECT ANY DICTIONARY                    SYSTEM PRIVILEGE   NONE       NONE
ORA_DATABASE_PARAMETER    ALTER DATABASE                           STANDARD ACTION    NONE       NONE
ORA_DATABASE_PARAMETER    ALTER SYSTEM                             STANDARD ACTION    NONE       NONE
ORA_DATABASE_PARAMETER    CREATE SPFILE                            STANDARD ACTION    NONE       NONE
ORA_LOGON_FAILURES        LOGON                                    STANDARD ACTION    NONE       NONE
ORA_RAS_POLICY_MGMT       ADD GLOBAL CALLBACK                      XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       ADD PROXY                                XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE ACL                               XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE DATA SECURITY                     XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE NAMESPACE TEMPLATE                XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE ROLE                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE ROLESET                           XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE SECURITY CLASS                    XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       CREATE USER                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE ACL                               XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE DATA SECURITY                     XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE GLOBAL CALLBACK                   XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE NAMESPACE TEMPLATE                XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE ROLE                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE ROLESET                           XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE SECURITY CLASS                    XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DELETE USER                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       DISABLE DATA SECURITY                    XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       ENABLE DATA SECURITY                     XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       ENABLE GLOBAL CALLBACK                   XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       GRANT ROLE                               XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       GRANT SYSTEM PRIVILEGE                   XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       REMOVE PROXY                             XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       REVOKE ROLE                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       REVOKE SYSTEM PRIVILEGE                  XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       SET USER PASSWORD                        XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       SET USER PROFILE                         XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       SET USER VERIFIER                        XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE ACL                               XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE DATA SECURITY                     XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE NAMESPACE TEMPLATE                XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE ROLE                              XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE ROLESET                           XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE SECURITY CLASS                    XS ACTION          NONE       NONE
ORA_RAS_POLICY_MGMT       UPDATE USER                              XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      ASSIGN USER                              XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      CREATE NAMESPACE ATTRIBUTE               XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      CREATE SESSION                           XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      CREATE SESSION NAMESPACE                 XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      DELETE NAMESPACE ATTRIBUTE               XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      DELETE SESSION NAMESPACE                 XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      DESTROY SESSION                          XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      DISABLE ROLE                             XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      ENABLE ROLE                              XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      GET NAMESPACE ATTRIBUTE                  XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      SET COOKIE                               XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      SET INACTIVE TIMEOUT                     XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      SET NAMESPACE ATTRIBUTE                  XS ACTION          NONE       NONE
ORA_RAS_SESSION_MGMT      SWITCH USER                              XS ACTION          NONE       NONE
ORA_SECURECONFIG          ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER DATABASE                           SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER DATABASE LINK                      STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          ALTER PROFILE                            STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          ALTER ROLE                               STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          ALTER USER                               STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          BECOME USER                              SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE DATABASE LINK                     STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          CREATE DIRECTORY                         STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          CREATE PROFILE                           STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE ROLE                              STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          CREATE USER                              SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          DROP DATABASE LINK                       STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          DROP DIRECTORY                           STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          DROP PROFILE                             STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          DROP ROLE                                STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          DROP USER                                SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          EXECUTE                                  OBJECT ACTION      SYS        DBMS_RLS
ORA_SECURECONFIG          EXECUTE                                  OBJECT ACTION      REMOTE_SCH ADD_AGENT_CERTI
                                                                                      EDULER_AGE FICATE
                                                                                      NT
ORA_SECURECONFIG          EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          LOGMINING                                SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE       NONE
ORA_SECURECONFIG          SET ROLE                                 STANDARD ACTION    NONE       NONE
ORA_SECURECONFIG          TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE       NONE

136 rows selected.

■ 관련 뷰

AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_ENABLED_POLICIES
UNIFIED_AUDIT_TRAIL
AUDIT_UNIFIED_POLICY_COMMENTS
AUDIT_UNIFIED_CONTEXTS

비밀번호를 변경하고 싶은데 평소에 이 유저에 접속하는 다른 머신을 확인하고 싶을 수가 있다.

sys나 system으로 진행

1. 테이블 생성

2. 트리거 생성