Tutorial

Limiting SQL Runner Permissions

  • 10 May 2022
  • 0 replies
  • 268 views

Userlevel 7
Badge

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:

 


0 replies

Be the first to reply!

Reply