Oracle database - required grants for java code generation

30 views
Skip to first unread message

Zoran

unread,
Nov 14, 2019, 7:30:15 AM11/14/19
to jOOQ User Group
Hello Lukas,

our client wants to create one database user on Oracle with as few grant possible for generating Java code. Tables, sequences, views are working fine but most materialized views are missing.

As I understood from source code, you are pulling catalog from SYS.ALL_MVIEW_COMMENTS. All materialized views have comments on them.

Current grants for that user :

CREATE SESSION
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE

If we grant SELECT ON SCHEMA.TABLE to this user, materialized view is picked up. However, that is not acceptable solution. Why SELECT ANY TABLE grant doesn't work?

Any ideas? 
Thanks in advance.

Lukas Eder

unread,
Nov 14, 2019, 7:37:49 AM11/14/19
to jOOQ User Group
Hi Zoran,

Thank you very much for your message. To my understanding, SELECT ANY TABLE should be sufficient to see all materialised views from a given schema, according to the documentation:

Could there be a bug in your Oracle version that prevents materialised views from being listed in ALL_MVIEW_COMMENTS, in that case? Can you see them in ALL_MVIEWS or ALL_OBJECTS? What Oracle version are you using?

Thanks,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/dc246d1b-4719-4679-9950-06d04886d8c9%40googlegroups.com.

Zoran

unread,
Nov 14, 2019, 7:44:48 AM11/14/19
to jOOQ User Group
Thank you for very fast response!

Yes, SELECT ANY TABLE should be enough, but unfortunately is not, it works only with strict grant to mat. view.

I see them them when I query "SELECT * FROM SYS.all_mviews" or "SELECT * FROM SYS.all_objects where object_type = 'MATERIALIZED VIEW'". It was strange to me that you are using ALL_MVIEW_COMMENTS instead of ALL_MVIEWS. Is there any special reason for that?

Version is Oracle Database 12c Standard Edition 12.2.0.1.0 64bit Production.

Thanks


On Thursday, November 14, 2019 at 1:37:49 PM UTC+1, Lukas Eder wrote:
Hi Zoran,

Thank you very much for your message. To my understanding, SELECT ANY TABLE should be sufficient to see all materialised views from a given schema, according to the documentation:

Could there be a bug in your Oracle version that prevents materialised views from being listed in ALL_MVIEW_COMMENTS, in that case? Can you see them in ALL_MVIEWS or ALL_OBJECTS? What Oracle version are you using?

Thanks,
Lukas

On Thu, Nov 14, 2019 at 1:30 PM Zoran <zoran...@gmail.com> wrote:
Hello Lukas,

our client wants to create one database user on Oracle with as few grant possible for generating Java code. Tables, sequences, views are working fine but most materialized views are missing.

As I understood from source code, you are pulling catalog from SYS.ALL_MVIEW_COMMENTS. All materialized views have comments on them.

Current grants for that user :

CREATE SESSION
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE

If we grant SELECT ON SCHEMA.TABLE to this user, materialized view is picked up. However, that is not acceptable solution. Why SELECT ANY TABLE grant doesn't work?

Any ideas? 
Thanks in advance.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

Lukas Eder

unread,
Nov 14, 2019, 7:51:56 AM11/14/19
to jOOQ User Group
Hi Zoran,

We're using ALL_MVIEW_COMMENTS, because we have to fetch the comments anyway, and it saves us a join or round trip. But if it doesn't work on some database versions, I guess we can resort to using a left join for the comments and query ALL_MVIEWS, instead.  You would still not get the comments on these tables, but at least you will get the tables themselves, which is far more important.

As a workaround, you could:

- Create those materialised views as tables in a dummy schema just for the code generator (assuming you check in generated code)
- Set the relevant grant on the tables explicitly on a development schema, just for the code generator (assuming you check in generated code)
- Extend OracleDatabase (used by the code generator) to return those materialised views explicitly, on OracleDatabase.getTables0()

I've created an issue for this, which we'll fix for the next versions 3.13.0 and 3.12.4:

Thanks again for your report,
Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fd91dfae-d3bb-4584-9351-92db51483052%40googlegroups.com.

Zoran

unread,
Nov 14, 2019, 8:00:42 AM11/14/19
to jOOQ User Group
Excellent!

We will wait for next release. In the meantime, I will try to extend OracleDatabase class. Do you have any example for this?


On Thursday, November 14, 2019 at 1:51:56 PM UTC+1, Lukas Eder wrote:
Hi Zoran,

We're using ALL_MVIEW_COMMENTS, because we have to fetch the comments anyway, and it saves us a join or round trip. But if it doesn't work on some database versions, I guess we can resort to using a left join for the comments and query ALL_MVIEWS, instead.  You would still not get the comments on these tables, but at least you will get the tables themselves, which is far more important.

As a workaround, you could:

- Create those materialised views as tables in a dummy schema just for the code generator (assuming you check in generated code)
- Set the relevant grant on the tables explicitly on a development schema, just for the code generator (assuming you check in generated code)
- Extend OracleDatabase (used by the code generator) to return those materialised views explicitly, on OracleDatabase.getTables0()

I've created an issue for this, which we'll fix for the next versions 3.13.0 and 3.12.4:

Thanks again for your report,
Lukas

Zoran

unread,
Nov 14, 2019, 8:20:50 AM11/14/19
to jOOQ User Group
Nevermind!

I did it already, it works!

Thank you for your help!

Lukas Eder

unread,
Nov 14, 2019, 8:36:31 AM11/14/19
to jOOQ User Group
Perfect, I'm glad you got it to work!

Thanks,
Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4b19a200-9700-41ff-a6ba-022e6b7bde78%40googlegroups.com.

Lukas Eder

unread,
Nov 15, 2019, 5:44:01 AM11/15/19
to jOOQ User Group
For the record, I could reproduce the issue and have documented it also here, on Stack Overflow:

Curious to learn if it is really a bug in Oracle. The fix querying ALL_MVIEWS and left joining ALL_MVIEW_COMMENTS has worked and has been applied to 3.13.0. A backport will be included in 3.12.4.

Thanks again for reporting,
Lukas

Zoran

unread,
Nov 15, 2019, 6:32:03 AM11/15/19
to jOOQ User Group
Hi Lukas,

I believe you posted wrong link.. it should be this one:
https://stackoverflow.com/questions/58874923/oracle-user-cannot-see-any-content-in-all-mview-comments

I'm very glad that you fixed this issue.

Lukas Eder

unread,
Nov 15, 2019, 8:11:00 AM11/15/19
to jOOQ User Group
Yes you're right, thanks for rectifying!

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages