Focal Point
[SOLVED / CASE-OPENED] Coding Issue Using a MFD_Profile

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

June 17, 2018, 07:02 PM
Doug
[SOLVED / CASE-OPENED] Coding Issue Using a MFD_Profile
All I want is to do some Row Level Security on my CAR2 Source based on a User ID (&&UserID). I'm running 8202M Gen 60.

Help to find the missing piece, of incorrect piece, in this puzzle...

Here's what I have which always results in
ERROR AT OR NEAR LINE     12  IN PROCEDURE ADHOCRQ FOCEXEC *
-*(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE: CAR2


The Synonym
FILENAME=CAR2, SUFFIX=FOC, MFD_PROFILE=ibisamp/CAR2_Profile,$
  SEGMENT=SEG01, SEGTYPE=S3, $
    FIELDNAME=COUNTRY,     ALIAS=E01, USAGE=A10, TITLE='Country', $
    FIELDNAME=CAR,         ALIAS=E02, USAGE=A16, TITLE='Car', $
    FIELDNAME=MODEL,       ALIAS=E03, USAGE=A24, TITLE='Model', $
    FIELDNAME=DEALER_COST, ALIAS=E04, USAGE=D7,  TITLE='Dealer Cost', $
    FIELDNAME=RETAIL_COST, ALIAS=E05, USAGE=D7,  TITLE='Retail Cost', $
    FIELDNAME=PROFIT,      ALIAS=E06, USAGE=D12C, $
END
DBA=ENGLAND,$
USER=ENG_FRA, ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY EQ ENGLAND OR FRANCE,$
USER=JAPAN,   ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY EQ JAPAN,$
USER=xxxxxx,  ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY NE COUNTRY,$

The File Creation
 -* CAR2 File Creation

-*-GOTO REPORT

-CREATE
APP HOLDMETA IBISAMP
APP HOLDDATA IBISAMP
TABLE FILE CAR
SUM DCOST RCOST 
COMPUTE PROFIT/D12C = RCOST - DCOST ;
BY COUNTRY BY CAR BY MODEL

ON TABLE HOLD AS CAR2 FORMAT FOCUS
END
-RUN
-*-GOTO TheEnd

-REPORT
APP PREPENDPATH IBISAMP
?FF CAR2
SET PASS=read
TABLE FILE CAR2
SUM DEALER_COST RETAIL_COST PROFIT BY COUNTRY BY CAR BY MODEL
END
-RUN
-GOTO TheEnd

-TheEnd


The Profile
-TYPE *** CAR2_Profile.fex *** START
-SET &ECHO = ALL ;
APP PATH ibisamp
-RUN
-SET &&PASS = DECODE &&UserID ('Tony' 'ENGLAND' 'Doug' 'ENG_FRA' 'Irene' 'FRA_ITA' 'Jackie' 'JAPAN' 'Willie' 'GERMANY' ELSE 'SWITZ') ;
-RUN
-SET &ECHO = OFF ;
-TYPE *** UserID = &&UserID Pass = &&PASS ***
-TYPE *** CAR2_Profile.fex *** ENDED


The Report Where I can -SET some values for '&&UserID' which carry thru.
-TYPE *** CARS_Report.fex *** START
APP PATH IBISAMP
-RUN

-*-SET &CAR2_user = DECODE &UserID ('Tony A' 'ENGLAND' 'Doug' 'FRANCE' 'Irene' 'ITALY' 'Jackie' 'JAPAN' 'Willie' 'W GERMANY' ELSE 'Switzerland') ;
-SET &&UserID = 'Tony'   ;
-*-SET &&UserID = 'Doug'   ;
-*-SET &&UserID = 'Irene'  ;
-*-SET &&UserID = 'Jackie' ;
-*-SET &&UserID = 'Willie' ;
-SET &ECHO = ALL ;
TABLE FILE CAR2
HEADING
"Here's &&UserID.EVAL's report"
-* for &&PASS "
PRINT *
-*WHERE COUNTRY EQ '&&CAR2_Filter.EVAL'
ON TABLE SET EMPTYREPORT ON
END
-RUN
-SET &ECHO = OFF ;
-TYPE *** CARS_Report.fex *** ENDED


-*-* Results for Tony:
*** CARS_Report.fex *** START
TABLE FILE CAR2
HEADING
"Here's Tony's report"
-* for &&PASS "
PRINT *
-*WHERE COUNTRY EQ '&&CAR2_Filter.EVAL'
ON TABLE SET EMPTYREPORT ON
END
-RUN
*** CAR2_Profile.fex *** START
APP PATH ibisamp
-RUN
-SET &&PASS = DECODE Tony ('Tony' 'ENGLAND' 'Doug' 'ENG_FRA' 'Irene' 'FRA_ITA' 'Jackie' 'JAPAN' 'Willie' 'GERMANY' ELSE 'SWITZ') ;
-TYPE *** UserID = Tony Pass = ENGLAND ***
*** UserID = Tony Pass = ENGLAND ***
-RUN
-SET &ECHO = OFF ;
*** CAR2_Profile.fex *** ENDED
0 ERROR AT OR NEAR LINE     12  IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE: CAR2
BYPASSING TO END OF COMMAND
-SET &ECHO = OFF ;
*** CARS_Report.fex *** ENDED


-* Results for Willie:
*** CARS_Report.fex *** START
TABLE FILE CAR2
HEADING
"Here's Willie's report"
-* for &&PASS "
PRINT *
-*WHERE COUNTRY EQ '&&CAR2_Filter.EVAL'
ON TABLE SET EMPTYREPORT ON
END
-RUN
*** CAR2_Profile.fex *** START
APP PATH ibisamp
-RUN
-SET &&PASS = DECODE Willie ('Tony' 'ENGLAND' 'Doug' 'ENG_FRA' 'Irene' 'FRA_ITA' 'Jackie' 'JAPAN' 'Willie' 'GERMANY' ELSE 'SWITZ') ;
-TYPE *** UserID = Willie Pass = GERMANY ***
*** UserID = Willie Pass = GERMANY ***
-RUN
-SET &ECHO = OFF ;
*** CAR2_Profile.fex *** ENDED
0 ERROR AT OR NEAR LINE     12  IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE: CAR2
BYPASSING TO END OF COMMAND
-SET &ECHO = OFF ;
*** CARS_Report.fex *** ENDED

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




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
June 18, 2018, 01:15 AM
TobyMills
Hey Doug

Good to get together with you tonight.

So here's something that might help to look at. Notice how they are using a && variable for the USER value.

Download this manual on the Master files - you probably already have it on your machine:

8201M Describing Data With WebFOCUS Language

Skip down to page 61
Example: Creating a Dynamic DBA Rule in a Master File

Small things to note here:

When you are trying to control things with DBA, the SET USER command is an environmental SET command as opposed to a -SET. I think you may not be saying SET PASS= in your examples.

Another thing - starting somewhere along the 82x or higher world, we started preferring that you use &FOCSECUSER instead of the CNCTUSR that you'll see in this exmaple. You can use either one really. I noticed in the 8203 manual that it says &FOCSECUSER is the preferred thing now for some reason.

Last thing for today, your values in WHERE tests - I think they need to be in single quotes like 'JAPAN'. Example: VALUE=COUNTRY EQ JAPAN,$ should be VALUE=COUNTRY EQ 'JAPAN',$

Maybe thats why you're always getting the FOC(047) all the time - even if you'd SET PASS (instead of -SET &&PASS), none of those USER descriptions would pass I think.

We should talk about this a little more. I think you're going to want to use DBAFILE to have a common file that describes who can see BU or CU. I also think you might get some value from checking &FOCSECGROUP to check people's Client side Security Center Groups instead of individual user ID's.

Maybe this week we can talk about it more. I have stuff to do tomorrow night but I may be able to get free Tuesday night if you're buying beer Wink

Hope that helps get the ball rolling.

Keys things for me in this area: the USER=&&UID - I think it REALLY helps to not have to explicitly set each user's ID in a file someplace. That works in combination with the MFD_PROFILE and is really slick. Beware the Master file that refers to itself in the MFD_PROFILE. That'll cause a loop that results in a hanging agent you'll have to kill (there's a technique in the manual that has a little coding safeguard to keep you from doing this).

