Posts Tagged security

Less Performance Impact with Unified Auditing in #Oracle 12c

There is a new auditing architecture in place with Oracle Database 12c, called Unified Auditing. Why would you want to use it? Because it has significantly less performance impact than the old approach. We buffer now audit records in the SGA and write them asynchronously to disk, that’s the trick.

Other benefits of the new approach are that we have now one centralized way (and one syntax also) to deal with all the various auditing features that have been introduced over time, like Fine Grained Auditing etc. But the key improvement in my opinion is the reduced performance impact, because that was often hurting customers in the past. Let’s see it in action! First, I will record a baseline without any auditing:

 

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 31 08:54:32 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select value from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL> @audit_baseline
Connected.

Table truncated.


Noaudit succeeded.


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.07
Connected.

PL/SQL procedure successfully completed.
SQL> host cat audit_baseline.sql
connect / as sysdba
truncate table aud$;
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

So that is just 100k SELECT against a 600M MB table with an index on ID without auditing so far. Key sections of the AWR report for the baseline:

unified_auditing1unified_auditing2

The most resource consuming SQL in that period was the AWR snapshot itself. Now let’s see how the old way to audit impacts performance here:

SQL>  show parameter audit_trail

NAME_COL_PLUS_SHOW_PARAM                 TYPE        VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------- ----------- ----------------------------------------
audit_trail                              string      DB, EXTENDED
SQL> @oldaudit
Connected.

Table truncated.


Audit succeeded.


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.

Elapsed: 00:00:56.42
Connected.

PL/SQL procedure successfully completed.
SQL> host cat oldaudit.sql
connect / as sysdba
truncate table aud$;
audit select on adam.sales by access;
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

That was almost 10 times slower! The AWR report confirms that and shows why it is so much slower now:

unified_auditing3unified_auditing4

It’s because of the 100k inserts into the audit trail, done synchronously to the SELECTs. The audit trail is showing them here:

 

SQL> select sql_text,sql_bind from dba_audit_trail where rownum<=10; 
SQL_TEXT                                           SQL_BIND 
-------------------------------------------------- ---------- 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):1 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):2 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):3 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):4 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):5 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):6 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):7 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):8 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(1):9 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1         #1(2):10 
10 rows selected. 
SQL> select count(*) from dba_audit_trail where sql_text like '%SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1%';

  COUNT(*)
----------
    100000

Now I will turn on Unified Auditing – that requires a relinking of the software while the database is down. Afterwards:

SQL> select value from v$option where parameter='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> @newaudit
Connected.

Audit policy created.


Audit succeeded.


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.90
Connected.

PL/SQL procedure successfully completed.
SQL> host cat newaudit.sql
connect / as sysdba
create audit policy audsales actions select on adam.sales;
audit policy audsales;
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

That was still slower than the baseline, but much better than with the old method! Let’s see the AWR report for the last run:

unified_auditing5

unified_auditing6

Similar to the first (baseline) run, the snapshot is the most resource consuming SQL during the period. DB time as well as elapsed time are shorter by far than with the old audit architecture. The 100k SELECTs together with the bind variables have been captured here as well:

SQL> select sql_text,sql_binds from unified_audit_trail where rownum<=10; 
SQL_TEXT                                                     SQL_BINDS 
------------------------------------------------------------ ---------- 
ALTER DATABASE OPEN 
create audit policy audsales actions select on adam.sales 
audit policy audsales 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):1 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):2 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):3 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):4 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):5 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):6 
SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1                   #1(1):7 
10 rows selected. 
SQL> select count(*) from unified_audit_trail where sql_text like '%SELECT PRODUCT FROM ADAM.SALES WHERE ID=:B1%';

  COUNT(*)
----------
    100000

The first three lines above show that sys operations are also recorded in the same (Unified!) Audit Trail, by the way. There is much more to say and to learn about Unified Auditing of course, but this may give you a kind of motivation to evaluate it, especially if you have had performance issues in the past related to auditing. As always: Don’t believe it, test it! :-)

, ,

Leave a comment

AUDIT_SYS_OPERATIONS defaults to TRUE in #Oracle 12c

A small but remarkable change in Oracle Database 12c is the default value of AUDIT_SYS_OPERATIONS has changed to TRUE now. In other words, all actions done by the superuser sys are being audited now by default!

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 15:23:10 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,value from v$spparameter where isspecified='TRUE';

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
memory_target                            1073741824
control_files                            /u01/app/oracle/oradata/prima/control01.ctl
db_block_size                            8192
compatible                               12.1.0.2
db_recovery_file_dest                    /u02/fra
db_recovery_file_dest_size               2147483648
undo_management                          auto
undo_tablespace                          undotbs1
remote_login_passwordfile                exclusive
db_name                                  prima
diagnostic_dest                          /u01/app/oracle

11 rows selected.


SQL> show parameter sys_oper

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

SQL> show parameter audit_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/rdbms/audit
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit

[oracle@uhesse audit]$ cat prima_ora_6204_20150724152310753136143795.aud
Audit file /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/prima_ora_6204_20150724152310753136143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      uhesse
Release:        3.8.13-68.2.2.el7uek.x86_64
Version:        #2 SMP Tue May 12 14:38:58 PDT 2015
Machine:        x86_64
Instance name: prima
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 6204, image: oracle@uhesse (TNS V1-V3)

Fri Jul 24 15:23:10 2015 +02:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'
[Output shortened...]
Fri Jul 24 15:23:56 2015 +02:00
LENGTH : '185'
ACTION :[31] 'select count(*) from scott.dept'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'

Something you might need to know as a DBA, don’t you think? :-)

,

2 Comments

Flashback Data Archive in #Oracle 12c

This article highlights some 12c changes of Flashback Data Archive (FDA). See here for an introduction. Remarkably, the feature is now included in all editions. I will focus here on the 12c enhancement that an application can be registered to FDA. That has two major benefits:

1. FDA can be turned on and off for all tables of that application with one command

2. Protection (better than READ ONLY) can be turned on and off for all tables of that application with one command

The playing field:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.
SQL> create tablespace flatbs datafile '/home/oracle/prima/flatbs01.dbf' size 50m;

Tablespace created.

SQL> create table t1 as select * from dual;

Table created.

SQL> create table t2 as select * from dual;

Table created.

SQL> create flashback archive fla_10y tablespace flatbs retention 10 year;

Flashback archive created.

So far no new feature as the same can be done with 11g. No I register the application – that is just declarative:

SQL> begin
     dbms_flashback_archive.register_application
     (application_name=>'MYAPP',
     flashback_archive_name=>'FLA_10Y');
     end;
     /  

PL/SQL procedure successfully completed.

Now I add all tables of that application:

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t1',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t2',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

I can now turn FDA on and off for all the tables with one command:

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

SQL> exec dbms_flashback_archive.enable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS
---------- ----------
T1         ENABLED
T2         ENABLED

SQL> exec dbms_flashback_archive.disable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Furthermore, I can now protect these tables with one command against modification and drop:

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> delete from t1;
delete from t1
            *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

This is a much better protection than just making tables READ ONLY:

SQL> create table t3 as select * from dual;

Table created.

SQL> alter table t3 read only;

Table altered.

SQL> drop table t3;

Table dropped.

Please notice also that the tables need not to be in FDA mode to be protected by the lock_down_application call. The doc by the way doesn’t mention the unlock procedure:

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

Conclusion: The 12c enhancements of Flashback Data Archive enable the fast change of the FDA mode for multiple tables. Furthermore, it is now easily possible to prevent tables from being modified and dropped.

, , ,

7 Comments

Follow

Get every new post delivered to your Inbox.

Join 3,467 other followers

%d bloggers like this: