August 15, 2007, 06:54 PM
Joan Williamson-KellySecurity Problem: DBAFILE or WFTRANSINOUT Plug-in
I have a problem where I need to implement some logic that uses value-based security that exists already in views on the Oracle database we use for reporting. The database is relational, so our master file contains the fact to dimension join relationships. I need to have certain security WHERE conditions passed to the database every time any "Fact" field from a particular master file is used in any focexec for a WebFOCUS TABLE FILE, GRAPH FILE, or even in a JOIN statement.
My current thought is that because I need to pass values based on a user id, I could use the WebFOCUS DBAFILE functionality. We already have WebFOCUS set up to set the user's login ID to the USER value, and I set up the logic to send the required WHERE statements. The problem is, that the WHERE statements don't get passed unless the focexec request includes fields from the segments (dimensions in the relational structure) that contain the secured values.
I think I need to write a plug-in and perhaps use WFTRANSINOUT to modify focexecs at the reporting server. And if I did, I may not need DBAFILE at all.
I'm including the example of how it is now, and then at the end what I need to see on the Oracle SQL side, which I am not currently seeing. I hope this makes sense.
------------------------------------------------------
MASTER FILE (.mas - defines fields):
------------------------------------------------------
FILENAME=XSALES_BILLED_SUM_SIMPLE, SUFFIX=SQLORA
SEGNAME=Billed_Summary_Fact, SEGTYPE=S0, $
FIELD=Revenue_Amount_, ALIAS=REV_AMT,USAGE=D20.2MBC, ACTUAL=D8, MISSING=ON, $
FIELD=JNL_TIME_KEY_Fact_, ALIAS=JNL_TIME_KEY,USAGE=P9, ACTUAL=P5, $
FIELD=PROD_KEY_, ALIAS=PROD_KEY,USAGE=D20.2, ACTUAL=D8, MISSING=ON, $
FIELD=ENTERPRISE_KEY_, ALIAS=ENTERPRISE_KEY,USAGE=D20.2, ACTUAL=D8, MISSING=ON, $
FIELD=SCE_SYS_CD_Fact_, ALIAS=SCE_SYS_CD,USAGE=A16, ACTUAL=A16, MISSING=ON, $
FIELD=CUST_SEGMENT_CD_Fact_, ALIAS=CUST_SEGMENT_CD,USAGE=A4, ACTUAL=A4, MISSING=ON, $
SEGNAME=Enterprise, SEGTYPE=U, PARENT=Billed_Summary_Fact, $
FIELD=Enterprise_ID, ALIAS=ENTERPRISE_IDENTIFIER, USAGE=A12, ACTUAL=A12, $
FIELD=Enterprise_Key_, ALIAS=ENTERPRISE_KEY,USAGE=D20.2, ACTUAL=D8, $
FIELD=Ent_Grantee_Oracle_ID, ALIAS=GRANTEE_ORACLE_ID,USAGE=A30, ACTUAL=A30, $
SEGNAME=Product, SEGTYPE=U, PARENT=Billed_Summary_Fact, $
FIELD=Product_Line_Description, ALIAS=PRODUCT_LINE_DESCRIPTION, USAGE=A60, ACTUAL=A60, $
FIELD=Product_Group_Description, ALIAS=PRODUCT_GROUP_DESCRIPTION, USAGE=A60, ACTUAL=A60, $
FIELD=Product_Key_, ALIAS=PRODUCT_KEY,USAGE=D20.2, ACTUAL=D8, $
FIELD=Prod_Grantee_Oracle_ID, ALIAS=GRANTEE_ORACLE_ID,USAGE=A30, ACTUAL=A30, $
SEGNAME=Segment, SEGTYPE=U, PARENT=Billed_Summary_Fact, $
FIELD=BU_EVP_Code, ALIAS=BU_EXEC_VP_NODE_IDENTIFIER, USAGE=A30, ACTUAL=A30, $
FIELD=BU_EVP_Description, ALIAS=BU_EXEC_VP_NODE_DESCRIPTION, USAGE=A50, ACTUAL=A50, MISSING=ON, $
FIELD=Customer_Segment_Code, ALIAS=CUSTOMER_SEGMENT_CODE, USAGE=A4, ACTUAL=A4, $
FIELD=Customer_Segment_Code_Description, ALIAS=CUSTOMER_SEGMENT_DESCRIPTION, USAGE=A50, ACTUAL=A50, MISSING=ON, $
FIELD=Seg_Grantee_Oracle_ID, ALIAS=GRANTEE_ORACLE_ID,USAGE=A30, ACTUAL=A30, $
SEGNAME=Source_System, SEGTYPE=U, PARENT=Billed_Summary_Fact, $
FIELD=Source_System_Code, ALIAS=SCE_SYS_CD, USAGE=A16, ACTUAL=A16, $
END
DBA=xxyyzzzz, DBAFILE=SEC_BISS,$
------------------------------------------------------
ACCESS FILE (.acx - defines relational joins):
------------------------------------------------------
SEGNAME=Billed_Summary_Fact,
TABLENAME=SALES_BLD_DATA_V,
KEYS=0,
CONNECTION=XSALESp2.world,$
SEGNAME=Enterprise,
KEYFLD=ENTERPRISE_KEY_,
TABLENAME=XSALES.ENTERPRISE_DIMENSION,
KEYS=0,
IXFLD=Enterprise_Key_,
CONNECTION=XSALESp2.world,$
SEGNAME=Product,
KEYFLD=PROD_KEY_,
TABLENAME=XSALES.PRODUCT_DIMENSION,
KEYS=0,
IXFLD=Product_Key_,
CONNECTION=XSALESp2.world,$
SEGNAME=Segment,
KEYFLD=CUST_SEGMENT_CD_Fact_,
TABLENAME=XSALES.CURRENT_SEGMENT_DIMENSION,
KEYS=0,
IXFLD=Customer_Segment_Code,
CONNECTION=XSALESp2.world,$
SEGNAME=Source_System,
KEYFLD=SCE_SYS_CD_Fact_,
TABLENAME=XSALES.SOURCE_SYSTEM_D_V,
KEYS=0,
IXFLD=Source_System_Code,
CONNECTION=XSALESp2.world,$
------------------------------------------------------
DBAFILE:
------------------------------------------------------
FILENAME=SEC_BISS,SUFFIX=FIX, $
SEGNAME=DBAINFO, SEGTYPE=S0, $
FIELDNAME=DUMMY, , A1, A1, $
END
DBA = xxyyzzzz , $
FILENAME=XSALES_BILLED_SUM_SIMPLE, $
USER=USER001, ACCESS=R,$
ACCESS=R, RESTRICT=VALUE, NAME=Enterprise,
VALUE=Ent_Grantee_Oracle_ID EQ 'USER001',$
ACCESS=R, RESTRICT=VALUE, NAME=Product,
VALUE=Prod_Grantee_Oracle_ID EQ 'USER001',$
ACCESS=R, RESTRICT=VALUE, NAME=Segment,
VALUE=Seg_Grantee_Oracle_ID EQ 'USER001',$
------------------------------------------------------
FEX in Dev Studio:
------------------------------------------------------
TABLE FILE XSALES_billed_sum_simple
SUM Revenue_Amount_
BY JNL_TIME_KEY_Fact_
BY SCE_SYS_CD_Fact_
WHERE JNL_TIME_KEY_Fact_ EQ '20070301'
END
------------------------------------------------------
SQL Passed to Oracle by FEX:
------------------------------------------------------
SELECT
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
, SUM(T1."REV_AMT")
FROM
SALES_BLD_DATA_V T1
WHERE
(T1."JNL_TIME_KEY" = 20070301)
GROUP BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
ORDER BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD";
------------------------------------------------------
What We Need SQL To Look Like:
------------------------------------------------------
SELECT
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
, SUM(T1."REV_AMT")
FROM
SALES_BLD_DATA_V T1
,XSALES.ENTERPRISE_DIMENSION T2
,XSALES.CURRENT_SEGMENT_DIMENSION T4
,XSALES.PRODUCT_DIMENSION T5
WHERE
(T2."ENTERPRISE_KEY" = T1."ENTERPRISE_KEY") AND
(T4."CUSTOMER_SEGMENT_CODE" = T1."CUST_SEGMENT_CD") AND
(T5."PRODUCT_KEY" = T1."PROD_KEY") AND
(T1."JNL_TIME_KEY" = 20070301) AND
(T2."GRANTEE_ORACLE_ID" = 'USER001') AND
(T4."GRANTEE_ORACLE_ID" = 'USER001') AND
(T5."GRANTEE_ORACLE_ID" = 'USER001')
GROUP BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
ORDER BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
;
August 16, 2007, 07:37 PM
Joan Williamson-KellyIt is great to get such good responses so quickly!
I need to have some time to absorb the dhagen information. A question... It looks like the example provided is meant to be included in a focexec, either in Dev Studio or in a report somehow in Managed Reporting. Is that right?
I tried hammo1j's number 2 suggestion, and it sort of worked, but there's a problem. Here's how I changed the dbafile:
------------------------------------------------------
DBAFILE:
------------------------------------------------------
FILENAME=SEC_BISS,SUFFIX=FIX, $
SEGNAME=DBAINFO, SEGTYPE=S0, $
FIELDNAME=DUMMY, , A1, A1, $
END
DBA = xxyyzzzz , $
FILENAME=XSALES_BILLED_SUM_SIMPLE, $
USER=USER001, ACCESS=R,$
ACCESS=R, RESTRICT=VALUE, NAME=Billed_Summary_Fact,
VALUE=Ent_Grantee_Oracle_ID EQ 'USER001',$
ACCESS=R, RESTRICT=VALUE, NAME=Billed_Summary_Fact,
VALUE=Prod_Grantee_Oracle_ID EQ 'USER001',$
ACCESS=R, RESTRICT=VALUE, NAME=Billed_Summary_Fact,
VALUE=Seg_Grantee_Oracle_ID EQ 'USER001',$
------------------------------------------------------
It seems to be a very cool way to achieve part of my objective.
------------------------------------------------------
THIS FEX in Dev Studio:
------------------------------------------------------
TABLE FILE XSALES_billed_sum_simple
SUM Revenue_Amount_
BY JNL_TIME_KEY_Fact_
BY SCE_SYS_CD_Fact_
WHERE JNL_TIME_KEY_Fact_ EQ '20070301'
END
------------------------------------------------------
SENDS THIS SQL: (EXACTLY AS NEEDED)
------------------------------------------------------
SELECT
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
, SUM(T1."REV_AMT")
FROM
SALES_BLD_DATA_V T1
,XSALES.ENTERPRISE_DIMENSION T2
,XSALES.CURRENT_SEGMENT_DIMENSION T4
,XSALES.PRODUCT_DIMENSION T5
WHERE
(T2."ENTERPRISE_KEY" = T1."ENTERPRISE_KEY") AND
(T4."CUSTOMER_SEGMENT_CODE" = T1."CUST_SEGMENT_CD") AND
(T5."PRODUCT_KEY" = T1."PROD_KEY") AND
(T1."JNL_TIME_KEY" = 20070301) AND
(T2."GRANTEE_ORACLE_ID" = 'USER001') AND
(T4."GRANTEE_ORACLE_ID" = 'USER001') AND
(T5."GRANTEE_ORACLE_ID" = 'USER001')
GROUP BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
ORDER BY
T1."JNL_TIME_KEY"
,T1."SCE_SYS_CD"
;
***********************************
However, there is a problem. I was not specific enough in my description of what is needed: I need to have the security WHERE conditions passed to the database every time any "Fact" field from a particular master file is used in any focexec for a WebFOCUS TABLE FILE, GRAPH FILE, or even in a JOIN statement
*** but ONLY when a request includes fields from the "Fact" table -- not if the request only has fields from other segments ***.
------------------------------------------------------
FOR EXAMPLE, THIS FOCEXEC:
------------------------------------------------------
TABLE FILE XSALES_billed_sum_simple
PRINT Enterprise_ID
END
------------------------------------------------------
SHOULD SEND THIS SQL:
------------------------------------------------------
SELECT
T1."ENTERPRISE_ID"
FROM
XSALES.ENTERPRISE_DIMENSION T1
;
Instead, it joins to the fact table and to the three security tables and runs the request with all three of the WHERE grantee statements.
***********************************
Is there a way to only send the WHERE statements on the "fact" fields?
(Apologies if my Oracle relational teminology is confusing.)