GRANT|V4.2.1| docs|Distributed Database

GRANT

Last Updated：2023-12-25 08:49:42 Updated

Purpose

System administrators can use this statement to grant object privileges, system privileges, and roles to users.

Note

To grant object privileges, you must be the owner of the object, or have the `GRANT OPTION` privilege and the privileges to be granted. For example, if user1 wants to grant the SELECT privilege on the tbl1 table to user2, user1 must have the GRANT OPTION privilege and the SELECT privilege on tbl1.
To grant system privileges or roles to a user, you must have the GRANT OPTION privilege and the privileges or roles to be granted.
After a user is granted a role, the privileges of the role takes effect only after the user reconnects to OceanBase Database.
The privileges directly granted to a user take effect immediately.

Syntax

/*Grant object privileges.*/
GRANT obj_with_col_priv_list
    ON obj_clause TO grant_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
    | DELETE
    | INDEX
    | INSERT
    | SELECT
    | UPDATE
    | REFERENCES
    | EXECUTE

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

grantee_list:
    grantee_user_list[,grantee_role_list]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

/*Grant system privileges.*/
GRANT {system_privilege_list | ALL PRIVILEGES}
    TO grantee_list [IDENTIFIED BY password];

system_privilege_list:
    system_privilege [, system_privilege ...]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

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_list [IDENTIFIED BY password][WITH ADMIN OPTION];

role_list:
    role_name [, role_name ...]

grantee_list:
    grantee_user_list[,grantee_role_list]

grantee_user_list:
    user_name[,user_name...]

grantee_role_list:
    role_name [, role_name ...]

Parameters

Parameter	Description
obj_with_col_priv	The type of the privilege to be granted. For information about the specific privilege types and their description, see the following table. To grant multiple privileges to a user, separate the privileges with commas (,).
system_privilege	The type of the system privilege to be granted. To grant multiple privileges to a user, separate the privileges with commas (,).
obj_clause	The object involved in object privileges.
WITH GRANT OPTION	Specifies whether to enable privilege delegation. When privilege delegation is enabled, grant revocation extends to dependent users.
WITH ADMIN OPTION	Specifies whether to enable admin privilege delegation. When admin privilege delegation is enabled, grant revocation does not extend to dependent users.
grantee_list	The list of objects to which privileges or roles are to be granted. Note: Such objects can be roles or users.
user_name	The username.
role_name	The role name.

The following table lists the types of privileges that can be granted.

Privilege types

