Focal Point
[SOLVED] Dynamic Security on Synonym problem

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7667005686

May 24, 2017, 06:22 PM
Michael Paul
[SOLVED] Dynamic Security on Synonym problem
Hello All,

I'm attempting to add dynamic row level security to a MFD based on a MFD_PROFILE. We are using the latest version of WF 8.2.
I tried to follow the instructions here, but it doesn't seem to work: https://webfocusinfocenter.inf...ang/source/ids24.htm

We are including the MFD_PROFILE into the master file via the following syntax:
-----------------------------------------
FILENAME=MY_LOADS, SUFFIX=MSODBC , MFD_PROFILE=myapp/SECURITY_LOCK, $
...
FIELDNAME=MYID, ALIAS=MYID, USAGE=I11, ACTUAL=I4, $
...

Then we specify the restrictions at the end of the master file via:

END
DBA=THAT_DBA, $
USER=UNRESTRICTED, ACCESS=R, $
USER=RESTRICTED, ACCESS=R, RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=MYID EQ (SECIDS);, $
-----------------------------------------


The MFD_PROFILE security_lock.fex contains the following content...
-----------------------------------------
FILEDEF SECIDS DISK secids.ftm
-RUN

TABLE FILE MYSECURITY_TABLE
PRINT MYID
WHERE ADGroupName EQ '&FOCSECUSER'
ON TABLE HOLD AS SECIDS
END
-RUN

-SET &USERVALUE = IF (&LINES EQ 1) THEN 'RESTRICTED' ELSE 'UNRESTRICTED' ;

SET PASS = &USERVALUE
SET REQSCOPE = CUBE
-----------------------------------------

The security_lock brings back the correct contents if I run it directly. Also if I set the filter value to a hardcoded value it works properly (Ie VALUE=MYID EQ 25Wink.

If I try to access anything from the MFD_PROFILE in the where clause it brings back all security nothing is limited. I've tried several variations of the above, but I seem to be spinning my wheels...

Could we be missing something obvious?

Thanks for any help in advance!

This message has been edited. Last edited by: FP Mod Chuck,
May 25, 2017, 08:09 AM
MartinY
Hi Michael,
In
quote:
VALUE=MYID EQ (SECIDS);, $

you have a semicolon ( ; ), it could be the cause.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
May 25, 2017, 10:32 AM
Michael Paul
Hi Martin,

When I remove the semicolon I get an error of:

(FOC101) ERROR IN DEFINE IN MASTER FILE:
BYPASSING TO END OF COMMAND

If I change the RESTRICT=VALUE from VALUE_WHERE it runs with no errors, but it doesn't limit the rows coming back.

Any other thoughts?

Thanks!

This message has been edited. Last edited by: Michael Paul,
May 25, 2017, 04:13 PM
Michael Paul
NOTE: I followed the instructions exactly using the sample empdata focus file in the following link and it works as expected (https://webfocusinfocenter.informationbuilders.com/wfappent/TLs/TL_lang/source/ids24.htm).


Is this not supported using the SUFFIX=MSODBC?
May 25, 2017, 05:39 PM
Michael Paul
I finally figured out the secret sauce. After I changed the following it is now working...


At the bottom of my MFD I put the following:


END
DBA=USERD, $
USER=UNRESTRICTED, ACCESS=R, $
USER=RESTRICTED, ACCESS=R, RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=DB_INFILE('SECIDS', MYID, MYID );, $


Then for the MFD profile fex of security_lock.fex I used the following:


FILEDEF SECIDS DISK secids.ftm
-RUN

TABLE FILE MYSECURITY_TABLE
PRINT MYID
WHERE ADGroupName EQ &FOCSECUSER.QUOTEDSTRING

ON TABLE HOLD AS SECIDS
END
-RUN

-SET &USERVALUE = IF (&LINES GE 1) THEN 'RESTRICTED' ELSE 'UNRESTRICTED' ;

SET PASS = &USERVALUE
SET REQSCOPE = CUBE
-RUN




I hope this helps someone else out there trying to accomplish the same...

Cheers!
Mike
June 01, 2017, 03:16 PM
eric.woerle
Michael Paul,

Just a point to ponder, when using the attribute NAME =SYSTEM, this means that every time that Master File is used in a join, the join will be invoked. If you only want to invoke that join when a field from the master file is utilized, then you should change it to NAME=[SEGMENT]. Where [SEGMENT] is the segment that holds MYID in your master file.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2