|
There is a seemingly a bug in SQL Anywhere 12 associated with permissions. The details are a bit long winded so I have posted a script here for those of you who are curious enough to download and take a look. To see the problem create a 12 database and execute the updated script I have posted, then log in as dba password sql and execute the command CALL AP2.SelectsFromJoinedTablesProcedure. You will receive the error "Permission denied: you do not have permission to select from "TableOne" The exact same scenario is duplicated in this script but without a join in the underlying table this can be tested by executing the command CALL AP2.SelectsFromSingleTableProcedure. No errors are reported. Thanks, Jim |
|
Yes, I can reproduce this issue ("Permission denied: you do not have permission to select from "TableOne"), and will confirm there's some type of bug here with how we annotate which permissions to grant from the execute procedure as invoker/definer from your sample: DBA -> AP2.SelectsFromJoinedTablesProcedure -> AP1.SelectsFromJoinedTables -> AP1.Tables This has now been reported as CR #748096. The issue can currently be worked around by either executing: GRANT SELECT ON AP1.TableOne TO AP2 go or by simply re-writing the JOIN to another format (using KEY JOIN or re-writing to use a WHERE clause will work, but INNER JOIN causes the error to be thrown): BEGIN SELECT TableTwo.PrimaryKeyColumn, TableTwo.ForeignKeyColumn, TableOne.ColumnOne, TableTwo.ColumnTwo FROM AP1.TableOne KEY JOIN AP1.TableTwo; END go Thanks for the verification. Our tests show that KEY JOIN does not work around the issue.
(03 Oct '13, 12:51)
J Diaz
Replies hidden
How about using a WHERE clause instead? That worked to resolve the issue also in my test:
Which version and build of SQL Anywhere are you using to test with?
(03 Oct '13, 12:56)
Jeff Albion
just tried and yes that worked so did FROM AP1.TableTwo JOIN AP1.TableOne ON TableTwo.ForeignKeyColumn = TableOne.PrimaryKeyColumn I am issuing ALTER FUNCTION "AP2"."SelectsFromJoinedTablesProcedure" RECOMPILE between changes. Using 12.0.1 build 3750
(03 Oct '13, 13:21)
J Diaz
|
looking further into this issue, it only occurs when there is a join in the procedure performing the select statement on the tables
"If you log in as dba password sql you will see the issues"... if you are not enthusiastic enough to describe your symptoms, don't be surprised if this audience shares the same lack of enthusiasm :)
I'd add to Breck's suggestion that it is really helpful to
if you think that other question is needed or valuable as background information. Don't expect others to remember your previous postings...
Sorry, I thought providing a script which allowed the replication of the problem would indicate a degree of enthusiasm.
Put yourself in our shoes... you are asking each and every one of us to DO EXTRA WORK before discovering what your problem is, before we can determine if we can help at all.
EVERYONE reads your question, not everyone can help... but you want everyone to run your script... it is just RUDE.
Breck, I'm sorry I normally would not respond but I believe you are the one being rude. I actually don't expect everyone to open my script only those who are interested enough to see the details. I realize you spend a lot of time on here helping many users which many of us appreciate. However some of us use this site to research documented issues and answer questions in which we have a particular interest in, so if I was in this forum and interested in permissions I would take the time to open the script and have a look.
That's funny... I'm being rude because I try to explain why you're not getting much in the way of replies.
For the benefit of other people: The best way to get a response on this forum is to phrase your question "How do I [do some thing or fix some error]?", then describe your symptom, then provide background information... in that order, not the reverse.