ORA-28056: Writing audit records to Windows Event Log failed

Problem Description
Connecting as sysdba fails with ORA-28056.

$ sqlplus / as sysdba
ORA-28056: Writing audit records to Windows Event Log failed.

Cause of the Problem
The problem happened because Event Viewer log is full and not able to log anymore events.

Solution of the Problem
You need to free an event log.

- Log in as a administrator or a member of the Administrators group in order to free an event log.

- Open Event Viewer. To do so click on Start Menu, click Control Panel, click Performance and Maintenance, click Administrative Tools, and then double-click Event Viewer.

Or, click on Start Menu, click Control Panel, click Administrative Tools, and then double-click Event Viewer.

- Now you can follow any of the following steps.

1) When a log is full, it stops recording new events. Clearing the log is one way to free the log and start recording new events. To do so, on the Action menu (left click)or on the left side Application/System/Security (as available) right click and select Clear All events.

2) You can also free a log and start recording new events by overwriting old events. To overwrite events, on the Action menu, or on the left side Application/System/Security (as available) click Properties, and then click Overwrite events as needed. This ensures that all new events are written to the log, even when the log is full.

3) You can also start logging new events by increasing the maximum log size. To increase the log size, on the Action menu or on the left side Application/System/Security (as available) click Properties, and then increase the Maximum log size by typing a bigger value.

Audit and Profile Management Exercises in Oracle

In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.
For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance.

L A B S T E P S

STEP 1: Define a New Profile

Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.

1. To begin, log into your instance as the SYS user.
2. Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
• Limit the number of failed login attempts to 3 in a row.
• Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
• Allow a session to be idle no more than 1 hour.
• Change the password every 60 days.
• Allow the user 3 days to change the password after it expires.
• Not allow a previous password be reused before there have been three password changes.
3. Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 2: Testing the New Profile

Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.

1. The first things we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
2. Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
3. There are several things that we can test related to the logging in and changing a password so here we go.
• You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
• Repeat the above process until you get the ORA-28000: the account is lockederror which will indicate that the profile is working here.
• At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
• Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
• Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
• Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
4. Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 3: Using the PRODUCT_USER_PROFILE table

As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.

1. To begin, copy the pupbld.sql file from $ORACLE_HOME/rdbms/admin to the C drive of your local computer or place it in the local F drive of your Citrix environment. Once you have downloaded it you will need to open the file and make two edits to the login strings; one at the top and one at the bottom. The login at the top of the script is for the SYSTEM user. The password is already set to MANAGER which is correct. You need to change the reference to the database instance to match your instance name (do not add the AS SYSDBA to this connection string). The other connection string is at the bottom and you also need to change the instance name here to match yours. Once you have made these changes then save the file.
2. Now login to your database instance as the SYS user. Run the PUPBLD.SQL script from the SQL> prompt (DO NOT copy and paste the script). Remember that at the end of the script you should be connect as the SYS user. You can test this by issuing a SHOW USER command.
3. Now we need to limit SCOTT from being able to use the GRANT command.
• Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
• Now we need to test our above settings and make sure they are working.
• Connect to the SCOTT user (remember that you changed the password to LION).
• Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
4. This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 4: Setting up the Database to use Auditing

Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.

1. First you need to make sure that you are logged into your instance as the SYS user.
2. At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
3. Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
4. Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
5. Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
6. Once you have completed this process you are ready to begin setting up the database to audit some activity.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 5: Creating an Audit Trail

Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.

1. Make sure that you are connected as user SYS.
2. Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
3. Now we can set up auditing to track what goes on in the database.
• Write SQL statements to audit
successful and unsuccessful login attempts by SCOTT.
• Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
4. Now we need to test the audits to verify that they work.
• Log into the SCOTT user (remember that the password is LION) and perform the following:
• write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.
• Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').
• Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted.
• Try to reconnect to the SCOTT user with an invalid password.
• Now connect back to the SYS user.

Now we need to see if our auditing worked.

1. While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
2. Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.

Related Documents

Basic Oracle Sql Exercise

Practice Exercise for Oracle PL/SQL

Oracle Procedure and Function Practice

Exercise Oracle package and package body

Database design practice for a Movie Company

