GRANT

Purpose

You can use this statement to grant privileges to a user or role or grant roles to a user or role.

Limitations and considerations

Circular role granting is not supported. For example, if you have granted role A to role B and role B to role C, an error will be reported when you grant role C to role A.

Required privileges

Privileges required for granting privileges to a user or role

• You must have the privileges that you want to grant. For example, if you want to grant the SELECT privilege on the tbl1 table to user2 as user1, user1 must have the SELECT privilege on the tbl1 table.

• You must have the GRANT OPTION privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Privileges required for granting roles to a user or another role

• If you have the SUPER privilege, you can execute GRANT to grant any roles to any users or roles.
• To grant a role to a user or role, you must have the granted role and the ADMIN OPTION privilege. For more information about how to view your privileges, see View user privileges.

Syntax

Grant privileges to a user or role

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

column_name_list:
    column_name [, column_name ...]

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 another role

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

user_or_role:
    user_name | role_name

Parameters

Examples

Example 1: Grant object privileges

• Grant the CREATE VIEW privilege on the db1 database to the user1 user and enable privilege delegation.

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

• Grant the CREATE privilege on the db1 database to the user1 user and change the password for user1.

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

  Check the password of user1 in the mysql.user table. The password is updated to the new one.

• Grant the CREATE privilege on the db1 database to a nonexistent user named 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 user001 user.

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

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

• Grant the role role001 to the role role002 and enable role delegation.

  GRANT role001 TO role002 WITH ADMIN OPTION;
  

• Grant the role role001 to the user user001 and enable role delegation.

  GRANT role001 TO user001 WITH ADMIN OPTION;
  

References

• For more information about how to grant user privileges, see Grant direct privileges.
• For more information about how to view user privileges, see View user privileges.
• You can query the information about the created user in the mysql.user table. For more information about the mysql.user table, see mysql.user.
• Grant privileges to a role
• Grant a role to a user or another role
• Activate a role
• Grant indirect privileges