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.
Allows the role to perform
SELECT statements on a table. If a column list is
specified, 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.
SELECTon 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.
SELECTgranted on a generated column doesn't grant
SELECTon the underlying base columns.
For interleaved tables,
SELECTgranted on the parent table doesn't propagate to the child table.
SELECTis used with an aggregate function on specific columns, for example
SUM(col_a), the role must have the
SELECTprivilege on those columns. If the aggregate function doesn't specify any columns, for example
COUNT(*), the role must have the
SELECTprivilege on at least one column in the table.
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;
In the preceding final example, all named columns must be present in both tables.
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.
INSERTcan't be granted on generated columns.
GRANT INSERT ON TABLE employees, contractors TO ROLE hr_manager; GRANT INSERT(name, address, phone) ON TABLE employees TO ROLE hr_rep;
Allows the role to update rows in the specified tables. Updates can be restricted to a subset of table columns.
In addition to the
UPDATEprivilege, the role needs the
SELECTprivilege on all key columns and all queried columns. Queried columns include columns in the
WHEREclause and columns that are used to compute the new values of updated columns and generated columns.
UPDATEcan't be granted on generated columns.
GRANT UPDATE ON TABLE employees, contractors TO ROLE hr_manager; GRANT UPDATE(name, address, phone) ON TABLE employees TO ROLE hr_rep;
Allows the role to delete rows from the specified tables.
DELETEcan't be granted at the column level.
The role also needs
SELECTon all key columns and any columns that might be included in query
For interleaved tables, the
DELETEprivilege 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
DELETEprivilege on the child table.
GRANT DELETE ON TABLE employees, contractors TO ROLE admin;
For more information, see: