Manage SQL accesses to all or some schemas in a database, instead of

Discussion

Sylvain Guilbaud · Sep 11, 2023

#Security #SQL #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health

Currently, the SQL privileges (SELECT, INSERT, UPDATE, DELETE) are managed at the tables level, which can be very tedious when you have to administer many roles in an organization, and need to keep them sync with a constantly evolving data models.
By managing privileges at the schemas level, will allow to give SELECT and other DML privileges to *all* or *several schemas* to a role|user, fixing the need to manually synchronize the new tables|views to the roles.

If you agree, I invite you to vote for this idea.

Discussion (6)2

Alexander Koblov · Sep 11, 2023

I believe IRIS already can GRANT privileges on schema:

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. The following example grants this user SELECT privilege for all objects in the Sample schema.

GRANT SELECT ON SCHEMA Sample TO Deborah

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

3 0

Sylvain Guilbaud · Sep 11, 2023

You're absolutely right @Alexander Koblov
Using GRANT statements does the job perfectly well 😊

0 0

Sylvain Guilbaud · Sep 11, 2023

And the cool thing is that as soon as your user has received a GRANT on a schema, when you edit afterwards the user in the Security Management portal, you can see on the SQL Tables tab all the tables appear as they are created :

0 1

Sylvain Guilbaud · Sep 11, 2023

Many thanks for pointing me towards this feature that I simply hadn't thought of.

1 0

Sylvain Guilbaud · Sep 12, 2023

It even works with all objects in a namespace :

GRANT SELECT ON * TO ROLE|USER

0 0

David Hockenbroch · Sep 13, 2023

Do so at your own risk, but if you use _PUBLIC as the role/user argument, then all users will have the granted permissions as soon as they are created.

0 0

Manage SQL accesses to all or some schemas in a database, instead of managing it a the tables level.