Authenticating with a user account for installed apps

This guide explains how to authenticate by using user accounts for access to the BigQuery API when your app is installed onto users' machines.

To ensure the app accesses only BigQuery tables that are available to the end user, authenticate by using a user credential. A user credential can run queries against only the end user's Google Cloud project rather than the app's project. As a result, the user is billed for queries instead of the app.

Before you begin

Create a Google Cloud project that represents your installed app.
Install the BigQuery client libraries.
Install required libraries.
Python
Install the oauthlib integration for Google Auth.
```
pip install --upgrade google-auth-oauthlib
```
Node.js
Install the oauthlib integration for Google Auth.
```
npm install google-auth-library
npm install readline-promise
```

Setting up your client credentials

Use the following button to select a project and create the required credentials.

Get Credentials

Manually creating credentials

Go to the API Credentials page in the Google Cloud Console.
Fill out the required fields on the OAuth consent screen.
On the Credentials page, click the Create credentials button.
Choose OAuth client ID.
Select Other as the app type, and then click Create.
Download the credentials by clicking the Download JSON button.

Save the credentials file to client_secrets.json. This file must be distributed with your app.

Authenticating and calling the API

Use the client credentials to perform the OAuth 2.0 flow.

Python

View on GitHub Feedback

from google_auth_oauthlib import flow

# TODO: Uncomment the line below to set the `launch_browser` variable.
# launch_browser = True
#
# The `launch_browser` boolean variable indicates if a local server is used
# as the callback URL in the auth flow. A value of `True` is recommended,
# but a local server does not work if accessing the application remotely,
# such as over SSH or from a remote Jupyter notebook.

appflow = flow.InstalledAppFlow.from_client_secrets_file(
    'client_secrets.json',
    scopes=['https://www.googleapis.com/auth/bigquery'])

if launch_browser:
    appflow.run_local_server()
else:
    appflow.run_console()

credentials = appflow.credentials

Node.js

View on GitHub Feedback

const {OAuth2Client} = require('google-auth-library');
const readline = require('readline-promise').default;

function startRl() {
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });

  return rl;
}

/**
 * Download your OAuth2 configuration from the Google
 * Developers Console API Credentials page.
 * https://console.cloud.google.com/apis/credentials
 */
const keys = require('./oauth2.keys.json');

/**
 * Create a new OAuth2Client, and go through the OAuth2 content
 * workflow. Return the full client to the callback.
 */
async function getRedirectUrl() {
  const rl = main.startRl();
  // Create an oAuth client to authorize the API call.  Secrets are kept in a `keys.json` file,
  // which should be downloaded from the Google Developers Console.
  const oAuth2Client = new OAuth2Client(
    keys.installed.client_id,
    keys.installed.client_secret,
    keys.installed.redirect_uris[0]
  );

  // Generate the url that will be used for the consent dialog.
  const authorizeUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: 'https://www.googleapis.com/auth/bigquery',
    prompt: 'consent',
  });

  console.info(
    `Please visit this URL to authorize this application: ${authorizeUrl}`
  );

  const code = await rl.questionAsync('Enter the authorization code: ');
  const tokens = await main.exchangeCode(code);
  rl.close();

  return tokens;
}

// Exchange an authorization code for an access token
async function exchangeCode(code) {
  const oAuth2Client = new OAuth2Client(
    keys.installed.client_id,
    keys.installed.client_secret,
    keys.installed.redirect_uris[0]
  );

  const r = await oAuth2Client.getToken(code);
  console.info(r.tokens);
  return r.tokens;
}

async function authFlow(projectId = 'project_id') {
  /**
   * TODO(developer):
   * Save Project ID as environment variable PROJECT_ID="project_id"
   * Uncomment the following line before running the sample.
   */
  // projectId = process.env.PROJECT_ID;

  const tokens = await main.getRedirectUrl();

  const credentials = {
    type: 'authorized_user',
    client_id: keys.installed.client_id,
    client_secret: keys.installed.client_secret,
    refresh_token: tokens.refresh_token,
  };

  return {
    projectId,
    credentials,
  };
}

Use the authenticated credentials to connect to the BigQuery API.

Python

View on GitHub Feedback

from google.cloud import bigquery

# TODO: Uncomment the line below to set the `project` variable.
# project = 'user-project-id'
#
# The `project` variable defines the project to be billed for query
# processing. The user must have the bigquery.jobs.create permission on
# this project to run a query. See:
# https://cloud.google.com/bigquery/docs/access-control#permissions

client = bigquery.Client(project=project, credentials=credentials)

query_string = """SELECT name, SUM(number) as total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE name = 'William'
GROUP BY name;
"""
query_job = client.query(query_string)

# Print the results.
for row in query_job.result():  # Wait for the job to complete.
    print("{}: {}".format(row['name'], row['total']))

Node.js

View on GitHub Feedback

async function query() {
  const {BigQuery} = require('@google-cloud/bigquery');

  const credentials = await main.authFlow();
  const bigquery = new BigQuery(credentials);

  // Queries the U.S. given names dataset for the state of Texas.
  const query = `SELECT name, SUM(number) as total
  FROM \`bigquery-public-data.usa_names.usa_1910_current\`
  WHERE name = 'William'
  GROUP BY name;`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));

  return rows;
}

const main = {
  query,
  authFlow,
  exchangeCode,
  getRedirectUrl,
  startRl,
};
module.exports = {
  main,
};

if (module === require.main) {
  query().catch(console.error);
}

When you run the sample code, it launches a browser that requests access to the project that is associated with the client secrets. You can use the resulting credentials to access the user's BigQuery resources because the sample requested the BigQuery scope.

What's next

Learn about other ways to authenticate your app to access the BigQuery API.
Learn about authentication with end user credentials for all Google Cloud APIs.