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:

• Table functions
• User-defined functions (UDFs)
• Stored procedures

Authorize routines

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

Quotas and limits

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

Authorized routine example

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

1. Create two datasets named private_dataset and public_dataset . For more information about creating a dataset, see Creating a dataset .

2. Run the following statement to create a table named private_table in private_dataset :

     CREATE 
     
    OR 
     
    REPLACE 
     
    TABLE 
     
    private_dataset 
    . 
    private_table 
    AS 
     
    SELECT 
     
    key 
     
    FROM 
     
    UNNEST 
    ( 
    [ 
    'key1' 
    , 
     
    'key1' 
    , 
    'key2' 
    , 
    'key3' 
    ] 
    ) 
     
    key 
    ; 
    
   

3. Run the following statement to create a UDF named count_key in public_dataset . The UDF includes a SELECT statement on private_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 
    )); 
    
   

4. 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 .

5. 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.

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

   bq  
   show  
   --format = 
   prettyjson  
   private_dataset > 
   dataset.json

   The output is saved to a local file named dataset.json .

7. Edit dataset.json to add the following JSON object to the access array:

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

   Replace PROJECT_ID with the project ID for public_dataset .

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

    bq 
     
    upda 
    te 
     
    -- 
    source 
     
    da 
    taset 
    .jso 
    n 
     
    priva 
    te 
    _da 
    taset 
   

9. To verify that the UDF has access to private_dataset , the user can run the following query:

     SELECT 
     
    public_dataset 
    . 
    count_key 
    ( 
    'key1' 
    );