SQL Privileges management

Question

Question

Nael Nasereldeen · Jan 1, 2019

Hi,

When Creating a custom security role, what privileges do I need to add to it, in order for the users that I grant him the role, to be able to use

dynamic SQL to perform read only queries on all tables in a namespace?

I have a security role, that contains the %Service_SQL privilege,

And yet when I connect to a terminal and try to run an SQL query using dynamic SQL , I get a "user is not privileged for the operation" error.

reading the documentation , I understand that %Service_SQL privilege privilege is not enough, but I don't understand what privilege to add,

I can't add specific tables in the "SQL Tables" section of the role definition, I need all the tables to be accessible.

this the documentation I mentioned:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

Regards,

Nael

Discussion (7)2

Log in or sign up to continue

Pravin Barton · Jan 2, 2019

You can add table-level privileges on all tables in the namespace by running "GRANT Select ON * TO Test", where Test is the name of the user or role. But if a new table is later added they won't automatically get access to it. I don't know of a way to do that with SQL privileges short of giving them %All.

0 0

score 0 · Answer 1 · 2019-01-03T01:57:26-05:00

Nael Nasereldeen · Jan 3, 2019

thank you very much Parvin!

Regards,

Nael

0 0

score 0 · Answer 2 · 2019-01-07T22:19:12-05:00

Oh, and I highly recommend enabling auditing and looking at the audit log to debug while configuring security. To do so, in the Management Portal, use System Administration > Security > Auditing.

score 0 · Answer 3 · 2019-01-09T05:43:41-05:00

Nael Nasereldeen · Jan 9, 2019

thank you Samuel for elaborating with more useful information.

0 0

score 0 · Answer 4 · 2019-02-26T14:00:14-05:00

Kyle! Really great staff!

This is also very helpful for DeepSee/IRIS Analytics projects, where you every time need to expand the access rules on yet another listing table.

score 0 · Answer 5 · 2019-01-07T22:15:43-05:00

Most likely the reason you can't connect to terminal is the user does not have use on the %Development and %Service_Terminal resources, and Read on whatever resource protects the default database for the default namespace of the user via a role or public permission. However, be careful here because then they can run almost code in the namespace that only requires Read access. So, you are giving them more privileges than you may realize.

As far as only granting select on all tables in a namespace, that is difficult to future proof as Pravin mentioned. An option would be to not give any specific permissions on tables and to only give Read on the resource(s) protecting the database(s) containing the tables. However, this would then give them read through OOP.

As far as I know, to truly only give someone SQL select on all tables in a namespace, they'd have to connect through an xDBC client. You'd need to give them select on all tables through the tables tab of the role (and update whenever there's a new table). That's all they need. Fun fact, if a user has a SQL privilege on a table, role escalation while running a SQL statement will occur to give them Read/Write on the resource protecting the database and the %SQL role grant Use on %Service_SQL. But, terminal is more than running SQL so the built in role escalation is insufficient. That's why you would need to do what I explained in the first paragraph if you want them to be able to run SQL in the terminal

score 1 · Answer 6 · 2019-02-25T09:14:03-05:00

I do know a way to add permissions on a table that doesn't exist yet - so I'm adding on to Pravin's answer.

GRANT SELECT ON SCHEMA SQLUser TO MyRole

Then any table added to the SQLUser schema will be accessible to the role "MyRole" when that table is added. Since Schemas (or schemata) are how we split up tables into more generic categories, this is a nice way to also spit up your permissions.