This topic describes the privilege types supported by the MySQL-compatible mode of OceanBase Database.
Privilege types
The MySQL-compatible mode of OceanBase Database supports the following three levels of privileges:
- Global privileges: the privileges to manage a tenant, such as modifying system settings and accessing all tables in the tenant.
- Database privileges: the privileges to manage all objects in a database, such as creating or deleting tables in the database, and accessing those tables.
- Object privileges: the privileges to manage a specific object, such as accessing a specific table, view, or index.
The following table lists the privileges supported by the MySQL-compatible mode of OceanBase Database.
| Type | Privilege | Description |
|---|---|---|
| Object privilege | CREATE | The privilege to execute the CREATE TABLE statement. |
| Object privilege | SELECT | The privilege to query data in all columns or the specified columns of a table. |
| Object privilege | INSERT | The privilege to insert data into all columns or the specified columns of a table. |
| Object privilege | UPDATE | The privilege to update data in all columns or the specified columns of a table. |
| Object privilege | DELETE | The privilege to delete existing data. |
| Object privilege | DROP | The privilege to execute statements such as DROP TABLE and DROP VIEW. |
| Object privilege | INDEX | The privilege to create and delete table indexes. |
| Object privilege | ALTER | The privilege to execute statements such as RENAME TABLE and ALTER TABLE. |
| Object privilege | CREATE VIEW | The privilege to create views. |
| Object privilege | SHOW VIEW | The privilege to query views or learn how views operate. |
| Object privileges | REFERENCES | The privilege to create foreign keys. When you create a foreign key for a table, you must have the REFERENCES privilege on the parent table. |
| Object privileges | TRIGGER | The privilege to to activate triggers. You must have this privilege to create, view, or drop triggers. |
| Database privilege | SELECT | The privilege to query data in tables. |
| Database privilege | INSERT | The privilege to insert row data into tables. |
| Database privilege | UPDATE | The privilege to modify existing data. |
| Database privilege | DELETE | The privilege to delete existing data. |
| Database privilege | CREATE | The privilege to execute statements such as CREATE DATABASE, CREATE TABLE, CREATE SEQUENCE, and CREATE OUTLINE. |
| Database privilege | DROP | The privilege to drop existing databases, tables, and views. |
| Database privilege | INDEX | The privilege to create and delete table indexes. |
| Database privilege | ALTER | The privilege to execute statements such as ALTER DATABASE, RENAME TABLE, ALTER TABLE, ALTER SEQUENCE, and ALTER OUTLINE. |
| Database privilege | CREATE VIEW | The privilege to create views. |
| Database privilege | SHOW VIEW | The privilege to query views or learn how views operate. |
| Database privilege | CREATE ROUTINE | The privilege to create procedures and functions. |
| Database privilege | ALTER ROUTINE | The privilege to modify and delete procedures and functions. |
| Database privilege | EXECUTE | The privilege to execute procedures and functions. |
| Database privileges | REFERENCES | The privilege to create foreign keys. |
| Database privileges | TRIGGER | The privilege to activate triggers. You must have this privilege to create, view, or drop triggers. |
| Global privilege | CREATE | The privilege to execute statements such as CREATE DATABASE, CREATE TABLEGROUP, CREATE TABLE, ALTER SEQUENCE, and ALTER OUTLINE. |
| Global privilege | ALTER | The privilege to execute statements such as ALTER DATABASE, ALTER TABLEGROUP, RENAME TABLE, and ALTER TABLE. |
| Global privilege | SELECT | The privilege to query data in tables. |
| Global privilege | INSERT | The privilege to insert row data into tables. |
| Global privilege | UPDATE | The privilege to modify existing data. |
| Global privilege | DELETE | The privilege to delete existing data. |
| Global privilege | DROP | The privilege to execute statements such as DROP DATABASE, DROP TABLEGROUP, DROP TABLE, DROP VIEW, DROP SEQUENCE and DROP OUTLINE. |
| Global privilege | INDEX | The privilege to create and delete table indexes. |
| Global privilege | CREATE VIEW | The privilege to create views. |
| Global privilege | SHOW VIEW | The privilege to query views or learn how views operate. |
| Global privileges | REFERENCES | The privilege to create foreign keys. |
| Global privilege | ALTER TENANT | The privilege to modify tenant information. |
| Global privilege | ALTER SYSTEM | The privilege to execute the ALTER SYSTEM statement. |
| Global privilege | CREATE RESOURCE POOL | The privilege to create, modify, and delete resource pools. |
| Global privilege | CREATE RESOURCE UNIT | The privilege to create, modify, and delete resource units. |
| Global privilege | CREATE DATABASE LINK | The privilege to create database links. |
| Global privilege | DROP DATABASE LINK | The privilege to drop database links. |
| Global privilege | CREATE USER | The privilege to execute statements such as CREATE USER, ALTER USER, DROP USER, CREATE ROLE, and DROP ROLE. |
| Global privilege | CREATE ROUTINE | The privilege to create procedures and functions. |
| Global privilege | ALTER ROUTINE | The privilege to modify and delete procedures and functions. |
| Global privilege | CREATE TABLESPACE | The privilege to execute statements such as CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE. |
| Global privileges | CREATE ROLE | The privilege to execute the CREATE ROLE statement. |
| Global privileges | DROP ROLE | The privilege to execute the DROP ROLE statement. |
| Global privilege | EXECUTE | The privilege to execute procedures and functions. |
| Global privilege | PROCESS | The privilege to execute the SHOW PROCESSLIST statement to view processes of other users. |
| Global privileges | TRIGGER | The privilege to activate triggers. You must have this privilege to create, view, or drop triggers. |
| Global privilege | SHOW DB | The privilege to view all database names on a server, including the names of the databases that are accessible to the user. |
| Global privilege | SHUTDOWN | The privilege to execute the mysqladmin shutdown command.
NoteThis privilege can be granted or revoked for a user using the |
| Global privilege | RELOAD | The privilege to perform the Flush operation.
NoteThis privilege can be granted or revoked for a user using the |
| Global privilege | FILE | The privilege to execute the SELECT INTO OUTFILE and LOAD DATA INFILE statements. |
| Global privilege | SUPER | The privilege to perform some advanced management operations, such as deleting a user process by using the KILL statement, modifying global MySQL variables by using the SET GLOBAL statement, and running various commands related to copying and logging. |
| Global privilege | GRANT OPTION | Allows a user to grant the same privileges they have to other users. It is usually used with other privileges, such as SELECT, INSERT, and UPDATE. |
| Global privilege | ALL PRIVILEGES | Indicates that the user has all available privileges in the database (except for GRANT OPTION/ENCRYPT/DECRYPT). |
| Global privilege | ENCRYPT | Determines whether the user can invoke the ENHANCED_AES_ENCRYPT function. The user must have both the ENCRYPT privilege and the corresponding DML privileges to use the encryption function in DML statements. GRANT/REVOKE ALL PRIVILEGES does not include this privilege. This privilege is supported starting from V4.3.5 BP1. |
| Global privilege | DECRYPT | Determines whether the user can invoke the ENHANCED_AES_DECRYPT function. The user must have both the DECRYPT privilege and the corresponding DML privileges to use the decryption function in DML statements. GRANT/REVOKE ALL PRIVILEGES does not include this privilege. This privilege is supported starting from V4.3.5 BP1. |
| Global privilege | PROXY | Allows a user to connect and operate as another user. For example, User A can grant User B the privilege to connect and operate as User A without sharing the password. This is useful in scenarios such as applications requiring accounts with different privilege levels or implementing user privilege delegation. This privilege is supported starting from V4.3.5 BP1. |
| Data catalog-level privileges | CREATE CATALOG | Determines whether a user can execute the CREATE EXTERNAL CATALOG and DROP CATALOG statements. |
| Data catalog-level privileges | USE CATALOG | Determines whether a user can execute the SET CATALOG, SHOW CATALOGS, SHOW CREATE CATALOG, and SELECT FROM CATALOG statements. |
| User-level privileges | CREATE SENSITIVE RULE | Determines whether a user can execute the CREATE/DROP SENSITIVE RULE statements. This privilege is supported starting from V4.3.5 BP3. |
| User-level privileges | PLAINACCESS | Determines whether a user can access all plaintext data, including sensitive data, without being restricted by sensitive rules. This privilege is not included in GRANT/REVOKE ALL PRIVILEGES. This privilege is supported starting from V4.3.5 BP3. |
| Rule-level privileges | PLAINACCESS | Determines whether a user can access plaintext data in the columns associated with a specific rule. This privilege is supported starting from V4.3.5 BP3. |
