Tetap teratur dengan koleksi
Simpan dan kategorikan konten berdasarkan preferensi Anda.
Rutinitas yang diotorisasi
Rutinitas yang diotorisasi memungkinkan Anda membagikan hasil kueri kepada pengguna atau grup tertentu
tanpa memberi mereka akses ke tabel dasar yang membuat hasil tersebut.
Contohnya, rutinitas yang diotorisasi dapat melakukan komputasi agregasi
atas data atau mencari nilai tabel dan menggunakan nilai tersebut dalam komputasi.
Secara default, jika pengguna memanggil rutinitas, pengguna harus memiliki akses untuk membaca
data dalam tabel. Sebagai alternatif, Anda dapat memberi otorisasi kepada rutinitas
untuk mengakses set data yang berisi tabel yang direferensikan. Rutinitas yang diotorisasi
dapat membuat kueri tabel dalam set data, meskipun pengguna yang memanggil rutinitas
tidak dapat membuat kueri tabel tersebut secara langsung.
Di panel navigasi, di bagian Explorer, luaskan project Anda dan pilih set data.
Di panel detail, luaskan Sharing, lalu klik
Authorize Routines.
Di halaman Rutinitas yang diotorisasi, di bagian Memberi otorisasi kepada rutinitas, pilih Project, Dataset, dan Routine untuk
rutinitas yang ingin Anda beri otorisasi.
Klik Tambahkan otorisasi.
bq
Gunakan perintah bq show untuk mendapatkan representasi JSON dari set data
yang Anda ingin rutinitas akses. Output dari perintah ini adalah
representasi JSON dari
resource Dataset.
Simpan hasilnya ke file lokal.
DATASET_NAME adalah nama set data yang berisi UDF.
PROJECT_ID adalah ID project untuk project yang berisi
UDF.
ROUTINE_NAME adalah nama rutinitas.
Panggil metode dataset.update
dengan representasi Dataset yang diubah.
Kuota dan batas
Rutinitas yang diotorisasi tunduk kepada batasan set data. Untuk mengetahui informasi selengkapnya, lihat
Batas set data.
Contoh rutinitas yang diotorisasi
Berikut adalah contoh end-to-end dalam membuat dan menggunakan UDF yang diotorisasi.
Buat dua set data bernama private_dataset dan public_dataset. Untuk mengetahui
informasi selengkapnya tentang cara membuat set data, lihat
Membuat set data.
Jalankan pernyataan berikut untuk membuat tabel bernama private_table di
private_dataset:
Berikan peran bigquery.dataViewer kepada pengguna di set data
public_dataset. Peran ini mencakup izin bigquery.routines.get, yang
memungkinkan pengguna memanggil rutinitas.
Untuk mengetahui informasi tentang cara menetapkan kontrol akses ke set data, lihat
Mengontrol akses ke set data.
Pada tahap ini, pengguna memiliki izin untuk memanggil rutinitas count_key,
tetapi tidak dapat mengakses tabel di private_dataset. Jika pengguna mencoba
memanggil rutinitas, mereka akan mendapatkan pesan error yang serupa dengan yang berikut ini:
Access Denied: Table myproject:private_dataset.private_table: User does
not have permission to query table myproject:private_dataset.private_table.
Dengan menggunakan alat command line bq, jalankan perintah show sebagai berikut:
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-08-17 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');"]]
