Organizations often want to grant users access to run their own ad-hoc queries with Looker's SQL Runner tool, but sometimes with more limited access. Perhaps a user should only be able to query one specific connection in SQL Runner, or only have access to a few connections. With a combination of Looker roles and database-level permissions, you can make user SQL Runner access as specific as needed.
Implementation
In this example, we are creating a user with limited SQL Runner permissions to restrict them to one specific connection.
Note: This approach will not be applicable for users that have an admin role, or users that otherwise have been granted the manage_models
permission.
- Database permissions: Outside of Looker, set up the more limited user (or users) and permissions on your database.
- Create a "limited" connection in Looker: From the Connections page in the Admin panel, add a new connection specifically for the restricted database user. Alternatively, you can leverage parameterized connections on an already existing connection to make the connection specific to the user running a query.
- Create a "limited" LookML model: Because the end goal of creating limited SQL Runner permissions is essentially unrelated to any actual LookML modeling, it is cleanest to create a separate project and model specifically for the limited connection. This model will help us create the appropriate model set and permission set in later steps:
- From the Develop menu, select Manage LookML Projects > New LookML Project:
- Next, from the Develop drop-down menu, select Manage LookML Projects > Configure New Model:
- Finally, add LookML to the model file for the model to be available for configuring permissions:
- From the Develop menu, select Manage LookML Projects > New LookML Project:
- Now, we will create and assign a limited Model Set and Permission Set to complete the process.
- To create a
limited_connection
model set, navigate to the new model set page in the Admin panel: From the Admin drop-down menu, select Admin > Roles > New Model Set (or navigate to the page by appending/admin/model_sets/new
to your instance URL):
- Create a
sql_runner
permission set: From the Admin drop-down menu, select Admin > Roles > New Permission Set (or navigate to the page by appending/admin/permission_sets/new
to your instance URL):
- Create a
limited_model_x_sql_runner
role: From the Admin drop-down menu, select Admin > Roles > New Role (or navigate to the page by appending/admin/roles/new
to your instance URL):
- Assign the role to users. Typically, we highly recommend assigning roles to groups for easier management. In this example, however, we will grant the role directly to a user:
- To create a
Now this user is able to explore the full connection:
But, can only query the limited-connection
in SQL Runner: