How to Automatically Grant Access For SQL Tables Which Are Involved in Listing of a Widget

Question

Question

Evgeny Shvarov · Sep 27, 2017

#Dashboards #Access control #Security #SQL #InterSystems IRIS BI (DeepSee)

Hi, folks!

When you deploy DeepSee solutions you often do not want grant a User %All Role to work with a particular Dashboard.

Consider a Dashboard 'Dash' with a few widgets where listings are being used.

If you manage a Role to get access to the Dash you need to grant access to %DB_DBNAME resource to have a database access, grant access to a Dashboard resource (if any) and ... grant SELECT accesses to all the tables involved in SQL queries being used in all the listings of widgets.

And every time(!) when you update a listing you need to rearrange that access level in a Role (either grant new or revoke not used anymore) to the tables.

How do you manage that? Manually? Or you have a magic shortcut script or recipes in hands? Would you please share?

Discussion (3)1

Log in or sign up to continue

Kyle Baxter · Sep 28, 2017

Currently the only way to give permissions on "Future" tables (that is, tables you haven't created yet) is to grant permissions on the schema and then add tables to that schema.

Note that when you assign SQL Privileges, you should do so to a Role. The InterSystems security model is such that you grant resources to roles, and roles to users. SQL Permissions are resources that you should grant to Roles.

2 0

score 1 · Answer 1 · 2017-09-28T03:12:09-04:00

If the owner of a table is _PUBLIC, users do not need to be granted object privileges to access the table.

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

based on that you can build an OWNER that has also limited rights (only SELECT, REFERENCES, .. or whatever)

So you do it for this Owner only

score 0 · Answer 2 · 2017-09-28T06:23:00-04:00

Currently the only way to give permissions on "Future" tables (that is, tables you haven't created yet) is to grant permissions on the schema and then add tables to that schema.

Hi, Kyle! I do not want to grant permissions to "Future" tables, though your suggestion is interesting and helpful. The question was if anyone has a kind of wise parser which examines all the Listings in all widgets of a Dashboard and parse FROM clause to grant permissions to tables listed there.