The Oracle RDBMS provides various control functions of the system security. Among these, there is also a function of AUDIT TRAIL that lets you record any activity carried out on the database of interest.
The control possibilities are very sophisticated and flexible, but it is necessary to avoid excessive control to avoid needlessly heavy and impossible access checks.
And ‘possible to activate controls aimed at:
• specific SQL statement (eg. connect, create table, …),
• specific privileges (eg. grant system, …),
• operations (select, insert, alter, execute, …) of objects RDBMS.
Can be controlled actions that returned errors and / or those performed successfully.
Result of auditing
Any action under control, when executed, generates one or more records in the table in the data dictionary SYS.AUD $. By querying this table, either directly or through special views, you get a report with different information about the actions performed under control in the RDBMS, in particular:
• an action,
• used the privilege to carry it out,
• the user who has accomplished it,
• the object on which it was accomplished,
• The day and time in which it was accomplished.
ACTIVATION OF THE AUDIT ON ORAMIX
The monitoring was activated by running the following command:
SQL> alter system set audit_trail = db_extended scope = spfile;
which was followed by restarting the instance. He was subsequently created a script for enabling ad hoc inspections required which has been run as SYS:
AUDIT INSERT, UPDATE, DELETE ON USER1.TAB_INFO by session;
AUDIT INSERT, UPDATE, DELETE ON USER1.TAB_LOG by session;
AUDIT INSERT, UPDATE, DELETE ON USER1.TAB_DATA by session;
AUDIT INSERT, UPDATE, DELETE ON USER2.TAB_DOC by session;
AUDIT INSERT, UPDATE, DELETE ON USER2.TAB_ANAG by session;
AUDIT INSERT, UPDATE, DELETE ON USER3.ECONOMY by session;
AUDIT INSERT, UPDATE, DELETE ON USER3.SELL by session;
As you can see almost all tables in the database have been under audit statement for INSERT, UPDATE, and DELETE statements, except for the objects of the schemes SYS, SYSTEM, SYSMAN, XDB, DBSNMP, OUTLN, perfstat, WMSYS, SCOTT, ORDDATA, OLAPSYS, MDSYS, EXFSYS, CTXSYS, APEX_030200, ADMIN_AREA, APPQOSSYS, FLOWS_FILES, ORDSYS, OWBSYS and tables TAB_TEMP, TAB_CI schema USER1 ..
Tests were performed unsuccessful.
CONTROL DATA RECORDED
For the verification of the recorded data you need to run the following statement:
alter session set NLS_DATE_FORMAT = ‘dd-mm-yyyy hh24: mi: ss’;
set lines 220 pages 1000
with the USERNAME for a10
with USERHOST for a30
with ACTION_NAME for a15
with SQL_TEXT for a75
with TERMINAL for a20
select username, userhost, terminal, OS_PROCESS, TIMESTAMP, ACTION_NAME, SQL_TEXT from dba_audit_trail;
Council to run it from sql developer as the output report is easier to read.
All data are stored in the SYSTEM tablespace that has been put in autoextend:
SQL> ALTER DATABASE DATAFILE ‘D: APP ORCL ORADATA oracl SYSTEM01.DBF’ AUTOEXTEND ON NEXT 50M MAXSIZE 6144M;
We estimate that it will take about 100MB per 24 hours.
CLEANING OF DATA
The table AUD $ can be cleaned at any time by logging in as SYS and running:
SQL> truncate table AUD $;
If you wish to export it before deleting the data will need to perform a manual export by running from a DOS prompt:
C: Users oracle> exp FILE = $ export_AUD _20130527.dmp LOG = $ export_AUD _20130527.log TABLE = AUD $
Username: sys as sysdba
Connected to: Oracle Database 11g Release 220.127.116.11.0 – 64bit Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
In the course of export specified tables via conventional path …
. ongoing export of the table AUD $ 35,800 rows exported
Export terminated without warnings.