All Products
Search
Document Center

GRANT

Last Updated: Jun 18, 2021

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)