The GRANT <privileges> statement lets you control each role or user's SQL privileges for interacting with specific databases and tables.

For privileges required by specific statements, see the documentation for the respective SQL statement.

Synopsis

GRANT ALL CREATE GRANT SELECT DROP INSERT DELETE UPDATE , ON TABLE table_name , DATABASE database_name , TO user_name ,

Required Privileges

The user granting privileges must have the GRANT privilege on the target databases or tables.

Supported Privileges

Roles and users can be granted the following privileges. Some privileges are applicable both for databases and tables, while other are applicable only for tables (see Levels in the table below).

  • When a role or user is granted privileges for a database, new tables created in the database will inherit the privileges, but the privileges can then be changed.
  • When a role or user is granted privileges for a table, the privileges are limited to the table.
  • The root user automatically belongs to the admin role and has the ALL privilege for new databases.
  • For privileges required by specific statements, see the documentation for the respective SQL statement.
Privilege Levels
ALL Database, Table
CREATE Database, Table
DROP Database, Table
GRANT Database, Table
SELECT Table
INSERT Table
DELETE Table
UPDATE Table

Parameters

Parameter Description
table_name A comma-separated list of table names. Alternately, to grant privileges to all tables, use *. ON TABLE table.* grants apply to all existing tables in a database but won't affect tables created after the grant.
database_name A comma-separated list of database names.

Privileges granted on databases will be inherited by any new tables created in the databases, but don't affect existing tables in the database.
user_name A comma-separated list of users and/or roles to whom you want to grant privileges.

Examples

Grant Privileges on Databases

copy
icon/buttons/copy
> GRANT CREATE ON DATABASE db1, db2 TO maxroach, betsyroach;
copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE db1, db2;
+----------+------------+------------+
| Database |    User    | Privileges |
+----------+------------+------------+
| db1      | betsyroach | CREATE     |
| db1      | maxroach   | CREATE     |
| db1      | root       | ALL        |
| db2      | betsyroach | CREATE     |
| db2      | maxroach   | CREATE     |
| db2      | root       | ALL        |
+----------+------------+------------+
(6 rows)

Grant Privileges on Specific Tables in a Database

copy
icon/buttons/copy
> GRANT DELETE ON TABLE db1.t1, db1.t2 TO betsyroach;
copy
icon/buttons/copy
> SHOW GRANTS ON TABLE db1.t1, db1.t2;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | betsyroach | DELETE     |
| t1    | root       | ALL        |
| t2    | betsyroach | DELETE     |
| t2    | root       | ALL        |
+-------+------------+------------+
(4 rows)

Grant Privileges on All Tables in a Database

copy
icon/buttons/copy
> GRANT SELECT ON TABLE db2.* TO henryroach;
copy
icon/buttons/copy
> SHOW GRANTS ON TABLE db2.*;
+-------+------------+------------+
| Table |    User    | Privileges |
+-------+------------+------------+
| t1    | henryroach | SELECT     |
| t1    | root       | ALL        |
| t2    | henryroach | SELECT     |
| t2    | root       | ALL        |
+-------+------------+------------+
(4 rows)

See Also



Yes No