Adatbázis audit. Fehér Lajos Október 11

Adatbázis audit Fehér Lajos

2016. Október 11.

30 percben Mindennapi Biztonság Standard auditing (11g/12c) 12c Unified Auditing Performancia Hogyan tovább?

HOUG Szakmai Nap 2016

Adatbázis Audit

A Bizalom alapja az Ellenőrzés .... Mi az adatbázis audit célja? § Kiválasztott adatbázis események és szereplők tevékenységének követése § Megfelelőségi kényszer (compliance) § Hozzáférés szabályzás visszamérése § Gyanús tevékenységek kiszűrése, követése § Illetéktelen hozzáférések kiszűrése, követése HOUG Szakmai Nap 2016

Adatbázis Audit

11g – Standard Auditing [oracle@hougdemo1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 11 03:22:26 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- -----------------------------audit_file_dest string /opt/oracle/admin/DB2/adump audit_sys_operation boolean FALSE audit_syslog_level string audit_trail string DB SQL>

mandatory auditing • • • •

sysdba logon, startup / shutdown, DML AUD$, DML FGA_LOG$

SQL • audit • noaudit • dba_audit_trail view


Adatbázis Audit

11g – Standard Auditing Gyári beállítás ALTER ANY PROCEDURE ALTER ANY TABLE ALTER DATABASE ALTER PROFILE AUDIT ROLE BY ACCESS ALTER SYSTEM ALTER USER CREATE PUBLIC DATABASE LINK CREATE SESSION CREATE USER DROP ANY PROCEDURE DROP ANY TABLE

AUDIT SYSTEM AUDIT SYSTEM BY ACCESS CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY PROCEDURE CREATE ANY TABLE CREATE EXTERNAL JOB DROP PROFILE DROP USER EXEMPT ACCESS POLICY GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE


Adatbázis Audit

11g – Standard Auditing

SYSDBA hozzáférés követése audit_sys_operations audit_file_dest

= TRUE = /opt/oracle/admin/DB2/adump

OS audit trail AUDIT_SYSLOG_LEVEL /etc/syslog.conf:

= 'LOCAL1.WARNING’ LOCAL1.WARNING /var/log/oracleaudit.log


Adatbázis Audit

12c – Unified Audit Unified Audit • • • • •

Egyetlen közös helyre írja az audit bejegyzéseket AUDSYS séma bevezetése (SYSAUX táblatér) Szerepkörök (audit_admin és audit_viewer) Mixed mód (kompatibilitás) Performancia (queue vagy direkt írás)


Adatbázis Audit

12c – Unified Audit – Mixed mód 11g upgrade / default § Standard + Unified audit § Minden 11g audit megoldás használható § Unified audit megoldás használható

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; • FALSE


Adatbázis Audit

12c – Unified Audit – Mixed mód [oracle@hougdemo1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 19:37:12 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand Real Application Testing options SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- -----------------------------audit_file_dest string /oracle/admin/DB12C/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 SQL>

mandatory auditing • • • •

sysdba logon, startup / shutdown, DML AUD$, DML FGA_LOG$

SQL • audit • noaudit • unified_audit_trail view


Adatbázis Audit

12c – Unified Audit Unified Audit natív mód § Nem érvényesek a 11g paraméterek § Csak a policy alapú audit események élnek § a standard audit során keletkezett rekordok nem vesznek el § Extended Audit Information (EIA) – RMAN, DP, RAS, OLS, DV, FGA cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; • TRUE


Adatbázis Audit

12c – Unified Audit Gyári beállítások § $ORACLE_HOME/audit/ könyvtár § audit_admin és audit_viewer szerepkörök SQL> select distinct policy_name from audit_unified_policies; ORA_ACCOUNT_MGMT ORA_CIS_RECOMMENDATIONS ORA_DATABASE_PARAMETER ORA_DV_AUDPOL ORA_LOGON_FAILURES ORA_RAS_POLICY_MGMT ORA_RAS_SESSION_MGMT ORA_SECURECONFIG


Adatbázis Audit

12c – Unified Audit Gyári beállítások #2 SQL> select audit_option from audit_unified_policies where policy_name = 'ORA_SECURECONFIG'; EXEMPT REDACTION POLICY PURGE DBA_RECYCLEBIN ADMINISTER KEY MANAGEMENT DROP ANY SQL TRANSLATION PROFILE ALTER ANY SQL TRANSLATION PROFILE CREATE ANY SQL TRANSLATION PROFILE CREATE SQL TRANSLATION PROFILE CREATE EXTERNAL JOB CREATE ANY JOB GRANT ANY OBJECT PRIVILEGE EXEMPT ACCESS POLICY CREATE ANY LIBRARY GRANT ANY PRIVILEGE DROP ANY PROCEDURE ALTER ANY PROCEDURE ........


Adatbázis Audit

12c – Unified Audit Audit példák


Adatbázis Audit

12c – Unified Audit Audit példák CREATE AUDIT POLICY dml_pol ACTIONS DELETE on hr.employees, INSERT on hr.employees, UPDATE on hr.employees, ALL on hr.departments; CREATE AUDIT POLICY dp_actions_pol ACTIONS COMPONENT = datapump IMPORT; CREATE AUDIT POLICY order_updates_pol ACTIONS UPDATE ON oe.orders WHEN 'SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') = ''EXTERNAL''' EVALUATE PER SESSION;


Adatbázis Audit

12c – Unified Audit Immediate-write mode BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE); END; /

Queued-write mode --- DEFAULT BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE); END; /


Adatbázis Audit

12c – Unified Audit Queued-write mode --- DEFAULT EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

UNIFIED_AUDIT_SGA_QUEUE_SIZE --- példány paraméter

SQL> select ksppinm, ksppdesc

from x$ksppi where ksppinm like '%aud%';

audit_sys_operations unified_audit_sga_queue_size

enable sys auditing Size of Unified audit SGA Queueaudit_file_dest Directory in which auditing files are to reside audit_syslog_level Syslog facility and level audit_trail enable system auditing _transaction_auditing transaction auditing records generated in the redo log _unified_audit_flush_threshold Unified Audit SGA Queue Flush Threshold _unified_audit_flush_interval Unified Audit SGA Queue Flush Interval _unified_audit_policy_disabled Disable Default Unified Audit Policies on DB Create


Adatbázis Audit

12c – Unified Audit / Standard Audit Performancia connect / as sysdba truncate table aud$; audit / noaudit select on adam.sales; exec dbms_workload_repository.create_snapshot connect adam/adam set timing on declare v_product adam.sales.product%type; Begin for i in 1..100000 loop select product into v_product from adam.sales where id=i;end loop; end; / set timing off connect / as sysdba exec dbms_workload_repository.create_snapshot

NOAUDIT Elapsed: 00:00:06.07

Standard Audit Elapsed: 00:00:56.42

Unified Audit Elapsed: 00:00:11.90

Forrás: https://uhesse.com/2015/07/31/less-performance-impact-with-unified-auditing-in-oracle-12c/

12c – Unified Audit Performancia Immediate / Queued-write mode

To simulate a real-world scenario, a standard workload was created to use 50% of system resource Throughput: timeFor used by the after auditing on before auditingAdditional was initiated. each test transaction run, the following resultswas wereturned recorded: • Additional CPU Usage: Measured additional CPU after auditing was turned on • Throughput: Additional time used by the transaction after auditing was turned on •

11g/12c – Standard Audit Performancia

For standard database auditing, a test was created to generate approximately 250 audit records per • Additional CPU Usage: Measured additional CPU after auditing was turned on second using the Oracle database standard audit command. For standard database auditing, a test was created to generate approximately 250 audit records per second using the Oracle database standard audit command. Audit Trail Setting Audit Trail OS Setting XML OS XML, Extended XML DB XML, Extended DB, Extended DB

• ~250 audit rekord / mp • “gyári beállítások” mellett

Additional Throughput Additional CPU Time Usage Additional Throughput Additional CPU 1.39% 1.75% Time Usage 1.70% 3.51% 1.39% 1.75% 3.70% 5.26%Database Auditing: Performance Guidelines Oracle White Paper—Oracle 1.70% 3.51% 4.57% 8.77% 3.70% 5.26% 14.09% 15.79% 4.57% 8.77%

DB, Extended 14.09% 15.79% Table 3 – Oracle Database 11.2.01 Standard Audit Trail with 50% CPU System Load Audit Trail Additional Throughput Additional CPU Table 3 – Oracle Database 11.2.01 Standard Audit Trail with 50% CPU System Load Setting Time Usage For FGA, a test was created to generate approximately 200 audit records per second using the XML 3.66% 4.35% DBMS_FGA package. The condition of the audit policy creates an audit record when an UPDATE or For FGA, a teston was created to 4.62% generate approximately 200 audit records perequal second using the XML, Extended 9.09% SELECT occurs the TPCC.ORDL table and the client_identifier value is to NULL. DBMS_FGA package. The condition of the audit policy creates an audit record when an UPDATE or DB 6.60% 11.11% SELECT occurs on the TPCC.ORDL table and the client_identifier value is equal to NULL. dbms_fga.add_policy DB, Extended 9.61% ( 20%

• FGA audit • ~200 audit rekord / mp

object_schema => 'TPCC', object_name => 'ORDL', dbms_fga.add_policy ( policy_name => 'Config_A', object_schema => 'TPCC', audit_condition => Table 4 – Oracle Database 11.2.01 Fine Grained Audit Trail with 50% CPU System Load object_name => 'ORDL', 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'') IS NULL', policy_name => 'Config_A', statement_types => 'UPDATE, SELECT', audit_condition => audit_trail => DBMS_FGA.XML +DBMS_FGA.EXTENDED); NULL', Based on the'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'') results, it shows that writing audit records to OS files, whether that be IS character based or statement_types => 'UPDATE, SELECT', XML based, has the least impact to system resources. As part of the test, 200+ audit records per audit_trail => DBMS_FGA.XML +DBMS_FGA.EXTENDED); second were generated. In general, this is a higher number of audit records than most environments generate. When writing less than 200 audit record per second, you will use less system resources. Forrás: http://www.oracle.com/technetwork/products/audit-vault/learnmore/twp-security-auditperformance-166655.pdf Results can vary from system to system and should be tested in your environment.

Hasznos oldalak §Database Security Guide http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm#DBSEG1023

§ Master Note For Oracle Database Auditing (Doc ID 1299033.1) § Oracle Database Auditing Performance (Doc ID 1509723.1) § Note 1375419.1 - Obsoleted "BY SESSION" Audit Option Leads to Massive Audit Output (Huge AUD$) § Note 1171314.1 - Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database § Note 73408.1 - How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$ § Note 731908.1 - New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information § Note 1269970.1 SCRIPT: How To Implement The Equivalent Of BY SESSION Auditing Using FGA on Objects?


Adatbázis Audit

Köszönöm a figyelmet

Fehér Lajos | [email protected] Omnit Solutions Kft. 1134 Budapest, Rózsafa utca 13-17. www.omnit.hu


Adatbázis Audit

Adatbázis audit. Fehér Lajos Október 11

Recommend Documents