BigQuery row-level security with Advanced Services

Objective: A viewer of a BigQuery dashboard will see only the data relevant for them.

For example, let's assume you are trying to create a solution for your sales organization. The sales organization is divided into multiple regions where each region has its own Regional Manager. The sales dataset in BigQuery has sales amount by region and by date.

The proposed solution will have a single dashboard where the Regional Managers can view sales data only for their own regions.

Requirements

• Dashboard viewers will be signed-in with a Google account.
• A mapping is available between the user's email and the data/rows they have access to.
• A service account will be used to access BigQuery data. Thus the billing will be centralized and managed by the dashboard provider.

Limitations

• The dashboard requires a one-time authorization from each viewer on first view.
• Viewers cannot edit the dashboard or share with others.
• If you are a Workspace customer and your administrator has disabled sharing Drive files to “Anyone with a Link”, either remove the sharing restriction or develop the solution on a Gmail.com account.

Solution

Complete all the following steps to implement the solution.

Create a new Community Connector

Review How Community Connectors Work and complete the Community Connector Codelab to get started. Use the Developer tool for creating connectors for a faster and easier development process.

Write the connector code

1. getAuthType() should return NONE.
2. getConfig() should return an empty config.
   • Optional: If you need specific inputs for configuring the dashboard, you can request user input here.
3. getSchema() should return the schema for your query.
   • Optional: You can add custom fields and calculations either in the SQL query or using calculated fields as part of the schema.
4. getData() will be completed at a later step.

Update manifest

View Manifest reference and complete the manifest with all required information including the following:

1. set dataStudio.forceViewersCredentials to true.
2. set dataStudio.advancedServices.data to true.
3. For oauthScopes add https://www.googleapis.com/auth/userinfo.email and https://www.googleapis.com/auth/script.external_request. See Authorization Scopes for Apps Script for more info.
   • Conditional: Add all relevant scopes for the services used in the connector.

The manifest should look like this:

{
  ...
  "dataStudio": {
    "forceViewersCredentials": true,
    "advancedServices": {
       "data": true
    },
    ...
  }
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/userinfo.email"
    ],
  ...
}

Implement a service account

1. Create a service account in your Google Cloud project. This will be your billing project.
2. Ensure this service account has BigQuery access in the cloud project.
   • Required Identity and Access Management (IAM) Roles: BigQuery Data Viewer, BigQuery Job User
3. Download the JSON file to get the service accounts keys. Store the keys in your connector project’s script properties.
4. Include the OAuth2 for Apps Script library in your Apps Script project.
5. Implement the required OAuth2 code for the service account:

   var SERVICE_ACCOUNT_CREDS = 'SERVICE_ACCOUNT_CREDS';
   var SERVICE_ACCOUNT_KEY = 'private_key';
   var SERVICE_ACCOUNT_EMAIL = 'client_email';
   var BILLING_PROJECT_ID = 'project_id';
   
   /**
    * Copy the entire credentials JSON file from creating a service account in GCP.
    */
   function getServiceAccountCreds() {
     return JSON.parse(scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS));
   }
   
   function getOauthService() {
     var serviceAccountCreds = getServiceAccountCreds();
     var serviceAccountKey = serviceAccountCreds[SERVICE_ACCOUNT_KEY];
     var serviceAccountEmail = serviceAccountCreds[SERVICE_ACCOUNT_EMAIL];
   
     return OAuth2.createService('RowLevelSecurity')
       .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
       .setTokenUrl('https://accounts.google.com/o/oauth2/token')
       .setPrivateKey(serviceAccountKey)
       .setIssuer(serviceAccountEmail)
       .setPropertyStore(scriptProperties)
       .setCache(CacheService.getScriptCache())
       .setScope(['https://www.googleapis.com/auth/bigquery.readonly']);
   }
   

Implement getData()

1. Construct your BigQuery query.
   • Using the email, lookup the mapping between the email and the data.
   • Use JOIN and/or WHERE clause to filter the data.
