승인된 루틴을 사용하면 결과를 생성한 기본 테이블에 대해 액세스 권한을 부여하지 않고도 특정 사용자 또는 그룹에 쿼리 결과를 공유할 수 있습니다.
예를 들어 승인된 루틴은 데이터에 대한 집계를 계산하거나 테이블 값을 조회하고 이 값을 계산에 사용할 수 있습니다.
기본적으로 사용자가 루틴을 호출할 경우 사용자에게 테이블 데이터를 읽을 수 있는 액세스 권한이 있어야 합니다. 또는 참조된 테이블이 포함된 데이터 세트에 루틴이 액세스하도록 승인할 수 있습니다. 루틴을 호출하는 사용자가 테이블을 직접 쿼리할 수 없더라도 승인된 루틴은 데이터 세트의 테이블을 쿼리할 수 있습니다.
public_dataset 데이터 세트에서 사용자에게 bigquery.dataViewer 역할을 부여합니다. 이 역할에는 사용자가 루틴을 호출할 수 있는 bigquery.routines.get 권한이 포함되어 있습니다.
데이터 세트에 액세스 제어를 할당하는 방법은 데이터 세트에 대한 액세스 제어를 참조하세요.
이때 사용자는 count_key 루틴을 호출할 수 있는 권한을 가지고 있지만 private_dataset의 테이블에 액세스할 수 없습니다. 사용자가 루틴을 호출하려고 하면 다음과 비슷한 오류 메시지가 표시됩니다.
Access Denied: Table myproject:private_dataset.private_table: User does
not have permission to query table myproject:private_dataset.private_table.
[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-09-04(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');"]]
