What privileges are needed to run this query?

Question

I use InterSystem's cache JDBC driver to query the "database dictionary" in order to retrieve source code from classes.

I have this SQL query:

final String query = "select id, super"
    + " from %Dictionary.ClassDefinition"
    + " where System = '0'"
    + " and name not like '\\%%' escape '\\'"
    + " and deployed != 2";

Right now, the user performing this query has %All as a role, which is of course not a good thing. I try and reduce the privileges of this user to the strict minimum necessary.

I am connected to namespace SAMPLES; I have created a role, say FOO to which I have given these privileges:

• %Service_SQL (U);
• %DB_SAMPLES (R);
• select privilege on table %Dictionary.ClassDefinition in namespace %SYS.

I have tried and given my user only this role; but it fails at execution (-99 error translated: "privilege violation"):

Exception in thread "main" java.sql.SQLException: [SQLCODE: <-99>:<Violación de privilegio >]
[Location: <Prepare>]
[%msg: <User sonar is not privileged for the operation>]
    at com.intersys.jdbc.CacheConnection.getServerError(CacheConnection.java:1312)
    at com.intersys.jdbc.CacheConnection.processError(CacheConnection.java:1552)
    at com.intersys.jdbc.InStream.readHeader(InStream.java:164)
    at com.intersys.jdbc.CachePreparedStatement.prepareInternal(CachePreparedStatement.java:633)
    at com.intersys.jdbc.CachePreparedStatement.prepare(CachePreparedStatement.java:594)
    at com.intersys.jdbc.CachePreparedStatement.<init>(CachePreparedStatement.java:141)
    at com.intersys.jdbc.CacheConnection.prepareStatement(CacheConnection.java:1860)
    at com.intersys.jdbc.CacheConnection.prepareStatement(CacheConnection.java:484)
    at es.litesolutions.cacheqc.dbread.CacheDbReader.listClasses(CacheDbReader.java:47)
    at es.litesolutions.cacheqc.dbread.CacheDbReader.main(CacheDbReader.java:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

What privileges are missing so that I can use PreparedStatements?

(Yes, I know the query above has no bind variables but other queries will have bind variables.)

Answer 1

And you should add access for concrete table. It is possibble to do for role or for user, in tab SQL tables. Just add table %Dictiinary.Classdefinition

To find errors in settings of permissions, you can also use Audit

You should add access for table %Dictionary.ClassDefinition from namespace SAMPLES. It is important to choose correct namespace, because you connect to SAMPLES.