SQLCODE: -99 when executing dynamic SQL on specific properties
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.
Comments
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.
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
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.
%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.
Can you share your working query and the query that is getting the privilege violation?