One last thing is that these && globals aren't exactly like you might think. Just because you turned on a && variable in a focexec before referencing a Master doesn't mean the master file will 'get it'. You have to put that VARIABLE NAME=&&UID, USAGE=A8 , $ inside your master for the variable to mean anything to the master file.

Talk tomorrow. It's already midnight and I still don't have my 8203 install finished.

Later!
Toby Mills, CISSP
June 18, 2018, 08:42 AM
Doug
Thanks Toby, A lot to digest, Checking it out now... The Tuesday night deal sounds doable.

This message has been edited. Last edited by: Doug,
June 18, 2018, 10:42 AM
FP Mod Chuck
Doug

I think it is the SET PASS=read. That is not a valid value in your DBA section of the master file.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 18, 2018, 11:45 AM
Doug
OK, I changed 'PASS=read' to 'PASS=DOUG' and am still working on it.
June 18, 2018, 12:09 PM
FP Mod Chuck
Doug

Based on what you posted valid options are ENG_FRA, JAPAN, xxxxxx, and the master password is ENGLAND


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 18, 2018, 03:29 PM
Doug
I agree, based on
DBA=ENGLAND,$
USER=ENG_FRA, ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY EQ ENGLAND OR FRANCE,$
USER=JAPAN,   ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY EQ JAPAN,$
USER=xxxxxx,  ACCESS=R,RESTRICT=VALUE,NAME=FIELD, VALUE=COUNTRY NE COUNTRY,$

