GRANT |V2.2.77|OceanBase Database| docs|Distributed Database

GRANT

Last Updated：2023-08-18 09:26:34 Updated

Description

This statement is used by the system administrator to grant a user some permissions, such as object permissions and system permissions and roles. Note

When a current user grants an object permission, the current user must be the object owner. For example, if user1 grants user2 the SELECT permission on table t1, user1 must have the SELECT permission on table t1. The current user must also have the GRANT OPTION permission.
When a current user grants a system permission or a role, the current user must have the permission or the role to be granted and the GRANT OPTION permission. This way, the permission or the role can be granted.
After the user is granted the permission, the permission can take effect only when the user reconnects to ApsaraDB for OceanBase.

Syntax

/*Grant object permissions*/
GRANT obj_with_col_priv_list
    ON obj_clause TO grant_user_list [WITH GRANT OPTION];
  
obj_with_col_priv_list:
  obj_with_col_priv
  | obj_with_col_priv_list, obj_with_col_priv
  
obj_with_col_priv:
  obj_privilege [column_list]

obj_privilege:
    ALTER
  | AUDIT
  | COMMENT
  | DELETE
  | GRANT
  | INDEX
  | INSERT
  | LOCK
  | RENAME
  | SELECT 
  | UPDATE
  | REFERENCES
  | EXECUTE
  | CREATE
  | FLASHBACK
  | READ
  | WRITE
  | DEBUG

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

grant_user_list:
    grant_user [, grant_user ...]

/*Grant system permissions*/
GRANT {system_privilege_list | ALL PRIVILEGES}
    TO grantee_user [IDENTIFIED BY password] [WITH {GRANT | ADMID} OPTION];

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
  CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

/*Grant roles*/
GRANT role_list TO grantee_user [IDENTIFIED BY password] [WITH {GRANT | ADMID} OPTION];

role_list:
role [, role ...]

Parameter description

Parameter	Description
priv_type	Specifies the type of the permission to be granted. For more information about permission types and description, see the following table for permission type description. If you grant multiple permissions to a user at a time, separate the permission types with commas (,).
system_privilege	Specifies the type of the system permission to be granted. If you grant multiple permissions to a user at a time, separate the permission types with commas (,).
obj_clause	Specifies the level of the permission to be granted. Permissions can be divided into the following levels: * Global level: The permissions apply to all the databases. * Database level: The permissions apply to all the objects in a specified database. * Table level: The table permissions apply to all the columns in a specified table.
WITH GRANT OPTION	Specifies whether the permission can be granted to another user. When the permission is canceled, cascading is performed.
WITH ADMIN OPTION	Specifies whether the permission can be granted to another user. When the permission is canceled, cascading is not performed.

The following table describes the types of permissions that can be granted.

Table for permission type description

