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,

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

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

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,

Doug

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

OK, I changed 'PASS=read' to 'PASS=DOUG' and am still working on it.

Doug

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

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 

Doug

Just trying to verify SET PASS=JAPAN failed?

OK, I changed 'PASS=DOUG' to 'PASS=ENGLAND' and am still working on it.

Doug,

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

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.

Doug

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

Speaking on Francis, I haven't seen him post in a while. Wonder if he retired or something. I miss his rants! lol

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!

Congrats on the 2500 contributions to the cause!

Thanks Don, It's good to see you here in Focal Point...