Modify user privileges

You can grant and revoke user privileges.

Grant privileges

You can use the GRANT statement to grant user-level, database-level, or object privileges to a user.

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, take note of the following items:

• When you grant a privilege to a user, the user is automatically created if the user does not exist. If sql_mode='no_auto_create_user' is specified and IDENTIFIED BY is not used to specify a password, the system cannot create a user.

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

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

• After a user is 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

Syntax:

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

privilege_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE
    | CREATE SYNONYM

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

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

Notes:

• 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 (,).

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

  • User level: Privileges at this level apply to all databases. You can use GRANT ... ON *.* to grant user-level 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-level 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.

• 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 user-level privileges

  User-level privileges are global privileges that apply to all databases. You can use GRANT ... ON *.* to grant user-level 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 privileges

Prerequisites

• You must have the privileges to be revoked and the GRANT OPTION privilege. 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 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 a time, separate the privileges with commas (,).

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

• The revocation does not extend to dependent users. For example, if the test1 user has granted some privileges to the test2 user, when the privileges of the test1 user are revoked, the privileges granted to the test2 user will not be revoked.

Syntax for revoking privileges

Syntax:

REVOKE priv_type
     ON priv_level
     FROM 'user_name';

priv_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE

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

Notes:

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

• priv_level: the level of the privilege to be revoked. OceanBase Database provides privileges of the following levels:

  • User level: Privileges at this level apply to all databases. You can use REVOKE ... ON *.* to revoke user-level privileges.

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

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

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

Examples

• Revoke user-level 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.