Permission	Description
ALL PRIVILEGES	All the permissions except the GRANT OPTION permission.
ALTER	The ALTER TABLE permission.
CREATE	The CREATE TABLE permission.
DELETE	The DELETE permission.
DROP	The DROP permission.
GRANT OPTION	The GRANT OPTION permission.
INSERT	The INSERT permission.
UPDATE	The UPDATE permission.
SELECT	The SELECT permission.
INDEX	The CREATE INDEX and DROP INDEX permissions.
SHOW VIEW	The SHOW CREATE VIEW permission.
SHOW DATABASES	The global SHOW DATABASES permission.
SUPER	The permission to execute the SET GLOBAL statement to modify global system parameters.
REFERENCES	The permission to create a constraint that refers to the table.
EXECUTE	The permission to execute the preprocessor program.
FLASHBACK	The FLASHBACK permission.
READ	The READ permission.
WRITE	The WRITE permission.
CREATE SESSION	The permission to connect to the database.
EXEMPT REDACTION POLICY	The permission to bypass existing redaction policies and view data.
SYSDBA	The SYSDBA permission.
SYSOPER	The SYSOPER permission.
SYSBACKUP	The SYSBACKUP permission.
CREATE TABLE	The permission to create a table in the specified user schema.
CREATE ANY TABLE	The permission to create tables in all the user schemas except SYS.
ALTER ANY TABLE	The permission to modify tables in all the user schemas except SYS.
BACKUP ANY TABLE	The permission to create tables in all the user schemas except SYS.
DROP ANY TABLE	The permission to back up tables in all the user schemas except SYS.
LOCK ANY TABLE	The permission to lock tables in all the user schemas except SYS.
COMMENT ANY TABLE	The permission to comment tables in all the user schemas except SYS.
SELECT ANY TABLE	The permission to view tables in all the user schemas except SYS.
INSERT ANY TABLE	The permission to insert rows into tables in all the user schemas except SYS.
UPDATE ANY TABLE	The permission to update rows in tables in all the user schemas except SYS.
DELETE ANY TABLE	The permission to delete tables in all the user schemas except SYS.
FLASHBACK ANY TABLE	The permission to flash back tables in all the user schemas except SYS.
CREATE ROLE	The permission to create a role.
DROP ANY ROLE	The permission to delete a role.
GRANT ANY ROLE	The permission to grant a role.
ALTER ANY ROLE	The permission to modify a role.
AUDIT ANY	The permission to modify objects in all the user schemas except SYS.
GRANT ANY PRIVILEGE	The permission to grant a system permission.
GRANT ANY OBJECT PRIVILEGE	The permission to grant an object permission.
CREATE ANY INDEX	The permission to create indexes in all the user schemas except SYS.
ALTER ANY INDEX	The permission to modify indexes in all the user schemas except SYS.
DROP ANY INDEX	The permission to delete indexes in all the user schemas except SYS.
CREATE ANY VIEW	The permission to create views in all the user schemas except SYS.
DROP ANY VIEW	The permission to delete indexes in all the user schemas except SYS.
CREATE VIEW	The permission to create a view in the specified user schema.
SELECT ANY DICTIONARY	The permission to query a dictionary in the specified user schema.
CREATE PROCEDURE	The permission to create a procedure in the specified user schema.
CREATE ANY PROCEDURE	The permission to create procedures in all the user schemas except SYS.
ALTER ANY PROCEDURE	The permission to modify procedures in all the user schemas except SYS.
DROP ANY PROCEDURE	The permission to delete procedures in all the user schemas except SYS.
EXECUTE ANY PROCEDURE	The permission to perform procedures in all the user schemas except SYS.
CREATE SYNONYM	The permission to create a synonym in the specified user schema.
CREATE ANY SYNONYM	The permission to create synonyms in all the user schemas except SYS.
DROP ANY SYNONYM	The permission to delete synonyms in all the user schemas except SYS.
CREATE PUBLIC SYNONYM	The permission to create a public synonym.
DROP PUBLIC SYNONYM	The permission to delete a public synonym.
CREATE SEQUENCE	The permission to create a sequence in the specified user schema.
CREATE ANY SEQUENCE	The permission to create sequences in all the user schemas except SYS.
ALTER ANY SEQUENCE	The permission to modify sequences in all the user schemas except SYS.
DROP ANY SEQUENCE	The permission to delete sequences in all the user schemas except SYS.
SELECT ANY SEQUENCE	The permission to query sequences in all the user schemas except SYS.
CREATE TRIGGER	The permission to create a trigger in the specified user schema.
CREATE ANY TRIGGER	The permission to create triggers in all the user schemas except SYS.
ALTER ANY TRIGGER	The permission to modify triggers in all the user schemas except SYS.
DROP ANY TRIGGER	The permission to delete triggers in all the user schemas except SYS.
CREATE PROFILE	The permission to create a profile.
ALTER PROFILE	The permission to modify a profile.
DROP PROFILE	The permission to delete a profile.
CREATE USER	The permission to create a user.
ALTER USER	The permission to modify a user.
DROP USER	The permission to delete a user.
CREATE TYPE	The permission to create a type in the specified user schema.
CREATE ANY TYPE	The permission to create types in all the user schemas except SYS.
ALTER ANY TYPE	The permission to modify types in all the user schemas except SYS.
DROP ANY TYPE	The permission to delete types in all the user schemas except SYS.
EXECUTE ANY TYPE	The permission to execute types in all the user schemas except SYS.
UNDER ANY TYPE	The permission to create subtypes on the basis of the types in all the user schemas except SYS.
PURGE DBA_RECYCLEBIN	The permission to delete all the objects from the system recycle bin.
CREATE ANY OUTLINE	The permission to create outlines in all the user schemas except SYS.
ALTER ANY OUTLINE	The permission to modify outlines in all the user schemas except SYS.
DROP ANY OUTLINE	The permission to delete outlines in all the user schemas except SYS.
SYSKM	The SYSKM permission.
CREATE TABLESPACE	The permission to create a tablespace.
ALTER TABLESPACE	The permission to modify a tablespace.
DROP TABLESPACE	The permission to delete a tablespace.
ALTER SYSTEM	The ALTER SYSTEM permission.
CREATE DATABASE LINK	The permission to create a database link in the specified user schema.
CREATE PUBLIC DATABASE LINK	The permission to create a public database link.
DROP DATABASE LINK	The permission to delete a database link in the specified user schema.
ALTER SESSION	The permission to modify a session.
ALTER DATABASE	The permission to modify a database.

Examples

Run the following command to grant the obsqluser01 user all the permissions.

OceanBase(admin@TEST)>GRANT ALL PRIVILEGES ON *. * TO obsqluser01 with grant option;
Query OK, 0 rows affected (0.03 sec)