GRANT

Description

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

Limitations and considerations

The GRANT statement does not support cyclic operations. For example, if role A is granted to role B, and role B is granted to role C, granting role C to role A will result in an error.

Privilege requirements

Privilege requirements for granting object privileges to users

• When executing the GRANT statement, the current user must have the privileges to be granted. For example, if you want to grant the SELECT privilege on table tbl1 to user user2 using user user1, user user1 must have the SELECT privilege on table tbl1.

• When executing the GRANT statement, the current user must have the GRANT OPTION privilege. For more information about seekdb privileges, see seekdb privilege types.

info

After granting privileges to a user, the user must reconnect to seekdb for the privileges to take effect.

Privilege requirements for granting roles to users or roles

• If the current user has the SUPER privilege, they can grant all roles.
• When granting a role to another user or role, the current user must have the role and the ADMIN OPTION privilege for the grant to be successful. For more information about how to view the privileges of the current user, see View user privileges.

Syntax

Grant object privileges to users or roles

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 roles to users or roles

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

user_or_role:
    user_name | role_name

Parameter description

Parameter	Description
priv_type	Specifies the type of privilege to be granted. You can grant multiple privileges. Separate the privileges with commas (,). For more information about the privilege types and their descriptions, see seekdb privilege types. Note: The PROXY parameter is only recorded in the syntax of the current version and does not take effect.
column_name_list	Optional. Specifies the list of columns to which column-level privileges are granted. The granted privileges apply only to the specified columns. If this parameter is 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 the object to which privileges are granted (i.e., the type of object to which the privileges apply). For more information, see object_type below.
priv_level	Specifies the level at which privileges are granted. The format is as follows: You can specify that the privileges apply to all databases and tables (*.*). You can specify that the privileges apply to a specific database or table (db_name.* or *.table_name). You can specify that the privileges apply to a specific table in a specific database (db_name.table_name). If the object type is a catalog, you must specify the name of the external data catalog (external_catalog_name).
user	Specifies the user to whom privileges are granted. You can specify one or more users. Separate multiple users with commas (,). If the user does not exist, the 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 password for the user to be granted privileges. The password is stored in plaintext in the mysql.user table and then converted to ciphertext by the server. If the password contains special characters ~!@#%^&*_-+=`|()[]:;',.?/, you must enclose it in English quotation marks ('' or "").
BY PASSWORD password	Specifies a password for the user to be granted privileges. The password is stored in ciphertext directly in the mysql.user table.
WITH GRANT OPTION	Specifies whether the privileges can be delegated and whether revocation is cascading.
role_name	Specifies the name of the role.
WITH ADMIN OPTION	Specifies whether the role privileges can be delegated and whether revocation is cascading.

object_type

tip

When using the object_type clause, make sure that the specified object is of the corresponding type. Otherwise, a syntax error will occur.

• TABLE: Specifies that the object type is a table.

• FUNCTION: Specifies that the object type is a function.

• PROCEDURE: Specifies that the object type is a stored procedure.

• CATALOG: Specifies that the object type is a catalog.

  info

  The CATALOG object type is introduced for seekdb privileges.

Examples:

• Grant the SELECT privilege on all objects in the test database to user001. Since the specified object is not of the specified type, an error will be returned.

  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 on table tbl1 in the test database to user001.

  GRANT SELECT ON TABLE test.tbl1 TO user001;

• Grant the execute privilege on function calculate_salary in the test database to user002.

  GRANT EXECUTE ON FUNCTION test.calculate_salary TO user002;

• Grant the execute privilege on stored procedure pro_generate_data in the test database to user003.

  GRANT EXECUTE ON PROCEDURE test.pro_generate_data TO user003;

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

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

Examples

Example 1: Grant object privileges

• Grant the CREATE VIEW privilege on the db1 database to the existing user user1, and allow the privilege to be granted to other users.

  GRANT CREATE VIEW ON db1.* TO user1 WITH GRANT OPTION;

• Grant the CREATE privilege on the db1 database to the existing user user1, and change the password of user1.

  GRANT CREATE ON db1.* TO user1 IDENTIFIED by '********';

  After the preceding statement is executed, you can query the mysql.user table to view the password of the user1 user. The password is updated to the latest one.

• Grant the CREATE privilege on the db1 database to the non-existent user user2, and set a password for user2.

  GRANT CREATE ON db1.* TO user2 IDENTIFIED by '********';

• Grant the SELECT privilege on the col1 column of the tbl1 table in the test database to the existing user user001.

  GRANT SELECT(col1) ON test.tbl1 TO user001;

• Grant the CREATE CATALOG and USE CATALOG privileges at the global level to the user user005.

  GRANT CREATE CATALOG, USE CATALOG ON *.* TO user004 WITH GRANT OPTION;

Example 2: Grant roles to users or roles

• Grant the privileges of the role001 role to the role002 role and allow the privilege to be granted to other users.

  GRANT role001 TO role002 WITH ADMIN OPTION;

• Grant the privileges of the role001 role to the user001 user and allow the privilege to be granted to other users.

  GRANT role001 TO user001 WITH ADMIN OPTION;

References

• For more information about how to grant privileges to a user, see Grant privileges.
• For more information about how to view the privileges of a user, see View user privileges.
• You can query the mysql.user table to view the information about the created users. For more information about the mysql.user table, see mysql.user.
• Add privileges to a role
• Grant a role to a user or role
• Activate a role
• Indirectly grant privileges