Purpose
You can use this statement to grant privileges to other users as the system administrator.
Note
- You can grant only the privileges that you have. For example,
user1attempts to grant theSELECTprivilege on tabletbl1touser2. In this case,user1must have theSELECTprivilege on tabletbl1and theGRANT OPTIONprivilege. - After a user is granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.
Syntax
GRANT priv_type
ON priv_level
TO user_specification [, user_specification]...
[WITH with_option ...]
priv_type:
ALTER
| CREATE
| CREATE USER
| CREATE VIEW
| DELETE
| DROP
| GRANT OPTION
| INDEX
| INSERT
| PROCESS
| SELECT
| SHOW DATABASES
| SHOW VIEW
| SUPER
| UPDATE
| USAGE
priv_level:
*
| *.*
| database_name.*
| database_name.table_name
| table_name
| database_name.rountine_name
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
with_option:
GRANT OPTION
Parameters
| Parameter | Description |
|---|---|
| priv_type | 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 (,). |
| priv_level | The level of the privilege to be granted. Privileges can be divided into the following levels:
|
| user_specification | Grants privileges to a specific user. If the user does not exist, you can directly create the user. When you grant privileges to multiple users, the usernames must be separated with commas (,). |
| user IDENTIFIED BY 'password' | Displays the password in plaintext. |
| user IDENTIFIED BY PASSWORD 'password' | Displays the password in ciphertext. |
| with_option | Specifies whether to enable privilege delegation. |
The following table lists the types of privileges that can be granted.
| Privilege type | Description |
|---|---|
| ALL PRIVILEGES | All privileges except GRANT OPTION. |
| ALTER | The ALTER TABLE privilege. |
| CREATE | The CREATE TABLE privilege. |
| CREATE USER | The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges. |
| CREATE VIEW | The CREATE VIEW and DROP VIEW privileges. |
| DELETE | The DELETE privilege. |
| DROP | The DROP privilege. |
| GRANT OPTION | The GRANT OPTION privilege. |
| INDEX | The CREATE INDEX and DROP INDEX privileges. |
| INSERT | The INSERT privilege. |
| PROCESS | The privilege to execute the SHOW PROCESSLIST statement to view processes of other users. |
| SELECT | The SELECT privilege. |
| SHOW DATABASES | The global SHOW DATABASES privilege. |
| SHOW VIEW | The SHOW CREATE VIEW privilege. |
| SUPER | The SET GLOBAL privilege for modifying global system parameters. |
| UPDATE | The UPDATE privilege. |
| USAGE | The synonym for no privileges. |
Note
Currently, all users in the sys tenant can execute the CHANGE EFFECTIVE TENANT statement to grant privileges to specified users in user tenants.
Examples
Grant all privileges to user obsqluser01.
obclient> GRANT ALL PRIVILEGES ON *.* TO obsqluser01 WITH GRANT OPTION;
Query OK, 0 rows affected
