Neo-Oracle Home

19c가 exadata에서 가능하게 됐다는 글이 오라클 블로그에 올라왔었다.

https://blogs.oracle.com/database/oracle-database-19c-available-exadata

곧 있으면 일반 on-premise non-exa 버전도 나올 것이다.

소개 중에서 저는 특히 기대되는 기능이 아래 두개 였다

automatic indexing

real-time statistics collections

라이센스 얘기가 없어서 19c docs의 라이센스 가이드에서 찾아봤더니

On-premise에선 EE-ES 부터만 가능하다.

ES는 Enterprise System(EXADATA, ODA등)이고 오른쪽 노트에서는 또 ODA는 안된다고 한다.

클라우드 서비스 사용할 경우엔 다 되는것도 확인할 수 있다.

OOW18에서 19c 예정 기능들 소개를 봤을 땐 일반 DB에서도 가능할 것 같았었는데!

테스트해보려면 19c 버전 이상의 db버전 19c가 있어야 하고, 아니면 클라우드를 사용해서 해보는 수밖에 없겠다.

아니면 EXADATA 시뮬 환경을 구성하는 것도 방법인데, 12c도 메모리 요구사항이 매우 높아 잘 구동이 안되는 경우도 있어서 19c가 잘 될런지? 메모리는 더 요구할 것 같다.

On-premise 운용 측면에선 앞으로도 EXA가 아닌 머신은 해당 기능들을 고려할 필요가 없겠다. 조금 아쉽다.

■ 개요

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

12c에선 보안용으로 ORA-STIG_PROFILE 이라는 profile을 기본으로 생성해준다.

SQL> select * from dba_profiles;

PROFILE           RESOURCE_NAME                  RESOURCE LIMIT                          COM
----------------- ------------------------------ -------- ------------------------------ ---
DEFAULT           COMPOSITE_LIMIT                KERNEL   UNLIMITED                      NO
DEFAULT           SESSIONS_PER_USER              KERNEL   UNLIMITED                      NO
DEFAULT           CPU_PER_SESSION                KERNEL   UNLIMITED                      NO
DEFAULT           CPU_PER_CALL                   KERNEL   UNLIMITED                      NO
DEFAULT           LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED                      NO
DEFAULT           LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED                      NO
DEFAULT           IDLE_TIME                      KERNEL   UNLIMITED                      NO
DEFAULT           CONNECT_TIME                   KERNEL   UNLIMITED                      NO
DEFAULT           PRIVATE_SGA                    KERNEL   UNLIMITED                      NO
DEFAULT           FAILED_LOGIN_ATTEMPTS          PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_LIFE_TIME             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_REUSE_TIME            PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_REUSE_MAX             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_VERIFY_FUNCTION       PASSWORD NULL                           NO
DEFAULT           PASSWORD_LOCK_TIME             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_GRACE_TIME            PASSWORD UNLIMITED                      NO
ORA_STIG_PROFILE  COMPOSITE_LIMIT                KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  SESSIONS_PER_USER              KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  CPU_PER_SESSION                KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  CPU_PER_CALL                   KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  LOGICAL_READS_PER_SESSION      KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  LOGICAL_READS_PER_CALL         KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  IDLE_TIME                      KERNEL   15                             NO
ORA_STIG_PROFILE  CONNECT_TIME                   KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  PRIVATE_SGA                    KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE  FAILED_LOGIN_ATTEMPTS          PASSWORD 3                              NO
ORA_STIG_PROFILE  PASSWORD_LIFE_TIME             PASSWORD 60                             NO
ORA_STIG_PROFILE  PASSWORD_REUSE_TIME            PASSWORD 365                            NO
ORA_STIG_PROFILE  PASSWORD_REUSE_MAX             PASSWORD 10                             NO
ORA_STIG_PROFILE  PASSWORD_VERIFY_FUNCTION       PASSWORD ORA12C_STRONG_VERIFY_FUNCTION  NO
ORA_STIG_PROFILE  PASSWORD_LOCK_TIME             PASSWORD UNLIMITED                      NO
ORA_STIG_PROFILE  PASSWORD_GRACE_TIME            PASSWORD 5                              NO

STIG란 Security Technical Implementation Guidelines 의 약자이다.

11g에선 Tablespace PITR만 가능했는데 12c에선 Table단위도 가능하게 되었다.

이를 간단히 테스트해본다.

1. 백업

2. 현재 시간 or scn 확인

3. scott에서 emp, bonus truncate

4. rman 에서 테이블 recover 실행

※동일 명의 테이블이 있으면 에러나기 때문에 remap 해야함.

5. 확인 및 insert