Manage SQL accesses to all or some schemas in a database, instead of managing it a the tables level.
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.
Comments
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?KEY=RSQL_grant
You're absolutely right @Alexander Koblov
Using GRANT statements does the job perfectly well 😊
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 :
.png)
Many thanks for pointing me towards this feature that I simply hadn't thought of.
It even works with all objects in a namespace :
GRANT SELECT ON * TOROLE|USER
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.