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

asked 01 Oct '13, 21:57

J%20Diaz's gravatar image

J Diaz
830243044
accept rate: 14%

edited 03 Oct '13, 10:09

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

(03 Oct '13, 06:35) J Diaz
1

"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 :)

(03 Oct '13, 08:53) Breck Carter
Replies hidden

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...

(03 Oct '13, 09:16) Volker Barth

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

(03 Oct '13, 10:11) J Diaz
Replies hidden

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.

(03 Oct '13, 11:33) Breck Carter

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.

(03 Oct '13, 13:08) J Diaz
Replies hidden

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.

(03 Oct '13, 15:18) Breck Carter
showing 4 of 7 show all flat view

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
permanent link

answered 03 Oct '13, 12:44

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 03 Oct '13, 12:48

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:

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

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 01 Oct '13, 21:57

question was seen: 667 times

last updated: 03 Oct '13, 15:18