KathysFriend
asked on
oracle FGA complex query - not logged
Hi,
I created an FGA policy:
begin
dbms_fga.add_policy (
object_schema=>'DAD',
object_name=>'HOSPITAL_DAT A_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"."ENT RY_CODE"=: "SYS_B_00"
then :"SYS_B_01"
else :"SYS_B_02"
end "C3", "HOSPITAL_DATA_ICD10"."TOT AL_STAY" "C4",
"HOSPITAL_DATA_ICD10"."ENT RY_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"."ENT RY_CODE",
case when "HOSPITAL_DATA_ICD10"."ENT RY_CODE"=: "SYS_B_03"
then :"SYS_B_04"
else :"SYS_B_05"
end , "HOSPITAL_DATA_ICD10"."TOT AL_STAY") "C6"
from (((("DAD"."HOSPITAL_DATA_I CD10" "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"="A GE_GROUPIN GS"."UNITS ") LEFT
OUTER JOIN "DAD"."DISCHARGE_NURSING_U NITS" "DISCHARGE_NURSING_UNITS" on
"HOSPITAL_DATA_ICD10"."DIS CHARGE_NUR SE_UNIT"=" DISCHARGE_ NURSING_UN ITS".
"NURSING_UNIT") LEFT OUTER JOIN "DAD"."INSTITUTION_2001"
"INSTITUTION_2001" on "HOSPITAL_DATA_ICD10"."INS TITUTION_N BR_SHORT"=
"INSTITUTION_2001"."INSTIT UTION_NBR" ) LEFT OUTER JOIN "DAD".
"DIAGNOSIS_ICD10" "DIAGNOSIS_ICD10" on "HOSPITAL_DATA_ICD10".
"FISCAL_YEAR"="DIAGNOSIS_I CD10"."FIS CAL_YEAR"
and "HOSPITAL_DATA_ICD10"."DAD _SEQ_NBR"= "DIAGNOSIS _ICD10"."D AD_SEQ_NBR ")
LEFT OUTER JOIN "DAD"."CATEGORY_TABLE" "CATEGORY_TABLE" on SUBSTR(
"DIAGNOSIS_ICD10"."DIAGNOS IS",:"SYS_ B_06" ,:"SYS_B_07")="CATEGORY_TA BLE"
."CATEGORY_CODE"
where "AGE_GROUPINGS"."CODE"=:"S YS_B_08"
and "AGE_GROUPINGS"."NUMBER">: "SYS_B_09"
and "AGE_GROUPINGS"."5YEAR"<>: "SYS_B_10"
and "HOSPITAL_DATA_ICD10"."DIS CHARGE_QUA RTER"=CAST ( :PQ3 AS VARCHAR( :
"SYS_B_11" CHAR ) )
and "DISCHARGE_NURSING_UNITS". "NURSING_U NIT"=CAST( :PQ2 AS VARCHAR( :
"SYS_B_12" CHAR ) )
and "INSTITUTION_2001"."INSTIT UTION_REGI ON"=CAST( :PQ1 AS VARCHAR( :
"SYS_B_13" CHAR ) )
and "HOSPITAL_DATA_ICD10"."FIS CAL_YEAR"= SUBSTR(:"S YS_B_14",: "SYS_B_15" ,
:"SYS_B_16")
and (
CASE WHEN("INSTITUTION_2001"."I NSTITUTION _REGION" IS NULL)
THEN NULL
ELSE (:"SYS_B_17"||"INSTITUTION _2001"."IN STITUTION_ 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?
I created an FGA policy:
begin
dbms_fga.add_policy (
object_schema=>'DAD',
object_name=>'HOSPITAL_DAT
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
"CATEGORY_DESC" "C1", "HOSPITAL_DATA_ICD10"."DAD
case when "HOSPITAL_DATA_ICD10"."ENT
then :"SYS_B_01"
else :"SYS_B_02"
end "C3", "HOSPITAL_DATA_ICD10"."TOT
"HOSPITAL_DATA_ICD10"."ENT
"DAD_SEQ_NBR") over (partition by "CATEGORY_TABLE"."CATEGORY
"CATEGORY_TABLE"."CATEGORY
"HOSPITAL_DATA_ICD10"."ENT
case when "HOSPITAL_DATA_ICD10"."ENT
then :"SYS_B_04"
else :"SYS_B_05"
end , "HOSPITAL_DATA_ICD10"."TOT
from (((("DAD"."HOSPITAL_DATA_I
JOIN "DAD"."AGE_GROUPINGS" "AGE_GROUPINGS" on "HOSPITAL_DATA_ICD10".
"AGE_CODE"="AGE_GROUPINGS"
and "HOSPITAL_DATA_ICD10"."AGE
OUTER JOIN "DAD"."DISCHARGE_NURSING_U
"HOSPITAL_DATA_ICD10"."DIS
"NURSING_UNIT") LEFT OUTER JOIN "DAD"."INSTITUTION_2001"
"INSTITUTION_2001" on "HOSPITAL_DATA_ICD10"."INS
"INSTITUTION_2001"."INSTIT
"DIAGNOSIS_ICD10" "DIAGNOSIS_ICD10" on "HOSPITAL_DATA_ICD10".
"FISCAL_YEAR"="DIAGNOSIS_I
and "HOSPITAL_DATA_ICD10"."DAD
LEFT OUTER JOIN "DAD"."CATEGORY_TABLE" "CATEGORY_TABLE" on SUBSTR(
"DIAGNOSIS_ICD10"."DIAGNOS
."CATEGORY_CODE"
where "AGE_GROUPINGS"."CODE"=:"S
and "AGE_GROUPINGS"."NUMBER">:
and "AGE_GROUPINGS"."5YEAR"<>:
and "HOSPITAL_DATA_ICD10"."DIS
"SYS_B_11" CHAR ) )
and "DISCHARGE_NURSING_UNITS".
"SYS_B_12" CHAR ) )
and "INSTITUTION_2001"."INSTIT
"SYS_B_13" CHAR ) )
and "HOSPITAL_DATA_ICD10"."FIS
:"SYS_B_16")
and (
CASE WHEN("INSTITUTION_2001"."I
THEN NULL
ELSE (:"SYS_B_17"||"INSTITUTION
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
See if you can make your look like mine and get your working OR make mine like yours and no longer work.
ASKER
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,ST ATEMENT_TY PE,SQL_TEX T,OS_USER, DB_USER
FGA_EMP,TAB1,SELECT,,PIERC ECL,PIERCE CL
FGA_EMP,TAB1,SELECT,,PIERC ECL,PIERCE CL
FGA_EMP,TAB1,SELECT,,PIERC ECL,PIERCE CL
FGA_EMP,TAB1,SELECT,,PIERC ECL,PIERCE CL
FGA_EMP,TAB1,SELECT,,PIERC ECL,PIERCE CL
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,ST
FGA_EMP,TAB1,SELECT,,PIERC
FGA_EMP,TAB1,SELECT,,PIERC
FGA_EMP,TAB1,SELECT,,PIERC
FGA_EMP,TAB1,SELECT,,PIERC
FGA_EMP,TAB1,SELECT,,PIERC
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.
Open in new window