Oracle Enterprise Manager Excercises

Oracle Database Installation Exercises

Oracle Database Creation Exercises

Database Administration Exercises - Week 3

Temporary Tables and Data Types Exercises

Exercises with Oracle Create Table Add Columns

Practice with User Profiles and User Roles in Oracle

Practice Oracle Joins Examples

Oracle Object type Exercises - VARRAY

Audit and Profile Management Exercises in Oracle

Issuing of moving SYS.AUD$ to another tablespace

Oracle stores audit trail records in the SYS.AUD$ base data dictionary table which resides in SYSTEM tablespace.

Based on the auditing option this SYS.AUD$ table grows out of order inside the SYSTEM tablespace and must have records deleted from it or be truncated, otherwise it will take up all the room in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace.

So it may be one's choice to move the audit table out of the SYSTEM tablespace and then enable auditing without touching SYSTEM tablespace.

Until 11g the way to move out SYS.AUD$ is not supported. If you want to do it you have to do it manually which is discussed in How to move audit table out of SYSTEM tablespace which is not supported. 'Not supported' means Oracle Support cannot involve development if problems arise because of the triggers you put on the AUD$ table.

In 11g you can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION provided with the DBMS_MGMT package.
Related Documents
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html

How to move audit table out of SYSTEM tablespace

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Related Documents
How to Reorganize Audit trail SYS.AUD$ Table
How to truncate or delete rows from audit trail table sys.aud$
About Audit_trail Parameter

How to Reorganize Audit trail SYS.AUD$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described.
1)Enable restricted session.
In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session.

SQL> connect / as sysdba
Connected.

SQL> alter system enable restricted session;
System altered.

Check if sessions are still connected by,
SQL> select sid, serial#, username from v$session;

If necessary kill these sessions with,
SQL> alter system kill session 'sid , serial#';

2)Copy SYS.AUD$ table.
SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$;
You now can take a dump of audit_record.

3)Truncate SYS.AUD$ table.
SQL> truncate table sys.aud$;
Table truncated.

SQL> select count(*) from SYS.AUD$;
COUNT(*)
--------
0

4)Copy the rows back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;

You can also import it if you exported it in step 2.

5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;

Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;

Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/10/how-to-reorganize-audit-trail-sysaud.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html

Audit Trigger Activity in Oracle

Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.

We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.

Here is a test. Inside test schema I have made an example.

Connect as test user and create three tables.

SQL> conn test/test
Connected.

SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.

SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.

SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.

2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.

SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.

3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE

SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.

4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.

SQL> commit;
Commit complete.

5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.

SQL> conn / as sysdba
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.

SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED

6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.

SQL> insert into test values(1,'Tes','T2');
1 row created.

SQL> commit;
Commit complete.

7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected

SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2

SQL> select * from test2;
A B C
---------- --- ---
1 Tes B

8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.

SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'

SQL> select username,sql_text from dba_audit_trail;

USERNAME                             SQL_TEXT
--------------                       --------------------------
TEST                                 insert into test values(2,'Test','Test2')

Related Documents
About Audit_trail Parameter
Configure and Administer Database Auditing

About Audit_trail Parameter

The parameter AUDIT_TRAIL enables or disables auditing as well as it specifies where the auditing information will be kept and in which format the audit information will be stored.

In order to set the parameter- if you start your database with spfile then use,
ALTER SYSTEM SET AUDIT_TRAIL={ none | os | db | db,extended | xml | xml,extended } scope=spfile;

And restart your database with modified spfile.

Now we see there may be 6 values that this parameter can take. In the next it is told what these 6 parameter indicates.

• none: The value none disables database auditing. So if use use AUDIT SELECT TABLE while this parameter is null then no auditing actually will do.

• os: Enables database auditing and directs all audit records to the operating system's audit trail. The audit information will be populated in the OS file.


• db: Enables database auditing and directs all audit records to the database audit trail. The audit information will be populated in SYS.AUD$ table.


• db,extended: According with db auditing which populate information in SYS.AUD$ table. In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

• xml: Enables database auditing and writes all audit records to XML format OS files.

