데이터베이스 역할 SELECT * FROM
AlbumsBudget가 Analyst 쿼리를 수행한다고 가정합니다. 이 역할에는 뷰에 대한 SELECT 권한만 필요합니다. 참조된 세 열 또는 Albums 테이블에 대한 SELECT 권한은 필요하지 않습니다.
테이블의 열 하위 집합에 SELECT를 부여하면 FGAC 사용자는 더 이상 해당 테이블에 SELECT *를 사용할 수 없습니다. 해당 테이블의 쿼리는 포함할 모든 열의 이름을 지정해야 합니다.
생성된 열에 부여된 SELECT는 기본 열에 SELECT를 부여하지 않습니다.
인터리브 처리된 테이블의 경우 상위 테이블에 부여된 SELECT가 하위 테이블에 전파되지 않습니다.
변경 내역에 대한 SELECT를 부여할 때는 변경 내역의 테이블 값 함수에 대한 EXECUTE도 부여해야 합니다. 자세한 내용은 EXECUTE를 참조하세요.
특정 열(예: SUM(col_a))에 집계 함수와 함께 SELECT를 사용하는 경우 역할에는 해당 열에 대한 SELECT 권한이 있어야 합니다. 집계 함수가 열(예: COUNT(*))을 지정하지 않는 경우 역할에는 테이블에 있는 하나 이상의 열에 대한 SELECT 권한이 있어야 합니다.
[[["이해하기 쉬움","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-05(UTC)"],[],[],null,["# Fine-grained access control privileges\n\nThis page describes the privileges that you can grant to a database role for\nfine-grained access control. This information applies to both GoogleSQL-dialect databases and PostgreSQL-dialect databases.\n\nTo learn about database roles and fine-grained access control, see\n[Fine-grained access control overview](/spanner/docs/fgac-about).\n\nThe following table shows the fine-grained access control privileges and the database objects\nthat they can be granted on.\n\nThe following sections provide details about each privilege.\n\n`SELECT`\n--------\n\nAllows the role to read or query from a table, view, change stream, sequence, or\nmodel.\n\n- If a column list is specified for a table, the privilege is valid on only\n those columns. If no column list is specified, then the privilege is valid\n on all columns in the table, including columns added afterward. A column\n list isn't allowed for a view.\n\n- Spanner supports both invoker's rights views and definer's\n rights views. For more information, see [Views overview](/spanner/docs/views).\n\n If you create a view with invoker's rights, to query the view, the database\n role or user needs the `SELECT` privilege on the view, and also the `SELECT`\n privilege on the underlying objects referenced in the view. For example,\n suppose the view `SingerNames` is created on the `Singers` table. \n\n CREATE VIEW SingerNames SQL SECURITY INVOKER AS\n SELECT Singers.SingerId, Singers.FirstName, Singers.LastName FROM Singers;\n\n Suppose that the database role `myRole` performs the query `SELECT * FROM\n SingerNames`. The role must have `SELECT` privilege on the view and must\n have `SELECT` privilege on the three referenced columns or on the entire\n `Singers` table.\n\n If you create a view with definer's rights, to query the view, the database\n role or user only needs the `SELECT` privilege on the view. For example,\n suppose the view `AlbumsBudget` is created on the `Albums` table. \n\n CREATE VIEW AlbumsBudget SQL SECURITY DEFINER AS\n SELECT Albums.Id, Albums.AlbumTitle, MarketingBudget FROM Albums;\n\n Suppose that the database role `Analyst` performs the query `SELECT * FROM\n AlbumsBudget`. The role only needs `SELECT` privilege on the view. It\n doesn't need the `SELECT` privilege on the three referenced columns or on\n the `Albums` table.\n- After granting `SELECT` on a subset of columns for a table, the FGAC user\n can no longer use `SELECT *` on that table. Queries on that table must name\n all columns to be included.\n\n- `SELECT` granted on a generated column doesn't grant `SELECT` on the\n underlying base columns.\n\n- For interleaved tables, `SELECT` granted on the parent table doesn't\n propagate to the child table.\n\n- When you grant `SELECT` on a change stream, you must also grant `EXECUTE` on\n the table-valued function for the change stream. For more information, see\n [EXECUTE](#execute-privilege).\n\n- When `SELECT` is used with an aggregate function on specific columns, for\n example `SUM(col_a)`, the role must have the `SELECT` privilege on those\n columns. If the aggregate function doesn't specify any columns, for example\n `COUNT(*)`, the role must have the `SELECT` privilege on at least one column\n in the table.\n\n- When you use `SELECT` with a sequence, you can only view sequences that you\n have privileges to view.\n\n#### Examples for using `GRANT SELECT`\n\n### GoogleSQL\n\n```googlesql\nGRANT SELECT ON TABLE employees TO ROLE hr_director;\n\nGRANT SELECT ON TABLE customers, orders, items TO ROLE account_mgr;\n\nGRANT SELECT(name, level, cost_center, location, manager) ON TABLE employees TO ROLE hr_manager;\n\nGRANT SELECT(name, address, phone) ON TABLE employees, contractors TO ROLE hr_rep;\n\nGRANT SELECT ON VIEW orders_view TO ROLE hr_manager;\n\nGRANT SELECT ON CHANGE STREAM ordersChangeStream TO ROLE hr_analyst;\n\nGRANT SELECT ON SEQUENCE sequence_name TO ROLE role_name;\n```\n\n### PostgreSQL\n\n```postgresql\nGRANT SELECT ON TABLE employees TO hr_director;\n\nGRANT SELECT ON TABLE customers, orders, items TO account_mgr;\n\nGRANT SELECT(name, level, cost_center, location, manager) ON TABLE employees TO hr_manager;\n\nGRANT SELECT(name, address, phone) ON TABLE employees, contractors TO hr_rep;\n\nGRANT SELECT ON TABLE orders_view TO hr_manager; // orders_view is an invoker rights view\n\nGRANT SELECT ON CHANGE STREAM orders_change_stream TO hr_analyst;\n\nGRANT SELECT ON SEQUENCE sequence_name TO hr_package;\n```\n\n`INSERT`\n--------\n\nAllows the role to insert rows into the specified tables. If a column list is\nspecified, the permission is valid on only those\ncolumns. If no column list is specified, then the privilege is valid on all\ncolumns in the table.\n\n- If column names are specified, any column not included gets its default\n value upon insert.\n\n- `INSERT` can't be granted on generated columns.\n\n#### Examples for using `GRANT INSERT`\n\n### GoogleSQL\n\n```googlesql\nGRANT INSERT ON TABLE employees, contractors TO ROLE hr_manager;\n\nGRANT INSERT(name, address, phone) ON TABLE employees TO ROLE hr_rep;\n```\n\n### PostgreSQL\n\n```postgresql\nGRANT INSERT ON TABLE employees, contractors TO hr_manager;\n\nGRANT INSERT(name, address, phone) ON TABLE employees TO hr_rep;\n```\n\n`UPDATE`\n--------\n\nAllows the role to update rows in the specified tables. Updates can be\nrestricted to a subset of table columns. When you use this with sequences, it\nallows the role to call the `get-next-sequence-value` function on the sequence.\n\nIn addition to the `UPDATE` privilege, the role needs the `SELECT` privilege on\nall queried columns. Queried columns include columns in the `WHERE` clause.\n\n`UPDATE` can't be granted on generated columns.\n\n#### Examples for using `GRANT UPDATE`\n\n### GoogleSQL\n\n```googlesql\nGRANT UPDATE ON TABLE employees, contractors TO ROLE hr_manager;\n\nGRANT UPDATE(name, address, phone) ON TABLE employees TO ROLE hr_rep;\n```\n\n### PostgreSQL\n\n```postgresql\nGRANT UPDATE ON TABLE employees, contractors TO hr_manager;\n\nGRANT UPDATE(name, address, phone) ON TABLE employees TO hr_rep;\n```\n\n`DELETE`\n--------\n\nAllows the role to delete rows from the specified tables.\n\n- `DELETE` can't be granted at the column level.\n\n- The role also needs `SELECT` on any columns that might be\n included in the query's `WHERE` clauses.\n\n- For interleaved tables in GoogleSQL-dialect databases, the\n `DELETE` privilege is required only on the parent table. If a child table\n specifies `ON DELETE CASCADE`, rows from the child table are deleted even without\n the `DELETE` privilege on the child table.\n\n#### Example for using `GRANT DELETE`\n\n### GoogleSQL\n\n```googlesql\nGRANT DELETE ON TABLE employees, contractors TO ROLE hr_admin;\n```\n\n### PostgreSQL\n\n```postgresql\nGRANT DELETE ON TABLE employees, contractors TO hr_admin;\n```\n\n`EXECUTE`\n---------\n\nWhen you grant `SELECT` on a change stream, you must also grant `EXECUTE` on the\nread function for the change stream. For more information, see\n[Change stream read functions and query syntax](/spanner/docs/change-streams/details#change_stream_query_syntax).\n\nWhen you use this with models, it allows the role to use the model in\n[machine learning functions](/spanner/docs/reference/standard-sql/ml-functions).\n\n#### Example for using `GRANT EXECUTE`\n\nThe following example shows how to grant `EXECUTE` on the read function for the\nchange stream named `my_change_stream`. \n\n### GoogleSQL\n\n```googlesql\nGRANT EXECUTE ON TABLE FUNCTION READ_my_change_stream TO ROLE hr_analyst;\n```\n\n### PostgreSQL\n\n```postgresql\nGRANT EXECUTE ON FUNCTION spanner.read_json_my_change_stream TO hr_analyst;\n```\n\nUSAGE\n-----\n\nWhen you grant `USAGE` to a named schema, it provides privileges to access\nobjects contained in the named schema. The `USAGE` privilege is granted, by\ndefault, to the default schema.\n\nWhat's next\n-----------\n\n- [Configure fine-grained access control](/spanner/docs/configure-fgac)\n- [Fine-grained access control overview](/spanner/docs/fgac-about)\n- [GRANT and REVOKE statements](/spanner/docs/reference/standard-sql/data-definition-language#grant_and_revoke_statements) (GoogleSQL-dialect databases)\n- [GRANT and REVOKE statements](/spanner/docs/reference/postgresql/data-definition-language#grant_and_revoke_statements) (PostgreSQL-dialect databases)"]]