Audit Vault/Firewall :: How Max Storage Can Audit Vault Server Support
Aug 18, 2013
AVDF current version 12.1 not support External/SAN storage. my question is, if customer get a huge number of Audit log and DBFW event records, then how max size can Audi Vault server support for online data (not archive data)? and can I use a Hardware server with multiple HDDs for AV Server?
Oracle Audit Vault 10.2.3.2 & Linux Red Hat 5 on a V.M. box
I'm new to the Audit Vault and am experiencing some issues. Right now my biggest question is how does A.V. deal with TNS issues (allowing the collectors to find remote target db's) when we do not add any tns entries to the local tnsnames file?
We've recently added more space and our sysadmins have moved all of our AV data to the new disk space and have supposedly updated pointers allowing us to continue seamlessly. However now my collectors won't start, they are complaining with message below. These had started previously before the space add.
============================================================== Dec 13, 2010 11:44:35 AM Thread-10 FINEST: resp.getData:<?xml version='1.0' encoding='UTF-8'?> <auditException errKey="av.auditservice.DAO_INITIALIZATION_FAILED.9" ><nestedException message="ORA-12154: TNS:could not resolve the connect identifier specified " exceptionClass="java.sql.SQLException"/></auditException> ===============================================================
Does installation of oracle label security and data vault causes overhead? How about after installation and then register with database. How much overhead?
Note that I don't want to use it for now. Then what are the reasons for registering with oracle database?
How can i implement audit logs in oracle XE ? Is there any way to enable the audit logs in Oracle XE? I also want to view the audit log, so is there any tool to view those ?
SQL> alter system set audit_trail=OS SCOPE=SPFILE;
System altered.
SQL> STARTUP FORCE ORACLE instance started.
Total System Global Area 171966464 bytes Fixed Size 2019320 bytes Variable Size 113246216 bytes Database Buffers 50331648 bytes Redo Buffers 6369280 bytes Database mounted. Database opened.
SQL> show parameter audit
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string OS SQL>
SQL> create user apexos identified by abc1;
User created.
SQL> grant connect, resource to apexos;
Grant succeeded.
SQL> audit select table, insert table by apexos by access;
Audit succeeded.
SQL> audit table by apexos by access;
SQL> SELECT audit_option, failure, success, user_name FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME ---------------------------------------- ---------- ---------- ------------------------------ TABLE BY ACCESS BY ACCESS APEXOS SELECT TABLE BY ACCESS BY ACCESS APEXOS INSERT TABLE BY ACCESS BY ACCESS APEXOS
cONN APPOS/ABC1
SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));
Table created.
SQL> insert into tab1 values (10, 'Michel');
1 row created.
SQL> insert into tab1 values (30, 'Andrew');
1 row created.
SQL> select * from tab1;
ID NAME ---------- -------------------- 10 Michel 30 Andrew
SQL> /
ID NAME ---------- -------------------- 10 Michel 30 Andrew
SQL>
SQL> select username, timestamp, action_name, action, SES_ACTIONs, sql_text 2 from USER_audit_trail where username='APEXOS';
no rows selected
SQL>
I also did not find any file contiaing the above statement as audit record in /u01/app/oracle/admin/orcl/adump.
There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location.
I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.
I am giving you all the statement I issued...
SQL> create user saimon identified by abc1;
User created.
SQL> grant connect, resource to saimon;
Grant succeeded.
SQL> audit table, insert table by saimon by session;
Audit succeeded.
SQL> show parameter audit
NAME TYPE VALUE -------------------- ----------- ------------- audit_file_dest string /u01/app/oracle/admin/orcl/adum audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DBSQL>
[oracle@DBTEST ~]$ sqlplus saimon/abc1
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create table TB1 (id number, name varchar2(20));
Table created.
SQL> create table TB3 (id number, name varchar2(20));
Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?
SQL> show user USER is "SYS"
SQL> SELECT audit_option, failure, success, user_name 2 FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME ----------------------------------- ---------- ---------- ------------------------------ TABLE BY SESSION BY SESSION SAIMON INSERT TABLE BY SESSION BY SESSION SAIMON
We can audit a particular table alone, I would like to audit one table, to find all different kinds of queries fired (Including select, insert and update) over a period of 2 months.
I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.
1. Created the audit table with similar number of records. 2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.
I am unable to figure out the proper way to implement the point 2 above in oracle database.
I facing the below issue while enable the audit on table.
SQL> audit delete on test; audit delete on test * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE") ORA-06512: at line 2
=== Related Information === SQL> select version from v$instance;
VERSION ----------------- 11.2.0.2.0
SQL> show parameter audit;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /orasw/product/11.2.0 /rdbms/audit audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
SQL> SELECT TABLESPACE_NAME,owner from dba_tables where table_name='AUD$';
We got an apps that queries an EMP table , using select * from EMP where fname like '%SCOTT%' and Lname like '%TIGER%';
How do I list all the hits being encountered by the query within the day? Is it available in the flashback query or archived logs or anywhere in the database?
We need to audit what data has changed from Vendor_B table when compared to Vendor_A table.
There is two tables:
a) VENDOR_A b) VENDOR_B
VENDOR_A and VENDOR_B table does not share the same structure but have 1 column in common - the ID and may or may not having changed Coordinates.
We need to audit what coordinates has changed from Vendor_B data when compared to Vendor A and get the report back in the format of:
<pre> VENDOR_A VENDOR_B ID Change_date LONGITUDE LATITUDE LONGITUDE LATITUDE 873 1/02/2013 -33.46711315 151.3195056 -33.46211316 151.3245057 694 3/02/2013 -33.46721315 151.3198056 -33.46214312 151.3265053
</pre> --------------------------------------------------------------------------------------------------------------------------------------------------- --Found a sample query from Ask Tom but I am not sure how I can apply them to get the structure above. --------------------------------------------------------------------------------------------------------------------------------------------------- <pre> create table base_tab (pk number, column1 number, column2 varchar2(30), column3 date); Create table refresh_tab (pk number, column1 number, column2 varchar2(30), column3 date); Create table diff (pk number, column1 number, column2 varchar2(30), column3 date, base_tab number, refresh_tab number, action varchar2(30));
[Code] .....
--clean out the differences table truncate table diff;
-------------------------------- -- build the difference data ------------------------------------ insert into diff select pk,column1,column2, column3, count(src1) base_tab, count(src2) refresh_tab, null action from ( select a.*,
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.
DROP TABLE emp; CREATE TABLE emp AS (SELECT * FROM scott.emp); CREATE TABLE audit_table
I am trying to setup logon/logoff auditing for our databases which reside in 9i and 10G on sun solaris servers. I am asked to turn on auditing sending the audit data to syslog! How exactly do you do that?
Let us say I want to audit data updates, deletes on existing table EMP_TAB that has a few hundred thousands of records.I created a shadow table Emp_tab_audit and added few audit columns
Emp_tab ( Empno NUMBER NOT NULL, Ename VARCHAR2(10), Job VARCHAR2(9),
[code]...
I am mostly interested in UPDATES and DELETES but I decided to add INSERTS to have full history for each eomplyee in one table (audit schema) instead of querying two tables all the time (production table and audit table) to see the changes.
I created this AFTER INSERT, UPDATE, DELETE trigger.decided to copy the :NEW values for INSERT and UPDATE and :OLD values for DELETE. attached.
so when insert happens, the first audit row is created in EMP_TAB_AUDIT. update happens, the 2nd new row is created in EMP_TAB_AUDIT.
The problem I am facing is the old records that curently exist. If someone updates an old row I am copying the :NEW values so I won't have a copy of the :OLD values unless I create 2 ROWS (one for the old and one for the new).
Do you think I should copy all the hundreds of thousands of records to the AUDIT tables for this to work.
******************************************************************* CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT AFTER INSERT OR DELETE OR UPDATE ON EMP_TAB FOR EACH ROW DECLARE v_operation VARCHAR2(10) := NULL;
getting logon timestamp for our auditing process. In some website, it says to get the logon timestamp I have to select it from timestamp column of dba_audit_session but when I do this, some results has a logoff earlier than logon. Is timestamp column really the logon timestamp?
I have a requirement to develop audit trails for non-updatable view.In order to do this I created an audit trail table and row level triggers on view's underlying tables.When I update any column value from front-end oracle form then both triggers (on both tables) fire and log audit information for all columns(for both tables) into generic audit table. so far it's good but there is small difference in time interval between the triggers firing and seeing time stamp difference for both tables audit data.
sample audit data:
changed_by changed_on changed_type table_name column_name old_val new_val myself 10/23/2012 10:15:*48* AM U TABLE1 COLUMN1 X Y myself 10/23/2012 10:15:*48* AM U TABLE1 COLUMN2 C D myself 10/23/2012 10:15:*49* AM U TABLE2 COLUMN5 A B myself 10/23/2012 10:15:*49* AM U TABLE2 COLUMN6 F G
My requirement is to see all columns data from both tables in audit table with same time stamp and we will query the audit table using time stamp to show up old and new values of the non-updatable view on particular time.How can I make two triggers log the same time stamp on audit table ?.
I want to audit dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it.
I have an issue when I enable auditing feature in oracle,I use 'DB' to store the audit from user in oracle, my command is
SQL>alter system set audit_trail=db scope=spfile; SQL>shutdown immediate; SQL>startup;
in show parameter audit, I see that the audit_trail already changed to DB after I create a user and enable audit for this user, I can see in the audit table dba_audit_trail for insert, update, delete,etc but If I am not wrong, if I using DB to setting the audit parameter then I will get audit file both in oracle table and in adump oracle folder installation, but in my case I cannot find any file in adump directory.
my operating system is windows7 and I use oracle database 10g express edition