All Products
Search
Document Center

GRANT

Last Updated: Jun 18, 2021

Description

You can execute the GRANT statement to grant permissions to a user as a system administrator.

Note

  • Before you grant permissions to a user, ensure that you have the permissions. For example, to grant User 2 the SELECT permission on Table 1 as User 1, ensure that User 1 has the permission to be granted and the GRANT OPTION permission. Otherwise, you cannot grant the permission to User 2.

  • After you grant a permission to a user, the user must log on to the ApsaraDB for OceanBase system again to ensure that the permission takes effect.

Syntax

GRANT priv_type 
    ON priv_level 
    TO user_specification [, user_specification]... 
     [WITH with_option ...]

privilege_type:
      ALTER
    | CREATE
    | CREATE USER
    | CREATE VIEW
    | DELETE
    | DROP
    | GRANT OPTION
    | INDEX
    | INSERT
    | PROCESS
    | SELECT
    | SHOW DATABASES
    | SHOW VIEW
    | SUPER
    | UPDATE
    | USAGE
    | CREATE SYNONYM
    
priv_level: 
     *
    | *.*
    | db_name.* 
    | db_name.tbl_name
    | tbl_name
    | db_name.rountine_name

user_specification: 
user [IDENTIFIED BY [PASSWORD] 'Password'] 

with_option:
 GRANT OPTION

Parameters

Parameter

Description

priv_type

The permission to be granted. For more information, see the Permissions table in the following description.

To grant multiple permissions to a user at a time, separate the permissions with commas (,).

priv_level

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. Use GRANT ALL ON *.* to grant global permissions.

  • Database level: The permissions apply to all the objects in the specified database. Use GRANT ALL ON db_name.* to assign database-level permissions.

  • Table level: The permissions apply to all the columns in the specified table. Use GRANT ALL ON db_name.tbl_name to assign table-level permissions.

You can use an asterisk (*) instead of specifying a table name to assign permissions on all tables in the specified database.

user_specification

Grant permissions to the specified user. If the specified user does not exist, the system can create a user.

If sql_mode is set to no_auto_create_user and you do not specify a password by using IDENTIFIED BY, the system does not automatically create a user.

To grant permissions to multiple users at a time, separate the usernames with commas (,).

user IDENTIFIED BY 'Password'

Specifies a plaintext password.

user IDENTIFIED BY PASSWORD 'Password'

Specifies a ciphertext password.

with_option

Specifies whether to allow authorized users to grant permissions to other users.

The following table lists the permissions that can be assigned.

Permissions

Permission

Description

ALL PRIVILEGES

All permissions except GRANT OPTION.

ALTER

The permission to execute the ALTER TABLE statement.

CREATE

The permission to execute the CREATE TABLE statement.

CREATE USER

The permission to execute the CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES statements.

CREATE TABLEGROUP

The global permission to execute the CREATE TABLEGROUP statement.

DELETE

The permission to execute the DELETE statement.

DROP

The permission to execute the DROP statement.

GRANT OPTION

The permission to execute the GRANT OPTION statement.

INSERT

The permission to execute the INSERT statement.

SELECT

The permission to execute the SELECT statement.

UPDATE

The permission to execute the UPDATE statement.

SUPER

The permission to execute the SET GLOBAL statement to modify global system parameters.

SHOW DATABASES

The global permission to execute the SHOW DATABASES statement.

INDEX

The permission to execute the CREATE INDEX and DROP INDEX statements.

CREATE VIEW

The permission to create or delete a view.

SHOW VIEW

The permission to execute the SHOW CREATE VIEW statement.

CREATE SYNONYM

The permission to create a synonym.

Note

The permission to execute the CHANGE EFFECTIVE TENANT statement is not limited. Therefore, all users under the system tenant can grant this permission to other users.

Examples

  • Run the following command to grant ALL PRIVILEGES to the obsqluser01 user:
OceanBase(admin@TEST)>GRANT ALL PRIVILEGES ON *. * TO obsqluser01 with grant option;
Query OK, 0 rows affected (0.03 sec)