Modify user privileges

Modifying user privileges includes granting and revoking user privileges.

Grant user privileges

You can use the GRANT statement to grant administrator privileges, database privileges, or object privileges to users.

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 the following:

• 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 the same time, separate the privileges with commas (,).

  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. The MySQL mode of OceanBase Database provides the following three levels of privileges:

  • Administrator level: Privileges at this level apply to all databases. You can use GRANT ... ON *.* to grant administrator privileges.

  • Database level: Privileges at this level apply to all objects in a specified database. GRANT ... ON db_name.* is used to grant database-level privileges.

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

• 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 the same 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 administrator privileges

  Administrator privileges are global privileges that apply to all databases. Generally, you can use GRANT ... ON *.* to grant administrator 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;
    

For more information about the GRANT statement, see GRANT.

Revoke user privileges

Prerequisites

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

• To revoke the ALL PRIVILEGES and GRANT OPTION privileges, you must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the table.

Considerations

• When you revoke multiple privileges from a user at the same time, separate the privileges with commas (,).

• When you revoke a privilege from multiple users at the same time, separate the usernames with commas (,).

• The revocation operation will not cascade. For example, if the test1 user has granted some privileges to the test2 user, revoking test1's privileges will not also revoke test2's corresponding privileges.

Syntax for revoking privileges

The syntax for revoking privileges is as follows:

REVOKE priv_type 
     ON priv_level 
     FROM 'user_name';

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

where

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

  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 revoked. The MySQL mode of OceanBase Database provides the following three levels of privileges:

  • Administrator level: Privileges at this level apply to all databases. You can use REVOKE ... ON *.* to revoke administrator privileges.

  • Database level: Privileges at this level apply to all objects in a specified database. REVOKE ... ON db_name.* is used to revoke database-level privileges.

  • Table level: Privileges at this level apply to all columns in a specified table. REVOKE ... ON database_name.table_name is used to revoke table-level privileges.

• user_name: the user whose privileges are to be revoked. When you revoke a privilege from multiple users at the same time, separate the usernames with commas (,).

Examples

• Revoke administrator privileges

  To revoke the INSERT and SELECT privileges on all tables in all databases from the test user, execute the following statement:

  obclient> REVOKE SELECT, INSERT ON *.* FROM 'test';
  

• Revoke database-level privileges

  To revoke the INSERT and SELECT privileges on all tables in the db1 database from the test user, execute the following statement:

  obclient> REVOKE SELECT, INSERT ON db1 FROM 'test';
  

• Revoke table-level privileges

  To revoke the INSERT and SELECT privileges on the tb1_name table in the db1 database from the test user, execute the following statement:

  obclient> REVOKE SELECT, INSERT ON db1.tb1_name FROM 'test';
  

For more information about the REVOKE statement, see REVOKE.