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 Audit Vault General Commands

Here are useful commands when using Oracle Audit Vault. Commands are listed for the collector to show the status and to start and stop the agent and there are commands for the server side to add and agent, start and stop the agent and also start and stop the collectors.

On a test system where collectors are created and removed often, sometimes it is necessary to redeploy using the commands listed.

On the Collector:
avctl show_agent_status
avctl start_agent
avctl stop_agent


On the Server:
avca add_agent -agentname avagent1 -agenthost <host>

avctl start_agent - starts the web server running on <host>:1158/av
avctl stop_agent - stops the web server running on <host>:1158/av
avctl show_av_status - shows if the web server at <host>:1158/av is running

avctl show_collector_status -collname DBAUD_Collector -srcname db1001
avctl start_collector -collname DBAUD_Collector -srcname db1001

avctl stop_collector -collname DBAUD_Collector -srcname db1001

If the avctl or avca commands return internal errors it may be necessary to redeploy the av using the deploy_av command.

Shrinking UNDO Tablespace in Oracle 11gR2

To shrink UNDO tablespace in Oracle first create the new undo tablespace, then alter the database to use the new undo tablespace. This will require enough available disk space to create the new tablspace. Then drop the old tablespace to free up the disk. Commands are provided below

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u02/datafile/undotbs02.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 50M;

Tablespace created.



ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;