Privilege type	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	The `ALTER TABLE` privilege.
CREATE	The `CREATE TABLE` privilege.
DELETE	The `DELETE` privilege.
DROP	The `DROP` privilege.
GRANT OPTION	The `GRANT OPTION` privilege.
INSERT	The `INSERT` privilege.
UPDATE	The `UPDATE` privilege.
SELECT	The `SELECT` privilege.
INDEX	The `CREATE INDEX` and `DROP INDEX` privileges.
REFERENCES	The privilege to create constraints on a table.
EXECUTE	The privilege to execute the precompiler program.
FLASHBACK	The `FLASHBACK` privilege.
READ	The `READ` privilege.
WRITE	The `WRITE` privilege.
CREATE SESSION	The privilege to connect to a database.
EXEMPT REDACTION POLICY	The privilege to bypass any existing redaction policies and to view data.
SYSDBA	The `SYSDBA` privilege.
SYSOPER	The `SYSOPER` privilege.
SYSBACKUP	The `SYSBACKUP` privilege.
CREATE TABLE	The privilege to create a table in the schema of a specified user.
CREATE ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
ALTER ANY TABLE	The privilege to modify a table in the schema of any user except the SYS user.
BACKUP ANY TABLE	The privilege to back up a table in the schema of any user except the SYS user.
DROP ANY TABLE	The privilege to drop a table from the schema of any user except the SYS user.
LOCK ANY TABLE	The privilege to lock a table in the schema of any user except the SYS user.
COMMENT ANY TABLE	The privilege to comment on a table in the schema of any user except the SYS user.
SELECT ANY TABLE	The privilege to view a table in the schema of any user except the SYS user.
INSERT ANY TABLE	The privilege to insert rows into a table in the schema of any user except the SYS user.
UPDATE ANY TABLE	The privilege to update rows of a table in the schema of any user except the SYS user.
DELETE ANY TABLE	The privilege to drop a table from the schema of any user except the SYS user.
FLASHBACK ANY TABLE	The privilege to flash back a table in the schema of any user except the SYS user.
CREATE ROLE	The privilege to create a role.
DROP ANY ROLE	The privilege to drop any role.
GRANT ANY ROLE	The privilege to grant any role.
ALTER ANY ROLE	The privilege to modify any role.
AUDIT ANY	The privilege to audit objects in the schema of any user except the SYS user.
GRANT ANY PRIVILEGE	The privilege to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	The privilege to grant any object privilege.
CREATE ANY INDEX	The privilege to create an index in the schema of any user except the SYS user.
ALTER ANY INDEX	The privilege to modify an index in the schema of any user except the SYS user.
DROP ANY INDEX	The privilege to drop an index from the schema of any user except the SYS user.
CREATE ANY VIEW	The privilege to create a view in the schema of any user except the SYS user.
DROP ANY VIEW	The privilege to drop a view in the schema of any user except the SYS user.
CREATE VIEW	The privilege to create a view in the schema of a specified user.
SELECT ANY DICTIONARY	The privilege to query a dictionary in the schema of a specified user.
CREATE PROCEDURE	The privilege to create a procedure in the schema of a specified user.
CREATE ANY PROCEDURE	The privilege to create a procedure in the schema of any user except the SYS user.
ALTER ANY PROCEDURE	The privilege to modify a procedure in the schema of any user except the SYS user.
DROP ANY PROCEDURE	The privilege to drop a procedure from the schema of any user except the SYS user.
EXECUTE ANY PROCEDURE	The privilege to execute a procedure in the schema of any user except the SYS user.
CREATE SYNONYM	The privilege to create a synonym in the schema of a specified user.
CREATE ANY SYNONYM	The privilege to create a synonym in the schema of any user except the SYS user.
DROP ANY SYNONYM	The privilege to drop a synonym from the schema of any user except the SYS user.
CREATE PUBLIC SYNONYM	The privilege to create a public synonym.
DROP PUBLIC SYNONYM	The privilege to drop a public synonym.
CREATE SEQUENCE	The privilege to create a sequence in the schema of a specified user.
CREATE ANY SEQUENCE	The privilege to create a sequence in the schema of any user except the SYS user.
ALTER ANY SEQUENCE	The privilege to modify a sequence in the schema of any user except the SYS user.
DROP ANY SEQUENCE	The privilege to drop a sequence from the schema of any user except the SYS user.
SELECT ANY SEQUENCE	The privilege to query a sequence in the schema of any user except the SYS user.
CREATE TRIGGER	The privilege to create a trigger in the schema of a specified user.
CREATE ANY TRIGGER	The privilege to create a trigger in the schema of any user except the SYS user.
ALTER ANY TRIGGER	The privilege to modify a trigger in the schema of any user except the SYS user.
DROP ANY TRIGGER	The privilege to drop a trigger in the schema of any user except the SYS user.
CREATE PROFILE	The privilege to create a profile.
ALTER PROFILE	The privilege to modify a profile.
DROP PROFILE	The privilege to drop a profile.
CREATE USER	The privilege to create a user.
ALTER USER	The privilege to modify a user.
DROP USER	The privilege to drop a user.
CREATE TYPE	The privilege to create a type in the schema of a specified user.
CREATE ANY TYPE	The privilege to create a type in the schema of any user except the SYS user.
ALTER ANY TYPE	The privilege to modify a type in the schema of any user except the SYS user.
DROP ANY TYPE	The privilege to drop a type from the schema of any user except the SYS user.
EXECUTE ANY TYPE	The privilege to execute a type in the schema of any user except the SYS user.
UNDER ANY TYPE	The privilege to create subtypes under a type in the schema of any user except the SYS user.
PURGE DBA_RECYCLEBIN	The privilege to purge all objects from the recycle bin.
CREATE ANY OUTLINE	The privilege to create an outline in the schema of any user except the SYS user.
ALTER ANY OUTLINE	The privilege to modify an outline in the schema of any user except the SYS user.
DROP ANY OUTLINE	The privilege to drop an outline from the schema of any user except the SYS user.
SYSKM	The `SYSKM` privilege.
CREATE TABLESPACE	The privilege to create a tablespace.
ALTER TABLESPACE	The privilege to modify a tablespace.
DROP TABLESPACE	The privilege to drop a tablespace.
ALTER SYSTEM	The `ALTER SYSTEM` privilege.
CREATE DATABASE LINK	The privilege to create a DBLink in the schema of a specified user.
DROP DATABASE LINK	The privilege to drop a DBLink from the schema of a specified user.
ALTER SESSION	The privilege to modify a session.
ALTER DATABASE	The privilege to modify a database.

Examples

Grant all privileges of the current user except the GRANT OPTION privilege to user1.
```
obclient> GRANT ALL PRIVILEGES TO user1;
Query OK, 0 rows affected
```
Grant the CREATE SESSION privilege to user2 so that the user can log on to OceanBase Database.
```
obclient> GRANT CREATE SESSION TO user2;
Query OK, 0 rows affected
```

Grant role1 to user2.

obclient> GRANT role1 TO user2;
Query OK, 0 rows affected

Grant role2 to role1.

obclient> GRANT role2 TO role1;
Query OK, 0 rows affected

Grant the role1 role the SELECT privilege on the user1.tbl1 table.

obclient> GRANT SELECT ON user1.tbl1 TO role1;
Query OK, 0 rows affected