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

Loop granting is not supported. For example, if you 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, you want to grant the SELECT privilege on the table tbl1 to user2 as user1. In this case, user1 must have the SELECT privilege on the table tbl1.

• 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 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 the privileges that you have, see View user privileges.

Syntax

Grant privileges to a user or role

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

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

Examples

Example 1: Grant a privilege

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

  obclient> 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.

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

  After executing the statement, check the password of user1 in the mysql.user table. The password is updated to the newly set one.

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

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

Example 2: Grant a role

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

  obclient [test]> GRANT role001 TO role002 WITH ADMIN OPTION;
  

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

  obclient [test]> GRANT role001 TO user001 WITH ADMIN OPTION;
  

References

• For more information about how to grant user privileges, see Grant privileges.
• For more information about how to view user privileges, see View user privileges.
• You can query the mysql.user table to view information about the created user. 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