| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
| Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 |
|
|
View PDF |
Auditing is always about accountability, and frequently is done to protect and preserve privacy for the information stored in databases. Concern about privacy policies and practices has been rising steadily with the ubiquitous use of databases in businesses and on the Internet. Oracle Database provides a depth of auditing that readily enables system administrators to implement enhanced protections, early detection of suspicious activities, and finely-tuned security responses.
The types of auditing available in Oracle systems were described in Chapter 8, "Database Auditing: Security Considerations".
The present chapter explains how to choose the types of auditing you need, how to manage that auditing, and how to use the information gained, in the following sections:
Regardless of whether database auditing is enabled, Oracle 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:
An audit record is generated that lists the operating system user connecting to Oracle as SYSOPER or SYSDBA. This provides for accountability of users with administrative privileges. Full auditing for these users can be enabled as explained in "Auditing Administrative Users".
An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until after startup has successfully completed.
An audit record is generated that lists the operating system user shutting down the instance, the user's terminal identifier, and the date and time stamp.
Oracle Database 10g gives you the option of sending audit records to the database audit trail or your operating system's audit trail, when the operating system is capable of receiving them. The audit trail for database administrators, for example, is typically written to a secure location in the operating system. Writing audit trails to the operating system provides a way for a separate auditor who is root on the operating system to hold all DBAs (who don't have root access) accountable for their actions. These options, added to the broad selection of audit options and customizable triggers or stored procedures, give you the flexibility to implement an auditing scheme that suits your specific business needs.
This section describes guidelines for auditing and contains the following topics:
Although auditing is relatively inexpensive, limit the number of audited events as much as possible. Doing so minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.
Use the following general guidelines when devising an auditing strategy:
After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.
For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.
Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.
For example, if you are auditing to gather information about database activity, determine exactly what types of activities you are tracking, audit only the activities of interest, and audit only for the amount of time necessary to gather the information you desire. As another example, do not audit objects if you are only interested in each session's logical I/O information.
When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:
To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.
After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.
Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and such monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company-policy concerns.
When you audit to monitor suspicious database activity, use the following guidelines:
When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to make concrete conclusions about the origin of the suspicious database activity.
When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.
Sessions for users who connect as SYS can be fully audited, including all users connecting as SYSDBA or SYSOPER. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify whether such users are to be audited. For example, the following setting specifies that SYS is to be audited:
AUDIT_SYS_OPERATIONS = TRUE
The default value, FALSE, disables SYS auditing.
All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$ (also viewable as DBA_AUDIT_TRAIL).
All SYS-issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.
Consider the following SYS session:
CONNECT / AS SYSDBA; ALTER SYSTEM FLUSH SHARED_POOL; UPDATE salary SET base=1000 WHERE name='myname';
When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follows:
Thu Jan 24 12:58:00 2002 ACTION: 'CONNECT' DATABASE USER: '/' OSPRIV: SYSDBA CLIENT USER: jeff CLIENT TERMINAL: pts/2 STATUS: 0 Thu Jan 24 12:58:00 2002 ACTION: 'alter system flush shared_pool' DATABASE USER: '' OSPRIV: SYSDBA CLIENT USER: jeff CLIENT TERMINAL: pts/2 STATUS: 0 Thu Jan 24 12:58:00 2002 ACTION: 'update salary set base=1000 where name='myname'' DATABASE USER: '' OSPRIV: SYSDBA CLIENT USER: jeff CLIENT TERMINAL: pts/2 STATUS: 0
Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs, who are regular database users with the DBA role, or a DBA role (for example, mydba or jr_dba) that your organization customizes.
You can often use triggers to record additional customized information that is not automatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could define a trigger on the EMP table to generate an audit record whenever an employee's salary is increased by more than 10 percent. You can include selected information, such as the values of SALARY before and after it was changed:
CREATE TRIGGER audit_emp_salaries AFTER INSERT OR DELETE OR UPDATE ON employee_salaries for each row begin if (:new.salary> :old.salary * 1.10) then insert into emp_salary_audit values ( :employee_no, :old.salary, :new.salary, user, sysdate); endif; end;
Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.
However, while Oracle triggers can readily monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly and, in some cases, uncertain. Triggers do not enable businesses to capture the statement executed as well as the result set from a query. They also do not enable users to define their own alert action in addition to simply inserting an audit record into the audit trail.
For these capabilities, use Oracle's Fine-grained Auditing, which provides an extensible auditing mechanism supporting definition of key conditions for granular audit as well as an event handler to actively alert administrators to misuse of data access rights. See Fine-Grained Auditing.
The data dictionary of every Oracle database has a table named SYS.AUD$, commonly referred to as the database audit trail, and viewable as DBA_AUDIT_TRAIL. This table is designed to store entries auditing database statements, privileges, or schema objects.
You can optionally choose to store the database audit information to an operating system file. If your operating system has an audit trail that stores audit records generated by the operating system auditing facility, and Oracle is allowed to write to it, you can choose to direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the Application Event Log, viewable by the Event Viewer.
Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.
Using the database audit trail offers the following advantages:
DBA_AUDIT_TRAIL.Alternatively, your operating system audit trail may allow you to consolidate audit records from multiple sources including Oracle and other applications. Therefore, examining system activity might be more efficient because all audit records are in one place. Another advantage to this approach is achieving a separation of duty between a DBA and an auditor.
See Also:
|
Oracle can write records to either the database audit trail, an operating system file, or both. This section describes what information the audit trail contains. asdf
The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.
Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The partial list in the following section shows columns that always appear in the audit trail: if the data they represent is available, that data populates the corresponding column. (For certain columns, this list has the column name as it displays in the audit record, shown here inside parentheses.) Certain audit columns (marked with an * in the following list) appear only if you have specified AUDIT_TRAIL=DB_EXTENDED in the database initialization file, init.ora. The operating system audit trail has only those columns marked (os).
CLIENT USER) (os)DATABASE USER)ACTION) (os)PRIVILEGE) (os)If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, an audited action cannot complete. Instead, it causes an error message and is not done. In some cases, an operating system log allows such an action to complete.
The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an UPDATE statement is audited. However, this specialized type of auditing can be performed using fine-grained auditing methods.
There is a new audit trail view that combines standard and fine-grained audit log records, named DBA_COMMON_AUDIT_TRAIL.
You can use the Flashback Query feature to show the old and new values of the updated rows, subject to any auditing policy presently in force. The current policies are enforced even if the flashback is to an old query that was originally subject to a different policy. Current business access rules always apply.
See Also:
|
The operating system file that contains the audit trail can contain any of the following:
SYS)Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:
| Encoded Information | How to Decode |
|---|---|
|
Action code |
Describes the operation performed or attempted. The |
|
Privileges used |
Describes any system privileges used to perform the operation. The |
|
Completion code |
Describes the result of the attempted operation. Successful operations return a value of zero; unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in Oracle Database Error Messages. |
This section describes various aspects of managing standard audit trail information, and contains the following topics:
Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate audit information for the standard database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.
This section discusses the initialization parameters that enable and disable standard auditing.
Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database's initialization parameter file. The parameter can be set to the following values:
Note that changes that alter what objects are audited do not require restarting the database, which is only required if a universal change is made, such as turning on or off all auditing.
|
Note: You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. For fine-grained auditing, you simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. For SYS auditing, you just set the SYS audit parameter for SYS audit. See the section titled Fine-Grained Auditing later in this chapter. |
The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when AUDIT_TRAIL=OS is specified. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS. AUDIT_FILE_DEST can be changed with "Alter System set AUDIT_FILE_DEST = <dir> DEFERRED", meaning the new destination will be effective for all subsequent sessions.
If the AUDIT_FILE_DEST parameter is not specified, the default location on Solaris is $ORACLE_HOME/rdbms/audit.
In Windows, the default location to which audit records are written is the Event Viewer log file.
|
Notes:
|
In a multitier environment, Oracle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do such auditing, you use the BY proxy clause in your AUDIT statement.
This clause allows you a few options. You can:
The following example audits SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve.
AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson;
| See Also:
Oracle Database Concepts and Oracle Database Application Developer's Guide - Fundamentals for more information on proxies and multitier applications |
You specify one of the three standard auditing options using the AUDIT statement:
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 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.
The implications of your choice of auditing option and specification of AUDIT statement clauses is discussed in subsequent sections.
A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options; existing sessions continue using the audit options in place at session creation.
|
Caution: The |
| See Also:
Oracle Database SQL Reference for a complete description of the |
Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in the Oracle Database SQL Reference.
Two special cases of statement auditing are discussed in the following sections.
The SESSION statement option is unique because it does not generate an audit record when a particular type of statement is issued; this option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session is stored in a single audit record that corresponds to the session. This record can include connection time, disconnection time, and logical and physical I/Os processed, among other information.
To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION (the default and only value for this option), enter the following statement:
AUDIT SESSION;
You can set this option selectively for individual users also, as in the next example:
AUDIT SESSION BY jeff, lori;
The NOT EXISTS statement option specifies auditing of all SQL statements that fail because the target object does not exist.
Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
Oracle's system privileges are listed in the Oracle Database SQL Reference.
To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:
AUDIT DELETE ANY TABLE;
To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.
The Oracle Database SQL Reference lists valid object audit options and the schema object types for which each option is available.
A user can set any object audit option for the objects contained in the user's own schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing option. Normally, the security administrator is the only user granted the AUDIT ANY privilege.
To audit all successful and unsuccessful DELETE statements on the jeff.emp table, BY SESSION (the default value), enter the following statement:
AUDIT DELETE ON jeff.emp;
To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, BY ACCESS, enter the following statement:
AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL;
To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:
AUDIT SELECT ON DEFAULT WHENEVER NOT SUCCESSFUL;
The NOAUDIT statement turns off the various audit options of Oracle Database 10g. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY user or BY proxy option to specify a list of users to limit the scope of the statement and privilege audit options.
You can use a NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and unsuccessful cases.
The BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT statement; audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.
|
Caution: The |
| See Also:
Oracle Database SQL Reference for a complete syntax listing of the |
The following statements turn off the corresponding audit options:
NOAUDIT session; NOAUDIT session BY jeff, lori; NOAUDIT DELETE ANY TABLE; NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
The following statement turns off all statement audit options:
NOAUDIT ALL;
The following statement turns off all privilege audit options:
NOAUDIT ALL PRIVILEGES;
To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.
The following statements turn off the corresponding auditing options:
NOAUDIT DELETE ON emp; NOAUDIT SELECT, INSERT, DELETE ON jward.dept;
Furthermore, to turn off all object audit options on the emp table, enter the following statement:
NOAUDIT ALL ON emp;
To turn off all default object audit options, enter the following statement:
NOAUDIT ALL ON DEFAULT;
All schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.
To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.
If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.
When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:
To control the growth of the audit trail, you can use the following methods:
AUDIT ANY system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.CREATE SESSION privilege is not granted to the corresponding user) and the security administrator is the only user granted the AUDIT ANY system privilege.In both scenarios, object auditing is controlled entirely by the security administrator.
The maximum size of the database audit trail (SYS.AUD$ table) is determined by the default storage parameters of the SYSTEM tablespace, in which it is stored.
| See Also:
Your operating system specific Oracle documentation for more information about managing the operating system audit trail when you are directing audit records to that location |
After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.
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';
|
Note: All deletes from the audit trail are audited without exception: see this chapter's sections entitled Auditing the Standard Audit Trail and Auditing Administrative Users. |
Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS has granted DELETE privilege on SYS.AUD$ can delete records from the database audit trail.
| See Also:
Oracle Database Utilities for information about exporting tables |
If audit trail information must be archived for historical purposes, the security administrator can copy the relevant records to a normal database table (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file.
As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.
If the database audit trail has many extents allocated for it, but many of them are not being used, the space allocated to the database audit trail can be reduced by following these steps:
EXPORT utility.SYS.AUD$ using the TRUNCATE statement.The new version of SYS.AUD$ is allocated only as many extents as are necessary to contain current audit trail records.
When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.
Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.
If an application needs to give SYS.AUD$ access to regular users (non-SYSDBA users), then such access needs to be audited.
To do so, you turn on the relevant auditing options for SYS.AUD$, which work a little differently because they are auditing actions on the audit trail(aud$) itself:
Please note that this command will AUDIT actions performed by non-SYSDBA users only.
Then if a regular user has select, update, insert and delete privileges on SYS.AUD$ and executes a SELECT operation, the audit trail will have a record of that operation. That is, SYS.AUD$ will have a row identifying the SELECT action on itself, as say row1.
If a user later tries to DELETE this row1 from SYS.AUD$, the DELETE will succeed, since the user has the privilege to perform this action. However, this DELETE action on SYS.AUD$ is also recorded in the audit trail.
Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions.
A logfile for an illustrative test case appears at the end of this chapter, at Example of Auditing Table SYS.AUD$.
The database audit trail (SYS.AUD$) is a single table in each Oracle database's data dictionary. Several predefined views are available to present auditing information from this table in a meaningful way. If you decide not to use auditing, you can later delete these views. The following subsections show you what's in these views, how to use them, and how to delete them:
The following views are created upon installation:
| See Also:
Oracle Database Reference for more detailed descriptions of the Oracle provided predefined views |
This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.
You would like to audit the database for the following suspicious activities:
emp table in jeff's schema.You suspect the users jward and swilliams of several of these detrimental actions.
To enable your investigation, you issue the following statements (in order):
AUDIT ALTER, INDEX, RENAME ON DEFAULT BY SESSION; CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp; AUDIT SESSION BY jward, swilliams; AUDIT ALTER USER; AUDIT LOCK TABLE BY ACCESS WHENEVER SUCCESSFUL; AUDIT DELETE ON jeff.emp BY ACCESS WHENEVER SUCCESSFUL;
The following statements are subsequently issued by the user jward:
ALTER USER tsmith QUOTA 0 ON users; DROP USER djones;
The following statements are subsequently issued by the user swilliams:
LOCK TABLE jeff.emp IN EXCLUSIVE MODE; DELETE FROM jeff.emp WHERE mgr = 7698; ALTER TABLE jeff.emp ALLOCATE EXTENT (SIZE 100K); CREATE INDEX jeff.ename_index ON jeff.emp (ename); CREATE PROCEDURE jeff.fire_employee (empid NUMBER) AS BEGIN DELETE FROM jeff.emp WHERE empno = empid; END; / EXECUTE jeff.fire_employee(7902);
The following sections display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:
The following query returns all the statement audit options that are set:
SELECT * FROM DBA_STMT_AUDIT_OPTS; USER_NAME AUDIT_OPTION SUCCESS FAILURE -------------------- ------------------- ---------- --------- JWARD SESSION BY SESSION BY SESSION SWILLIAMS SESSION BY SESSION BY SESSION LOCK TABLE BY ACCESS NOT SET
Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or BY ACCESS.
The following query returns all the privilege audit options that are set:
SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- ALTER USER BY SESSION BY SESSION
The following query returns all audit options set for any objects whose name starts with the characters emp and which are contained in jeff's schema:
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'JEFF' AND OBJECT_NAME LIKE 'EMP%'; OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ... ----- ----------- --------- --- --- --- --- --- --- --- --- ... JEFF EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- ... JEFF EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- ...
Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
BY SESSION.BY ACCESS.WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by "/". For example, the DELETE audit option for jeff.emp is set BY ACCESS for successful delete statements and not set at all for unsuccessful delete statements.The following query returns all default object audit options:
SELECT * FROM ALL_DEF_AUDIT_OPTS; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /-
Notice that the view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (see previous example).
The following query lists audit records generated by statement and object audit options:
SELECT * FROM DBA_AUDIT_OBJECT;
The following query lists audit information corresponding to the AUDIT SESSION statement audit option:
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION; USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO ---------- --------- ---------- ---------- ---------- ---------- JWARD 02-AUG-91 53 2 24 0 SWILLIAMS 02-AUG-91 3337 256 630 0
If you disable auditing and no longer need the audit trail views, delete them by connecting to the database as SYS and running the script file CATNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating system dependent.
The code in this section illustrates the auditing of changes made to SYS.AUD$.
SQL> @t SQL> SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SQL> column username format a10 SQL> column owner format a10 SQL> column obj_name format a6 SQL> column action_name format a17 SQL> SET ECHO ON SQL> SQL> connect sys/newdbapassword as sysdba Connected. SQL> grant select, insert, update, delete on sys.aud$ to jeff; Grant succeeded. SQL> grant select on dba_audit_trail to jeff; Grant succeeded. SQL> audit select, update, delete on sys.aud$ by access; Audit succeeded. SQL> truncate table sys.aud$; Table truncated. SQL> SQL> connect jeff/wolf Connected. SQL> select count(*) from emp COUNT(*) ---------- 0 1 row selected. SQL> SQL> select statementid,entryid,username,action_name,returncode,owner, 2 obj_name,substr(priv_used,1,8) priv, SES_ACTIONS 3 from dba_audit_trail 4 order by sessionid,entryid; STATEMENTID ENTRYID USERNAME ACTION_NAME RETURNCODE OWNER OBJ_NA ----------- ---------- ---------- ----------------- ---------- ---------- ------ PRIV SES_ACTIONS -------- ------------------- 8 1 JEFF SELECT 0 SYS AUD$ 1 row selected. SQL> SQL> update sys.aud$ set userid = 0; 2 rows updated. SQL> select statementid,entryid,username,action_name,returncode,owner, 2 obj_name,substr(priv_used,1,8) priv, SES_ACTIONS 3 from dba_audit_trail 4 order by sessionid,entryid; STATEMENTID ENTRYID USERNAME ACTION_NAME RETURNCODE OWNER OBJ_NA ----------- ---------- ---------- ----------------- ---------- ---------- ------ PRIV SES_ACTIONS -------- ------------------- 8 1 0 SELECT 0 SYS AUD$ 9 2 0 SELECT 0 SYS AUD$ 10 3 JEFF UPDATE 0 SYS AUD$ 3 rows selected. SQL> SQL> delete from sys.aud$; 3 rows deleted. SQL> select statementid,entryid,username,action_name,returncode,owner, 2 obj_name,substr(priv_used,1,8) priv, SES_ACTIONS 3 from dba_audit_trail 4 order by sessionid,entryid; STATEMENTID ENTRYID USERNAME ACTION_NAME RETURNCODE OWNER OBJ_NA ----------- ---------- ---------- ----------------- ---------- ---------- ------ PRIV SES_ACTIONS -------- ------------------- 10 3 JEFF UPDATE 0 SYS AUD$ 12 5 JEFF DELETE 0 SYS AUD$ 2 rows selected. SQL> SQL> connect sys/newdbapassword as sysdba Connected. SQL> noaudit insert, select, update, delete on sys.aud$; Noaudit succeeded. SQL> SQL> spool off
As described earlier in this chapter and in Chapter 8, standard Oracle auditing is highly configurable. Its audit trail provides a fixed set of facts that monitor privileges, object access, or (optionally) SQL usage, including information about the environment or query results. The scope of standard auditing can be substantially expanded by using triggers, providing additional customized information.
However, there is no mechanism to specify audit conditions so as to minimize unhelpful audits, and reconstructing events from access logs often fails to prove access rights were violated.
Oracle's Fine-Grained Auditing addresses these needs, taking you beyond standard auditing and enabling you to minimize false or unhelpful audits by specifying more detailed audit conditions. You do not need to set AUDIT_TRAIL to enable fine-grained auditing. You simply add and remove FGA policies as you see fit, applying them to the specific operations or objects you want to monitor. A built-in audit mechanism in the database prevents users from bypassing the audit. Fine-grained auditing records are stored in the DBA_FGA_AUDIT_TRAIL view, and also in the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.
| See Also:
To add, drop, enable, or disable policies, you use the package described later in this chapter: The DBMS_FGA Package |
Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can establish what columns and conditions you want audit records for. Your conditions can include limiting the audit to specific types of DML statements used in connection with the columns you specify. You can also provide the name of the routine you want called when an audit event occurs, to notify or alert administrators or to handle errors or anomalies.
For example, most companies logically want to limit access to the specifications for a product under development, or its test results, and prefer that salary information remain private. Auditors want enough detail to be able to determine what data was accessed. Knowing only that SELECT privilege was used by a specific user on a particular table is not specific enough to provide accountability.
A central tax authority has similar privacy concerns, needing to track access to tax returns so that employees don't snoop. Similarly, a government agency needs detailed tracking of access to its database of informants. Such agencies also need enough detail to determine what data was accessed, not simply that the SELECT privilege was used by JEFF on the TAXPAYERS or INFORMANTS table.
Fine-grained auditing meets these needs by providing functionality (and efficiency) beyond what triggers can do. Triggers incur a PL/SQL process call for every row processed, and create an audit record only when a relevant column is changed by a DML statement.
An FGA policy, on the other hand, does not incur this cost for every row. Instead, it audits only once for every policy. Specifically, it audits when a specified relevant column occurs in a specified type of DML statement, either being changed by the statement or being in its selection criteria. This combination of criteria uncovers users who hope their information gathering will be masked because they only use the selection criteria of a DML statement. Triggers also cannot monitor the activity of another "instead-of" trigger on the same object, while fine-grained auditing supports tables and views.
Organizations can thus use fine-grained auditing to define policies specifying the data access conditions that are to trigger audit events. These policies can use flexible event handlers that notify administrators when a triggering event has occurred. For example, an organization may allow HR clerks to access employee salary information, but trigger an audit event when salaries are greater than $500K are accessed. The audit policy (where SALARY > 500000) is applied to the EMPLOYEES table through an audit policy interface (DBMS_FGA, a PL/SQL package).
The audit function (handler_module) is an alerting mechanism for the administrator. The required interface for such a function is as follows:
PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_ name VARCHAR2 ) AS ...
where fname is the name of the procedure, object_schema is the name of the schema of the table audited, object_name is the name of the table to be audited, and policy_name is the name of the policy being enforced.
For additional flexibility in implementation, organizations can employ a user-defined function to determine the policy condition, and identify an audit column (called a relevant column) to further refine the audit policy. For example, the function could cause an audit record only when a salary greater than $250,000 is accessed.
Specifying a relevant column helps reduce the instances of false or unnecessary audit records, because the audit need only be triggered when a particular column is referenced in the query. For example, an organization may only wish to audit executive salary access when an employee name is accessed, because accessing salary information alone is not meaningful unless an HR clerk also selects the corresponding employee name. You can, however, specify that auditing occur only when all relevant columns are referenced.
If more than one relevant audit column is specified, Oracle produces an audit record if the SQL statement references any of those audit columns.
The DBMS_FGA package administers these value-based audit policies. The security administrator creates an audit policy on the target object using the functions in the DBMS_FGA package.
| See also:
The DBMS_FGA Package (the next major section) |
If any rows returned from a query block match the audit condition, then an audit event entry is inserted into the fine-grained audit trail. This entry includes username, SQL text, bind variable, policy name, session ID, time stamp, and other attributes. Only one row of audit information is inserted into the audit trail for every FGA policy that evaluates to TRUE. As part of the extensibility framework, administrators can also optionally define an appropriate audit event handler to process the event, for example sending an alert page to the administrator.
To guarantee auditing of the specified actions ("statement_types") affecting the specified columns ("audit_column"), specify the audit_condition as NULL (or omit it), which is interpreted as TRUE. Only specifying NULL will guarantee auditing of the specified actions ("statement_types") affecting the specified columns ("audit_column"). The former practice of specifying an audit condition of "1=1" to force such auditing should no longer be used and will not reliably achieve the desired result. NULL will cause audit even if no rows were processed, so that all actions on an audit_column with this policy are audited.
|
Note: Using an empty string is not equivalent to NULL and will not reliably cause auditing of all actions on a table with this policy. |
The audit function is executed as an autonomous transaction, committing only the actions of the handler_module and not any user transaction. This function has no effect on any user SQL transaction.
If NULL or no audit condition is specified, then any action on a table with that policy causes an audit record to be created, whether or not rows are returned.
The administrator uses the DBMS_FGA.ADD_POLICY interface to define each FGA policy for a table or view, identifying any combination of select, update, delete, or insert statements. Oracle supports MERGE statements as well, by auditing the underlying actions of INSERT and UPDATE. To audit MERGEs, set up FGA on these INSERTs and UPDATEs. Only one record is generated, for each policy, for successful MERGEs.
FGA policies associated with a table or view may also specify relevant columns, so that any specified statement type affecting a particular column is audited. More than one column can be included as relevant columns in a single FGA policy. Examples include privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. If no relevant column is specified, auditing applies to all columns. That is, auditing occurs whenever any specified statement type affects any column, unless you specify in the policy that auditing is to occur only when all relevant columns are referenced.
In general, fine-grained auditing policies are based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle can execute a user-defined audit event handler, if specified in the policy, using autonomous transactions to process the event.
Fine-grained auditing can be implemented in user applications using the DBMS_FGA package or by using database triggers.
The following example shows how you can audit statements (INSERT, UPDATE, DELETE, and SELECT) on table hr.emp to monitor any query that accesses the salary column of the employee records which belong to sales department:
DBMS_FGA.ADD_POLICY( object_schema => 'hr', object_name => 'emp', policy_name => 'chk_hr_emp', audit_condition => 'dept = ''SALES'' ', audit_column => 'salary' statement_types => 'insert,update,delete,select');
Then, any of the following SQL statements will cause the database to log an audit event record.
SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000; SELECT salary FROM hr.emp WHERE dept = 'SALES'; DELETE from hr.emp where salary >1000000 With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event. Consider the following commands: /* create audit event handler */ CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS BEGIN UTIL_ALERT_PAGER(schema1, table1, policy1); -- send an alert note to my pager END; /* add the policy */ DBMS_FGA.ADD_POLICY( object_schema => 'hr', object_name => 'emp', policy_name => 'chk_hr_emp', audit_condition => 'dept = ''SALES'' ', audit_column => 'salary', handler_schema => 'sec', handler_module => 'log_id', enable => TRUE);
|
Note: Since the words "schema" and "table" are reserved words, they cannot be used as variables without some alteration, such as appending "1" as is done here. |
What happens when these commands are issued? After the fetch of the first interested row, the event is recorded, and the audit function SEC.LOG_ID is executed. The audit event record generated is stored in DBA_FGA_AUDIT_TRAIL (fga_log$), which has reserved columns (such as SQL_TEXT and SQL_BIND) for recording SQL text, policy name, and other information. The query's SQLBIND and SQLTEXT are recorded in the LSQLTEXT and LSQLBIND columns of fga_log$ only if the policy specified audit_trail = DBMS_FGA.DB_EXTENDED.
See Also:
|
|
Note: Policies currently in force on an object involved in a flashback query are applied to the data returned from the specified flashback snapshot (based on time or SCN). |
The DBMS_FGA package provides fine-grained security functions. Execute privilege on DBMS_FGA is needed for administering audit policies. Because the audit function can potentially capture all user environment and application context values, policy administration should be executable by privileged users only.
This feature is available for only cost-based optimization. The rule-based optimizer may generate unnecessary audit records since audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind variables that are issued.
The procedures for this package are described in the following subsections:
The syntax, parameters, and usage notes accompanying each procedure description also discuss the defaults and restrictions that apply to it.
This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view object is 256.
DBMS_FGA.ADD_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, audit_condition VARCHAR2, audit_column VARCHAR2, handler_schema VARCHAR2, handler_module VARCHAR2, enable BOOLEAN, statement_types VARCHAR2, audit_trail BINARY_INTEGER IN DEFAULT, audit_column_opts BINARY_INTEGER IN DEFAULT);
DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE');TRUE, but it cannot contain the following elements:
Specifying an audit condition of "1=1" to force auditing of all specified statements ("statement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. NULL will cause audit even if no rows were processed, so that all actions on a table with this policy are audited.
PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_ name VARCHAR2 ) AS ...
where fname is the name of the procedure, object_schema is the name of the schema of the table audited, object_name is the name of the table to be audited, and policy_name is the name of the policy being enforced.
INSERT-WITH-APPEND-hint.)LSQLTEXT and LSQLBIND:
The audit_trail parameter appears in the ALL_AUDIT_POLICIES view.
DBMS_FGA.ANY_COLUMNS), orDBMS_FGA.ALL_COLUMNS).The default is DBMS_FGA.ANY_COLUMNS.
The ALL_AUDIT_POLICIES view also shows audit_column_opts.
This procedure drops an audit policy.
DBMS_FGA.DROP_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 );
The DBMS_FGA procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_FGA procedures are part of the DDL transaction. The default value for object_schema is NULL. (If NULL, the current effective user schema is assumed.)
This procedure enables an audit policy.
DBMS_FGA.ENABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2, enable BOOLEAN);
This procedure disables an audit policy.
DBMS_FGA.DISABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 );
The default value for object_schema is NULL. (If NULL, the current effective user schema is assumed.)
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|