User-Specific Connections with LDAP (3.48+)


(Nate Pickens) #1

Note: As of Looker 4.8, we recommend that you parameterize connections using User Attributes.

Overview

By default, Looker connects to SQL databases using a single set of credentials. With user-specific connections, a connection can be configured to use a set of credentials specific to the user running a query. This allows user-specific, database-level permissions to be applied to queries run in Looker, as well as query auditing at the database level.

Prerequisites

At this time, Looker supports user-specific querying functionality by way of users’ LDAP credentials, which means in order to take advantage of user-specific connections, the following conditions must be met:

  • Looker must be configured to use LDAP for user authentication.
  • Every user must log out then log in with LDAP if that user has been created prior to LDAP configuration.
  • Each user’s database credentials must match their LDAP credentials.
  • User configuring this connection must be logged in with LDAP.

Additionally, it is important that Looker be able to stop queries in certain situations. It is not uncommon for users to initiate long-running/resource-intensive queries in Looker, and also not uncommon for users to walk way from those queries (navigate to a different page, close their browser tab, etc.). In those situations, Looker automatically runs a SQL statement to kill the query behind the scenes. Since that SQL statement is also issued using the user’s credentials to connect to the database, the following is very important:

  • Each database user must have permission to kill their own queries.

Setup

Looker Hosted

If your instance is hosted on one of Looker’s servers, Looker will do most of the configuration for you.

  1. Ensure that your Looker instance is configured to use LDAP for user authentication.
  2. Email support@looker.com to request that user-specific connections be enabled and configured for your Looker instance.

On Premise

If Looker is hosted on your own server, you will still need to have the feature enabled on your license. Once that is done, configuration should only take a few minutes.

  1. Ensure that your Looker instance is configured to use LDAP for user authentication.
  2. Email support@looker.com to request that user-specific connections be enabled on your Looker license.
  3. Shut down Looker.
  4. Delete all existing user sessions by running ./looker logout-all-users from the main Looker directory on the server where Looker is installed. If you do not have a looker shell script, use java -jar looker.jar logout-all-users.
    Why is this necessary? Looker does not normally store users’ LDAP credentials (they are simply verified when a user logs in and then discarded). With user-specific connections enabled, Looker will save each user’s (encrypted) LDAP credentials when they log in so they can be retrieved and used when connecting to a database. Deleting all user sessions forces every user to log back in, ensuring that Looker then saves their LDAP credentials.
  5. Update your looker shell script to start Looker with the --user-db-credentials command line option. If you do not have a shell script, be sure to include the command line option whenever Looker is started (java -jar looker.jar start --user-db-credentials). See the Looker Startup Options documentation for more details.
  6. Start Looker.

Configuring a Connection

User-specific credentials are enabled on a per-connection basis, and can be enabled on both existing and new connections. Simply edit the desired connection or create a new one from the Admin > Connections panel and check the “Per User Credentials” checkbox:

That’s it! From here on a user’s LDAP credentials will be used to authenticate to the database each time they run a query against that connection.

Notes on Transitioning to User-Specific Connections

As mentioned above, a user must log in at least once following user-specific connections being enabled in order for Looker to capture their LDAP credentials. There are a few contexts in which Looker will attempt to run a query with a user’s credentials even if they are not logged in, which means that under these contexts those queries will produce errors until the user has re-logged in.

  • Scheduled tasks — Scheduled tasks run as the user who created the schedule. Therefore any scheduled task which runs one or more queries against a connection that is configured for user-specific credentials will result in an error until the user has logged in again.
  • Admins sudo-ed as another user — An admin who sudos as a user who has not yet re-logged in will see an error about missing credentials if they attempt to run a query against a connection configured for user-specific credentials.

Disabled Features

At this time, Looker does not support any form of a “service account” for user-specific connections. That is, any query against a connection configured for user-specific credentials must always have a specific user’s credentials. Consequently features in Looker which run outside the context of a user are disabled for user-specific connections.

  • Public Looks — Since public URLs are accessed without any user authentication, they cannot be used with Looks that run against a user-specific connection. Note that any Look can still be made public, but those that run against connections with user-specific credentials will return an error when accessed.
  • Persistent Derived Tables (PDTs) — Since PDTs are generated automatically by Looker (i.e. outside the request of a user), there are no user credentials available when the queries are run. They are therefore disabled for any model that uses a connection configured for user-specific credentials. The LookML Validator will return a fatal error for any PDT found in the model.

Other Notes

Derived Tables (non-persistent) will continue to work
Non-persistent derived tables are generated at query run time, so they will continue to function like normal. Note for MySQL connections: each database user will need to be granted select permission on the temporary schema, as MySQL uses the temporary schema even for derived tables that are not persisted (see the MySQL configuration doc, specifically the “Temp Schema Setup for Persistent Derived Tables” section).

Users who log in without LDAP credentials cannot run queries against user-specific connections
If all users in Looker are logging in with their LDAP credentials, this is of no concern. But if there are any Looker accounts that were created by manually adding email addresses, those users will not be able to run queries against user-specific connections (other queries will run just fine).

Queries are cached on a per-user basis
Normally in Looker, query cache lookups are based on the SQL used to run the query. That is, if Alice runs a query and Bob runs the same query shortly after, he will receive the cached results generated from Alice’s run.

With user-specific connections, however, Bob may have different permissions than Alice at the database level. So to ensure data is not leaked, cache entries are specific to each user. That is, although the SQL generated for Bob’s query may be exactly the same as Alice’s, he will get a cache miss and the query will be rerun, thus ensuring he does not see data that Alice has permission to that he does not.

Scheduled tasks run as the user who created the schedule
This is no different than how Looker operates normally. But it is important to note that if Alice creates a schedule and adds Bob and Carol as recipients, Bob and Carol will receive the same results as Alice even if they do not have the same database-level permissions as Alice.


Looker 3.48 Release Notes
Configuring Delegation on Impala using User Attributes