Neo-Oracle Home

■ 개요

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

특정 시간대에 갑자기 SQL*Net break/reset to client 이벤트가 늘었다가 사라짐을 확인했다.

액티브 세션을 확인해보니 Insert 문 두개가 해당 이벤트를 보이고 있었다.

오라클 매뉴얼에서 찾은 이벤트 내용은 아래와 같았고, 애매했다

The server sends a break or reset message to the client. The session running on the server waits for a reply from the client.

Wait Time: The actual time it takes for the break or reset message to return from the client

Tanel poder 포스트(http://blog.tanelpoder.com/2008/04/10/sqlnet-breakreset-to-client/)에서 자세한 내용을 확인할 수 있었다.

결국 에러가 발생했을 때 SQL*Net break/reset to client 이벤트가 발생한다는건데 Insert에서 에러 발생할만한 건은 대부분 중복키.

간단히 테스트를 해본다.

중복키 Insert 의심을 하고 WAS쪽 확인을 요구할 생각이다.