on 08-28-2013 9:27 AM
Hi Experts
As per advice from 'Mastering SQL for SAP Business One by Gordon Du' (great book BTW) I have by default set all authorisations to query wizard, generator and manager to 'no authorisation'. But my users cannot now access some cells in SOP that contain a formatted search.
How do I give them access to the formatted search but NOT the query wizard, generator and manager? I note in the book it advises that you set authorisation groups against query categories - Is this the BEST way to control access?
Dear Advisors
I have had an answer back from SAP via my VAR and found out the missing ingredient to allow users access to the formatted search queries embedded in forms. I'd already set the FMS query category to group 15 and set the user read only authorisation level 15 too, without success.
The missing link was that besides allowing the above authorisations you also have to allow the user full authorisation to - 'Execute non-select SQL statement' which wasn't the case and hence the problem. See screen shot below: I hope you find this feed back useful and thank you for your help.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have now reverted to where I started and given full authorisation to query wizard/generator & manager and now --- the error does not happen anymore.
Its not ideal, I know but at least we know its not the FMS and/or syntax but a User and/or authorisation issue.
Does anyone know where I go from here or do I log it with SAP?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Unfortunately, it may not be possible to disable all users' access to query manager and FMS also work.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you everyone for your interest and help. I shall post the information you require below to see if we can find a solution.
It is most definitely an authorisation issue as the error message ONLY started after I denied the users access to ALL query reports (admin/authorisations/etc.) . I then realised the errors of my way (by actually reading Gordon's book ) and have reinstated access to just the formatted search queries ONLY (by using query/category/group 15)
It is only since I made these authorisation changes that normal users get the error message. Our superusers whom didn't get their authorisations changed DO NOT get the error message.
However here is the syntax for ONE of the formatted searches and Johan, you are correct - it errors out when I follow your instructions (see screen shot error note). This however doesn't explain why superusers don't error out?:
SELECT T0.ItemCode, T0.Dscription
FROM INV1 T0
INNER JOIN OINV T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T2.CardCode = $[ORDR.CardCode]
GROUP BY T0.ItemCode, T0.Dscription
Once again guys your help and interest is most appreciated.
Hi Gordon
I'm not sure I understand your reply? By FMS I assume you mean Formatted Search?
If so I have authorised our normal users to access only the formatted search category but still get the error message as previously indicated.
If I've misunderstood your answer would you mind explaining in more detail.
Hi Martin Lewis,
1. The error appear due to card code selection. You need to select card code from OINV table not from ORDR table.
Try this:
SELECT T0.ItemCode, T0.Dscription
FROM INV1 T0
INNER JOIN OINV T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T2.CardCode = $[OINV.CardCode]
GROUP BY T0.ItemCode, T0.Dscription
Note: Tested in 8.81 version. No error message.
2. Before run, just copy and paste it in query generator and execute. Sure the system throws an error message. Just ignore and save it under query manager with appropriate name.
3. Open invoice document, run your saved query from --->Tools---->Queries--->User queries.
Thanks & Regards,
Nagarajan
Gordon Du wrote:
...
In your screenshot, you have shown the query running under query manager. That is not supported...
That is not entirely correct. It is supported, but Martin did not follow the entire instruction.
The trick is to first open the form that contains the formatted search, browse to any record, and then run the query under the query manager.
Following your exact method Johan, I get the below error message (2nd screen). When I do the same operation as a super user I get no error message (1st screen). Could it be I have just assigned the normal user with just 'group 15' authorisation ONLY and FMS query category is also set as 'all groups'?
To create the error what I changed is as follows:
1. I set query/category to 'all groups' in the query manager
2. I set user/authorisations to 'no authorisation' for any query groups
3. I then altered the user/authorisation to = group 15 only
I think its in changing the authorisations is where I have gone wrong as this caused the error and superusers (who didn't get any authorisations changes) show no error.
Hi Martin,
You may well be right.
However looking at the result from the query, and at the error message, I wonder about two things:
The first thing may be a fluke, but the error could be explained by the user having read-only rights only on the form that the formatted search is in.
Technically speaking, a formatted search will add or update a value in the form, in other words, it edits the form. As the user has read-only rights to this form, he is not allowed to edit it, et voila...
You may test this by giving the user full rights to sales orders.
Regards,
Johan
My VAR states there is nothing wrong with the FMS or the syntax therein (as it works perfectly well with Superusers) and they propose I alter the normal users BACK to full authorisation on query wizard/generator & manager.
Yes......that's just where I started from and goes against any security advice given previously.
Your point about form read-only rights is valid and I have passed this along the food chain and await their comments.
I will have to give FULL access to ALL queries, just to get my users working again, but I'm not comfortable with it and will request my VAR log it as a formal issue (however that's is done?)
Hi Martin,
Martin Lewis wrote:
...
Your point about form read-only rights is valid and I have passed this along the food chain and await their comments...
Did you test it though ? I mean, that was an educated guess, but we can't be sure that that is it.
Martin Lewis wrote:
...and will request my VAR log it as a formal issue (however that's is done?)
What happens now, is that your VAR will (should) try to reproduce the issue on their own test system, and try to find a solution using normal system settings. If that doesn't work, they will log a support message with SAP (second tier) Support for SAP Business One, on the SAP Service Marketplace.
SAP Support will investigate, and they will then provide some sort of solution.
Regards,
Johan
Thanks for your conformation Nagarajan. I've set up the Query/category authorisation groups to 'ALL (1-15) and set the users authorisation level to 15 so they now have access to formatted searches (I think) But they now get an error message concerning a table called OOAT. I'm not sure what this table or error is. Can you help?
Thanks again
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Martin,
This is a random error message that B1 will throw when there is an error in a query. In this case the formatted search probably uses a query, and the error is in the syntax of that query.
Queries for formatted searches can easily be tested by first opening the form that contains the formatted search, and then from the Query menu (Tools > Queries > User Queries) opening the query in question.
The query window should then show the desired result.
Regards,
Johan
Thanks for your help but one quick question before I look at syntax error. All my users who are 'superusers' and use the same form do NOT get an error message? Only the non-superusers where I've changed their authorisations get the error message!
Logic dictates therefore that the syntax or error message is not the issue. I must admit SAP doesn't always follow logic though. Any suggestions?
As Nagarajan said, it would help to see the query to determine if it is the culprit here.
It is possible that we are dealing with an authorization issue.
However, the test I suggested earlier is simple enough to perform and perfectly safe, so you might want to try that to being with.
Regards,
Johan
Hi Martin Lewis,
Yes. Set authorizations under "Group" in query manager.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.