But, I still get following message:
0 ERROR AT OR NEAR LINE     12  IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE: CAR2
BYPASSING TO END OF COMMAND 

June 18, 2018, 05:42 PM
FP Mod Chuck
Doug

Just trying to verify SET PASS=JAPAN failed?


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 18, 2018, 06:28 PM
Doug
OK, I changed 'PASS=DOUG' to 'PASS=ENGLAND' and am still working on it.
July 28, 2018, 06:56 AM
Don Garland
Doug,

I'm betting you figured this out. Can you post your findings on this?


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
August 02, 2018, 08:51 AM
Doug
quote:
I'm betting you figured this out. Can you post your findings on this?


1) You're right, I did figure it out, with the help from Toby Mills and Walter Blood (IBI Case 180618057).

2) Posting the findings: I'll do what I can, but it's complicated. Most of the issue was that it doesn't work with Variable length fields as your selection criteria in a hold file. The values used in the selection criteria from a hold file (in file) need to be straight Alpha (/A##) not Variable (/V##).

Basically, the "DBA" function within the synonym is great for Row and Column Level Security. Although, if a user attempts to write a report using a synonym to which they are DENIED access to a column (field) and uses that column in their report, it will prevent the display of the report, not the report without that column being displayed.

~ Thanks, Doug

PS: This is, for the record, my 2500th post.
August 02, 2018, 12:06 PM
FP Mod Chuck
Doug

Congratulations on 2,500 posts. Your contributions are greatly appreciated... I think Francis holds the record with 10,577...


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
October 23, 2018, 02:48 PM
CoolGuy
Speaking on Francis, I haven't seen him post in a while. Wonder if he retired or something. I miss his rants! lol


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
November 29, 2018, 10:09 AM
Doug
Yeah, 'Where in the world is Francis'?

His last post was on May 28 2018.

I hope everything is fine. "Wonder if he retired or something" would be great!




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
November 29, 2018, 01:52 PM
Don Garland
Good One

Congrats on the 2500 contributions to the cause!


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
December 11, 2018, 10:10 AM
Doug
Thanks Don, It's good to see you here in Focal Point...