All Products
Search
Document Center

GRANT

Last Updated: Mar 18, 2021

GRANT is used to grant permissions to an account.

Syntax

GRANT privilege_type
ON privilege_level 
TO user[, …]

Parameters

  • privilege_type: indicates the permission type. Valid values: SELECT, SHOW, ALTER, DROP, CREATE, INSERT, UPDATE, DELETE, GRANT OPTION, ALL, ALL PRIVILEGES, and USAGE.

    • Separate multiple types of permissions with commas (,), for example, SELECT,DELETE,UPDATE,INSERT.

    • ALL and ALL PRIVILEGES are used to grant all permissions to an account. In this case, existing permissions granted to the account become invalid. GRANT OPTION is not included in ALL and must be used to grant permissions in explicit mode.

    • GRANT OPTION is used to grant permissions on the DCL statements. GRANT is used to grant permissions and REVOKE is used to revoke the granted permissions. You can execute the GRANT GRANT OPTION statement or the WITH GRANT OPTION clause to grant permissions.

    • GRANT SELECT is used to grant permissions on the SELECT statements.

    • GRANT SHOW is used to grant permissions on the SHOW and USE statements.

    • GRANT ALTER is used to grant permissions on the DDL ALTER statements or other similar DDL statements that are used to modify information.

    • GRANT CREATE is used to grant permissions on the DDL CREATE statements.

    • GRANT DROP is used to grant permissions on the DDL DROP statements.

    • GRANT INSERT is used to grant permissions on the DML INSERT statements.

    • GRANT UPDATE is used to grant permissions on the DML UPDATE statements.

    • GRANT DELETE is used to grant permissions on the DML DELETE statements.

    • GRANT USAGE indicates that no permission is granted.

  • privilege_level: indicates the level of the permission that you want to grant. To query the permissions on a schema, enter schema_name.*. To query the permissions on a table, enter table_name.

    • * indicates that a schema is used, and permissions at the schema or database level are granted.

    • *.* indicates that global-level permissions on all tables in all databases are granted.

    • xxDb.* indicates that permissions on the schema or database are granted.

    • xxDb.yyTable indicates that permissions on a table in the schema or database are granted.

    • yyTable indicates that permissions on a table in the database of the current connection are granted.

    • Field-level permissions cannot be granted.

  • user: indicates the authorized user.

    • Only the DLA root account can be used to grant permissions to non-root accounts.

    • Non-root accounts cannot be used to grant permissions to other accounts.

    • Permissions cannot be granted or revoked in cross-account mode.

    • The root account can be used to execute the SHOW GRANTS statement to view only the permissions of accounts that are within the same Alibaba Cloud account as the root account.

Example

  • Execute the following statement to grant the ALL permission on the ots_account_test database to the dla_test account:

      grant all on ots_account_test.* to dla_test;
  • Execute the following statement to grant the SELECT, SHOW, DROP, and ALTER permissions on the finished_orders_v1 view in the tpch schema to the dla_test account:

      grant select,show,drop,alter on tpch.finished_orders_v1 to dla_test;