Home arrow Oracle arrow Auditing to Secure Oracle Database XE

Auditing to Secure Oracle Database XE

In this conclusion to a ten-part article series on securing Oracle Database XE, you will learn about statement auditing, privilege auditing, and more. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

TABLE OF CONTENTS:
  1. Auditing to Secure Oracle Database XE
  2. Privilege Auditing
  3. Protecting the Audit Trail
By: Apress Publishing
Rating: starstarstarstarstar / 2
February 10, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Statement Auditing

No matter what type of auditing you wish to do, you use the AUDIT command to turn on auditing and NOAUDIT to turn off auditing. For statement auditing, the format of the AUDIT command looks something like the following:

AUDIT sql_statement_clause BY {SESSION | ACCESS}
     WHENEVER [NOT] SUCCESSFUL;

Thesql_statement_clause contains a number of different pieces of information, such as the type of SQL statement you want to audit and who you are auditing. In addition, you want to either audit the action every time it happens (BY ACCESS) or only once
(BY SESSION). The default isBY SESSION.

Sometimes you want to audit successful actions—statements that did not generate an error message. For these statements, you addWHENEVER SUCCESSFUL. Other times you only care if the commands using the audited statements fail, either due to privilege violations, running out of space in the tablespace, or syntax errors. For these you useWHENEVER NOT SUCCESSFUL.

For most categories of auditing methods, you can specifyALLinstead of individual statement types or objects if you truly want all types of access to a table or any privileges by a certain user to be audited.

The types of statements you can audit, with the statements that are covered in each category, are listed in Table 31-13. IfALL is specified, any statement in this list is audited. However, the types of statements in Table 31-14 do not fall into theALLcategory when enabling auditing; they must be explicitly specified in anyAUDITcommands.

Table 31-13. Auditable Statements Included in the ALL Category  

Statement Option

SQL Operations

CLUSTER

CREATE, ALTER, DROP, or TRUNCATEa cluster

CONTEXT

CREATEor DROPa CONTEXT

DATABASE LINK

CREATEor DROPa database link

DIMENSION

CREATE, ALTER, or DROPa dimension

DIRECTORY

CREATEor DROPa dimension

INDEX

CREATE, ALTER, or DROPan index

MATERIALIZED VIEW

CREATE, ALTER, or DROPa materialized view

NOT EXISTS

Failure of SQL statement due to nonexistent referenced objects

PROCEDURE

CREATEor DROP FUNCTION, LIBRARY, PACKAGE, PACKAGE BODY, or PROCEDURE

PROFILE

CREATE, ALTER, or DROPa profile

PUBLIC DATABASE LINK

CREATEor DROPa public database link

PUBLIC SYNONYM

CREATEor DROPa public synonym

 

 

Table 31-13. Auditable Statements Included in the ALL Category (Continued)

Statement Option SQL Operations
ROLE CREATE,ALTER,DROP, orSETa role
ROLLBACK SEGMENT CREATE,ALTER, orDROPa rollback segment
SEQUENCE CREATEorDROPa sequence
SESSION Logons and logoffs
SYNONYM CREATEorDROPsynonyms
SYSTEM AUDIT AUDITorNOAUDITof system privileges
SYSTEM GRANT GRANTorREVOKEsystem privileges and roles
TABLE CREATE,DROP, orTRUNCATEa table
TABLESPACE CREATE,ALTER, orDROPa tablespace
TRIGGER

CREATE,ALTER(enable/disable),DROPtriggers;ALTER TABLEwith eitENABLE ALL TRIGGERSorDISABLE ALL TRIGGERS her

TYPE CREATE,ALTER, orDROPtypes and type bodies
USER CREATE,ALTER, orDROPa user
VIEW CREATEorDROPa view

 

 

Table 31-14. Explicitly Specified Statement Types

 

 

Statement Option SQL Operations
ALTER SEQUENCE AnyALTER SEQUENCEcommand
ALTER TABLE AnyALTER TABLEcommand
COMMENT TABLE

Add a comment to a table, view, materialized view, or any of their columns

DELETE TABLE Delete rows from a table or view
EXECUTE PROCEDURE

Execute a procedure, function, or any variables or cursors within a package

GRANT DIRECTORY GRANTorREVOKEa privilege on aDIRECTORYobject
GRANT PROCEDURE GRANTorREVOKEa privilege on a procedure, function, or package
GRANT SEQUENCE GRANTorREVOKEa privilege on a sequence
GRANT TABLE GRANTorREVOKEa privilege on a table, view, or materialized view
GRANT TYPE GRANTorREVOKEa privilege on aTYPE
INSERT TABLE INSERT INTOa table or view
LOCK TABLE LOCK TABLEcommand on a table or view
SELECT SEQUENCE Any command referencing the sequence’sCURRVALorNEXTVAL
SELECT TABLE SELECT FROMa table, view, or materialized view
UPDATE TABLE ExecuteUPDATEon a table or view

 

 

Some examples will help make all these options a lot clearer. In our sample database, the userKELLYChas all privileges on the tables in theHRschema and other schemas.KELLYCis allowed to create indexes on some of these tables, but we want to know when the indexes are created in case we have some performance issues related to execution plans changing. We can audit index creation byKELLYCwith the following command:

SQL> audit index by kellyc whenever successful;
Audit succeeded.

Later that day,KELLYCcreates an index on theHR.JOBStable:

SQL> create index job_title_idx on hr.jobs(job_title);
Index created.

Checking the audit trail in the data dictionary viewDBA_AUDIT_TRAIL, we see thatKELLYCdid indeed create an index at 9:21 p.m. on September 17:

SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp,
 
2      obj_name, action_name, sql_text from dba_audit_trail
 
3  where username = 'KELLYC';

 

 

USERNAME

TIMESTAMP OBJ_NAME

ACTION_NAME

SQL_TEXT

 

 

 

 

KELLYC

09/17/06 21:21 JOB_TITLE_IDX

CREATE INDEX

create index j ob_title_idx on

 

 

 

hr.jobs(job_ti tle)

 

 

1 row selected.

To turn off auditing forKELLYCfor indexing operations, use theNOAUDIT command, as follows:

SQL> noaudit index by kellyc;
Noaudit succeeded.

You also may wish to routinely audit both successful and unsuccessful logins. This requires twoAUDITcommands:

SQL> audit session whenever successful; Audit succeeded.
SQL> audit session whenever not successful; Audit succeeded.

Statement auditing also includes startup and shutdown operations. Although you can audit the commandSHUTDOWN IMMEDIATEin theSYS.AUD$table, it is not possible to audit theSTARTUPcommand inSYS.AUD$because the database has to be started before rows can be added to this table. For these cases, you can look in the directory$ORACLE_HOME/rdbms/auditto see a record of a startup operation performed by a system administrator.



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: