This topic describes the privilege types in MySQL mode of OceanBase Database.
Privilege levels
In MySQL mode, privileges are divided into three levels:
- Global privileges: Privileges that affect the entire tenant. Examples include modifying system settings and accessing all tables.
- Database privileges: Privileges that affect all objects in a specific database. Examples include creating and deleting tables in the database and accessing tables.
- Object privileges: Privileges that affect specific objects. Examples include accessing a specific table, view, or index.
The following table lists the privileges related to MySQL mode in OceanBase Database.
| Privilege category | Privilege | Description |
|---|---|---|
| Object privileges | CREATE | Determines whether the user can execute the CREATE TABLE statement. |
| Object privileges | SELECT | Determines whether the user can query all columns or specified columns in the table. |
| Object privileges | INSERT | Determines whether the user can insert data into all columns or specified columns in the table. |
| Object privileges | UPDATE | Determines whether the user can update data in all columns or specified columns in the table. |
| Object privileges | DELETE | Determines whether the user can delete existing data. |
| Object privileges | DROP | Determines whether the user can execute the DROP TABLE, DROP VIEW, and other similar statements. |
| Object privileges | INDEX | Determines whether the user can create and drop table indexes. |
| Object privileges | ALTER | Determines whether the user can execute the RENAME TABLE, ALTER TABLE, and other similar statements. |
| Object privileges | CREATE VIEW | Determines whether the user can create views. |
| Object privileges | SHOW VIEW | Determines whether the user can view views or understand how views execute. |
| Object privileges | REFERENCES | Determines whether the 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 the user has the privilege to activate trigger operations. The user must have this privilege to create, view, and drop triggers. |
| Database privileges | SELECT | Determines whether the user can query data in the table. |
| Database privileges | INSERT | Determines whether the user can insert row data into the table. |
| Database privileges | UPDATE | Determines whether the user can modify existing data. |
| Database privileges | DELETE | Determines whether the user can delete existing data. |
| Database privileges | CREATE | Determines whether the user can execute the CREATE DATABASE, CREATE TABLE, CREATE SEQUENCE, and CREATE OUTLINE statements. |
| Database privileges | DROP | Determines whether the user can execute the DROP DATABASE, DROP TABLE, DROP VIEW, DROP SEQUENCE, and DROP OUTLINE statements. |
| Database privileges | INDEX | Determines whether the user can create and drop table indexes. |
| Database privileges | ALTER | Determines whether the user can execute the ALTER DATABASE, RENAME TABLE, ALTER TABLE, ALTER SEQUENCE, and ALTER OUTLINE statements. |
| Database privileges | CREATE VIEW | Determines whether the user can create views. |
| Database privileges | SHOW VIEW | Determines whether the user can view views or understand how views execute. |
| Database privileges | CREATE ROUTINE | Determines whether the user can create procedures and functions. |
| Database privileges | ALTER ROUTINE | Determines whether the user can modify or drop procedures and functions. |
| Database privileges | EXECUTE | Determines whether the user can execute procedures and functions. |
| Database privileges | REFERENCES | Determines whether the user can create foreign keys. |
| Database privileges | TRIGGER | Determines whether the user has the privilege to activate trigger operations. The user must have this privilege to create, view, and drop triggers. |
| Global privileges | CREATE | Determines whether the user can execute the CREATE DATABASE, CREATE TABLEGROUP, CREATE TABLE, CREATE SEQUENCE, and CREATE OUTLINE statements. |
| Global privileges | ALTER | Determines whether the user can execute the ALTER DATABASE, ALTER TABLEGROUP, RENAME TABLE, ALTER TABLE, ALTER SEQUENCE, and ALTER OUTLINE statements. |
| Global privileges | SELECT | Determines whether the user can query data in the table. |
| Global Privileges | INSERT | Indicates whether a user can insert rows into a table. |
| Global Privileges | UPDATE | Indicates whether a user can modify existing data. |
| Global Privileges | DELETE | Indicates whether a user can delete existing data. |
| Global Privileges | DROP | Indicates whether a user can execute the DROP DATABASE, DROP TABLEGROUP, DROP TABLE, DROP VIEW, DROP SEQUENCE, DROP OUTLINE, and other similar statements. |
| Global Privileges | INDEX | Indicates whether a user can create and drop table indexes. |
| Global Privileges | CREATE VIEW | Indicates whether a user can create views. |
| Global Privileges | SHOW VIEW | Indicates whether a user can view views or understand how views execute. |
| Global Privileges | REFERENCES | Indicates whether a user can create foreign keys. |
| Global Privileges | ALTER TENANT | Grants the privilege to modify tenant information. |
| Global Privileges | ALTER SYSTEM | Grants the privilege to execute the ALTER SYSTEM statement. |
| Global Privileges | CREATE RESOURCE POOL | Grants the privilege to create, modify, and drop resource pools. |
| Global Privileges | CREATE RESOURCE UNIT | Grants the privilege to create, modify, and drop resource units. |
| Global Privileges | CREATE DATABASE LINK | Grants the privilege to create database links. |
| Global Privileges | DROP DATABASE LINK | Grants the privilege to drop database links. |
| Global Privileges | CREATE USER | Indicates whether a user can execute the CREATE USER, ALTER USER, DROP USER, CREATE ROLE, and DROP ROLE statements. |
| Global Privileges | CREATE ROUTINE | Indicates whether a user can create stored procedures and functions. |
| Global Privileges | ALTER ROUTINE | Indicates whether a user can modify or drop stored procedures and functions. |
| Global Privileges | CREATE TABLESPACE | Indicates whether a user can execute the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. |
| Global Privileges | CREATE ROLE | Indicates whether a user can execute the CREATE ROLE statement. |
| Global Privileges | DROP ROLE | Indicates whether a user can execute the DROP ROLE statement. |
| Global Privileges | EXECUTE | Indicates whether a user can execute stored procedures and functions. |
| Global Privileges | PROCESS | Indicates whether a user can view other users' processes by using the SHOW PROCESSLIST statement. |
| Global Privileges | TRIGGER | Indicates whether a user can activate trigger operations. To create, view, or drop a trigger, the user must have this privilege. |
| Global Privileges | SHOW DB | Indicates 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 | Indicates whether a user can execute the mysqladmin shutdown statement.
NoteYou can use the |
| Global Privileges | RELOAD | Indicates whether a user can execute flush operations.
NoteYou can use the |
| Global Privileges | FILE | Indicates whether a user can execute the SELECT INTO OUTFILE and LOAD DATA INFILE statements. |
| Global Privileges | SUPER | Indicates whether a user can execute powerful management functions, such as using the KILL statement to terminate user processes, using the SET GLOBAL statement to modify global MySQL variables, and executing various statements related to replication and logs. |
| Global Privileges | GRANT OPTION | Grants the privilege to grant the privileges that the user owns to other users. This privilege is usually used with other privileges, such as SELECT, INSERT, and UPDATE. |
| Global Privileges | ALL PRIVILEGES | Indicates whether a user has all available privileges in the database, except for GRANT OPTION, ENCRYPT, and DECRYPT. |
| Global Privilege | ENCRYPT | Specifies whether a user can call the ENHANCED_AES_ENCRYPT function. A user can only call the 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. This privilege is supported starting from V4.3.5 BP1. |
| Global Privilege | DECRYPT | Specifies whether a user can call the ENHANCED_AES_DECRYPT function. A user can only call the 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. This privilege is supported starting from V4.3.5 BP1. |
| Global Privilege | PROXY | Allows a user to connect and operate on behalf of another user without sharing the password. For example, user A can grant user B the privilege to connect and operate on behalf of user A without sharing the password. This privilege is supported starting from V4.3.5 BP1. |
| Global Privilege | CREATE LOCATION
NoteFor OceanBase Database V4.4.x, the |
Specifies whether a user can execute the CREATE LOCATION, ALTER LOCATION, and DROP LOCATION statements. |
| Catalog-level privilege | CREATE CATALOG | Specifies whether a user can execute the CREATE EXTERNAL CATALOG and DROP CATALOG statements. |
| Catalog-level privilege | USE CATALOG | Specifies whether a user can execute the SET CATALOG, SHOW CATALOGS, SHOW CREATE CATALOG, and SELECT FROM CATALOG statements. |
| Object privilege | CREATE AI MODEL | Specifies whether a user can call the CREATE_AI_MODEL and CREATE_AI_MODEL_ENDPOINT procedures in the DBMS_AI_SERVICE system package. |
| Object privilege | ALTER AI MODEL | Specifies whether a user can call the ALTER_AI_MODEL_ENDPOINT procedure in the DBMS_AI_SERVICE system package. |
| Object privilege | DROP AI MODEL | Specifies whether a user can call the DROP_AI_MODEL and DROP_AI_MODEL_ENDPOINT procedures in the DBMS_AI_SERVICE system package. |
| Object privilege | ACCESS AI MODEL | Specifies whether a user can call the AI functions such as AI_COMPLETE, AI_EMBED, and AI_RERANK. |
| Global Privilege | CREATE SENSITIVE RULE | Specifies whether a user can execute the CREATE/DROP SENSITIVE RULE statement. The root user has this privilege by default and it cannot be revoked. This privilege is automatically granted when a new cluster is created or after an upgrade. |
| Global Privilege | PLAINACCESS | Specifies whether a user can 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 automatically granted when a new cluster is created or after an upgrade. |
| Rule-level privilege | PLAINACCESS | Specifies whether a user can access plaintext data in the columns associated with a specific rule. The user who creates a rule does not automatically have the PLAINACCESS privilege for that rule. This privilege must be granted separately. |