Overview|V4.1.0| docs|Distributed Database

Overview

Last Updated：2023-08-04 08:22:14 Updated

This topic describes the privileges of users under MySQL and Oracle tenants.

Applicability

OceanBase Database Community Edition provides only MySQL mode.

OceanBase Database users are classified into two categories: users under the sys tenant and users under a user tenant. User tenants can be in Oracle or MySQL mode, which are respectively referred to as Oracle tenants and MySQL tenants. When you create a user, if the current session is under the sys tenant, the created user is a user under the sys tenant. Otherwise, the user is a user under a user tenant. Different tenants have independent user privileges.

Privileges of users under MySQL tenants

Users under a MySQL tenant of OceanBase Database have access only to the objects under the tenant, and the privileges for the users are compatible with those in MySQL.

OceanBase Database in MySQL mode supports three types of privileges:

User-level privileges

User-level privileges are global privileges that apply to all databases.
Database-level privileges

Database-level privileges apply to a specific database and all its objects.
Object privileges

Object privileges apply to objects of the specified type in all databases. These privileges can be applied to the specified objects in the databases, all objects of the specified type in the databases (for example, all tables in the databases), or all database objects (for example, tables, indexes, and views).

The following table lists the privileges in OceanBase Database in MySQL mode.

Privilege type	Privilege	Description
Object privileges	CREATE	The privilege to create databases and tables.
Object privileges	SELECT	The privilege to query data in tables.
Object privileges	INSERT	The privilege to insert row data into tables.
Object privileges	UPDATE	The privilege to modify existing data.
Object privileges	DELETE	The privilege to delete existing data.
Object privileges	DROP	The privilege to drop existing databases, tables, and views.
Object privileges	INDEX	The privilege to create and delete table indexes.
Object privileges	ALTER	The privilege to rename and modify schemas.
Object privileges	CREATE VIEW	The privilege to create views.
Object privileges	SHOW VIEW	The privilege to query views or learn how views operate.
Database-level privileges	SELECT	The privilege to query data in tables.
Database-level privileges	INSERT	The privilege to insert row data into tables.
Database-level privileges	UPDATE	The privilege to modify existing data.
Database-level privileges	DELETE	The privilege to delete existing data.
Database-level privileges	CREATE	The privilege to create databases and tables.
Database-level privileges	DROP	The privilege to drop existing databases, tables, and views.
Database-level privileges	GRANT	The privilege to grant privileges to other users.
Database-level privileges	INDEX	The privilege to create and delete table indexes.
Database-level privileges	ALTER	The privilege to modify databases and schemas.
Database-level privileges	CREATE VIEW	The privilege to create views.
Database-level privileges	SHOW VIEW	The privilege to query views or learn how views operate.
User-level privileges	SELECT	The privilege to query data in tables.
User-level privileges	INSERT	The privilege to insert row data into tables.
User-level privileges	UPDATE	The privilege to modify existing data.
User-level privileges	DELETE	The privilege to delete existing data.
User-level privileges	CREATE	The privilege to create databases and tables.
User-level privileges	DROP	The privilege to drop existing databases, tables, and views.
User-level privileges	PROCESS	The privilege to execute the `SHOW PROCESSLIST` statement to view processes of other users.
User-level privileges	FILE	The privilege to execute the `SELECT INTO OUTFILE` and `LOAD DATA INFILE` statements.
User-level privileges	CREATE SYNONYM	The privilege to create synonyms.
User-level privileges	INDEX	The privilege to create and delete table indexes.
User-level privileges	ALTER	The privilege to modify databases and schemas.
User-level privileges	SHOW DB	The privilege to view all database names on a server, including the names of the databases that are accessible to the user.
User-level privileges	SUPER	Indicates whether the user can 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.
User-level privileges	CREATE VIEW	The privilege to create views.
User-level privileges	SHOW VIEW	The privilege to query views or learn how views operate.
User-level privileges	CREATE USER	The privilege to execute the `CREATE USER` statement to create MySQL accounts.
User-level privileges	ALTER TENANT	The privilege to modify tenant information.
User-level privileges	ALTER SYSTEM	The privilege to execute the `ALTER SYSTEM` statement.
User-level privileges	CREATE RESOURCE POOL	The privilege to create, modify, and delete resource pools.
User-level privileges	CREATE RESOURCE UNIT	The privilege to create, modify, and delete resource units.

Privileges of users under Oracle tenants

Users under an Oracle tenant of OceanBase Database have access only to objects under the tenant, and the privileges for the users are compatible with those in Oracle.

OceanBase Database in Oracle mode supports two types of privileges:

System privileges

System privileges allow users to perform standard administrator tasks in the database. If you want to perform operations on schema objects, you must have the required system privileges. System privileges are very powerful. We recommend that they be granted only to trusted users.
Object privileges

Object privileges allow you to perform specific operations on specified objects.

The following table lists available privileges in OceanBase Database in Oracle mode.

Privilege type	Feature	Description
Object privileges	ALTER	The privilege to modify schemas.
Object privileges	INSERT	The privilege to insert data into tables and views.
Object privileges	UPDATE	The privilege to modify data in tables and views.
Object privileges	DELETE	The privilege to delete data from tables and views.
Object privileges	SELECT	The privilege to use tables, views, synonyms, and sequences.
Object privileges	INDEX	The privilege to add indexes to tables.
Object privileges	REFERENCE	The privilege to reference tables.
Object privileges	EXECUTE	The privilege to execute stored procedures, functions, and system packages.
Object privileges	DEBUG	The privilege to debug procedures.
Object privileges	READ	The read privilege.
Object privileges	WRITE	The write privilege.
System privileges	CREATE SESSION	The privilege to create sessions.
System privileges	CREATE TABLE	The privilege to create tables.
System privileges	CREATE ANY TABLE	The privilege to create any tables.
System privileges	ALTER ANY TABLE	The privilege to modify any tables.
System privileges	DELETE ANY TABLE	The privilege to delete data from any tables.
System privileges	DROP ANY TABLE	The privilege to drop any tables.
System privileges	BACKUP ANY TABLE	The privilege to back up any tables.
System privileges	LOCK ANY TABLE	The privilege to lock any tables.
System privileges	COMMENT ANY TABLE	The privilege to add comments to any tables.
System privileges	INSERT ANY TABLE	The privilege to insert rows into any tables.
System privileges	SELECT ANY TABLE	The privilege to use any tables.
System privileges	FLASHBACK ANY TABLE	The privilege to flash back any tables.
System privileges	UPDATE ANY TABLE	The privilege to modify rows of any tables.
System privileges	CREATE ROLE	The privilege to create a role.
System privileges	DROP ANY ROLE	The privilege to drop any role.
System privileges	GRANT ANY ROLE	The privilege to grant any role.
System privileges	ALTER ANY ROLE	The privilege to modify any role.
System privileges	AUDIT ANY	The privilege to set audit options for any objects.
System privileges	GRANT ANY PRIVILEGE	The privilege to grant any system privileges to users.
System privileges	GRANT ANY OBJECT PRIVILEGE	The privilege to grant any object privileges to users.
System privileges	CREATE ANY INDEX	The privilege to create any indexes.
System privileges	ALTER ANY INDEX	The privilege to modify any indexes.
System privileges	DROP ANY INDEX	The privilege to delete any indexes.
System privileges	CREATE VIEW	The privilege to create views.
System privileges	CREATE ANY VIEW	The privilege to create any views.
System privileges	DROP ANY VIEW	The privilege to drop any views.
System privileges	SELECT ANY DICTIONARY	The privilege to use any data dictionaries.
System privileges	CREATE PROCEDURE	The privilege to create a stored procedure for a specified user.
System privileges	CREATE ANY PROCEDURE	The privilege to create any stored procedures for users.
System privileges	ALTER ANY PROCEDURE	The privilege to modify any stored procedures.
System privileges	DROP ANY PROCEDURE	The privilege to drop any stored procedure.
System privileges	EXECUTE ANY PROCEDURE	The privilege to execute any stored procedures.
System privileges	CREATE SYNONYM	The privilege to create a synonym for a specified user.
System privileges	CREATE ANY SYNONYM	The privilege to create any synonyms for users.
System privileges	CREATE PUBLIC SYNONYM	The privilege to create public synonyms.
System privileges	DROP ANY SYNONYM	The privilege to drop any synonyms.
System privileges	DROP PUBLIC SYNONYM	The privilege to drop public synonyms.
System privileges	CREATE SEQUENCE	The privilege to create a sequence for a specified user.
System privileges	CREATE ANY SEQUENCE	The privilege to create any sequences.
System privileges	ALTER ANY SEQUENCE	The privilege to modify any sequences.
System privileges	DROP ANY SEQUENCE	The privilege to drop any sequences.
System privileges	SELECT ANY SEQUENCE	The privilege to use any sequences.
System privileges	CREATE TRIGGER	The privilege to create a trigger for a specified user.
System privileges	CREATE ANY TRIGGER	The privilege to create any triggers for users.
System privileges	ALTER ANY TRIGGER	The privilege to modify any triggers.
System privileges	DROP ANY TRIGGER	The privilege to drop any triggers.
System privileges	CREATE PROFILE	The privilege to create profiles.
System privileges	ALTER PROFILE	The privilege to modify profiles.
System privileges	DROP PROFILE	The privilege to drop profiles.
System privileges	CREATE USER	The privilege to create a user.
System privileges	ALTER USER	The privilege to modify a user.
System privileges	DROP USER	The privilege to drop a user.
System privileges	BECOME USER	The privilege to switch the user status.
System privileges	CREATE TYPE	The privilege to create types.
System privileges	CREATE ANY TYPE	The privilege to create any types.
System privileges	ALTER ANY TYPE	The privilege to modify any types.
System privileges	DROP ANY TYPE	The privilege to drop any types.
System privileges	EXECUTE ANY TYPE	The privilege to execute any types.
System privileges	PURGE DBA_RECYCLEBIN	The privilege to purge the recycle bin.
System privileges	CREATE ANY OUTLINE	The privilege to create any execution plans.
System privileges	ALTER ANY OUTLINE	The privilege to modify any execution plans.
System privileges	DROP ANY OUTLINE	The privilege to drop any execution plans.
System privileges	CREATE TABLESPACE	The privilege to create a tablespace.
System privileges	ALTER TABLESPACE	The privilege to modify a tablespace.
System privileges	DROP TABLESPACE	The privilege to drop a tablespace.
System privileges	SHOW PROCESS	The privilege to view all user threads.
System privileges	ALTER SYSTEM	The privilege to modify server settings.
System privileges	CREATE DATABASE LINK	The privilege to create database connections.
System privileges	CREATE PUBLIC DATABASE LINK	The privilege to create public database connections.
System privileges	DROP DATABASE LINK	The privilege to drop database connections.
System privileges	ALTER SESSION	The privilege to modify sessions.
System privileges	ALTER DATABASE	The privilege to modify databases.
System privileges	CREATE ANY DIRECTORY	The privilege to create any directories.
System privileges	DROP ANY DIRECTORY	The privilege to drop any directories.
System privileges	DEBUG CONNECT SESSION	The privilege to debug connection sessions.
System privileges	DEBUG ANY PROCEDURE	The privilege to debug any procedures.
System privileges	CREATE ANY CONTEXT	The privilege to create any contexts.
System privileges	DROP ANY CONTEXT	The privilege to drop any contexts.

More information

For more information about the users under each tenant, see the following topics: