Solved

oracle FGA complex query - not logged

Posted on 2011-03-02
5
295 Views
Last Modified: 2012-10-05
Hi,
I created an FGA policy:
begin
   dbms_fga.add_policy (
      object_schema=>'DAD',
      object_name=>'HOSPITAL_DATA_ICD10',
      policy_name=>'HOSP_ACCESS',
      statement_types=> 'SELECT'
  );
end;


select * from dba_audit_policies
where object_schema='DAD';
 
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLUMN_OPTIONS
DAD HOSPITAL_DATA_ICD10 HOSP_ACCESS YES YES NO NO NO DB+EXTENDED ANY_COLUMNS

A simple query such as:
select * from dad.hospital_data_icd10
where rownum < 6;
is logged:

See snippet from log file:
select * from dad.hospital_data_icd10 where rownum < :"SYS_B_0"        #1(1):6

But a more complex query:

/* Formatted on 2011-03-02 2:14:52 PM (QP5 v5.115.810.9015) */
select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", "T0".
      "C4" "C4", "T0"."C6" "C5", "T0"."C5" "C6"
      from (
      select "CATEGORY_TABLE"."CATEGORY_CODE" "C0", "CATEGORY_TABLE".
      "CATEGORY_DESC" "C1", "HOSPITAL_DATA_ICD10"."DAD_SEQ_NBR" "C2",
      case  when "HOSPITAL_DATA_ICD10"."ENTRY_CODE"=:"SYS_B_00"
      then :"SYS_B_01"
      else :"SYS_B_02"
      end  "C3", "HOSPITAL_DATA_ICD10"."TOTAL_STAY" "C4",
      "HOSPITAL_DATA_ICD10"."ENTRY_CODE" "C5", count("HOSPITAL_DATA_ICD10".
      "DAD_SEQ_NBR") over (partition by "CATEGORY_TABLE"."CATEGORY_CODE",
      "CATEGORY_TABLE"."CATEGORY_DESC", "HOSPITAL_DATA_ICD10"."DAD_SEQ_NBR",
      "HOSPITAL_DATA_ICD10"."ENTRY_CODE",
      case  when "HOSPITAL_DATA_ICD10"."ENTRY_CODE"=:"SYS_B_03"
      then :"SYS_B_04"
      else :"SYS_B_05"
      end , "HOSPITAL_DATA_ICD10"."TOTAL_STAY") "C6"
      from (((("DAD"."HOSPITAL_DATA_ICD10" "HOSPITAL_DATA_ICD10" LEFT OUTER
      JOIN "DAD"."AGE_GROUPINGS" "AGE_GROUPINGS" on "HOSPITAL_DATA_ICD10".
      "AGE_CODE"="AGE_GROUPINGS"."CODE"
      and "HOSPITAL_DATA_ICD10"."AGE_UNITS"="AGE_GROUPINGS"."UNITS") LEFT
      OUTER JOIN "DAD"."DISCHARGE_NURSING_UNITS" "DISCHARGE_NURSING_UNITS" on
      "HOSPITAL_DATA_ICD10"."DISCHARGE_NURSE_UNIT"="DISCHARGE_NURSING_UNITS".
      "NURSING_UNIT") LEFT OUTER JOIN "DAD"."INSTITUTION_2001"
      "INSTITUTION_2001" on "HOSPITAL_DATA_ICD10"."INSTITUTION_NBR_SHORT"=
      "INSTITUTION_2001"."INSTITUTION_NBR") LEFT OUTER JOIN "DAD".
      "DIAGNOSIS_ICD10" "DIAGNOSIS_ICD10" on "HOSPITAL_DATA_ICD10".
      "FISCAL_YEAR"="DIAGNOSIS_ICD10"."FISCAL_YEAR"
      and "HOSPITAL_DATA_ICD10"."DAD_SEQ_NBR"="DIAGNOSIS_ICD10"."DAD_SEQ_NBR")
      LEFT OUTER JOIN "DAD"."CATEGORY_TABLE" "CATEGORY_TABLE" on SUBSTR(
      "DIAGNOSIS_ICD10"."DIAGNOSIS",:"SYS_B_06" ,:"SYS_B_07")="CATEGORY_TABLE"
      ."CATEGORY_CODE"
      where "AGE_GROUPINGS"."CODE"=:"SYS_B_08"
      and "AGE_GROUPINGS"."NUMBER">:"SYS_B_09"
      and "AGE_GROUPINGS"."5YEAR"<>:"SYS_B_10"
      and "HOSPITAL_DATA_ICD10"."DISCHARGE_QUARTER"=CAST( :PQ3 AS VARCHAR( :
      "SYS_B_11" CHAR ) )
      and "DISCHARGE_NURSING_UNITS"."NURSING_UNIT"=CAST( :PQ2 AS VARCHAR( :
      "SYS_B_12" CHAR ) )
      and "INSTITUTION_2001"."INSTITUTION_REGION"=CAST( :PQ1 AS VARCHAR( :
      "SYS_B_13" CHAR ) )
      and "HOSPITAL_DATA_ICD10"."FISCAL_YEAR"=SUBSTR(:"SYS_B_14",:"SYS_B_15" ,
      :"SYS_B_16")
      and (
      CASE WHEN("INSTITUTION_2001"."INSTITUTION_REGION" IS NULL)
      THEN NULL
      ELSE (:"SYS_B_17"||"INSTITUTION_2001"."INSTITUTION_REGION")
      END in (:"SYS_B_18", :"SYS_B_19", :"SYS_B_20", :"SYS_B_21", :"SYS_B_22",
      :"SYS_B_23", :"SYS_B_24", :"SYS_B_25", :"SYS_B_26", :"SYS_B_27", :
      "SYS_B_28", :"SYS_B_29") or :"SYS_B_30" in (:"SYS_B_31", :"SYS_B_32", :
      "SYS_B_33", :"SYS_B_34", :"SYS_B_35", :"SYS_B_36", :"SYS_B_37", :
      "SYS_B_38", :"SYS_B_39", :"SYS_B_40", :"SYS_B_41", :"SYS_B_42") or :
      "SYS_B_43" in (:"SYS_B_44", :"SYS_B_45", :"SYS_B_46", :"SYS_B_47", :
      "SYS_B_48", :"SYS_B_49", :"SYS_B_50", :"SYS_B_51", :"SYS_B_52", :
      "SYS_B_53", :"SYS_B_54", :"SYS_B_55"))) "T0"
      order by "C5" desc nulls last, "C0" asc nulls last, "C1" asc nulls last,
      "C2" asc nulls last, "C6" asc nulls last, "C3" asc nulls last, "C4" asc
      nulls last

