Stay organized with collections
Save and categorize content based on your preferences.
Authorized routines
Authorized routines let you share query results with specific users or groups
without giving them access to the underlying tables that generated the results.
For example, an authorized routine can compute an aggregation
over 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 the
data in the table. As an alternative, you can authorize the routine
to access the dataset that contains the referenced table. An authorized routine
can query the tables in the dataset, even if the user who calls the routine
can't query those tables directly.
The following types of routines can be authorized:
In the navigation panel, in the Explorer section, expand your
project and select a dataset.
In the details panel, expand Sharing, and then click
Authorize Routines.
In the Authorized routines page, in the Authorize routine
section, select the Project, Dataset, and Routine for the
routine that you want to authorize.
Click Add authorization.
bq
Use the bq show command to get the JSON representation of the dataset
that you want the routine to access. The output from the command is a
JSON representation of the
Dataset resource.
Save the result to a local file.
DATASET_NAME is the name of the dataset that contains the
routine.
PROJECT_ID is the project ID of the project that contains
the routine.
ROUTINE_NAME is the name of the routine.
Use the bq update command to update the dataset.
bqupdate--sourcedataset.jsonTARGET_DATASET
API
Call the datasets.get
method to fetch the dataset that you want the routine to access. The
response body contains a representation of the
Dataset resource.
Add the following JSON object to the access array in the Dataset
resource:
Grant the bigquery.dataViewer role to a user on the public_dataset
dataset. This role includes the bigquery.routines.get permission, which
lets the user call the routine.
For information about how to assign access controls to a dataset, see
Controlling access to datasets.
At this point, the user has permission to call the count_key routine
but cannot access the table in private_dataset. If the user tries to
call the routine, they get an error message similar to the following:
Access Denied: Table myproject:private_dataset.private_table: User does
not have permission to query table myproject:private_dataset.private_table.
Using the bq command-line tool, run the show command as follows:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-29 UTC."],[[["\u003cp\u003eAuthorized routines allow sharing query results with specific users or groups without granting them access to the underlying tables.\u003c/p\u003e\n"],["\u003cp\u003eThese routines, including table functions, user-defined functions (UDFs), and stored procedures, can be authorized to access specific datasets, even if the user lacks direct access.\u003c/p\u003e\n"],["\u003cp\u003eAuthorizing a routine requires adding a JSON object with the routine's project ID, dataset name, and routine name to the access array of the target dataset's configuration, which can be done via the Google Cloud console, bq command-line tool, or the REST API.\u003c/p\u003e\n"],["\u003cp\u003eStored procedures that are authorized as routines have the ability to make changes to database objects, meaning you should be careful when granting them to other users.\u003c/p\u003e\n"],["\u003cp\u003eIf a routine is modified using a \u003ccode\u003eCREATE OR REPLACE\u003c/code\u003e statement, it must be re-authorized to maintain access to the intended datasets.\u003c/p\u003e\n"]]],[],null,["# Authorized routines\n===================\n\nAuthorized routines let you share query results with specific users or groups\nwithout giving them access to the underlying tables that generated the results.\nFor example, an authorized routine can compute an aggregation\nover data or look up a table value and use that value in a computation.\n\nBy default, if a user invokes a routine, the user must have access to read the\ndata in the table. As an alternative, you can *authorize* the routine\nto access the dataset that contains the referenced table. An authorized routine\ncan query the tables in the dataset, even if the user who calls the routine\ncan't query those tables directly.\n\nThe following types of routines can be authorized:\n\n- [Table functions](/bigquery/docs/table-functions)\n- [User-defined functions (UDFs)](/bigquery/docs/user-defined-functions)\n- [Stored procedures](/bigquery/docs/procedures)\n\n| **Caution:** Stored procedures authorized as routines have DDL and DML access. These procedures can create, modify, and delete database objects. Principals with access to authorized stored procedures can bypass IAM permissions and perform actions that are normally denied to them. Only grant authorized stored procedure access to principals that you trust to run the procedure in its entirety.\n\nAuthorize routines\n------------------\n\nTo authorize a routine, use the Google Cloud console, the bq command-line tool, or the REST API: \n\n### Console\n\n1. Go to the BigQuery page in the Google Cloud console.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the navigation panel, in the **Explorer** section, expand your\n project and select a dataset.\n\n3. In the details panel, expand **Sharing** , and then click\n **Authorize Routines**.\n\n4. In the **Authorized routines** page, in the **Authorize routine**\n section, select the **Project** , **Dataset** , and **Routine** for the\n routine that you want to authorize.\n\n5. Click **Add authorization**.\n\n### bq\n\n1. Use the `bq show` command to get the JSON representation of the dataset\n that you want the routine to access. The output from the command is a\n JSON representation of the\n [`Dataset`](/bigquery/docs/reference/rest/v2/datasets#Dataset) resource.\n Save the result to a local file.\n\n ```bash\n bq show --format=prettyjson TARGET_DATASET \u003e dataset.json\n ```\n\n Replace \u003cvar translate=\"no\"\u003eTARGET_DATASET\u003c/var\u003e with the name of the dataset that\n the routine can access.\n2. Edit the file to add the following JSON object to the `access` array in\n the `Dataset` resource:\n\n ```json\n {\n \"routine\": {\n \"datasetId\": \"\u003cvar translate=\"no\"\u003eDATASET_NAME\u003c/var\u003e\",\n \"projectId\": \"\u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e\",\n \"routineId\": \"\u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e\"\n }\n }\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eDATASET_NAME\u003c/var\u003e is the name of the dataset that contains the routine.\n - \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e is the project ID of the project that contains the routine.\n - \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e is the name of the routine.\n3. Use the `bq update` command to update the dataset.\n\n ```json\n bq update --source dataset.json TARGET_DATASET\n ```\n\n### API\n\n1. Call the [`datasets.get`](/bigquery/docs/reference/rest/v2/datasets/get)\n method to fetch the dataset that you want the routine to access. The\n response body contains a representation of the\n [`Dataset`](/bigquery/docs/reference/rest/v2/datasets#Dataset) resource.\n\n2. Add the following JSON object to the `access` array in the `Dataset`\n resource:\n\n ```json\n {\n \"routine\": {\n \"datasetId\": \"\u003cvar translate=\"no\"\u003eDATASET_NAME\u003c/var\u003e\",\n \"projectId\": \"\u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e\",\n \"routineId\": \"\u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e\"\n }\n }\n ```\n\n Where:\n - \u003cvar translate=\"no\"\u003eDATASET_NAME\u003c/var\u003e is the name of the dataset that contains the UDF.\n - \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e is the project ID of the project that contains the UDF.\n - \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e is the name of the routine.\n3. Call the [`dataset.update`](/bigquery/docs/reference/rest/v2/datasets/update)\n method with the modified `Dataset` representation.\n\n| **Note:** If you modify a routine by running a `CREATE OR REPLACE` statement ([`CREATE OR REPLACE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement), [`CREATE OR REPLACE PROCEDURE`](/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure), or [`CREATE OR REPLACE TABLE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_table_function_statement)) you must re-authorize the routine.\n\nQuotas and limits\n-----------------\n\nAuthorized routines are subject to dataset limits. For more information, see\n[Dataset limits](/bigquery/quotas#dataset_limits).\n\nAuthorized routine example\n--------------------------\n\nThe following is an end-to-end example of creating and using an authorized UDF.\n\n1. Create two datasets named `private_dataset` and `public_dataset`. For more\n information about creating a dataset, see\n [Creating a dataset](/bigquery/docs/datasets#create-dataset).\n\n2. Run the following statement to create a table named `private_table` in\n `private_dataset`:\n\n CREATE OR REPLACE TABLE private_dataset.private_table\n AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;\n\n3. Run the following statement to create a UDF named `count_key` in\n `public_dataset`. The UDF includes a `SELECT` statement on `private_table`.\n\n CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING)\n RETURNS INT64\n AS\n ((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));\n\n4. Grant the `bigquery.dataViewer` role to a user on the `public_dataset`\n dataset. This role includes the `bigquery.routines.get` permission, which\n lets the user call the routine.\n For information about how to assign access controls to a dataset, see\n [Controlling access to datasets](/bigquery/docs/dataset-access-controls).\n\n | **Note:** Instead of using a built-in role, consider creating a custom role with minimal permissions. For more information, see [Creating and managing custom roles](/iam/docs/creating-custom-roles).\n5. At this point, the user has permission to call the `count_key` routine\n but cannot access the table in `private_dataset`. If the user tries to\n call the routine, they get an error message similar to the following:\n\n ```\n Access Denied: Table myproject:private_dataset.private_table: User does\n not have permission to query table myproject:private_dataset.private_table.\n ```\n6. Using the bq command-line tool, run the `show` command as follows:\n\n ```bash\n bq show --format=prettyjson private_dataset \u003e dataset.json\n ```\n\n The output is saved to a local file named `dataset.json`.\n7. Edit `dataset.json` to add the following JSON object to the `access`\n array:\n\n ```json\n {\n \"routine\": {\n \"datasetId\": \"public_dataset\",\n \"projectId\": \"\u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e\",\n \"routineId\": \"count_key\"\n }\n }\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with the project ID for `public_dataset`.\n8. Using the bq command-line tool, run the `update` command as follows:\n\n ```json\n bq update --source dataset.json private_dataset\n ```\n9. To verify that the UDF has access to `private_dataset`, the user can run the\n following query:\n\n SELECT public_dataset.count_key('key1');"]]