# **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](../Other/SHOW-Statements/show-grants.md).

The REVOKE statement is related to `GRANT` and allows tenants to delete user permissions. For more information about `REVOKE`, see [REVOKE](revoke.md).

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](../../privileges.md).

`GRANT` After successful execution, the result is `Query OK, 0 rows affected`. To view permissions generated by the operation, use [SHOW GRANTS](../Other/SHOW-Statements/show-grants.md)

## **Grammar Structure**

```sql
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:

```sql
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:

```sql
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:

```sql
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**

```sql
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)
```