New post
Question
· Oct 13, 2021

Implicitly Grant SELECT to Custom Role for All Classes in Pkg Path

#Message Search #Security #SQL #InterSystems IRIS #InterSystems IRIS for Health

Good morning -

As we're starting to create more custom message classes to represent out JSON-based integrations, I was pondering how to implicitly grant SELECT privileges to a specific Security Role so they can utilize Message Viewer to search through the message history.

So if all of our custom JSON message classes are under OurParentPkg.Messages.REST.* (e.g., OurParentPkg.Messages.REST.AddPatientRequest and OurParentPkg.Messages.REST.AddPatientResponse), is there a method to ensure that our analysts can be granted the SELECT permission to anything created under OurParentPkg.Messages.REST without needing to go into the Role and explicitly granting it with each and every new class?

I poked through the documentation in August a bit on this, playing with the SQL properties on the package tree but didn't get much traction. Hoping someone else has thought through this more than I've had the time to do and can share how they accomplished it.

 

Thanks in advance,

Craig

Product version: IRIS 2021.1
$ZV: IRIS for UNIX (IBM AIX for System Power System-64) 2021.1 (Build 215U) Wed Jun 9 2021 09:55:44 EDT [Health:3.3.0]
Discussion (6)2
Log in or sign up to continue
Tani Frankel · Oct 21, 2021

Hi Craig,

Perhaps this could help -

From: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_grant

You can use SCHEMA schema-name as the object-list value to grant the object-privilege to all of the tables, views, and stored procedures in the named schema, in the current namespace. For example, GRANT SELECT ON SCHEMA Sample TO Deborah grants this user SELECT privilege for all objects in the Sample schema. This includes all objects that will be defined in this schema in the future. You can specify multiple schemas as a comma-separated list; for example, GRANT SELECT ON SCHEMA Sample,Cinema TO Deborah grants SELECT privilege for all objects in both the Sample and the Cinema schemas.

0 0
Craig Regester · Oct 27, 2021

Thanks, this does work... it's not quite as granular as I'd like it to be...  Example, I can can do the following:

GRANT SELECT ON * TO MyAnalystRole

Within the appropriate namespace and that indeed grants SELECT to all tables in that namespace. but if I have packages like MyPkg.Messages.VendorA, MyPkg.Messages.VendorB, I can't do:

GRANT SELECT ON MyPkg.Messages.* TO MyAnalystRole

I think I would have to update the Pkging of VendorA and VendorB to roll up to the MyPkg.Messages schema.

0 0
Tani Frankel · Oct 27, 2021

Hi Craig,

I'm not 100% sure this is the problem you are seeing (you might be aware of this already) - but worth a try - 

When you define an OO package name (and sub-packages) for your classes, these "project" to SQL Schema names a little differently, so instead of "dots" (.) in your full package name you would have underscores (_) in your schema name.

For example PackageA.PackageB.Class would be PackageA_PackageB.Class - so the schema name would be PackageA_PackageB (and not PackageA.PackageB).

See here for example about this in the Documentation.

0 0
Craig Regester · Oct 27, 2021

Yeah, understood on the projection (. -> _) but I see I reflected it wrong on my response - was trying it too quickly this morning when I got an auto-email bugging me to evaluate the reply as an answer. :)

So I tried this numerous ways actually and here's the errors I get (they vary based on the approach):

GRANT SELECT ON MyPkg_Messages.* TO myRole

ERROR #5540: SQLCODE: -1 Message:  IDENTIFIER expected, * found^GRANT SELECT ON MyPkg_Messages.*

GRANT SELECT ON MyPkg_Messages_* TO myRole

ERROR #5540: SQLCODE: -1 Message:  TO expected, * found^GRANT SELECT ON MyPkg_Messages_*

So then I thought... well, maybe if I just grant to MyPkg_Messages and leave off the * it'll cascade?

GRANT SELECT ON MyPkg_Messages TO myRole

ERROR #5475: Error compiling routine: %sqlcq.MYNS.cls41.  Errors:  %sqlcq.MYNS.cls41.cls
ERROR:%sqlcq.MYNS.cls41.1(14) : SQLCODE=-30 : Table 'SQLUSER.MYPKG_MESSAGES' not found

:( This leads me to why I think I need to 'roll up the packaging' of the classes to a higher level as really the message class itself might be:

MyPkg_Messages_REST.AddPatientRequest or something like that.

Appreciate the responses though - I haven't had time to play with this more and the first approach you called out will work for most of my cases anyway. 

0 0
Tani Frankel · Oct 28, 2021

Ok, Carig, so sorry about the superfluous explanation about the SQL Package to Schema name mapping...

But now I do understand the problem better -

First, as you realized you don't need the asterisk (*), just the name of the schema.

And second you need to use the term SCHEMA in your statement before the schema name, or else we expect a table name (hence the error you got - table ... not found).

So per the example I quoted originally above from the documentation:

GRANT SELECT ON SCHEMA Sample TO Deborah

In your example this would be:

GRANT SELECT ON SCHEMA MyPkg_Messages TO myRole

I just added the word 'SCHEMA' before the schema name.

Hope this will get it to work for you.

0 0