I never really understood why select grant to a table you want to reference in your Oracle Apex report has to be granted to parsing user directly and why you cannot use role. This is in my opinion huge issue in cases where your source tables are in different schema (or different server) and you simply cannot get direct grant because of common company policy “grants on tables are strictly role based”
If you try to add a table to your query and the select on source table is granted to you via role, you get following error
The parsing user which I used in the example above has DBA rights, yet I get the error, because I do not have SELECT granted directly to the user (it is granted via role).
Is there a workaround without granting direct access?
Yes there is and it’s actually very simple. Create database link with password pointing back to your instance in the parsing schema. For example if my parsing user is JOHN with password john123 and my Oracle instance is MY_ORACLE then the db-link would look like this
-- execute this in JOHN schema (JOHN is parsing schema for oracle apex in my case) CREATE DATABASE LINK LOCALHOST CONNECT TO JOHN IDENTIFIED BY john123 USING 'MY_ORACLE';
now I can easily change the query to and let Oracle Apex parse it (user JOHN has to have select grant via role, this of course does not work if JOHN does not have select granted at all)
SELECT * FROM JIRI.PERIOD@LOCALHOST
This tricks works with local tables as well as tables across db-link you don’t have access directly to (you have access via role only), it works also with stored procedures and packages, there are few gotcha you should know before you start using this
- you need to maintain the password (if password for JOHN user changes, you need to change db-link)
- some operations like selecting from CLOB are not allowed accross db-link, please make sure you fully understand this (there are workarounds for CLOB and few other operations)
- if you point back to your server and not remote server then you should look at your listener, query above will use TCP just to get the data and thus this can cause serious performance degradation and increased network traffic on your card. You should configure listener to use IPC