Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED / CASE-OPENED] Coding Issue Using a MFD_Profile
Go
New
Search
Notify
Tools
Reply
  
[SOLVED / CASE-OPENED] Coding Issue Using a MFD_Profile
 Login/Join
 
Expert
posted
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 PC FOCUS to WebFOCUS 8204M Gen48
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 53 | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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,
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
OK, I changed 'PASS=read' to 'PASS=DOUG' and am still working on it.
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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 
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
OK, I changed 'PASS=DOUG' to 'PASS=ENGLAND' and am still working on it.
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Doug,

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


Senior Business Intelligence Analyst (BIA) @ Enclara Pharmacia
Production: WF 8.09, App Studio, BIP, RC, MSSQL / Test: WF 8204, App Studio, BIP, Reportcaster, Resource Manager, MSSQL
 
Posts: 286 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1206 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1055 | Location: USA | Registered: January 27, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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 PC FOCUS to WebFOCUS 8204M Gen48
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Good One

Congrats on the 2500 contributions to the cause!


Senior Business Intelligence Analyst (BIA) @ Enclara Pharmacia
Production: WF 8.09, App Studio, BIP, RC, MSSQL / Test: WF 8204, App Studio, BIP, Reportcaster, Resource Manager, MSSQL
 
Posts: 286 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks Don, It's good to see you here in Focal Point...
 
Posts: 2713 | Location: Nashville TN Area [Test:8204Mg48/Prod:8202Mg60] | Registered: February 23, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED / CASE-OPENED] Coding Issue Using a MFD_Profile

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.