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)