Skip to content

GRANT

Grammar Description

The GRANT statement assigns permissions and roles to MatrixOne Intelligence users and roles.

GRANT Overview

System permissions are permissions for the Admin administrator of the MatrixOne Intelligence instance. Admin administrators can create and delete other users, manage users, and grant them roles and permissions.

To grant permissions to other users or roles using GRANT, you must first have WITH GRANT OPTION permission, and you must have the permissions you are granting. To learn about your current role authorization status or other role authorization status, please use the SHOW GRANTS statement. For more information, see SHOW GRANTS.

The REVOKE statement is related to GRANT and allows tenants to delete user permissions. For more information about REVOKE, see REVOKE.

Generally, a cluster has a root by default. root first uses CREATE ACCOUNT to create a new account and define its non-privileged permissions, such as its password, and then the tenant uses CREATE USER to create a user and empower it with GRANT. ALTER ACCOUNT can be used to modify the non-privileged characteristics of an existing tenant. REVOKE is used to modify the permission characteristics of an existing user. For information about the permissions supported by MatrixOne Intelligence and the permissions at different levels, see MatrixOne Intelligence Permission Classification.

GRANT After successful execution, the result is Query OK, 0 rows affected. To view permissions generated by the operation, use SHOW GRANTS

Grammar Structure

> GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

Parameter definition

The GRANT statement allows administrators to grant permissions and roles that can be granted to users and roles. The syntax instructions are as follows:

  • GRANT cannot grant permissions and roles at the same time in the same statement.

  • The ON clause distinguishes whether a statement grants permissions or roles:

  • Use ON, which grants permissions.

  • If there is no ON, the statement is granted to the role.

  • Permissions and roles must be assigned to a user using a separate GRANT statement, and the syntax of each GRANT statement is appropriate to what to grant.

Database permissions

Database permissions apply to all objects in a given database. To assign database-level permissions, use the ON db_name * syntax, as shown below:

grant all on database * to role1;

Table permissions

Table permissions apply to all columns in a given table. To assign table-level permissions, use the ON db_name.tbl_name syntax, as shown below:

grant all on table *.* to role1;

Authorized roles

The GRANT syntax that does not carry the ON clause will empower the role, not the individual. Roles are named collections of permissions. Examples are as follows:

grant role3 to role_user;

To authorize a role or to a user, it is necessary to ensure that both the user and the role exist.

Granting roles requires these permissions:

  • You have the right to grant or revoke any role to the user or role.

Example

> drop user if exists user_prepare_01;
> drop role if exists role_prepare_1;
> create user user_prepare_01 identified by '123456';
> create role role role_prepare_1;
> create database if not exists p_db;
> grant create table ,drop table on database *.* to role_prepare_1;
Query OK, 0 rows affected (0.01 sec)

> grant connect on account * to role_prepare_1;
Query OK, 0 rows affected (0.01 sec)

> grant insert,select on table *.* to role_prepare_1;
Query OK, 0 rows affected (0.01 sec)

> grant role_prepare_1 to user_prepare_01;
Query OK, 0 rows affected (0.01 sec)