Maintaining Privilege grants via configuration management

1

I’m using Dremio Cloud.
I want to define who has access to what folder via configuration files that I store in git and can run at any time (idempotent).

What is the best way to do this?

I see these 2 tables that I would have thought would show the current grants by user:

  • sys.project.privileges
  • sys.organization.priviliges

However, they don’t seem to change when I execute “grant” sql statements, even though the permissions are indeed updated for the user.

Do I need to use the API to view and manage privileges?

To recap, my goal is to use config files to define what users have what permissions on which objects/folders/datasources. Also, let me know if you are aware of any good python libraries or projects that might make this an easier task.

2

I have created a python script that does this. It works well.

Of note, the sys.project.privileges table does not seem to show privilege grants for the Arctic catalog; it only shows those for datasources. So, I had to use the API to get all the grants and then diff them with the grants I want to be there, and then I submit SQL statements to grant privileges to users.

3

@capnjosh I have been thinking on same lines and was going to explore maintaining the SQL (that grants privilege) in version control before considering API as last option. Was there any particular reason you preferred python API over SQL or you are saying you are sending SQL statements from config version control via API to Dremio

4

The current state is that you will have to get the current grants from 3 different places:

  • API for Arctic Catalog object privileges
  • sys.project.privileges for datasource privileges with a project
  • sys.organization.privileges for privileges on the project itself

That is, currently you will have to use the API to find out what the current privileges are on the Arctic catalog.

5

@capnjosh @rdkworld Currently, Arctic privileges don’t show up in “sys. privileges”

6

Here are the sources I use to get the current privileges:

  • pull sys.projects.privileges to get data source privileges
  • pull sys.organization.privileges to get project privileges
  • use the API and loop through projects > Arctic catalogs > folders to get privileges on the Arctic catalog
7

Thanks Balaji and Josh!