GRANT|V4.3.5| docs|Distributed Database

GRANT

Last Updated：2025-11-14 07:33:32 Updated

Purpose

This statement is used to grant privileges to a specified user or role, or to grant a role to a user or role.

Limitations and considerations

Circular GRANT operations are not supported. For example, if Role A is granted to Role B, and Role B is granted to Role C, then granting Role C to Role A will result in an error.

Required privileges

Privileges required for granting privileges to a user or role

When executing a GRANT statement, the current user must have the corresponding privileges. For example, if user user1 wants to grant the SELECT privilege on table tbl1 to user user2, user user1 must already have the SELECT privilege on table tbl1.
When executing a GRANT statement, the current user must have the GRANT OPTION privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Note

After privileges are granted to a user, the user must reconnect to OceanBase Database for the privileges to take effect.

Privileges required for granting roles to a user or another role

If the current user has the SUPER privilege, they can GRANT all roles.
When granting a role to other users or roles, the current user must own the role being granted and have the ADMIN OPTION privilege for the grant to succeed. For more information about how to view the privileges owned by the current user, see Query user privileges.

Syntax

Grant object privileges to a user or role

GRANT priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]
    ON [object_type] priv_level 
    TO {user [, user...]} 
    [WITH GRANT OPTION];

column_name_list:
    column_name [, column_name ...]

object_type:
    TABLE
    | FUNCTION
    | PROCEDURE
    | CATALOG

user:
    user_or_role
    | user_name IDENTIFIED [WITH auth_plugin] BY password 
    | user_name IDENTIFIED [WITH auth_plugin] BY PASSWORD password

user_or_role:
    user_name | role_name

Grant a role to a user or role

GRANT role_name [, role_name ...]
    TO user_or_role [, user_or_role ...]
    [WITH ADMIN OPTION];

user_or_role:
    user_name | role_name

Parameters

Parameter	Description
priv_type	The type of privileges to grant. Multiple privilege types can be granted by separating them with commas (`,`). For more information, see Privilege types in MySQL mode. Note: The PROXY privilege is currently supported only in the syntax, and cannot be used.
column_name_list	Optional. A list used to grant column-level privileges, where the granted permissions apply only to the specified columns. If not specified, the privileges apply to the entire table or object. For more information, see Directly grant privileges.
object_type	Optional. Specifies the type of object to which the privilege is being granted. For more information, see object_type below.
priv_level	Specifies the level at which the privileges are granted. The format is as follows: Privileges can be applied to all databases and all tables (`.`). Privileges can be applied to a specific database or a specific table (`db_name.`, `.table_name`). Privileges can be applied to a specific table within a specific database (`db_name.table_name`). If the object type to which the privilege applies is a catalog, the name of the external data catalog must be specified (`external_catalog_name`).
user	Specifies the user(s) to whom the privileges are granted. It can be one or more users, separated by commas (`,`). If the user does not exist, this statement will directly create the user.
auth_plugin	Specifies the authentication method for the user. Currently, only the `mysql_native_password` authentication plugin is supported.
BY password	Specifies a plaintext password for the user being granted privileges. After being saved in the `mysql.user` table, the server will store the password in encrypted form. If the password contains special characters such as ~!@#%^&*_-+=`\|(){}[]:;',.?/, it must be enclosed in single quotes (`''`) or double quotes (`""`).
BY PASSWORD password	Specifies an encrypted password for the user being granted privileges. This encrypted password will be stored directly in the `mysql.user` table.
WITH GRANT OPTION	Specifies whether the granted privileges can be further granted to other users. When revoking privileges, this will cascade.
role_name	Specifies the name of the role.
WITH ADMIN OPTION	Specifies whether the role's privileges can be further granted to others. When revoking privileges, this will not cascade.

object_type

Notice

When you use the object_type clause, make sure that the specified objects are of the corresponding types. Otherwise, a syntax error will occur.

TABLE: indicates that the object type to which the privileges apply is a table.
FUNCTION: indicates that the object type to which the privileges apply is a function.
PROCEDURE: indicates that the object type to which the privileges apply is a stored procedure.
CATALOG: indicates that the object type to which the privileges apply is a catalog.

Note

For OceanBase Database V4.3.5, the object type CATALOG is introduced starting from V4.3.5 BP2.

Here are some examples:

Grant the SELECT privilege to user001 on all objects in the test database. Since the specified objects are not of the specified type, an error occurs.
```
GRANT SELECT ON TABLE test.* TO user001;
```
The return result is as follows:
```
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
```
Grant the SELECT privilege to user001 on the tbl1 table in the test database.
```
GRANT SELECT ON TABLE test.tbl1 TO user001;
```
Grant the execute privilege to user002 on the calculate_salary function in the test database.
```
GRANT EXECUTE ON FUNCTION test.calculate_salary TO user002;
```
Grant the execute privilege to user003 on the pro_generate_data stored procedure in the test database.
```
GRANT EXECUTE ON PROCEDURE test.pro_generate_data TO user003;
```

Grant user004 the SELECT and USE CATALOG privileges on the catalog object test_odps_catalog.

GRANT SELECT, USE CATALOG ON CATALOG test_odps_catalog TO user004 WITH GRANT OPTION;

Examples

Example 1: Grant object privileges

Grant the existing user user1 the CREATE VIEW privilege on database db1 and allow the privilege to be granted to other users.
```
GRANT CREATE VIEW ON db1.* TO user1 WITH GRANT OPTION;
```
Grant the existing user user1 the CREATE privilege on database db1 and update user1's password.
```
GRANT CREATE ON db1.* TO user1 IDENTIFIED BY '********';
```
After execution, check the mysql.user table for the password of user user1, and you will see it has been updated to the new password.
Grant the non-existent user user2 the CREATE privilege on database db1 and set a password for user2.
```
GRANT CREATE ON db1.* TO user2 IDENTIFIED BY '********';
```
Grant the existing user user001 the SELECT privilege on column col1 of the table tbl1 in the database test.
```
GRANT SELECT(col1) ON test.tbl1 TO user001;
```
Grant the user user005 global-level privileges CREATE CATALOG and USE CATALOG.
```
GRANT CREATE CATALOG, USE CATALOG ON *.* TO user004 WITH GRANT OPTION;
```

Example 2: Grant a role to a user or role

Grant the privileges of role role001 to role role002 with the ability to grant it further.
```
GRANT role001 TO role002 WITH ADMIN OPTION;
```
Grant the privileges of role role001 to user user001 with the ability to grant it further.
```
GRANT role001 TO user001 WITH ADMIN OPTION;
```

References

For more information about how to grant privileges, see Grant privileges.
For more information about how to view user privileges, see View user privileges.
For more information about how to query the mysql.user table, see mysql.user.
For more information about how to add privileges to a role, see Add privileges to a role.
For more information about how to grant a role to a user or another role, see Grant a role to a user or role.
For more information about how to activate a role, see Activate a role.
For more information about indirect authorization, see Indirect authorization