-- Returns metadata for the access control bindings for mydataset.SELECT*FROMmyproject.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGESWHEREobject_name="mydataset";
제한사항
OBJECT_PRIVILEGES 쿼리에는 단일 데이터 세트, 테이블, 뷰로 쿼리를 제한하는 WHERE 절이 있어야 합니다.
데이터 세트에 대해 액세스 제어 메타데이터를 검색하는 쿼리는 object_name을 지정해야 합니다.
테이블 또는 뷰의 액세스 제어 메타데이터를 검색하는 쿼리는 object_name 및 object_schema를 모두 지정해야 합니다.
예시
다음 예시에서는 INFORMATION_SCHEMA.OBJECT_PRIVILEGES 뷰의 모든 열을 검색합니다.
쿼리가 실행되는 프로젝트가 아닌 다른 프로젝트에 대해 쿼리를 실행하려면 `project_id`.`region_id`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES 형식으로 프로젝트 ID를 해당 리전에 추가합니다.
다음 예시는 mycompany 프로젝트의 mydataset 데이터 세트에 대해 모든 액세스 제어 메타데이터를 가져옵니다.
INFORMATION_SCHEMA.OBJECT_PRIVILEGES 뷰에는 명시적으로 설정된 액세스 제어 바인딩만 표시됩니다. 첫 번째 예시는 cloudysanfrancisco@gmail.com 사용자가 mydataset 데이터 세트에 대한 bigquery.dataOwner 역할을 가지고 있음을 보여줍니다. cloudysanfrancisco@gmail.com 사용자는 testdata 테이블을 포함하여 mydataset에서 테이블을 만들고, 업데이트하고, 삭제할 수 있는 권한을 상속받습니다. 그러나 이러한 권한이 testdata 테이블에 명시적으로 부여되지 않았기 때문에 두 번째 예시의 결과에는 나타나지 않습니다.
[[["이해하기 쉬움","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-08-26(UTC)"],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.OBJECT_PRIVILEGES\u003c/code\u003e view provides metadata about explicitly set access control bindings on BigQuery objects, excluding inherited bindings.\u003c/p\u003e\n"],["\u003cp\u003eQuerying this view requires specific IAM permissions, including \u003ccode\u003ebigquery.datasets.get\u003c/code\u003e for datasets and \u003ccode\u003ebigquery.tables.getIamPolicy\u003c/code\u003e for tables and views.\u003c/p\u003e\n"],["\u003cp\u003eQueries must include a region qualifier and are limited to a single dataset, table, or view, using a \u003ccode\u003eWHERE\u003c/code\u003e clause.\u003c/p\u003e\n"],["\u003cp\u003eThe view's schema includes columns like \u003ccode\u003eOBJECT_CATALOG\u003c/code\u003e, \u003ccode\u003eOBJECT_SCHEMA\u003c/code\u003e, \u003ccode\u003eOBJECT_NAME\u003c/code\u003e, \u003ccode\u003eOBJECT_TYPE\u003c/code\u003e, \u003ccode\u003ePRIVILEGE_TYPE\u003c/code\u003e, and \u003ccode\u003eGRANTEE\u003c/code\u003e, which detail the project, dataset, object, type, role, and user granted the role, respectively.\u003c/p\u003e\n"],["\u003cp\u003ePre-GA status means this feature is available "as is" with potential limitations in support, and is subject to the Pre-GA Offerings Terms.\u003c/p\u003e\n"]]],[],null,["# OBJECT_PRIVILEGES view\n======================\n\n|\n| **Preview**\n|\n|\n| This product or feature is subject to the \"Pre-GA Offerings Terms\" in the General Service Terms section\n| of the [Service Specific Terms](/terms/service-terms#1).\n|\n| Pre-GA products and features are available \"as is\" and might have limited support.\n|\n| For more information, see the\n| [launch stage descriptions](/products#product-launch-stages).\n\nThe `INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view contains metadata about access\ncontrol bindings that are explicitly set on BigQuery objects.\nThis view does not contain metadata about the inherited access control bindings.\n\nRequired permissions\n--------------------\n\nTo query the `INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view, you need following\nIdentity and Access Management (IAM) permissions:\n\n- `bigquery.datasets.get` for datasets.\n- `bigquery.tables.getIamPolicy` for tables and views.\n\nFor more information about BigQuery permissions, see\n[Access control with IAM](/bigquery/docs/access-control).\n\nSchema\n------\n\nWhen you query the `INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view, the query\nresults contain one row for each access control binding for a resource.\n\nThe `INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view has the following schema:\n\nScope and syntax\n----------------\n\nQueries against this view must include a [region qualifier](/bigquery/docs/information-schema-intro#syntax).\nA project ID is optional. If no project ID is specified, then the project that\nthe query runs in is used. The following table explains the region scope for\nthis view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: any [dataset region name](/bigquery/docs/locations). For example, ```region-us```.\n\n \u003cbr /\u003e\n\n \u003cbr /\u003e\n\n | **Note:** You must use [a region qualifier](/bigquery/docs/information-schema-intro#region_qualifier) to query `INFORMATION_SCHEMA` views. The location of the query execution must match the region of the `INFORMATION_SCHEMA` view.\n\n\u003cbr /\u003e\n\n**Example** \n\n -- Returns metadata for the access control bindings for mydataset.\n SELECT * FROM myproject.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES\n WHERE object_name = \"mydataset\";\n\nLimitations\n-----------\n\n- `OBJECT_PRIVILEGES` queries must contain a `WHERE` clause limiting queries to a single dataset, table, or view.\n- Queries to retrieve access control metadata for a dataset must specify the `object_name`.\n- Queries to retrieve access control metadata for a table or view must specify both `object_name` AND `object_schema`.\n\nExamples\n--------\n\nThe following example retrieves all columns from the\n`INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view.\n\nTo run the query against a project other than the project that the query is\nrunning in, add the project ID to the region in the following format:\n```````\u003cvar translate=\"no\"\u003eproject_id\u003c/var\u003e````.````\u003cvar translate=\"no\"\u003eregion_id\u003c/var\u003e````.INFORMATION_SCHEMA.OBJECT_PRIVILEGES```.\n\nThe following example gets all access control metadata for the `mydataset` dataset\nin the `mycompany` project: \n\n SELECT *\n FROM mycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES\n WHERE object_name = \"mydataset\"\n\nThe results should look like the following:\n\n\u003cbr /\u003e\n\n```\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n | object_catalog | object_schema | object_name | object_type | privilege_type | grantee |\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n | mycompany | NULL | mydataset | SCHEMA | roles/bigquery.dataEditor | projectEditor:mycompany |\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n | mycompany | NULL | mydataset | SCHEMA | roles/bigquery.dataOwner | projectOwner:mycompany |\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n | mycompany | NULL | mydataset | SCHEMA | roles/bigquery.dataOwner | user:cloudysanfrancisco@gmail.com |\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n | mycompany | NULL | mydataset | SCHEMA | roles/bigquery.dataViwer | projectViewer:mycompany |\n +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+\n \n```\n\n\u003cbr /\u003e\n\nThe following example gets all access control information for the `testdata` table\nin the `mydataset` dataset: \n\n SELECT *\n FROM mycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES\n WHERE object_schema = \"mydataset\" AND object_name = \"testdata\"\n\nThe results should look like the following:\n\n\u003cbr /\u003e\n\n```\n +----------------+---------------+--------------+-------------+----------------------+------------------------------------+\n | object_catalog | object_schema | object_name | object_type | privilege_type | grantee |\n +----------------+---------------+--------------+-------------+----------------------+------------------------------------+\n | mycompany | mydataset | testdata | TABLE | roles/bigquery.admin | user:baklavainthebalkans@gmail.com |\n +----------------+---------------+--------------+-------------+----------------------+------------------------------------+\n \n```\n\n\u003cbr /\u003e\n\nThe `INFORMATION_SCHEMA.OBJECT_PRIVILEGES` view only shows access control\nbindings that are explicitly set. The first example shows that the user\n`cloudysanfrancisco@gmail.com`\nhas the `bigquery.dataOwner` role on the `mydataset` dataset. The user\n`cloudysanfrancisco@gmail.com` inherits permissions to create, update, and\ndelete tables in `mydataset`, including the `testdata` table. However, since\nthose permissions were not explicitly granted on the `testdata` table, they\ndon't appear in the results of the second example."]]
