Set user permissions on data in your database

You can set up hila to respect the permissions you have set on your database views. This way, users see only the data they have permission to see.

You can set permissions based on individual users, or groups of users that hila calls roles. A role can be anything that groups users, from functional groups like “analysts” to groups based on the level of access like “read-only” or “admin”, or groups based on department or business unit.

The general flow is:

1. In your database, create a set of views with permissions defined for users and roles from the database perspective. For example, you might set a view to allow one group of users to see a set of rows in your database and another group of users to see different rows.

   The steps for doing this depends on the database you are using. See the documentation for your database.

2. In the hila monitoring app, create users and roles that match the users and roles in your database views.

   See Users, Roles, & Permissions.

3. Run the hila notebook that sets the permissions for the users and roles in the database views.

Running the hila notebook, an example

hila provides a notebook that creates a views to an internal Clickhouse database and sets a user to have read-only access to those views.

1. Open the edahub service in hila. See Open edahub.

2. In edahub, open the notebook /notebooks/public/hila_dataloading/cf_canonical/cf_limited_permissions.ipynb.

3. In the cell under configuration, set the following variables:
   • username — the username of the admin who has full permissions to the database.
   • password — the password of the admin.
   • original_database_name — the name of the database you want to restrict access to.
   • original_user — the database username of the admin who has full permissions to the database.
   • original_password — the database password of the admin.
   • host — the database system name.
   • new_db_name — the name of the new database that contains the read-only views.
   • new_user — the username or name of the role of the users who will have read-only access to the views.
   • new_password — the password of the new user or role.

4. In following cells, the notebook creates the connection to the database views.

5. In the cell under create views, set the following variables:

   • views — a list with the names of the views you want to create.
   • where_clauses — a list of the where clauses for the views. The where clauses restrict the data that the user can see. For example, if you have a view that shows all the rows in a table, you can set a where clause to restrict the rows to only those that have a certain value in a certain column.

6. In the following cells, the notebook checks the views and verifies the connection for the read-only user.

7. The notebook then logs in to hila, creates the connection to the new database, and it finally creates the metadata against which users can ask questions, the users being restricted to the data in the views you defined in this procedure.