Monday, August 14, 2006

Auditing

Oracle provides Database auditing. Value based auditing can be implemented using triggers & PL/SQL code by the applications. Database auditing pertains to Database object access, user session activity, startup, shutdown, etc. This audit trail can be used for malicious activities or to fine-tune the performance.

Oracle always audits DBA operations such as starting & shut down of instance, logins as SYSDBA & SYSOPER. This information can be found in the ALERT log along with information on log switches, checkpoints, and tablespaces taken offline or put online.

Actions that are audited by default :

Instance startup

The Operating system user starting the instance, the user's terminal identifier, the date & time stamp, whether database auditing was enabled or disables.

Instance shutdown

The Operating system user shutting down the instance, the user's terminal identifier, the date & time stamp.

Administrative privileges

details the operating system user who is connecting with administrator privileges

Auditing Guidelines:

Database auditing is most effective when the DBA knows what to look for, and only specific auditing is done. This helps in limiting the amount of information generated for easy analysis.

        AUDIT delete ON sys.aud$

Once created, all information will stay in the AUD$ table owned by SYS. If the audit trail becomes full, no more audit records can be inserted and audited statements will not execute successfully. Errors are returned to all users who issue and audited statement. Space will need to be released in the audit trail for them to execute.

Note: If creating a new aud$ table using copying, deleting original & renaming then grant delete on the new AUD$ to DELETE_CATALOGUE_ROLE

Generating audit data:

1. Setting up system wide auditing using initSID.ora. :


AUDIT_TRAIL =


(Default : These is no default value.)

TRUE | DB

Audit records will be kept in AUD$ tables owned by SYS

OS

Audit information stored in AUDIT_FILE_DEST directory.

FALSE | NONE :

Audit is turned off.


AUDIT_FILE_DEST

Default is set to $ORACLE_HOME/rdbms/audit directory.

Note : Audit information will not be written to the audit trail unless the AUDIT_TRAIL parameter is set to DB or OS, even though AUDIT & NOAUDIT can be used at all times.

2. Auditing using SQL :

General Syntax of audit setup:


Name of the statement or system privilege to be audited

update, create table, etc...

Users to be monitored

BY USERNAME:

whenever the statement is issued by username

BY SESSION:

compile record by session as opposed to ACCESS

BY ACCESS:

whenever the statement is issued by anyone.

Condition of audit

WHENEVER [NOT ] SUCCESSFUL

Using AUDIT command for privilege Audit:

Any privilege that can be granted can also be audited.

AUDIT CREATE TABLE, ALTER TABLE, DROP TABLE
BY scott
WHENEVER SUCCESSFUL;

Schema Object auditing.

AUDIT UPDATE, INSERT
ON hr.regions
BY ACCESS
WHENEVER NOT SUCCESSFUL;

To specify audit options for objects yet to be created use the DEFAULT option :

AUDIT INSERT
ON DEFAULT
WHENEVER SUCCESSFUL;

The omission of a clause defaults to the widest scope permitted by the omission: AUDIT INSERT ON workers;

Statement auditing :

This is the selective auditing of SQL statements not the specific schema objects on which it operates. For example AUDIT TABLE will tract several DDL statements regardless of the table on which they are issued:

AUDIT TABLE
BY SESSION;

AUDIT TABLE
BY Scott
WHENEVER SUCCESSFUL;


Audit definition shortcuts

Connect :

Audits the login & logout activities of every database users. It can substituted with session for the same effect. eg. audit connect;

Resource:

Audits detailed information of activities typically performed by a DBA or a application developer such as creating tables, views, clusters, links, stored procedures, and rollback segments. eg.: audit resource;

Dba :

Audits true DBA activities such as creating users & roles, granting system privileges, and system audits. eg. audit dba;

All :

Equivalent to an on/off switch, where all database activities are monitored & recorded.

Disabling Auditing:

1. Disabling auditing using initSID.ora. :

Set AUDIT_TRAIL=NONE or FALSE. Shutdown database & restart.

2. Disabling auditing using SQL:

NOAUDIT command

Turning off auditing :

NOAUDIT INSERT ON workers;

NOAUDIT ALL;

NOAUDIT ALL PRIVILEGES;

