GRANT

Purpose

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 operation does not support cycles. 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.

Privilege requirements

Privilege requirements for granting object privileges to a user

• When executing the GRANT statement, the current user must have the privilege being granted. For example, if you want to use user user1 to grant the SELECT privilege on table tbl1 to user user2, then 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 OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Privilege requirements for granting roles to a user or role

• 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 being granted and the ADMIN OPTION privilege for the grant to be successful. For information about how to view the privileges of the current user, see View user privileges.

Syntax

Syntax for granting 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];

priv_type:
    READ
    | WRITE

column_name_list:
    column_name [, column_name ...]

object_type:
    TABLE
    | FUNCTION
    | PROCEDURE
    | CATALOG
    | LOCATION

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

Syntax for granting roles 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

object_type

• TABLE: The object to which the privilege is granted is a table.

• FUNCTION: The object to which the privilege is granted is a function.

• PROCEDURE: The object to which the privilege is granted is a stored procedure.

• CATALOG: The object to which the privilege is granted is a catalog.

• LOCATION: The object to which the privilege is granted is a location. You can grant the following privileges to a user for a location object:

  • READ: The read privilege for a location object.
  • WRITE: The write privilege for a location object.

  Note

  For OceanBase Database V4.4.x, the GRANT statement supports the LOCATION parameter and the READ and WRITE privileges starting from V4.4.1.

Here are some examples:

• Grant the SELECT privilege on all objects in the test database to user001. The specified object is not of the specified type, so an error is 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 the tbl1 table in the test database to user001.

  GRANT SELECT ON TABLE test.tbl1 TO user001;
  

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

  GRANT EXECUTE ON FUNCTION test.calculate_salary TO user002;
  

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

  GRANT EXECUTE ON PROCEDURE test.pro_generate_data TO user003;
  

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

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

• Grant the READ privilege on the test_location_local location to user005.

  GRANT READ ON LOCATION test_location_local TO user005 WITH GRANT OPTION;
  

Examples

Example 1: Grant object privileges

• Grant the CREATE VIEW privilege on database db1 to 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 database db1 to existing user user1 and change the password of user1.

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

  After execution, check the password of user1 in the mysql.user table. You will see that it has been updated to the new password.

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

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

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

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

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

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

Example 2: Grant a role to a user or another role

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

  GRANT role001 TO role002 WITH ADMIN OPTION;
  

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

  GRANT role001 TO user001 WITH ADMIN OPTION;
  

References

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