This topic describes the privilege types in MySQL-compatible mode of OceanBase Database.
Privilege types
In MySQL-compatible mode, OceanBase Database supports the following privilege levels:
- Global privileges: These privileges affect the entire tenant. For example, you can modify system settings and access all tables.
- Database privileges: These privileges affect all objects in a specific database. For example, you can create and drop tables in the database and access tables.
- Object privileges: These privileges affect specific objects. For example, you can access a specific table, view, or index.
The following table lists the privileges supported in OceanBase Database's MySQL-compatible mode.
| Privilege category | Privilege | Description |
|---|---|---|
| Object privileges | CREATE | Determines whether a user can execute the CREATE TABLE statement. |
| Object privileges | SELECT | Determines whether a user can query data in all columns or specified columns of a table. |
| Object privileges | INSERT | Determines whether a user can insert data into all columns or specified columns of a table. |
| Object privileges | UPDATE | Determines whether a user can update data in all columns or specified columns of a table. |
| Object privileges | DELETE | Determines whether a user can delete existing data. |
| Object privileges | DROP | Determines whether a user can execute the DROP TABLE or DROP VIEW statement. |
| Object privileges | INDEX | Determines whether a user can create and drop indexes on tables. |
| Object privileges | ALTER | Determines whether a user can execute the RENAME TABLE or ALTER TABLE statement. |
| Object privileges | CREATE VIEW | Determines whether a user can create a view. |
| Object privileges | SHOW VIEW | Determines whether a user can view a view or understand how a view executes. |
| Object privileges | REFERENCES | Determines whether a user can create foreign keys. When creating a foreign key for a table, the user must have the REFERENCES privilege on the parent table. |
| Object privileges | TRIGGER | Determines whether a user has the privilege to activate trigger operations. The user must have this privilege to create, view, or drop triggers. |
| Database privileges | SELECT | Determines whether a user can query data in tables. |
| Database privileges | INSERT | Determines whether a user can insert rows into tables. |
| Database privileges | UPDATE | Determines whether a user can modify existing data. |
| Database privileges | DELETE | Determines whether a user can delete existing data. |
| Database privileges | CREATE | Determines whether a user can execute the CREATE DATABASE, CREATE TABLE, CREATE SEQUENCE, or CREATE OUTLINE statement. |
| Database privileges | DROP | Determines whether a user can execute the DROP DATABASE, DROP TABLE, DROP VIEW, DROP SEQUENCE, or DROP OUTLINE statement. |
| Database privileges | INDEX | Determines whether a user can create and drop indexes on tables. |
| Database privileges | ALTER | Determines whether a user can execute the ALTER DATABASE, RENAME TABLE, ALTER TABLE, ALTER SEQUENCE, or ALTER OUTLINE statement. |
| Database privileges | CREATE VIEW | Determines whether a user can create a view. |
| Database privileges | SHOW VIEW | Determines whether a user can view a view or understand how a view executes. |
| Database privileges | CREATE ROUTINE | Determines whether a user can create stored procedures and functions. |
| Database privileges | ALTER ROUTINE | Determines whether a user can modify or drop stored procedures and functions. |
| Database privileges | EXECUTE | Determines whether a user can execute stored procedures and functions. |
| Database privileges | REFERENCES | Determines whether a user can create foreign keys. |
| Database privileges | TRIGGER | Determines whether a user has the privilege to activate trigger operations. The user must have this privilege to create, view, or drop triggers. |
| Global privileges | CREATE | Determines whether a user can execute the CREATE DATABASE, CREATE TABLEGROUP, CREATE TABLE, CREATE SEQUENCE, or CREATE OUTLINE statement. |
| Global privileges | ALTER | Determines whether a user can execute the ALTER DATABASE, ALTER TABLEGROUP, RENAME TABLE, ALTER TABLE, ALTER SEQUENCE, or ALTER OUTLINE statement. |
| Global privileges | SELECT | Determines whether a user can query data in tables. |
| Global privileges | INSERT | Determines whether a user can insert rows into a table. |
| Global privileges | UPDATE | Determines whether a user can modify existing data. |
| Global privileges | DELETE | Determines whether a user can delete existing data. |
| Global privileges | DROP | Determines whether a user can execute DROP DATABASE, DROP TABLEGROUP, DROP TABLE, DROP VIEW, DROP SEQUENCE, DROP OUTLINE, and other similar statements. |
| Global privileges | INDEX | Determines whether a user can create and drop table indexes. |
| Global privileges | CREATE VIEW | Determines whether a user can create views. |
| Global privileges | SHOW VIEW | Determines whether a user can view views or understand how they execute. |
| Global privileges | REFERENCES | Determines whether a user can create foreign keys. |
| Global privileges | ALTER TENANT | The privilege to modify tenant information. |
| Global privileges | ALTER SYSTEM | The privilege to execute ALTER SYSTEM commands. |
| Global privileges | CREATE RESOURCE POOL | The privilege to create, modify, and drop resource pools. |
| Global privileges | CREATE RESOURCE UNIT | The privilege to create, modify, and drop resource units. |
| Global privileges | CREATE DATABASE LINK | The privilege to create database links. |
| Global privileges | DROP DATABASE LINK | The privilege to drop database links. |
| Global privileges | CREATE USER | Determines whether a user can execute CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE, and other similar statements. |
| Global privileges | CREATE ROUTINE | Determines whether a user can create procedures and functions. |
| Global privileges | ALTER ROUTINE | Determines whether a user can modify or drop procedures and functions. |
| Global privileges | CREATE TABLESPACE | Determines whether a user can execute CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, and other similar statements. |
| Global privileges | CREATE ROLE | Determines whether a user can execute CREATE ROLE statements. |
| Global privileges | DROP ROLE | Determines whether a user can execute DROP ROLE statements. |
| Global privileges | EXECUTE | Determines whether a user can execute procedures and functions. |
| Global privileges | PROCESS | Determines whether a user can view other users' processes by using the SHOW PROCESSLIST command. |
| Global privileges | TRIGGER | Determines whether a user can activate trigger operations. When creating, viewing, or dropping a trigger, this privilege is required. |
| Global privileges | SHOW DB | Determines whether a user can view the names of all databases on the server, including those to which the user has sufficient access privileges. |
| Global privileges | SHUTDOWN | Determines whether a user can execute the mysqladmin shutdown command.
NoteYou can use the |
| Global privileges | RELOAD | Determines whether a user can execute flush operations.
NoteYou can use the |
| Global privileges | FILE | Determines whether a user can execute the SELECT INTO OUTFILE and LOAD DATA INFILE commands. |
| Global privileges | SUPER | Determines whether a user can execute powerful management features, such as using the KILL command to delete user processes, using SET GLOBAL to modify global MySQL variables, and executing various commands related to replication and logs. |
| Global privileges | GRANT OPTION | Allows a user to grant the privileges they own to other users. This privilege is usually used with other privileges, such as SELECT, INSERT, and UPDATE. |
| Global privileges | ALL PRIVILEGES | Indicates that a user has all available privileges in the database, except for GRANT OPTION, ENCRYPT, and DECRYPT. |
| Global privileges | ENCRYPT | Specifies whether a user has the privilege to call the ENHANCED_AES_ENCRYPT function. A user can only use the above encryption function in DML statements if they have both the ENCRYPT privilege and the corresponding DML privileges. The GRANT/REVOKE ALL PRIVILEGES statement does not include this privilege. |
| Global privileges | DECRYPT | Specifies whether a user has the privilege to call the ENHANCED_AES_DECRYPT function. A user can only use the above decryption function in DML statements if they have both the DECRYPT privilege and the corresponding DML privileges. The GRANT/REVOKE ALL PRIVILEGES statement does not include this privilege. |
| Global privileges | PROXY | Allows a user to connect and operate on behalf of another user. For example, it allows user A to grant user B the privilege to connect and operate on behalf of user A without sharing passwords. This privilege is applicable in scenarios such as when an application needs to use accounts with different permission levels or to implement user permission delegation. In the current version, only the keyword privilege is supported, and the feature is not effective. |
| Global privileges | CREATE LOCATION
NoteFor OceanBase Database V4.4.x, the |
Specifies whether a user has the privilege to execute the CREATE LOCATION, ALTER LOCATION, and DROP LOCATION statements. |
| Data catalog privileges | CREATE CATALOG | Specifies whether a user has the privilege to execute the CREATE EXTERNAL CATALOG and DROP CATALOG statements. |
| Data catalog privileges | USE CATALOG | Specifies whether a user has the privilege to execute the SET CATALOG, SHOW CATALOGS, SHOW CREATE CATALOG, and SELECT FROM CATALOG statements. |
| Object privileges | CREATE AI MODEL | Specifies whether a user has the privilege to call the CREATE_AI_MODEL and CREATE_AI_MODEL_ENDPOINT procedures in the DBMS_AI_SERVICE system package. |
| Object privileges | ALTER AI MODEL | Specifies whether a user has the privilege to call the ALTER_AI_MODEL_ENDPOINT procedure in the DBMS_AI_SERVICE system package. |
| Object privileges | DROP AI MODEL | Specifies whether a user has the privilege to call the DROP_AI_MODEL and DROP_AI_MODEL_ENDPOINT procedures in the DBMS_AI_SERVICE system package. |
| Object privileges | ACCESS AI MODEL | Specifies whether a user has the privilege to call AI functions such as AI_COMPLETE, AI_EMBED, and AI_RERANK. |
| Global privileges | CREATE SENSITIVE RULE | Specifies whether a user has the privilege to execute the CREATE/DROP SENSITIVE RULE statements. The root user has this privilege by default and it cannot be revoked. This privilege is also automatically granted when a new cluster is created or after an upgrade. |
| Global privileges | PLAINACCESS | Specifies whether a user has the privilege to access all plaintext data, including sensitive data, without being restricted by sensitive rules. The GRANT/REVOKE ALL PRIVILEGES statement does not include this privilege. The root user has this privilege by default and it cannot be revoked. This privilege is also automatically granted when a new cluster is created or after an upgrade. |
| Rule privileges | PLAINACCESS | Specifies whether a user has the privilege to access plaintext data in the columns associated with a specific rule. The user who creates the rule does not automatically have the PLAINACCESS privilege for that rule and must be granted it separately. |