SQLCODE: -99 when executing dynamic SQL on specific properties

Question

Question

Hi, I am unsure how to remove this restriction; when I am performing dynamic SQL using ##class(%SQL.Statement).%ExecDirectNoPriv(, .query, args...)

It works fine, but the moment I add specific properties from the persistent class I am performing the select on into the WHERE clause, I get: ERROR #5540: SQLCODE: -99 Message: User UnknownUser is not privileged for the operation. Despite using %ExecDirectNoPriv, I've tried with prepared statement as well, exact same situation.

I have read that it is possible to set privileges on individual properties on a persistent class, but I have no clue where to remove or change these privileges; the only solution I found is to add the role %All to UnknownUser, but that is not a fix but despite attempting to add full privileges for the persistent class for UnknownUser, I keep getting this privilege error.

Product version: IRIS 2024.2

$ZV: IRIS for UNIX (Ubuntu Server LTS for ARM64) 2024.1.2 (Build 398U) Thu Oct 3 2024 13:42:05 EDT

Discussion (5)3

Log in or sign up to continue

Martin Nielsen · May 20

I figured it out, however I am not sure why this is the case:

Firstly the permissions were in order, and I did not change anything since the full permission to the entire table was granted.

When I added a WHERE clause with tbl.SomeBoolean = ?, it would prompt the -99 SQLCODE error, I then attempted to use tbl.SomeBoolean LIKE (?) which worked, I then tried tbl.SomeBoolean = (?) which ALSO worked.

My conclusion: when filtering by boolean (bit) columns using dynamic queries (%SQL.Statement) parentesis must be surrounded by the value, I don't know why this is and it seems strange to only be needed when dealing with bit columns.

0 0

score 1 · Answer 1 · 2025-05-02T09:13:52-04:00

First of all, I'll mention the suggestion in our documentation that, "Generally, if you configure InterSystems to allow unauthenticated access [i.e. UnknownUser], it is recommended there be unauthenticated access exclusively." That is, trying to manage security for UnknownUser isn't necessarily recommended.

The documentation on granting SQL privileges on specific columns (properties) is here. I don't know enough about your situation to feel confident about whether that is what's going on, though. You can also enable auditing for %System/%SQL/PrivilegeFailure to see if that gives you more information. Feel free to open a WRC issue if you are having trouble with this.

score 0 · Answer 2 · 2025-05-02T09:40:54-04:00

Thank you this will help a ton, this is exactly what I was looking for. I'll look into implementing your suggestion instead as yes you're right, our REST codebase does manage security for UnknwonUser - looks like Delegated Authentication is what I am after.

I'll need to read into column-privileges as I had no clue this was a thing, thank you

score 0 · Answer 3 · 2025-05-06T02:22:51-04:00

%ExecDirectNoPrivs just omits the access check on prepare, access rights are still checked on execute of the SQL.

You can create a Security Role that Grants SQL access to the required storage table via System Management Studio, then assign this access role to the UnknownUser.

score 0 · Answer 4 · 2025-05-14T16:23:03-04:00

Ali Nasser · May 14

Can you share your working query and the query that is getting the privilege violation?

0 0