Dynamic Connection Switching in Looker for Embedded Dashboards

[prashant_nakum]

Hi,

I have embedded a Looker dashboard using SSO embedding within a product that serves multiple customers. Each customer has their own separate database, but all databases follow the same schema. The goal is to dynamically switch the database connection based on the user accessing the embedded dashboard.

For example:
Customer 1 should use db-1
Customer 2 should use db-2

Since the dashboard structure is the same for all customers, I need a way to ensure that each user sees only their own data while keeping database connections separate for privacy reasons.

Approach I Considered:

I explored the option of using user attributes to store connection details (e.g., username, password, dataset). These attributes are then passed dynamically when generating the embed URL, allowing Looker to use the correct connection for each user.

However, this method requires passing database credentials with every API call, which raises security concerns.

Question:
Is there a better way to dynamically switch connections in a Looker model based on the user, without exposing credentials in every request?

Any best practices or alternative approaches would be highly appreciated!

Thanks,
Prashant

[GavinW]

Just to clarify, have you tried using the approach detailed here in the documentation?

[prashant_nakum]

Thanks for your quick response!

Yes, I have reviewed the documentation, and I check that we can dynamically change the schema and table names based on user attribute values.

However, our use case is slightly different. In our scenario, each user has their own separate database, though the schemas and table structures are identical across all databases. This means that when a user accesses the dashboard:

User 1 should connect to database_1
User 2 should connect to database_2

The challenge is dynamically switching the entire database connection based on the user, rather than just modifying the schema or table name within a single connection.

Is there a way to achieve this in Looker, without exposing credentials in every request of embed URL creation?

[GavinW]

From the documentation on user attributes, "The host, port, database, username, password, and schema of  connection can each be given the value of a user attribute." Would this approach work for you?

I think the difference here is that it would handled on the server side rather than passed through the API call as you mention above.

https://cloud.google.com/looker/docs/admin-panel-users-user-attributes#database_connections

[prashant_nakum]

Apologies for the delayed update.

We can assign the values for host, port, database, username, and password as user attributes, which can be set from the server side. However, every customer needs to provide these fields.

For SSO-embedded URL creation, we call an API where we must pass these fields in the each request to set them as user attributes.

Is there an alternative approach that allows us to dynamically change the connection without needing to pass all these fields in the API call each time?

[GavinW]

If you were taking this approach, I think you would only need to pass the name of the database through, rather than all of those elements? Because the connection could rely on using the same password, host, port etc.?

[prashant_nakum]

We have only one connection available in Looker, where we dynamically modify the host, port, database, username, and password to switch database connections based on the user.

As you mentioned, passing only the database would not be sufficient because each user has a unique username, password, host, and port.

Therefore, whenever a user accesses the dashboards, we must pass their specific username, password, host, port, and database to establish the correct connection.

[GavinW]

Ah, I understand - I thought it would be possible for you to re-use the same connection parameters and only change the DB name, as the other parameters would be invisible to anyone outside your organisation anyway.

I can't think of another way to handle this, unfortunately, but perhaps someone else can.