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