is not logged.

Is it because of the join?


 

0
Comment
Question by:KathysFriend
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
MarioAlcaide earned 500 total points
ID: 35020858
Hi,

It is not logged because the audited table is referenced in a subquery. If you audit all the tables used in the query, then it will be audited.

Regards
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35021427
>>It is not logged because the audited table is referenced in a subquery

Not true at least in 10gR2.

Check the test case below.  I show two audit records when it's done.

Note:  My database user is BUD.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
commit;


exec DBMS_FGA.DROP_POLICY('BUD', 'TAB1', 'FGA_EMP');
exec DBMS_FGA.ADD_POLICY( -
         object_schema   => 'BUD', -
         object_name     => 'TAB1', -
         policy_name     => 'FGA_EMP', -
         statement_types => 'SELECT', -
         audit_trail     => DBMS_FGA.DB);

delete from sys.fga_log$;

SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;

select col1 from (select col1 from tab1);
select 'Hello' from dual where dummy in (select col1 from tab1);

SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;

Open in new window

0
 

Author Comment

by:KathysFriend
ID: 35181212
I tried your test program and it worked for me as well.  So if it is not the subquery - then why doesn't it audit my table?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35181240
I'm away from my test database right now so cannot run code.  What appears different between your config and my test?

See if you can make your look like mine and get your working OR make mine like yours and no longer work.
0
 

Author Comment

by:KathysFriend
ID: 35181663
I can break yours by...
select col1 from (select col1 from tab1 left outer join dual on 1 = 1);

In 10g this will not be logged, however  this works for me in 11g.

Now what?!?  For some reason - I am not getting the SQL_Text for any queries on TAB1 in both 10g and 11g.  Although FGA is picking up SQL text for other queries.  Why would it not show me the select statement even Select * from tab1;  The select event is logged but not the SQL_TEXT - that is null.

SELECT policy_name, object_name, statement_type, sql_text, os_user, db_user FROM dba_fga_audit_trail where object_schema = 'PIERCECL' and object_name = 'TAB1';

POLICY_NAME,OBJECT_NAME,STATEMENT_TYPE,SQL_TEXT,OS_USER,DB_USER
FGA_EMP,TAB1,SELECT,,PIERCECL,PIERCECL
FGA_EMP,TAB1,SELECT,,PIERCECL,PIERCECL
FGA_EMP,TAB1,SELECT,,PIERCECL,PIERCECL
FGA_EMP,TAB1,SELECT,,PIERCECL,PIERCECL
FGA_EMP,TAB1,SELECT,,PIERCECL,PIERCECL







0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pl/SQL Query 31 74
Calculating percentages per course - Oracle Query 3 41
compre toata in where clue oracle 4 55
passing parameters to sql script oracle 4 25
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now