Authorized routines let you share BigQuery user-defined functions (UDFs) and stored procedures across different projects.
Here’s a BigQuery authorized routine in action:
-- Create a dataset in project 'source-project'
CREATE SCHEMA IF NOT EXISTS source_project.shared_udfs;
-- Create an authorized UDF in the source project
CREATE OR REPLACE FUNCTION source_project.shared_udfs.greet(name STRING)
RETURNS STRING
AS ("Hello, " || name || "!");
-- Grant access to the UDF to another project
GRANT `roles/bigquery.user`
ON FUNCTION source_project.shared_udfs.greet(STRING)
TO "user:another-user@example.com", "serviceAccount:my-service-account@consumer-project.iam.gserviceaccount.com";
-- In 'consumer-project', query the UDF
SELECT source_project.shared_udfs.greet('World');
This allows users or service accounts in consumer-project to execute the greet function defined in source-project without needing direct access to the source_project.shared_udfs dataset. The GRANT statement is key here; it explicitly permits specified principals to use the routine.
The problem this solves is the duplication of complex or standardized logic. Imagine you have a set of data validation rules or formatting functions that are used across many different teams or applications, each residing in its own GCP project. Without authorized routines, you’d have to copy and paste the UDF code into each project, leading to maintenance nightmares and inconsistencies. Authorized routines centralize this logic in a "source" project and make it accessible to "consumer" projects.
Internally, BigQuery handles this by maintaining metadata about which principals are authorized to invoke specific routines. When a query in consumer-project references source_project.shared_udfs.greet, BigQuery checks the IAM policies of the source-project to verify if the caller (the user or service account running the query) has been granted the bigquery.user role (or a custom role with equivalent permissions) on that specific routine. If authorized, BigQuery executes the UDF in the context of the source project’s dataset.
The exact levers you control are:
- The source project and dataset: Where the routine is defined.
- The routine name and signature: The specific function or procedure being shared.
- The principals (users/service accounts): Who you grant access to.
- The IAM role: Typically
roles/bigquery.user, but custom roles can be used for finer-grained control.
When you grant a principal access to a routine, you’re not granting them permission to modify the routine, only to invoke it. The creation and management of the routine remain solely within the source project. This is a crucial security boundary.
The next concept you’ll likely encounter is managing the lifecycle of these shared routines. What happens when you need to update a widely used UDF? You’ll need a strategy for versioning or coordinated rollouts across all consuming projects to avoid breaking existing workflows.