Grant direct privileges

This topic describes how to grant privileges to users by using the GRANT statement.

Prerequisites

You must have the GRANT OPTION privilege and the privileges to be granted. For example, if the test1 user wants to grant the SELECT privilege on the t1 table to the test2 user, the test1 user must have the GRANT OPTION privilege and the SELECT privilege on the t1 table.

For information about how to view your privileges, see View user privileges.

Considerations

Before you grant a privilege, note that:

• When you grant a privilege to a user, the user is automatically created if the user does not exist. However, if sql_mode='no_auto_create_user' is specified and IDENTIFIED BY is not used in the statement to specify a password, the user cannot be created directly.

• When granting multiple privileges to a user at the same time, separate the privileges with commas (,).

• When granting a privilege to multiple users at the same time, separate the usernames with commas (,).

• If a user has been granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.

• OceanBase Database does not support the CHANGE EFFECTIVE TENANT statement for privilege control. Therefore, you can grant a privilege to all users in the sys tenant.

Syntax for granting privileges

The syntax for granting privileges is as follows:

GRANT priv_type
    ON priv_level
    TO user_specification [, user_specification]...
     [WITH GRANT OPTION];

priv_level:
      *
    | *.*
    | database_name.*
    | database_name.table_name
    | table_name
    | database_name.routine_name

user_specification:
user_name [IDENTIFIED BY [PASSWORD] 'password']

where:

• priv_type: the type of the privilege to be granted. When you grant multiple privileges to a user at a time, separate the privileges with commas (,).

  To grant a column-level privilege, specify the column names after the privilege type in the priv(col_list) format, and separate the column names with commas (,). For example, to grant the SELECT privilege on the c1 column, specify SELECT(c1).

  When you grant a privilege on the columns of a table, observe the following notes:

  • To allow a user to execute the UPDATE statement on a column, you need to grant the user the UPDATE privilege on the column and the SELECT privilege on other columns to be accessed.

    Here is an example:

    obclient> UPDATE tb1 SET c1=c3, c2=1+3 WHERE c4=1;
    

    The preceding statement specifies to update the c1 and c2 columns, which requires access to the c3 and c4 columns. Therefore, you need to grant the user the UPDATE privilege on the c1 and c2 columns, and the SELECT privilege on the c3 and c4 columns.

  • To allow a user to execute the INSERT or REPLACE statement on a column, you need to grant the user the INSERT privilege on the column and the SELECT privilege on other columns to be accessed.

  • To allow a user to execute other statements on a column, grant the user the SELECT privilege on the columns to be accessed.

  For more information about the privilege types supported in MySQL mode, see Privilege types in MySQL mode.

• priv_level: the level of the privilege to be granted. In MySQL mode, OceanBase Database provides privileges of the following levels:

  • Global: Privileges at this level apply to all databases. You can use the GRANT ... ON *.* statement to grant global privileges.

  • Database level: Privileges at this level apply to all objects in a specified database. You can use GRANT ... ON db_name.* to grant database privileges.

  • Table level: Privileges at this level apply to all columns in a specified table. You can use GRANT ... ON database_name.table_name to grant table-level privileges.

    When you grant column-level privileges, you can also use GRANT ... ON database_name.table_name to specify the table name.

• user_specification: the user to which one or more privileges are granted. If the user does not exist, the user is automatically created.

  When you grant a privilege to multiple users at a time, separate the usernames with commas (,).

• user_name IDENTIFIED BY 'password' and user_name IDENTIFIED BY PASSWORD 'password': The password in the user_name IDENTIFIED BY 'password' clause is in plaintext. The password in the user_name IDENTIFIED BY PASSWORD 'password' clause is in ciphertext.

• WITH GRANT OPTION: specifies whether the privileges can be delegated or revoked.

Examples

• Grant global privileges

  Global privileges apply to all databases. You can use the GRANT ... ON *.* statement to grant global privileges.

  • To grant all privileges on all objects in all databases to the test user, execute the following statement:

    obclient> GRANT ALL ON *.* TO test;
    

  • To grant the INSERT and SELECT privileges on all tables in all databases to the test user, execute the following statement:

    obclient> GRANT SELECT, INSERT ON *.* TO test;
    

• Grant database-level privileges

  Database-level privileges are management privileges on all objects in a specified database. You can use GRANT ... ON db_name.* to grant database-level privileges.

  • To grant management privileges on all objects in the db1 database to the test user, execute the following statement:

    obclient> GRANT ALL ON db1.* TO test;
    

  • To grant the INSERT and SELECT privileges on all tables in the db1 database to the test user, execute the following statement:

    obclient> GRANT SELECT, INSERT ON db1.* TO test;
    

• Grant table-level privileges

  Table-level privileges are management privileges on a specified table in a specified database. You can use GRANT ... ON db_name.tb1_name to grant table-level privileges.

  • To grant the INSERT and SELECT privileges on the tb1_name table in the db1 database to the test user, execute the following statement:

    obclient> GRANT SELECT, INSERT ON db1.tb1_name TO test;
    

  • To grant management privileges on the tb1_name table in the db1 database to the test user, execute the following statement:

    obclient> GRANT ALL ON db1.tb1_name TO test;
    

• Grant column-level privileges

  Column-level privileges are management privileges on a specified column of a specified table.

  The following sample statement grants the test user the SELECT privilege on the c1 column and the INSERT privilege on the c1 and c2 columns of the tb1_name table in the db1 database:

  obclient> GRANT SELECT(c1), INSERT (c1, c2) ON db1.tb1_name TO test;
  

For more information about the GRANT statement, see GRANT.