Authorized routines

Authorized routines let you share query results with specific users or groupswithout giving them access to the underlying tables that generated the results.For example, an authorized routine can compute an aggregationover data or look up a table value and use that value in a computation.

By default, if a user invokes a routine, the user must have access to read thedata in the table. As an alternative, you canauthorize the routineto access the dataset that contains the referenced table. An authorized routinecan query the tables in the dataset, even if the user who calls the routinecan't query those tables directly.

The following types of routines can be authorized:

Caution: Stored procedures authorized as routines have DDL and DML access.These procedures can create, modify, and delete database objects. Principalswith access to authorized stored procedures can bypassIdentity and Access Management (IAM) permissions and perform actions that are normallydenied to them. Only grant authorized stored procedure access to principalsthat you trust to run the procedure in its entirety.

Authorize routines

To authorize a routine, use the Google Cloud console, the bq command-line tool, or the REST API:

Console

  1. Go to the BigQuery page in the Google Cloud console.

    Go to BigQuery

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, clickExpand left pane to open the pane.

  3. In theExplorer pane, expand your project, clickDatasets, andthen select a dataset.

  4. In the details pane, clickShare> Authorize Routines.

  5. In theAuthorized routines page, in theAuthorize routinesection, select theProject,Dataset, andRoutine for theroutine that you want to authorize.

  6. ClickAdd authorization.

bq

  1. Use thebq show command to get the JSON representation of the datasetthat you want the routine to access. The output from the command is aJSON representation of theDataset resource.Save the result to a local file.

    bqshow--format=prettyjsonTARGET_DATASET >dataset.json

    ReplaceTARGET_DATASET with the name of the dataset thatthe routine can access.

  2. Edit the file to add the following JSON object to theaccess array intheDataset resource:

    {"routine":{"datasetId":"DATASET_NAME","projectId":"PROJECT_ID","routineId":"ROUTINE_NAME"}}

    Replace the following:

    • DATASET_NAME: the name of the dataset thatcontains the routine.
    • PROJECT_ID: the project ID of the project thatcontains the routine.
    • ROUTINE_NAME: the name of the routine.

  3. Optional: If you are authorizing astored procedure, attach an IAM role. This role restrictsaccess to the authorized procedure based on its permissions. To do this,add"role" to the JSON object:

    {"role":"ROLE_NAME","routine":{"datasetId":"DATASET_NAME","projectId":"PROJECT_ID","routineId":"ROUTINE_NAME"}}

    ReplaceROLE_NAME with the name of the role thatyou want to attach. You can attach the following roles to a storedprocedure:

    Note: You can attach these roles only to stored procedures. Other typesof routines don't support roles.

  4. Use thebq update command to update the dataset:

    bqupdate--sourcedataset.jsonTARGET_DATASET

API

  1. Call thedatasets.getmethod to fetch the dataset that you want the routine to access. Theresponse body contains a representation of theDataset resource.

  2. Add the following JSON object to theaccess array in theDatasetresource:

    {"routine":{"datasetId":"DATASET_NAME","projectId":"PROJECT_ID","routineId":"ROUTINE_NAME"}}

    Replace the following:

    • DATASET_NAME: the name of the dataset thatcontains the UDF.
    • PROJECT_ID: the project ID of the project thatcontains the UDF.
    • ROUTINE_NAME: the name of the routine.

  3. Optional: If you are authorizing a stored procedure, attach anIAM role. This role restricts access to the authorizedprocedure based on its permissions. To do this, add"role" to the JSONobject:

    {"role":"ROLE_NAME","routine":{"datasetId":"DATASET_NAME","projectId":"PROJECT_ID","routineId":"ROUTINE_NAME"}}

    ReplaceROLE_NAME with the name of the role thatyou want to attach. You can attach the following roles to a storedprocedure:

    Note: You can attach these roles only to stored procedures. Other typesof routines don't support roles.

  4. Call thedataset.updatemethod with the modifiedDataset representation.

Note: If you modify a routine by running aCREATE OR REPLACE statement(CREATE OR REPLACE FUNCTION,CREATE OR REPLACE PROCEDURE,CREATE OR REPLACE TABLE FUNCTION),or by calling theroutines.update method, then you must re-authorize the routine.

Quotas and limits

Authorized routines are subject to dataset limits. For more information, seeDataset limits.

Authorized routine example

The following is an end-to-end example of creating and using an authorized UDF.

  1. Create two datasets namedprivate_dataset andpublic_dataset. For moreinformation about creating a dataset, seeCreating a dataset.

  2. Run the following statement to create a table namedprivate_table inprivate_dataset:

    CREATEORREPLACETABLEprivate_dataset.private_tableASSELECTkeyFROMUNNEST(['key1','key1','key2','key3'])key;

  3. Run the following statement to create a UDF namedcount_key inpublic_dataset. The UDF includes aSELECT statement onprivate_table.

    CREATEORREPLACEFUNCTIONpublic_dataset.count_key(input_keySTRING)RETURNSINT64AS((SELECTCOUNT(1)FROMprivate_dataset.private_tabletWHEREt.key=input_key));

  4. Grant thebigquery.dataViewer role to a user on thepublic_datasetdataset. This role includes thebigquery.routines.get permission, whichlets the user call the routine.For information about how to assign access controls to a dataset, seeControlling access to datasets.

    Note: Instead of using a built-in role, consider creating a custom rolewith minimal permissions. For more information, seeCreating and managing custom roles.

  5. At this point, the user has permission to call thecount_key routinebut cannot access the table inprivate_dataset. If the user tries tocall the routine, they get an error message similar to the following:

    Access Denied: Table myproject:private_dataset.private_table: User doesnot have permission to query table myproject:private_dataset.private_table.

  6. Using the bq command-line tool, run theshow command as follows:

    bqshow--format=prettyjsonprivate_dataset >dataset.json

    The output is saved to a local file nameddataset.json.

  7. Editdataset.json to add the following JSON object to theaccessarray:

    {"routine":{"datasetId":"public_dataset","projectId":"PROJECT_ID","routineId":"count_key"}}

    ReplacePROJECT_ID with the project ID forpublic_dataset.

  8. Using the bq command-line tool, run theupdate command as follows:

    bqupdate--sourcedataset.jsonprivate_dataset

  9. To verify that the UDF has access toprivate_dataset, the user can run thefollowing query:

    SELECTpublic_dataset.count_key('key1');

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.