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 cannot be cyclic. 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 to be 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 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 and the ADMIN OPTION privilege for the grant to succeed. For more 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:
    ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INSERT
    | SELECT
    | UPDATE
    | INDEX
    | EXECUTE
    | SUPER
    | ALL [PRIVILEGES]
    | 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: 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.

• LOCATION: Specifies that the object type is a location. You can grant the following privileges to a user for a location object:

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

  Note

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

Examples

Example 1: Grant object privileges

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

  obclient> GRANT CREATE VIEW ON sales_db.* TO developer_user WITH GRANT OPTION;
  

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

  obclient> GRANT CREATE ON sales_db.* TO admin_user IDENTIFIED BY '********';
  

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

• Grant the CREATE privilege on the sales_db database to the non-existent user report_user, and set the password for report_user.

  obclient> GRANT CREATE ON sales_db.* TO report_user IDENTIFIED BY '********';
  

• Grant the SELECT privilege on the salary column of the employee_info table in the hr_db database to the existing user analyst_user.

  obclient> GRANT SELECT(salary) ON hr_db.employee_info TO analyst_user;
  

• Grant the CREATE CATALOG and USE CATALOG privileges on the global level to the user data_admin.

  obclient> GRANT CREATE CATALOG, USE CATALOG ON *.* TO data_admin WITH GRANT OPTION;
  

Example 2: Grant privileges by specifying the object type

• Grant the SELECT privilege on the orders table in the sales_db database to the user query_user.

  obclient> GRANT SELECT ON TABLE sales_db.orders TO query_user;
  

• Grant the execution privilege on the calculate_bonus function in the hr_db database to the user developer_user.

  obclient> GRANT EXECUTE ON FUNCTION hr_db.calculate_bonus TO developer_user;
  

• Grant the execution privilege on the update_employee_info stored procedure in the hr_db database to the user dba_user.

  obclient> GRANT EXECUTE ON PROCEDURE hr_db.update_employee_info TO dba_user;
  

• Grant the SELECT and USE CATALOG privileges on the external_data_catalog catalog object to the user data_engineer.

  obclient> GRANT SELECT, USE CATALOG ON CATALOG external_data_catalog TO data_engineer WITH GRANT OPTION;
  

• Grant the read privilege on the oss_data_location location object to the user etl_user.

  obclient> GRANT READ ON LOCATION oss_data_location TO etl_user WITH GRANT OPTION;
  

Example 3: Grant roles to users or roles

• Grant the privileges of the app_admin_role role to the department_admin_role role, and allow the privileges to be granted to other users.

  obclient> GRANT app_admin_role TO department_admin_role WITH ADMIN OPTION;
  

• Grant the privileges of the read_only_role role to the user analyst_user, and allow the privileges to be granted to other users.

  obclient> GRANT read_only_role TO analyst_user 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