Product Documentation

Appearance

All platforms

ON TABLE ​

Syntax ​

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES  }
   [, ...] | ALL [ PRIVILEGES ] }
   ON { [ TABLE ] table_name [, ...]
        | ALL TABLES IN SCHEMA schema_name [, ...] }
   TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
   [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
   ON [ TABLE | VIEW ] table_name | view_name [, ...]
   TO role_specification [, ...] [ WITH GRANT OPTION ]

Parameters ​

ON TABLE

Grant privileges on a table, a view, or specific columns in a table. You can use one statement to grant privileges on several tables or all of the tables in a schema ("all tables" includes views).

SELECT

Privilege to select from the specified table or view, or specified columns in a table or view. This privilege applies to both temporary tables and persistent tables. This privilege also permits references to existing column values in UPDATE and DELETE statements.

The TABLE keyword is supported for both tables and views. The keyword VIEW is only supported for views.

INSERT

Privilege to insert or bulk load rows into the specified table or table column. Bulk loading with ybload also requires the BULK LOAD privilege on the database.

UPDATE

Privilege to update the specified table or the specified columns in a table. In most cases, UPDATE commands require SELECT privilege on the same table. UPDATE commands reference columns to determine which rows to update and to compute column values. For example, you would have to give SELECT access on the whole team table to vicky for this UPDATE command to work:

premdb=# grant select(teamid) on team to vicky;
GRANT
premdb=# grant update(teamid) on team to vicky;
GRANT
premdb=# \c premdb vicky
You are now connected to database "premdb" as user "vicky".
premdb=> update team set teamid=0 where teamid=3000;
ERROR:  permission denied for relation team
DELETE

Privilege to delete rows from the specified table or the specified columns in a table. In most cases, DELETE commands require SELECT privilege on the same table. DELETE commands reference columns to determine which rows to update.

TRUNCATE

Privilege to truncate a table.

REFERENCES

Privilege to create a foreign-key constraint (required on both the referencing table and the referenced table).

Examples ​

Grant all privileges on the match table to the user bobr:

premdb=# grant all on match to bobr;
GRANT

Grant SELECT privilege on all tables in the sys schema to the user vicky:

premdb=# grant select on all tables in schema sys to vicky;
GRANT

Grant SELECT privilege on a view called teamview to the user bobr:

premdb=# grant select on view teamview to bobr;
GRANT

You can also use the TABLE keyword to refer to views:

premdb=# grant select on table teamview to bobr;
GRANT