Auditing is always about accountability, and is frequently 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. Show
The types of auditing available in Oracle Database are 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:
Actions Audited by DefaultRegardless of whether database auditing is enabled, Oracle Database 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:
Guidelines for AuditingOracle Database 10g enables you to send audit records to the database audit trail or the operating
system audit trail, when the operating system is capable of receiving them. The database audit records can also be written to operating system files in XML format. 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 has This section describes guidelines for auditing and contains the following topics:
Keeping Audited Information ManageableAlthough auditing is relatively inexpensive, limit the number of audited events as far 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:
Auditing Normal Database ActivityWhen your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:
Auditing Suspicious Database ActivityWhen you audit to monitor suspicious database activity, use the following guidelines:
Auditing Administrative UsersSessions for users who connect as AUDIT_SYS_OPERATIONS = TRUE The default value, All audit records for
All Consider the following CONNECT / AS SYSDBA; ALTER SYSTEM FLUSH SHARED_POOL; UPDATE salary SET base=1000 WHERE name='myname'; When 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 Using TriggersYou 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 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 For these capabilities, use 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. Refer to Fine-Grained Auditing. Deciding Whether to Use the Database or Operating System Audit TrailThe data dictionary of every Oracle database has a table named You can optionally choose to store the database audit information in an operating system file. If auditing facility of your operating system writes audit records to a file that Oracle can also write to, then you can 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:
Using the operating system audit trail offers the following advantages:
See Also:
What Information Is Contained in the Audit Trail?Oracle Database can write records to either the database audit trail, an operating system file, or both. This section describes what information the audit trail contains.
Database Audit Trail ContentsThe database audit trail is a single table named 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, then 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.) The operating system audit trail has only those columns marked Table 12-1 Audit Trail Record Data
Footnote 1: Asterisked (*) columns in Table 12-1 appear in the audit records only if your database initialization file, Footnote 2: Process number is populated as Note: If the If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, then an audited action cannot complete. Instead, it causes an error message and is not done. In most cases, using an operating system log as the audit trail destination allows such an action to complete. See Also:
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 The 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:
Note: To read
from Audit Information Stored in an Operating System FileThe operating system file that contains the audit trail can include any of the following data:
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:
Managing the Standard Audit TrailThis section describes various aspects of managing standard audit trail information, and contains the following topics:
Enabling and Disabling Standard AuditingAny authorized database user can set statement, privilege, and object auditing options at any time. However, Oracle Database 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. Note:
Setting the AUDIT_TRAIL Initialization ParameterDatabase auditing is enabled and disabled by the
Note that changes altering what objects are audited do not require restarting the database. Restart is only required if a universal change is made, such as turning on or off all auditing. Note: You do not need to set Specifying a Directory for the Operating System Auditing TrailThe If the Notes:
Specifying the Syslog LevelTo enable syslog auditing, you assign a value of OS to the AUDIT_SYSLOG_LEVEL=local1.warning Caution: You should have a strong understanding of how to work with syslog before enabling syslog auditing. Standard Auditing in a Multitier EnvironmentIn 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 This clause allows to do the following:
The following example audits AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson; Enabling Standard Auditing OptionsYou
specify one of the four standard auditing options using the
To use the Audit statements that set statement and privilege audit options can include a When setting auditing options, you can also specify the following conditions for auditing:
The implications of your choice of auditing options and the specification of 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 Enabling Statement AuditingValid statement audit options that can be
included in Two special cases of statement auditing are discussed in the following sections. Auditing Connections and DisconnectionsThe To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, AUDIT SESSION; You can set this option selectively for individual users also, as in the next example: AUDIT SESSION BY jeff, lori; Auditing Statements That Fail Because an Object Does Not ExistThe Enabling Privilege AuditingPrivilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; Oracle Database system privileges are listed in the Oracle Database SQL Reference. To audit all successful and unsuccessful uses of the AUDIT DELETE ANY TABLE; To audit all unsuccessful AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; The Enabling Object AuditingThe 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 schema of the user. The To audit all successful and unsuccessful AUDIT DELETE ON jeff.emp; To audit all successful AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL; To set the default object auditing options to audit all unsuccessful AUDIT SELECT ON DEFAULT WHENEVER NOT SUCCESSFUL; Enabling Network AuditingValid statement audit options that can be included in The errors that network auditing uncovers (such as ACTION 122 Network Error in AUDIT_ACTIONS) are not connect failures, but rather can have several possible causes. One such possible cause could be an internal event set by an Oracle engineer purely for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption. Table 12-2 shows four such conditions. Table 12-2 Auditable Network Error Conditions
Disabling Standard Audit OptionsThe You can use a The Caution: The Turning Off Statement and Privilege AuditingThe 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 Turning Off Object AuditingThe 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 NOAUDIT ALL ON emp; To turn off all default object audit options, enter the following statement: NOAUDIT ALL ON DEFAULT; All schema objects that are created before this 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 Turning Off Network AuditingThe following statement turns off network auditing: NOAUDIT NETWORK; Controlling the Growth and Size of the Standard Audit TrailIf the audit trail is full and no more audit records can be inserted, then 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:
The maximum size of the database audit trail ( See Also: Operating-system-specific Oracle documentation for more information about managing the operating system audit trail when directing audit records to that location Purging Audit Records from the Audit TrailAfter 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 DELETE FROM SYS.AUD$ WHERE obj$name='EMP'; Only the user Note: If the audit trail is full and connections are being audited (that is, if the Archiving Audit Trail InformationIf audit trail information must be archived for historical purposes, then the security administrator can copy the relevant records to a normal database table (for example, using Reducing the Size of the Audit TrailAs 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, then the space allocated to the database audit trail can be reduced by following these steps:
The new version of Note:
Protecting the Standard Audit TrailWhen 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 Auditing the Standard Audit TrailIf an application needs to give To do so, you turn on the relevant auditing options for
Please note that this command will If a regular user has If a user later tries to Note:
Viewing Database Audit Trail InformationThe database audit trail (
Audit Trail ViewsThe following views are created upon installation:
Using Audit Trail Views to Investigate Suspicious ActivitiesThis 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:
You suspect the users To investigate, you issue the following statements (in the order specified): 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 ALTER USER tsmith QUOTA 0 ON users; DROP USER djones; The following statements are
subsequently issued by the user 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:
Listing Active Statement Audit OptionsThe 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 Listing Active Privilege Audit OptionsThe 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 Listing Active Object Audit Options for Specific ObjectsThe following query returns all audit options set for any objects with names that start with the characters 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:
Listing Default Object Audit OptionsThe 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 REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/- Notice that the view returns information similar to the Listing Audit RecordsThe following query lists audit records generated by statement and object audit options: SELECT * FROM DBA_AUDIT_OBJECT; Listing Audit Records for the AUDIT SESSION OptionThe following query lists audit information corresponding to the 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 Deleting the Audit Trail ViewsIf you disable auditing and no longer need the audit trail views, then delete them by connecting to the database
as The SYS.AUD$ Auditing Table: ExampleThe code in this section illustrates the auditing of changes made to 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 Fine-Grained AuditingAs 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 also be substantially expanded by using triggers, and providing additional customized information. However, two auditing goals are not directly addressed by any mechanism in standard auditing: minimizing unhelpful audits, and proving that access rights were violated. Access logs, while helpful in reconstructing events, can often be inconclusive. 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
Note: The Policies in Fine-Grained AuditingPolicies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies. For example, most companies logically want to limit access to the specifications or test results for a product under development, and prefer that salary information remain private. Auditors need enough detail to be able to determine the data that was accessed. Knowing only that Advantages of Fine-Grained Auditing over TriggersFine-grained auditing meets these needs by providing functionality (and efficiency) beyond triggers. 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. A fine-grained auditing 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 Extensible Interface Using Event Handler FunctionsOrganizations can define fine-grained auditing policies to specify 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 greater than $500K are accessed. The audit policy (where The audit function ( PROCEDURE fname ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS ... Where:
Functions and Relevant Columns in Fine-Grained AuditingFor 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, then Oracle Database produces an audit record if the SQL statement references any of those audit columns. The Audit Records in Fine-Grained AuditingIf 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 user name, 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. NULL Audit ConditionsTo guarantee auditing of the specified actions ( Note:
Using an empty string is not equivalent to The audit function is executed as an autonomous transaction, committing only the actions of the If Defining FGA PoliciesThe administrator uses the 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, then 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. An Added Benefit to Fine-Grained AuditingIn 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 Database can run 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 The following example shows how you can audit statements ( 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: Because After the first row of
interest is fetched, the event is recorded, and the Note:
See Also:
The DBMS_FGA PackageThe This feature is available only for cost-based optimization. The rule-based optimizer may generate unnecessary audit records because audit monitoring can occur before
row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to 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. ADD_POLICY ProcedureThis 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. SyntaxDBMS_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); ParametersTable 12-3 ADD_POLICY Procedure Parameters
Usage NotesUsage notes are described as follows:
V$XML_AUDIT_TRAIL ViewThe new values for the Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Operating system storage for audit records also offers higher availability, because such records remain available even if the database is temporarily inaccessible. A new dynamic view, The Because the audit XML files are stored in files with Table 12-4 Elements in the V$XML_AUDIT_TRAIL Dynamic View
Note 1: The Note 2: For Note 3: Note 4: For a large numbers of XML audit files, querying Error handling is the same as when ExamplesDBMS_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', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS); DISABLE_POLICY ProcedureThis procedure disables an audit policy. SyntaxDBMS_FGA.DISABLE_POLICY( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ); ParametersTable 12-5 DISABLE_POLICY Procedure Parameters
The default value for DROP_POLICY ProcedureThis procedure drops an audit policy. SyntaxDBMS_FGA.DROP_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );
ParametersTable 12-6 DROP_POLICY Procedure Parameters
Usage NotesThe ENABLE_POLICY ProcedureThis procedure enables an audit policy. SyntaxDBMS_FGA.ENABLE_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
enable BOOLEAN);
ParametersTable 12-7 ENABLE_POLICY Procedure Parameters
|