Authorized functions
Authorized functions let you share query results with particular users or groups without giving those users or groups access to the underlying tables. For example, an authorized function can compute an aggregation over data or look up a table value and use that value in a computation.
By default, if a user-defined function (UDF) or table function queries a table, then a user who invokes that function must have permission to read the data in the table. As an alternative, you can authorize the function to access the dataset that contains the referenced table. An authorized function can query the tables in the dataset, even if the user who calls the function can't query those tables directly.
Authorize a function
You can authorize both UDFs and table functions. To authorize a function, you
can use the Google Cloud console, the REST API, or the bq
command-line tool:
Console
Go to the BigQuery page in the console.
In the navigation panel, in the Resources section, expand your project and select a dataset.
In the details panel, click Authorize Routines.
In the Authorized routines page, in the Authorize routine section, select the project ID, dataset ID, and routine ID for the function that you want to authorize.
Click Add authorization.
API
Call the
datasets.get
method to fetch the dataset that you want the function to access. The response body contains a representation of theDataset
resource.Add the following JSON object to the
access
array in theDataset
resource:{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }
Where:
- DATASET_NAME is the name of the dataset that contains the UDF.
- PROJECT_ID is the project ID of the project that contains the UDF.
- ROUTINE_NAME is the name of the function.
Call the
dataset.update
method with the modifiedDataset
representation.
bq
Use the
bq show
command to get the JSON representation of the dataset that you want the function to access. The output from the command is a JSON representation of theDataset
resource. Save the result to a local file.bq show --format=prettyjson TARGET_DATASET > dataset.json
Replace TARGET_DATASET with the name of the dataset that the function will have access to.
Edit the file to add the following JSON object to the
access
array in theDataset
resource:{ "routine": { "datasetId": "DATASET_NAME", "projectId": "PROJECT_ID", "routineId": "ROUTINE_NAME" } }
Where:
- DATASET_NAME is the name of the dataset that contains the function.
- PROJECT_ID is the project ID of the project that contains the function.
- ROUTINE_NAME is the name of the function.
Use the
bq update
command to update the dataset.bq update --source dataset.json TARGET_DATASET
Authorized function example
The following is an end-to-end example of creating and using an authorized UDF.
Create two datasets named
private_dataset
andpublic_dataset
. For more information about creating a dataset, see Creating a dataset.Run the following statement to create a table named
private_table
inprivate_dataset
:CREATE OR REPLACE TABLE private_dataset.private_table AS SELECT key FROM UNNEST(['key1', 'key1','key2','key3']) key;
Run the following statement to create a UDF named
count_key
inpublic_dataset
. The UDF includes aSELECT
statement onprivate_table
.CREATE OR REPLACE FUNCTION public_dataset.count_key(input_key STRING) RETURNS INT64 AS ((SELECT COUNT(1) FROM private_dataset.private_table t WHERE t.key = input_key));
Grant the
bigquery.dataViewer
role to a user on thepublic_dataset
dataset. This role includes thebigquery.routines.get
permission, which lets the user call the function. 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
function but cannot access the table inprivate_dataset
. If the user tries to call the function, 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 theshow
command as follows:bq show --format=prettyjson private_dataset > dataset.json
The output is saved to a local file named
dataset.json
.Edit
dataset.json
to add the following JSON object to theaccess
array:{ "routine": { "datasetId": "public_dataset", "projectId": "PROJECT_ID", "routineId": "count_key" } }
Replace PROJECT_ID with the project ID for
public_dataset
.Using the
bq
command-line tool, run theupdate
command as follows:bq update --source dataset.json private_dataset
To verify that the UDF has access to
private_dataset
, the user can run the following query:SELECT public_dataset.count_key('key1');