Limiting SQL Runner Permissions

[fabio1]

Tutorial

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

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.

1. Database permissions: Outside of Looker, set up the more limited user (or users) and permissions on your database.
2. 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.
    

   

3. 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:
      

     

4. 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:
      

     

Now this user is able to explore the full connection:

But, can only query the limited-connection in SQL Runner: