Querying Oracle for Fine Grained Auditing settings

An earlier post about Fine Grained Auditing shows how to enable FGA in an Oracle 11gR2 database - http://notastrophe.blogspot.com/2013/01/oracle-11gr2-fine-grained-auditing-fga.html . But how do you tell what FGA policies are applied. Typically to query audit settings you query DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS, and these are shown in OEM's audit page and can be queried from there, but FGA requires a different query -

SELECT PNAME, PCOL,  FROM SYS.FGA$;

Gives the policy name and affected columns.

SELECT DBUID, CLIENTID, POLICYNAME FROM SYS.FGA_LOG$;

Gives audit trail of user, client id and audited policy. Can also get query details and object schema and name

Query against the regular audit log can be done against DBA_AUDIT_TRAIL and SYS.AUD$

Specifying comments in SQL to support auditing in Oracle

Adding comments to an SQL statement in Oracle is a good way of providing context to auditing and sql forensic activities directed against an Oracle database audit log. The Oracle Audit trail, and related software like Oracle Audit Vault will capture any comments embedded within an sql statement as part of its normal activities. This, then, provides a way for applications built on Oracle to provide metadata that can assist in analytics performed on the audit record.

As an example, an online shopping cart may audit searches by keyword and may audit individual product pages. At the database level the sql run to generate such pages can be marked with a comment and a textual description of the functionality of the application that uses such a query. This permits application level auditing to be performed in the database, and not require that the auditor is educated in the full set of statements that may be performed by the application. This method of auditing can be done in addition to application level auditing functionality.

Comments are also useful in the enterprise manager to look up statements that are candidates for automated sql tuning.

To add comments to the statement use the following syntax -

SELECT /* [text] */ .....

When the statements require the use of hints ensure the comments do not get in the way of the correct hint definition. Ensure the comments are of the form


{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]]... */

Oracle 11gR2 Fine Grained Auditing (FGA) Policy

Example of adding a FGA policy to Oracle 11gR2 that will be collected by Audit Vault. This will create a policy on the SCHEMA.SCHEMA_TBL table called "QRY_TBL" for any select statement on COL1. The audited event will be written to the extended DB audit trail. This will provide the full sql command run, the column that triggered the event, various session data and will include any bind variable values used in the query.

Multiple columns and statement types can be provided in one statement. It is usual to have one policy statement to cover all columns that need to be audited on a table. The audit trail will show details of which columns caused the event to be audited.

BEGIN

  DBMS_FGA.ADD_POLICY (object_schema=>'"SCHEMA"',      object_name=>'"SCHEMA_TBL"',                policy_name=>'QRY_TBL’,

                audit_condition=>NULL,

                statement_types=>'SELECT', audit_column=>'COL1',

                audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

                audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED   );

END;

Oracle Audit Vault AV Auditor Data Warehouse Tables

The following are the useful tables in Oracle Audit Vault Data Warehouse


avsys.audit_event_fact;

This is the centre of the star schema, containing the keys to other tables, and time and data information.

avsys.audit_option_tab;
avsys.authentication_method_tab;
avsys.client_host_dim;

This is the full list of client hosts

avsys.client_id_tab;

This olds all the client ids

avsys.client_tool_dim;
avsys.comment_text_tab;
avsys.context_dim;
avsys.event_dim;
avsys.event_status_tab;
avsys.fga_policyname_tab; -- contains fine grained auditing info
avsys.privileges_dim;
avsys.session_actions_tab;
avsys.source_dim;
avsys.target_dim;
avsys.time_dim;
avsys.transaction_name_t;
avsys.user_dim;

AUDIT CONNECT creates CREATE SESSION audited privilege

The query to find out if auditing of succesful or unsuccessful logins is enabled and to find out what login activity has occured on an Oracle database is below. It queries against the dba_priv_audit_opts table for use of the "CREATE SESSION" privilege.

To audit successful and failed logins run
AUDIT CONNECT BY ACCESS

To find if this is already enabled query
SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE PRIVILEGE = 'CREATE SESSION'

USER_NAME        PROXY_NAME        PRIVILEGE     SUCCESS        FAILURE
        |          | CREATE SESSION        |    BY ACCESS        | BY ACCESS

Oracle 11g Auditing Failed Statements

To find out what queries cause ORA-00942 - table or view does not exist errors you can use the auditing features of Oracle. You can audit successful as well as unsuccessful attempt to run particular queries against the Oracle database.

audit select table by access whenever not successful; -- also insert table, delete table, update table

To read the audit trail -

select * from dba_audit_trail

To make sure auditing is on do -
SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>
Auditing is disabled by default,
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

as SYSDBA
alter system set audit_trail = 'db_extended' scope=pfile; -- or none, os, db, xml, xml_extended

db_extended audits sql_text and bind values;

SHUTDOWN
STARTUP


Otherwise do this from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414

create table t( id number, seq number, msg varchar2(4000) ); create sequence s; create or replace trigger failed_to_extend_temp after servererror on database declare l_sql_text ora_name_list_t; l_n number; begin if ( is_servererror(942) ) then insert into t values ( s.nextval, 1, 'ora_sysevent = ' || ora_sysevent ); insert into t values ( s.currval, 2, 'ora_login_user = ' || ora_login_user ); insert into t values ( s.currval, 3, 'ora_server_error = ' || ora_server_error(1) ); l_n := ora_sql_txt( l_sql_text ); for i in 1 .. l_n loop insert into t values ( s.currval, 3+i, 'l_sql_text(' || i || ') = ' || l_sql_text(i) ); end loop; end if; end;

Audit unsuccessful login in Oracle 11gR2

Oracle can audit access by object, statement and by privilege as well as via the fine grained auditing capability. This is the command to enable auditing of unsuccessful login which might indicate an attack on the database.


audit session whenever not successful;