2. Get the effective user's email (user identity reference).
3. Use the Looker Studio Advanced Services to return the query configuration from getData.
   • Pass the constructed query, the billing project, and the Service Account OAuth token.
   • Conditional: If you are taking user input via the connector getConfig, you should incorporate the input as BigQuery parameters.

Create the dashboard

1. Understand how deployments and versions work for connectors.
2. Create a production deployment for the connector.
3. Use the Production deployment to create a data source and a new report in Looker Studio.
4. Add all tables and charts in the report.
5. The dashboard is now ready to be shared with your users.

Make the dashboard available to users

1. Share the connector script with selected users or with “Anyone with link”.
2. Share the dashboard with selected users or with “Anyone with link”.
3. Optional: Use a URL shortener service to create a short-link for the dashboard URL. Share the shortened URL with your users. This helps to replace the dashboard URL later if needed.
4. Optional: Measure dashboard usage by setting up Google Analytics for your report.

Example Code

The following is a complete example of the connector code:

main.js

var cc = DataStudioApp.createCommunityConnector();
var scriptProperties = PropertiesService.getScriptProperties();

function isAdminUser() {
  return true;
}

function getAuthType() {
  var AuthTypes = cc.AuthType;
  return cc
    .newAuthTypeResponse()
    .setAuthType(AuthTypes.NONE)
    .build();
}

function getConfig(request) {
  var config = cc.getConfig();

  config
    .newInfo()
    .setId('generalInfo')
    .setText('This is an example connector to showcase row level security.');

  return config.build();
}

function getFields() {
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;

  fields
    .newDimension()
    .setId('region')
    .setName('Region')
    .setType(types.TEXT);

  fields
    .newMetric()
    .setId('sales')
    .setName('Sales')
    .setType(types.NUMBER)
    .setAggregation(aggregations.SUM);

  fields
    .newDimension()
    .setId('date')
    .setName('Date')
    .setType(types.YEAR_MONTH_DAY);

  return fields;
}

function getSchema(request) {
  return {schema: getFields().build()};
}

var SERVICE_ACCOUNT_CREDS = 'SERVICE_ACCOUNT_CREDS';
var SERVICE_ACCOUNT_KEY = 'private_key';
var SERVICE_ACCOUNT_EMAIL = 'client_email';
var BILLING_PROJECT_ID = 'project_id';

/**
 * Copy the entire credentials JSON file from creating a service account in GCP.
 */
function getServiceAccountCreds() {
  return JSON.parse(scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS));
}

function getOauthService() {
  var serviceAccountCreds = getServiceAccountCreds();
  var serviceAccountKey = serviceAccountCreds[SERVICE_ACCOUNT_KEY];
  var serviceAccountEmail = serviceAccountCreds[SERVICE_ACCOUNT_EMAIL];

  return OAuth2.createService('RowLevelSecurity')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setPrivateKey(serviceAccountKey)
    .setIssuer(serviceAccountEmail)
    .setPropertyStore(scriptProperties)
    .setCache(CacheService.getScriptCache())
    .setScope(['https://www.googleapis.com/auth/bigquery.readonly']);
}

var BASE_SQL =
  'SELECT d.region, d.sales, d.date ' +
  'FROM `datastudio-solutions.row_level_security.data` d ' +
  'INNER JOIN `datastudio-solutions.row_level_security.access` a ' +
  'ON d.region = a.region ' +
  'where a.email=@email';

function getData(request) {
  var accessToken = getOauthService().getAccessToken();
  var serviceAccountCreds = getServiceAccountCreds();
  var billingProjectId = serviceAccountCreds[BILLING_PROJECT_ID];
  var email = Session.getEffectiveUser().getEmail();

  var bqTypes = DataStudioApp.createCommunityConnector().BigQueryParameterType;

  return cc
    .newBigQueryConfig()
    .setAccessToken(accessToken)
    .setBillingProjectId(billingProjectId)
    .setUseStandardSql(true)
    .setQuery(BASE_SQL)
    .addQueryParameter('email', bqTypes.STRING, email)
    .build();
}