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"
;

Joan,

A WFTRANSINOUT would help you modify the fex to include the appropriate where statements only if this is for adhoc or a MRE based focexec. It may not help you for self serve requests as the transIn will effectively be a -INCLUDE fexname. The entire fex request would be passed if you are using MRE, hence you could change the request.

It would still be fun to try!

The following is what the transin string would look like.

Example of transin for adhoc/mre request:

EX -LINES 4 EDAPUT FOCEXEC,transin,C,MEM,TABLE FILE CAR
PRINT CAR MODEL SEATS
BY COUNTRY
END




EX * _transin WFDESCRIBE=XMLPROMPT
IBIWEB SETHTTP -LINES 3 -VALUES 3
HTTP_USER_AGENT='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)'&
SERVER_NAME=localhost&
SERVER_PORT=8080&


SET STYLE=ON 
SET ONLINE-FMT=HTML 
SET CGI-RELEASE=5230 
-TYPE WEBFOCUS CGIVAR OLAPDRILL=drill.html 
-SET &FOCEXURL='/ibi_apps/WFServlet?IBIF_webapp=/ibi_apps' | '&'; 
-SET &FOCEXURL=&FOCEXURL | 'IBIC_server=EDASERVE' | '&'; 
-SET &FOCEXURL=&FOCEXURL | 'IBIWF_msgviewer=OFF' | '&'; 
-SET &FOCHTMLURL='/ibi_html';
SET FOCEXURL=&FOCEXURL
-IF &FOCREL LT 'R720523B' THEN GOTO NOHTM;
SET FOCHTMLURL = &FOCHTMLURL
-NOHTM
SET GRAPHEDIT=SERVER
-IF &FOCREL LT 'R720526B' THEN GOTO SKIPOPTSET;
SET OPTIMIZEIO=ON
-SKIPOPTSET
-SET &REMOTE_USER='';
-SET &OWNERID='admin';
-SET &REMOTE_ADDR='127.0.0.1';
-SET &IBIMR_user='admin';
-SET &IBIMR_domain='untitled/untitled.htm';
-SET &MRUSER='admin';
-SET &WF_REMOTE_USER='';
-SET &HOST='localhost';
-SET &LibraryID='admin';

SET PERMPASS=admin

SET GRAPHSERVURL=http://localhost:8080/ibi_apps/IBIGraphServlet
-IF &FOCREL LT 'R720530B' THEN GOTO SKIPSET;
SET GRAPHENGINE=GRAPH53
-SKIPSET
-INCLUDE mrheader
-INCLUDE transin
-TYPE <AMPERS>  
-SET &EXP = IF &FOCREL LT 'R720710B' THEN ' ' ELSE 'EXPANDED';  
-? && &EXP.EVAL  
-TYPE </AMPERS>
END*

Example for self serve request:

APP ENABLE
    APP PREPENDPATH IBISAMP
    SET BASEURL=http://localhost:8080/approot/IBISAMP/

EX * _carinst WFDESCRIBE=OFF
IBIWEB SETHTTP -LINES 3 -VALUES 3
HTTP_USER_AGENT='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)'&
SERVER_NAME=localhost&
SERVER_PORT=8080&


SET STYLE=ON 
SET ONLINE-FMT=HTML 
SET CGI-RELEASE=5230 
-TYPE WEBFOCUS CGIVAR OLAPDRILL=drill.html 
-SET &FOCEXURL='/ibi_apps/WFServlet?IBIF_webapp=/ibi_apps' | '&'; 
-SET &FOCEXURL=&FOCEXURL | 'IBIC_server=EDASERVE' | '&'; 
-SET &FOCEXURL=&FOCEXURL | 'IBIWF_msgviewer=OFF' | '&'; 
-SET &FOCHTMLURL='/ibi_html';
-SET &FOCEXURL=&FOCEXURL | 'IBIAPP_app=IBISAMP' | '&'
SET FOCEXURL=&FOCEXURL
-IF &FOCREL LT 'R720523B' THEN GOTO NOHTM;
SET FOCHTMLURL = &FOCHTMLURL
-NOHTM
SET GRAPHEDIT=SERVER
-IF &FOCREL LT 'R720526B' THEN GOTO SKIPOPTSET;
SET OPTIMIZEIO=ON
-SKIPOPTSET
-SET &REMOTE_USER='';
-SET &servername='default';
-SET &OWNERID='';
-SET &REMOTE_ADDR='127.0.0.1';
-SET &IBIMR_user='';
-SET &appbx='1';
-SET &IBIMR_domain='';
-SET &MRUSER='';
-SET &WF_REMOTE_USER='';
-SET &HOST='localhost';
-SET &LibraryID='';
-SET &COUNTRY='ENGLAND';

SET GRAPHSERVURL=http://localhost:8080/ibi_apps/IBIGraphServlet
-IF &FOCREL LT 'R720530B' THEN GOTO SKIPSET;
SET GRAPHENGINE=GRAPH53
-SKIPSET
-INCLUDE carinst
-TYPE <AMPERS>  
-SET &EXP = IF &FOCREL LT 'R720710B' THEN ' ' ELSE 'EXPANDED';  
-? && &EXP.EVAL  
-TYPE </AMPERS>
END*
APP DISABLE 

This message has been edited. Last edited by: dhagen,

Joan

I have been doing similar with Oracle Clusters in wf.

A couple of points that might help

1. From a point of view of DBA the cluster is treated as a hierachy with a fixed top even though the Oracle interface acts as if the most appropriate alternate view is applied.

(In traditional FOCUS the alternate view is a way of specifying the root segment in the tree structure that is to be reported on eg CAR.SEATS starts retrieval at the segment containing SEATS).

2. You dont have to apply dba rules for a segment only using fields that are in that segment. This allows a bit of creativity and can force a reference table to always appear in the generated SQL for instance.

Hope this is of some use.

John

It 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.)

Joan,

What I was showing you above is the what the web focus actually sends to the reporting server when you ask it to execute a report. Specifically, it is the dumped output of the transin function. For the first dump of stuff, that is what the web focus client would end up sending to the web focus reporting server if you had a focexec in MRE called transin.fex. The contents would be:

TABLE FILE CAR
PRINT CAR MODEL SEATS
BY COUNTRY
END

That's it. Web focus adds all that other stuff when you click on a report in MRE or BID and select run.

The second list of stuff is what the web focus client would add to self service focexec when you click run on a form or execute the /ibi_apps/WFServlet?IBIF_ex=carinst&IBIAPP_app=ibisamp

I hope that makes sense!