Permissions in SQL Anywhere 12 Strange Behavior

[JimDiaz]

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.

link text

Thanks,

Jim

[JimDiaz]

looking further into this issue, it only occurs when there is a join in the procedure performing the select statement on the tables

[Breck_Carter]

"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 🙂

[VolkerBarth]

I posted previously about the problems

I'd add to Breck's suggestion that it is really helpful to

• restate the problem here and/or
• link back to that other question

if you think that other question is needed or valuable as background information. Don't expect others to remember your previous postings...

[JimDiaz]

Sorry, I thought providing a script which allowed the replication of the problem would indicate a degree of enthusiasm.

[Breck_Carter]

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.

[JimDiaz]

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.

[Breck_Carter]

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.

[jeff_albion]

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

[JimDiaz]

Thanks for the verification. Our tests show that KEY JOIN does not work around the issue.

[jeff_albion]

How about using a WHERE clause instead? That worked to resolve the issue also in my test:

FROM AP1.TableOne, AP2.TableTwo WHERE TableOne.PrimaryKeyColumn = TableTwo.ForeignKeyColumn;

Which version and build of SQL Anywhere are you using to test with?

[JimDiaz]

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