Craig Regester · Oct 13, 2021

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

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,


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]
0 178
Discussion (6)2
Log in or sign up to continue

Hi Craig,

Perhaps this could help -


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.

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


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.

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.

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. 

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:


In your example this would be:


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

Hope this will get it to work for you.

No apologies necessary! Ugh, I see I forgot to drop in the SCHEMA key word. Let me give that a try today when I get a moment and report back.