About table privileges|V4.0.0| docs|Distributed Database

This topic describes how to grant and revoke table privileges.

Grant privileges

Overview

You can use the GRANT statement to grant privileges to other users as the system administrator.

Note

You can grant only the privileges that you have. For example, user1 attempts to grant the SELECT privilege on table tbl1 to user2. In this case, user1 must have the SELECT privilege on table tbl1 and the GRANT OPTION privilege.

After a user is granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.

Syntax

GRANT priv_type
    ON priv_level
    TO user_specification [, user_specification]...
     [WITH with_option ...]

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

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

with_option:
 GRANT OPTION

Parameters

Parameter	Description
priv_type	The type of the privilege to be granted. For information about the specific privilege types and their description, see the following table. To grant multiple privileges to a user, separate the types with commas (,).
priv_level	The level of the privilege to be granted. Privileges can be divided into the following levels: Global: Privileges at this level apply to all databases. You can use the GRANT ALL ON . statement to grant global privileges. Database level: Privileges at this level apply to all objects in a specified database. You can use the GRANT ALL ON db_name.* statement to grant database privileges. Table level: Privileges at this level apply to all columns in a specified table. You can use the GRANT ALL ON database_name.table_name statement to grant table privileges. If you replace `table_name` with an asterisk (*), privileges will be granted to all tables in the database.
user_specification	Grants privileges to a specific user. If the user does not exist, you can directly create the user. When you grant privileges to multiple users, the usernames must be separated with commas (,).
user IDENTIFIED BY 'password'	Displays the password in plaintext.
user IDENTIFIED BY PASSWORD 'password'	Displays the password in ciphertext.
with_option	Specifies whether to enable privilege delegation.

The following table lists the types of privileges that can be granted.

Privilege	Note
ALL PRIVILEGES	All privileges except GRANT OPTION.
ALTER	The ALTER TABLE privilege.
CREATE	The CREATE TABLE privilege.
CREATE USER	The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges.
CREATE TABLEGROUP	The global CREATE TABLEGROUP privilege.
DELETE	The DELETE privilege.
DROP	The DROP privilege.
GRANT OPTION	The GRANT OPTION privilege.
INSERT	The INSERT privilege.
SELECT	The SELECT privilege.
UPDATE	The UPDATE privilege.
SUPER	The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES	The global SHOW DATABASES privilege.
INDEX	The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW	The CREATE VIEW and DROP VIEW privileges.
SHOW VIEW	The SHOW CREATE VIEW privilege.

Examples

Grant all privileges to a user.

Grant all privileges to the ny user.

obclient> GRANT ALL PRIVILEGES ON *.* TO ny WITH GRANT OPTION;
Query OK, 0 rows affected

View the privileges of the ny user.

obclient> SHOW GRANTS FOR ny;
+-------------------------------------------------------+
| Grants for ny@%                                       |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ny' WITH GRANT OPTION |
+-------------------------------------------------------+
1 row in set

Grant a user all the privileges on a database.

Grant the ny user all the privileges on the data_ny database.

obclient> GRANT ALL PRIVILEGES ON data_ny.* to ny with GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

View the privileges of the ny user on the data_ny database.

obclient> SHOW GRANTS FOR ny;
+---------------------------------------------------------------+
| Grants for ny@%                                               |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                                    |
| GRANT ALL PRIVILEGES ON `data_ny`.* TO 'ny' WITH GRANT OPTION |
+---------------------------------------------------------------+
2 rows in set

Grant a user all the privileges on a table.

Grant the ny user all the privileges on the dws_ny table in the data_ny database.

obclient> GRANT ALL PRIVILEGES ON data_ny.dws_ny to ny with GRANT OPTION;
Query OK, 0 rows affected

View the privileges of the ny user on the dws_ny table in the data_ny database.

obclient> SELECT * FROM information_schema.TABLE_PRIVILEGES where TABLE_NAME='dws_ny';
+----------+---------------+--------------+------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+----------------+--------------+
| 'ny'@'%' | def           | data_ny      | dws_ny     | ALTER          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DELETE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DROP           | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INSERT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | UPDATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SELECT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INDEX          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE VIEW    | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SHOW VIEW      | YES          |
+----------+---------------+--------------+------------+----------------+--------------+
10 rows in set

Note

Both the root user and ny user can query the privileges on the dws_ny table in the TABLE_PRIVILEGES table.

Revoke privileges

Overview

You can use the REVOKE statement as the system administrator to revoke privileges from a user.

Usage notes:

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

REVOKE priv_type
     ON database.table_name
     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

Parameters

Parameter	Description
priv_type	The type of the privilege to be revoked. For information about the specific privilege types and their description, see the following table. Multiple privileges must be separated with commas (,).
database.table_name	The name of the table in the database on which the privilege is to be revoked. If you replace database or table_name with an asterisk (*), operation privileges on all tables in the database will be revoked.
user_name	The name of the user from which the privilege is to be revoked. Multiple usernames must be separated with commas (,).

The following table lists the privileges that can be revoked.

Privilege	Note
ALL PRIVILEGES	All privileges except GRANT OPTION.
ALTER	The ALTER TABLE privilege.
CREATE	The CREATE TABLE privilege.
CREATE USER	The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges.
CREATE TABLEGROUP	The global CREATE TABLEGROUP privilege.
DELETE	The DELETE privilege.
DROP	The DROP privilege.
GRANT OPTION	The GRANT OPTION privilege.
INSERT	The INSERT privilege.
SELECT	The SELECT privilege.
UPDATE	The UPDATE privilege.
SUPER	The SET GLOBAL privilege for modifying global system parameters.
SHOW DATABASES	The global SHOW DATABASES privilege.
INDEX	The CREATE INDEX and DROP INDEX privileges.
CREATE VIEW	The CREATE VIEW and DROP VIEW privileges.
SHOW VIEW	The SHOW CREATE VIEW privilege.

Note

Currently, all users in the SYS tenant can execute the CHANGE EFFECTIVE TENANT statement to revoke privileges of specified users in business tenants.

Examples

Revoke the UPDATE privilege from a user.

Revoke the UPDATE privilege on the dws_ny table in the data_ny database from the ny user.
```
obclient> revoke update on data_ny.dws_ny from ny;
Query OK, 0 rows affected
```

View the current privileges of the ny user on the dws_ny table in the data_ny database.

obclient> select * from information_schema.TABLE_PRIVILEGES where TABLE_NAME='dws_ny';
+----------+---------------+--------------+------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+----------------+--------------+
| 'ny'@'%' | def           | data_ny      | dws_ny     | ALTER          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DELETE         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | DROP           | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INSERT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SELECT         | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | INDEX          | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | CREATE VIEW    | YES          |
| 'ny'@'%' | def           | data_ny      | dws_ny     | SHOW VIEW      | YES          |
+----------+---------------+--------------+------------+----------------+--------------+
9 rows in set

Note

Both the root user and ny user can query the privileges on the dws_ny table in the TABLE_PRIVILEGES table.

Revoke the UPDATE privilege on a database from a user.

Revoke the UPDATE privilege on the dws_ny table in the data_ny database from the ny user.
```
obclient> revoke update on data_ny.* from ny;
Query OK, 0 rows affected
```

View the current privileges of the ny user on the data_ny database.

obclient> SHOW GRANTS FOR ny;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for ny@%                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                                                                                                       |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.* TO 'ny' WITH GRANT OPTION        |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.`dws_ny` TO 'ny' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set

Revoke the UPDATE privilege from a user.

Revoke the UPDATE privilege from the ny user.

obclient> revoke UPDATE on *.* from ny;
Query OK, 0 rows affected

View the current privileges of the ny user.

obclient> SHOW GRANTS FOR ny;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for ny@%                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ny'                                                                                                       |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.* TO 'ny' WITH GRANT OPTION        |
| GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, INDEX, CREATE VIEW, SHOW VIEW ON `data_ny`.`dws_ny` TO 'ny' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set