NOAUDIT ALL ON HR.regions;

Note : A NOAUDIT reverses the effect of a previous AUDIT statement. A NOAUDIT must have the same syntax as the previous AUDIT statement and it only reverses that.


AUDIT TABLE BY HR;

AUDIT TABLE ;

NOAUDIT TABLE;

NOAUDIT TABLE BY HR;

<--- specific user audit

<--- all user being audited

<--- user HR is still being audited.

<--- now all audits turned off

Auditing Options: Fine grained auditing :

This provides the monitoring of data based on content. A PL/SQL package DBMS_FGA administers value-based audit policies. Using this packages a DBA can create audit policy on a target table. If any of the rows returned from a query block matches the audit condition, then an audit event entrym including username, SQL text, vind variable, policy name, session ID, timestamp, and other attributes is inserted in the audit trail.

VIEWing Enabled Auditing Options:


DBA_OBJ_AUDIT_OPTS

Schema auditing options

DBA_PRIV_AUDIT_OPTS

Privilege auditing options

DBA_STMT_AUDIT_OPTS

Statement auditing options

ALL_DEF_AUDIT_OPTS

Default auditing options

VIEWing Enabled Auditing Options:


DBA_AUDIT_EXISTS

Records for AUDIT EXISTS/NOT EXISTS

DBA_AUDIT_OBJECT

Audit entries generated for schema object audits

DBA_AUDIT_SESSION

All connect / disconnect entries

DBA_AUDIT_STATEMENT

Statement auditing records

DBA_AUDIT_TRAIL

All audit trails


Examples:


SQL> AUDIT ALL ON hr.regions;

SQL> select * from DBA_OBJ_AUDIT_OPTS where instr(ALT||AUD||COM||DEL||GRA||IND|

|INS||LOC||REN||SEL||UPD||REF||EXE||CRE||REA||WRI,'S/S')>0;

OWNER OBJECT_NAME OBJECT_TY ALT AUD
------------------------------ ------------------------------ --------- --- ---
COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI
--- --- --- --- --- --- --- --- --- --- --- --- --- ---
HR REGIONS TABLE S/S S/S
S/S S/S S/S S/S S/S S/S S/S S/S S/S -/- -/- -/- -/- -/-

SQL> audit connect;
SQL> audit resource;
SQL> audit dba;

Audit succeeded.

SQL> desc DBA_OBJ_AUDIT_OPTS;
Name Null? Type
----------------------------------------- -------- ------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(9)
ALT VARCHAR2(3)
AUD VARCHAR2(3)
COM VARCHAR2(3)
DEL VARCHAR2(3)
GRA VARCHAR2(3)
IND VARCHAR2(3)
INS VARCHAR2(3)
LOC VARCHAR2(3)
REN VARCHAR2(3)
SEL VARCHAR2(3)
UPD VARCHAR2(3)
REF VARCHAR2(3)
EXE VARCHAR2(3)
CRE VARCHAR2(3)
REA VARCHAR2(3)
WRI VARCHAR2(3)

SQL> DESC DBA_PRIV_AUDIT_OPTS;
Name Null? Type
----------------------------------------- -------- ------------
USER_NAME VARCHAR2(30)
PROXY_NAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
SUCCESS VARCHAR2(10)
FAILURE VARCHAR2(10)

SQL> desc DBA_STMT_AUDIT_OPTS;
Name Null? Type
----------------------------------------- -------- --------------
USER_NAME VARCHAR2(30)
PROXY_NAME VARCHAR2(30)
AUDIT_OPTION NOT NULL VARCHAR2(40)
SUCCESS VARCHAR2(10)
FAILURE VARCHAR2(10)

SQL> DESC ALL_DEF_AUDIT_OPTS;
Name Null? Type
----------------------------------------- -------- -----------

ALT VARCHAR2(3)
AUD VARCHAR2(3)
COM VARCHAR2(3)
DEL VARCHAR2(3)
GRA VARCHAR2(3)
IND VARCHAR2(3)
INS VARCHAR2(3)
LOC VARCHAR2(3)
REN VARCHAR2(3)
SEL VARCHAR2(3)
UPD VARCHAR2(3)
REF VARCHAR2(3)
EXE VARCHAR2(3)

Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google