Audit
Auditing:-Auditing
is a mechanism by which we can capture and stores operations
information into data dictionary.If auditing is enabled for any
Operation then Oracle audit that operation and maintain those operations
in data dictionary .
Parameters For Auditing :-
SQL> show parameter audit
NAME TYPE VALUE
---------------------- ------------ -------------
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string NONE
audit_trail string DB
transaction_auditing boolean TRUE
Note:-Parameter AUDIT_TRAIL can have the following values.
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}
Below is the description of each value
1) NONE or FALSE -> Auditing is disabled
2) DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
3) DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
4) XML -> Auditing is enabled, with all audit records stored as XML format OS files.
5) XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
6) OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.
Note:-AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
Start Auditing:-Audit command is used to start auditing.
Syntax:-
audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}];
Note:-Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.
Level of Auditing :- There are 3 levels
1) Statement level :- Auditing will be done at statement level. All Statements that can be audited are stored in STMT_AUDIT_OPTION_MAP.
Example
SQL> audit table by scott;
Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
2) Object level:-Auditing will be done at object level.These objects can be audited for example tables, views, sequences, packages, stored procedures and stored functions.
Example
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
3) Privilege level:-Auditing will be done at privilege level. All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
Example
SQL> audit create tablespace, alter tablespace by all;
Notr:-Specify ALL PRIVILEGES to audit all system privileges.
Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
Audit options:-
BY SESSION:- "BY SESSION" if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.
Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.
SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;
BY ACCESS:-"BY ACCESS" is used when you want Oracle database to write one record for each audited statement and operation.
Note:-DDL statements Audidting automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;
WHENEVER [NOT] SUCCESSFUL:-"WHENEVER SUCCESSFUL" is used to audit only SQL statements and operations that succeed.While "WHENEVER NOT SUCCESSFUL" is used to audit only SQL statements and operations that fail or result in errors.
Note :- If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
Example
SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;
SQL> audit materialized view by pingme by access whenever successful;
Examples
Auditing for every SQL statement related to roles (create, alter, drop or set a role).
SQL> AUDIT ROLE;
Auditing for every statement that reads files from database directory
SQL> AUDIT READ ON DIRECTORY ext_dir;
Auditing for every statement that performs any operation on the sequence
SQL> AUDIT ALL ON hr.emp_seq;
Data Dictionaries where Auditing data stored :-
DBA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
AUDIT_ACTIONS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
The audit trail contains lots of data, but the following are most likely to be of interest:
Username - Oracle Username.
Terminal - Machine that the user performed the action from.
Timestamp - When the action occurred.
Object Owner - The owner of the object that was interacted with.
Object Name - name of the object that was interacted with.
Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)
Fine Grained Auditing (FGA):- FGA allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.
Several fields have been added to both the standard and fine-grained audit trails:
EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.
PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
GLOBAL_UID - Global Universal Identifier for an enterprise user.
INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
OS_PROCESS - Operating system process id for the oracle process.
TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
SCN - System change number of the query. This column can be used in flashback queries.
SQL_BIND - The values of any bind variables if any.
SQL_TEXT - The SQL statement that initiated the audit action.
The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.
Maintenance of Auditing Records:-The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
Auditing modifications of the data in the audit trail itself can be achieved as follows
SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Delete Audit Records:-We can delete audit records by issuing delete statement on aud$ table.
SQL> DELETE FROM sys.aud$;
Note:-We can delete periodically audit trail records using DBMS_AUDIT_MGMT package.
Disabling Auditing :-We can disable Auditing by "NOAUDIT" statement. Also we can issue privilege and object audit options.
Example:-
SQL>NOAUDIT;
SQL>NOAUDIT session;
SQL>NOAUDIT session BY scott, hr;
SQL>NOAUDIT DELETE ON emp;
SQL>NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL>NOAUDIT ALL;
SQL>NOAUDIT ALL PRIVILEGES;
SQL>NOAUDIT ALL ON DEFAULT;
SQL> show parameter audit
NAME TYPE VALUE
---------------------- ------------ -------------
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string NONE
audit_trail string DB
transaction_auditing boolean TRUE
Note:-Parameter AUDIT_TRAIL can have the following values.
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}
Below is the description of each value
1) NONE or FALSE -> Auditing is disabled
2) DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
3) DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
4) XML -> Auditing is enabled, with all audit records stored as XML format OS files.
5) XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
6) OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.
Note:-AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
Start Auditing:-Audit command is used to start auditing.
Syntax:-
audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}];
Note:-Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.
Level of Auditing :- There are 3 levels
1) Statement level :- Auditing will be done at statement level. All Statements that can be audited are stored in STMT_AUDIT_OPTION_MAP.
Example
SQL> audit table by scott;
Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
2) Object level:-Auditing will be done at object level.These objects can be audited for example tables, views, sequences, packages, stored procedures and stored functions.
Example
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
3) Privilege level:-Auditing will be done at privilege level. All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
Example
SQL> audit create tablespace, alter tablespace by all;
Notr:-Specify ALL PRIVILEGES to audit all system privileges.
Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
Audit options:-
BY SESSION:- "BY SESSION" if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.
Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.
SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;
BY ACCESS:-"BY ACCESS" is used when you want Oracle database to write one record for each audited statement and operation.
Note:-DDL statements Audidting automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;
WHENEVER [NOT] SUCCESSFUL:-"WHENEVER SUCCESSFUL" is used to audit only SQL statements and operations that succeed.While "WHENEVER NOT SUCCESSFUL" is used to audit only SQL statements and operations that fail or result in errors.
Note :- If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
Example
SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;
SQL> audit materialized view by pingme by access whenever successful;
Examples
Auditing for every SQL statement related to roles (create, alter, drop or set a role).
SQL> AUDIT ROLE;
Auditing for every statement that reads files from database directory
SQL> AUDIT READ ON DIRECTORY ext_dir;
Auditing for every statement that performs any operation on the sequence
SQL> AUDIT ALL ON hr.emp_seq;
Data Dictionaries where Auditing data stored :-
DBA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
AUDIT_ACTIONS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
The audit trail contains lots of data, but the following are most likely to be of interest:
Username - Oracle Username.
Terminal - Machine that the user performed the action from.
Timestamp - When the action occurred.
Object Owner - The owner of the object that was interacted with.
Object Name - name of the object that was interacted with.
Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)
Fine Grained Auditing (FGA):- FGA allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.
Several fields have been added to both the standard and fine-grained audit trails:
EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.
PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
GLOBAL_UID - Global Universal Identifier for an enterprise user.
INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
OS_PROCESS - Operating system process id for the oracle process.
TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
SCN - System change number of the query. This column can be used in flashback queries.
SQL_BIND - The values of any bind variables if any.
SQL_TEXT - The SQL statement that initiated the audit action.
The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.
Maintenance of Auditing Records:-The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
Auditing modifications of the data in the audit trail itself can be achieved as follows
SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Delete Audit Records:-We can delete audit records by issuing delete statement on aud$ table.
SQL> DELETE FROM sys.aud$;
Note:-We can delete periodically audit trail records using DBMS_AUDIT_MGMT package.
Disabling Auditing :-We can disable Auditing by "NOAUDIT" statement. Also we can issue privilege and object audit options.
Example:-
SQL>NOAUDIT;
SQL>NOAUDIT session;
SQL>NOAUDIT session BY scott, hr;
SQL>NOAUDIT DELETE ON emp;
SQL>NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL>NOAUDIT ALL;
SQL>NOAUDIT ALL PRIVILEGES;
SQL>NOAUDIT ALL ON DEFAULT;
No comments:
Post a Comment