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.
Minimise information generated by:
Object auditing should be used to where possible to reduce no. of entries generated
If a statement or privilege auditing must be used, then minimize audit generation by:
specifying users to audit
audit by session and not be access
audit either success of failure, and not both
Tightly control schema object auditing, Users can turn on auditing for the objects they own
Grant AUDIT ANY privilege sparingly.
Protect audit information:
Write protect $ORACLE_HOME/rdbms/audit directory
Monitor removal of data from SYS.AUD$ table.
AUDIT delete ON sys.aud$
Only the DBAs should have the DELETE_CATALOG_ROLE to prevent unauthorized deletions of the audot trail.
Moving audit trail out of the SYSTEM tablespace due to the volatile & high volume nature of data:
Ensure that auditing is currently disabled.
Make a copy of the AUD$ :
ALTER TABLE aud$ MOVE TABLESPACE AUD_TAB
Create index :
CREATE INDEX i_aud1 ON aud$( sessionid,ses$tid) TABLESPACE AUDIT_IDX;
Enable auditing for the instance.
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:
It is independent of user transaction, therefore if a transaction is rolled back, the audit trail record will still remain intact.
Audit information is generated during execute phase, so an error in the parse parse will not generate any audit information.
1. Setting up system wide auditing using initSID.ora. :
| AUDIT_TRAIL =
(Default : These is no default value.) |
| ||||||
| 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 |
| ||||||
| 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
|
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)