• xml,extended: Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values and writes all audit records to XML format OS files.

Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html

Configure and Administer Database Auditing

In my previous topic I have given an idea about basics of oracle database auditing. Before entering in this topic I assume that my previous topic “Basics of Database Auditing” has already been read.

Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. This fact is called mandatory auditing, and it includes the following operations:

• Connections to the instance with administrator privileges (connecting to Oracle Database as SYSOPER or SYSDBA.)

• Database startup

• Database shutdown

Enabling and Disabling Standard Auditing

(A)Setting the AUDIT_TRAIL Initialization Parameter

ALTER SYSTEM SET AUDIT_TRAIL =value scope=spfile;

The AUDIT_TRAIL initialization parameter must be set to enable standard auditing. It may take any of the six values.

1. DB: Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail.
2. XML: All elements of the AuditRecord node except Sql_Text and Sql_Bind will be printed to the operating system XML audit file.
3. DB,EXTENDED: Does all actions of AUDIT_TRAIL=DB and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These two columns are populated only when this parameter is specified.)
4. XML,EXTENDED: Does all actions of AUDIT_TRAIL=XML and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)
5. OS: Enables database auditing and directs all audit records to an operating system file
6. NONE: Disables standard auditing (This value is the default.)

(B)Specifying a Directory for the Operating System Auditing Trail

The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when either AUDIT_TRAIL=OS or AUDIT_TRAIL=XML is specified.

Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified.

AUDIT_FILE_DEST can be changed with ALTER SYSTEM SET AUDIT_FILE_DEST = DEFERRED, meaning the new destination will be effective for all subsequent sessions.

If the AUDIT_FILE_DEST parameter is not specified, then the default location on Solaris is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump.

(C)Enabling Standard Auditing Options

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege.

To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

• BY SESSION/BY ACCESS

BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

Auditing Connections and Disconnections

AUDIT SESSION;

AUDIT SESSION BY PROD7, PROD72;
Enabling Privilege Auditing

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

Enabling Object Auditing

AUDIT SELECT, INSERT, DELETE ON PROD7.USER_ACTIVITY BY ACCESS WHENEVER SUCCESSFUL;

(D)Turning Off Statement and Privilege Auditing

The following statement turns off all statement audit options:

NOAUDIT ALL;
The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

Turning Off Object Auditing

NOAUDIT ALL ON PROD7;

Turning Off Network Auditing

NOAUDIT NETWORK;

Purging Audit Records from the Audit Trail

For example, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;
Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.AUD$ WHERE obj$name=’EMP’;

(E)Viewing Database Audit Trail Information DBA_STMT_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

Describes current system auditing options across the system and by user.

DBA_PRIV_AUDIT_OPTS

Describes current system privileges being audited across the system and by user.

DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS

Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user.

DBA_AUDIT_TRAIL USER_AUDIT_TRAIL

Lists all audit trail entries. The USER view shows audit trail entries relating to current user.

DBA_AUDIT_OBJECT USER_AUDIT_OBJECT

Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user.

DBA_AUDIT_SESSION USER_AUDIT_SESSION

Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user.

DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT

Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.

DBA_AUDIT_EXISTS

Lists audit trail entries produced BY AUDIT NOT EXISTS.

Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html

Basics of Database Auditing.

What is Auditing?

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

Why we need it?

• Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content.

• Deter users (or others) from inappropriate actions based on that accountability.

• Investigate suspicious activity.

• Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations.

• Monitor and gather data about specific database activities.

• Detect problems with an authorization or access control implementation.

For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented.

Oracle allows audit options to be focused or broad, enabling you to audit the following:

• Successful statement executions, unsuccessful statement executions, or both.

• Statement executions once in each user session or once every time the statement is executed.

• Activities of all users or of a specific user

Type of Auditing

1.Statement Auditing.(DDL statement and DML statements)
2.Privilege Auditing(system privilege)
3.Schema Object Auditing
4.Fine-Grained Auditing(Monitor Data Acess based on Content)

Audit Records and Audit Trails

Audit records include information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

When Are Audit Records Created?

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become effective for current sessions immediately.

Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter, AUDIT_TRAIL.

Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html