Modify user privileges|V3.2.4|OceanBase Database| docs|Distributed Database

Modify user privileges

Last Updated：2023-10-27 09:57:43 Updated

You can grant or revoke user privileges as needed.

Grant privileges by using SQL statements

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 user1 user wants to grant the SELECT privilege on the t1 table to the user2 user, the user1 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

The syntax for granting privileges is as follows:

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

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

Some notes about this:

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.
For more information about user privileges supported in MySQL mode, see Overview of user 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 user named user, execute the following statement:
```
obclient> GRANT ALL ON *.* TO user;
```
- To grant the INSERT and SELECT privileges on all tables in all databases to the user named user, execute the following statement:
```
obclient> GRANT SELECT, INSERT ON *.* TO user;
```
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 user named user, execute the following statement:
```
obclient> GRANT ALL ON db1.* TO user;
```
- To grant the INSERT and SELECT privileges on all tables in the db1 database to the user named user, execute the following statement:
```
obclient> GRANT SELECT, INSERT ON db1.* TO user;
```
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 user named user, execute the following statement:
```
obclient> GRANT SELECT, INSERT ON db1.tb1_name TO user;
```
- To grant management privileges on the tb1_name table in the db1 database to the user named user, execute the following statement:
```
obclient> GRANT ALL ON db1.tb1_name TO user;
```

For more information about the GRANT statement, see GRANT.

Revoke privileges by using SQL statements

Prerequisites

You must have the privileges to be revoked and the GRANT OPTION privilege. For example, if the user1 user wants to revoke the SELECT privilege on the t1 table from the user2 user, the user1 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 user1 has granted some privileges to user2, when the privileges of user1 are revoked, the privileges granted to user2 are not revoked.

Syntax for revoking privileges

The syntax for revoking privileges is as follows:

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

Some notes about this:

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.
For more information about user privileges supported in MySQL mode, see Overview of user 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 user named user, execute the following statement:
```
obclient> REVOKE SELECT, INSERT ON *.* FROM 'user';
```
Revoke database-level privileges

To revoke the INSERT and SELECT privileges on all tables in the db1 database from the user named user, execute the following statement:
```
obclient> REVOKE SELECT, INSERT ON db1 FROM 'user';
```
Revoke table-level privileges

To revoke the INSERT and SELECT privileges on the tb1_name table in the db1 database from the user named user, execute the following statement:
```
obclient> REVOKE SELECT, INSERT ON db1.tb1_name FROM 'user';
```

For more information about the REVOKE statement, see REVOKE.

Grant or revoke privileges in the OCP console

OceanBase Cloud Platform (OCP) V2.5.0 and later support modification of user privileges in MySQL mode.

Prerequisites

Before you modify user privileges, ensure that:

The TENANT_MANAGER role is assigned to you. Otherwise, request the OCP administrator to assign the role. For more information, see "Edit a user" in the OCP User Guide of the corresponding version.
Your password box contains the root password of the tenant. For more information about the password box, see the OCP User Guide of the corresponding version.

Procedure

Log on to the OCP console.
In the left-side navigation pane, click Tenants to go to the Tenants page.
In the tenant list, select a tenant whose Tenant Mode is MySQL to go to the Overview page.
In the left-side navigation pane, click User Management.
In the user list, find the target user. In the Actions column, click Modify Privileges.

In the dialog box that appears, modify the global privileges and database privileges.

The following table describes the privileges that you can specify in Global Permissions and Database Permissions.

Privilege	Description
ALTER	The ALTER TABLE privilege.
CREATE	The CREATE TABLE privilege.
DELETE	The DELETE privilege.
DROP	The DROP privilege.
INSERT	The INSERT privilege.
SELECT	The SELECT privilege.
UPDATE	The UPDATE privilege.
INDEX	The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW	The privilege to create and drop views.
SHOW VIEW	The SHOW CREATE VIEW privilege.
CREATE USER	The CREATE USER, DROPUSER, RENAME USER, and REVOKE ALLPRIVILEGES privileges.
PROCESS	The PROCESS privilege.
SUPER	The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES	The global SHOW DATABASES privilege.
GRANT OPTION	The GRANT OPTION privilege.

Click Submit.