Fine-grained access control privileges

Stay organized with collections Save and categorize content based on your preferences.

This page describes the privileges that you can grant to a database role for fine-grained access control.

To learn about database roles and fine-grained access control, see About fine-grained access control.

The following table shows the fine-grained access control privileges and the database objects that they can be granted on.

SELECT INSERT UPDATE DELETE EXECUTE
Table ✓ ✓ ✓ ✓
Column ✓ ✓ ✓
View ✓
Change stream ✓
Change stream's TVF ✓

The following sections provide details about each privilege.

SELECT

Allows the role to read or query from a table, view, or change stream.

  • If a column list is specified for a table, the privilege is valid on only those columns. If no column list is specified, then the privilege is valid on all columns in the table, including columns added afterward. A column list is not allowed for a view.

  • Because Cloud Spanner currently only supports invoker rights views, a database role needs the SELECT privilege on the view, and also the SELECT privilege on the underlying objects referenced in the view. For example, suppose the view SingerNames is created on the Singers table.

    CREATE VIEW SingerNames SQL SECURITY INVOKER AS 
  SELECT Singers.SingerId, Singers.FirstName, Singers.LastName FROM Singers;

    Suppose also that the database role myRole performs the query SELECT * FROM SingerNames. The role must have SELECT on the view and must have SELECT on the three referenced columns or on the entire Singers table.

  • After granting SELECT on a subset of columns for a table, the FGAC user can no longer use SELECT * on that table. Queries on that table must name all columns to be included.

  • SELECT granted on a generated column doesn't grant SELECT on the underlying base columns.

  • For interleaved tables, SELECT granted on the parent table doesn't propagate to the child table.

  • When you grant SELECT on a change stream, you must also grant EXECUTE on the table-valued function for the change stream. For more information, see EXECUTE.

  • When SELECT is used with an aggregate function on specific columns, for example SUM(col_a), the role must have the SELECT privilege on those columns. If the aggregate function doesn't specify any columns, for example COUNT(*), the role must have the SELECT privilege on at least one column in the table.

Examples

GRANT SELECT ON TABLE employees TO ROLE hr_director;

GRANT SELECT ON TABLE customers, orders, items TO ROLE account_mgr;

GRANT SELECT(name, level, cost_center, location, manager) ON TABLE employees TO ROLE hr_manager;

GRANT SELECT(name, address, phone) ON TABLE employees, contractors TO ROLE hr_rep;

GRANT SELECT ON VIEW ordersSummary TO ROLE hr_manager;

GRANT SELECT ON CHANGE STREAM ordersChangeStream TO ROLE hr_analyst;

INSERT

Allows the role to insert rows into the specified tables. If a column list is specified, the permission is valid on only those columns. If no column list is specified, then the privilege is valid on all columns in the table.

  • If column names are specified, any column not included gets its default value upon insert.

  • INSERT can't be granted on generated columns.

Examples

GRANT INSERT ON TABLE employees, contractors TO ROLE hr_manager;

GRANT INSERT(name, address, phone) ON TABLE employees TO ROLE hr_rep;

UPDATE

Allows the role to update rows in the specified tables. Updates can be restricted to a subset of table columns.

  • In addition to the UPDATE privilege, the role needs the SELECT privilege on all key columns and all queried columns. Queried columns include columns in the WHERE clause and columns that are used to compute the new values of updated columns and generated columns.

  • UPDATE can't be granted on generated columns.

Examples

GRANT UPDATE ON TABLE employees, contractors TO ROLE hr_manager;

GRANT UPDATE(name, address, phone) ON TABLE employees TO ROLE hr_rep;

DELETE

Allows the role to delete rows from the specified tables.

  • DELETE can't be granted at the column level.

  • The role also needs SELECT on all key columns and any columns that might be included in query WHERE clauses.

  • For interleaved tables, the DELETE privilege is required only on the parent table. If a child table specifies ON DELETE CASCADE, rows from the child table are deleted even without the DELETE privilege on the child table.

Example

GRANT DELETE ON TABLE employees, contractors TO ROLE hr_admin;

EXECUTE

When you grant SELECT on a change stream, you must also grant EXECUTE on the table-valued function (TVF) for the change stream. For information about TVFs, see Change stream query syntax.

Example

GRANT EXECUTE ON TABLE FUNCTION READ_my_change_stream TO ROLE hr_analyst;

More information

For more information, see: