Section 6.7. Troubleshooting FGA


6.7. Troubleshooting FGA

Like any other complex and low-level feature in Oracle, FGA might at times produce unexpected results. In this section, I'll go over some of the common FGA-related errors and how to handle them. Please note that I am not going to talk about FGA bugspublished or unpublishedthat might affect its behavior. Bugs are often platform-specific and transient. I will instead focus on errors that can occur with typical use of FGA.

Any time there is a problem with FGA, a trace file is produced in the directory specified by the database initialization parameter USER_DUMP_DEST. This trace file shows important information about the exact error condition and offers clues for diagnosing it further. Here is an except from one such trace file:

     /opt/app/oracle/admin/gridr/udump/gridr_ora_14424.trc     Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production     With the Partitioning, Oracle Label Security, OLAP and Data Mining options     ORACLE_HOME = /opt/app/oracle/product/10g_gridr     System name:    SunOS     Node name:      smiley2.proligence.com     Release:        5.9     Version:        Generic_117171-12     Machine:        sun4u     Instance name: gridr     Redo thread mounted by this instance: 1     Oracle process number: 54     Unix process pid: 14424, image: oraclegridr@smiley2.proligence.com     *** 2005-07-12 19:01:44.337     *** ACTION NAME:(  ) 2005-07-12 19:01:44.288     *** MODULE NAME:(SQL*Plus) 2005-07-12 19:01:44.288     *** SERVICE NAME:(SYS$USERS) 2005-07-12 19:01:44.288     *** SESSION ID:(165.51503) 2005-07-12 19:01:44.288          FGA supports simple predicates only - error 28138     FGA Policy EMP_DML 

Note the last two (highlighted) lines, which show two important facts:

  • The policy namein this case, EMP_DML

  • The errorin this case, FGA supports simple predicates only

This error was caused when a user updated the table. You can view the audit condition of the policy by specifying:

     SQL> SELECT policy_text       2    FROM dba_audit_policies       3   WHERE policy_name = 'EMP_DML';     POLICY_TEXT     --------------------------------------------     SALARY >= 1500 or EMPID=304

Note that this policy's audit condition, "SALARY >= 1500 or EMPID=304", has more than one condition and thus is not a simple predicate. The error message simply confirms that observation. The solution is straightforward: you need to re-create the policy with a simple predicate such as "SALARY >= 1500" or "EMPID=304", not both at the same time. Policies with non-simple predicates work for SELECT statementsjust not for DML statements.

In some cases, FGA will raise errors that occur silentlyin other words, the user is not made aware of the error. One good example is when the handler module (if defined) fails for some rows: in this case, no error is reported to the user. In addition, the rows for which the handler module failed are not returned in the user's query, so the user doesn't have the slightest idea that some rows were not returned by the query. In such a case, an entry in the trace file is the only evidence that an error has occurred. An excerpt from a trace file is shown below.

     *** 2005-07-12 17:52:07.590     *** ACTION NAME:(  ) 2005-07-12 17:52:07.536     *** MODULE NAME:(SQL*Plus) 2005-07-12 17:52:07.536     *** SERVICE NAME:(SYS$USERS) 2005-07-12 17:52:07.536     *** SESSION ID:(165.50693) 2005-07-12 17:52:07.536     -----------------------------------     Error during execution of handler in Fine Grained Auditing     Audit handler  : begin ARUP.ACCESS_FLAGGER(:sn, :on, :pl); end;     Error Number 1  : 604     Logon user     : SYSMAN     Object Schema: ARUP, Object Name: EMP, Policy Name: EMP_SEL     *** 2005-07-12 17:52:32.891     Error Number 2: 1438-----------------------------------     Error during execution of handler in Fine Grained Auditing     Audit handler  : begin ARUP.ACCESS_FLAGGER(:sn, :on, :pl); end;     Error Number 1  : 604     Logon user     : SYSMAN     Object Schema: ARUP, Object Name: EMP, Policy Name: EMP_SEL

There are two different sections here. The first starts from the top, ending at the second date line, "*** 2005-07-12 17:52:32.891", indicating the first time this error occurred. The set of lines in the next section shows the true error; note the line shown in bold: "Error Number 2: 1438". This error is the true reason why the operation failed. You can look up the error code using the oerr utility as follows.

     Smiley2:/opt/app/oracle/admin/gridr/udump>oerr ora 1438     01438, 00000, "value larger than specified precision allows for this column"     // *Cause:     // *Action:

The cause of the error is clear now: the handler module was trying to insert a value larger than the column allowed. Fixing that value will fix this error.

The last error drives home a very important point: if you enable FGA on any of the tables in your database, always check for trace files in the USER_DUMP_DEST directory so that you will catch these silent errors. You may be already checking for trace files anyway, but this is another reason to continue doing so.




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net