![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Expert |
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 *** ENDEDThis message has been edited. Last edited by: Doug, | ||
|
Gold member |
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 | |||
|
Expert |
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, | |||
|
Virtuoso |
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 | |||
|
Expert |
OK, I changed 'PASS=read' to 'PASS=DOUG' and am still working on it. | |||
|
Virtuoso |
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 | |||
|
Expert |
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 | |||
|
Virtuoso |
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 | |||
|
Expert |
OK, I changed 'PASS=DOUG' to 'PASS=ENGLAND' and am still working on it. | |||
|
Guru |
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 | |||
|
Expert |
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. | |||
|
Virtuoso |
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 | |||
|
Virtuoso |
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. | |||
|
Expert |
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! | |||
|
Guru |
![]() 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 | |||
|
Expert |
Thanks Don, It's good to see you here in Focal Point... | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|