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.routine_name
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
with_option:
GRANT OPTION
Parameters
| Parameter | Description |
|---|---|
| priv_type | Specifies the type of the privilege to be granted. For more information about the specific privilege types and their description, see the following table. To grant multiple privileges to a user, separate the types with commas (,). |
| priv_level | Specifies 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' | Specifies a password displayed in plaintext. |
| user IDENTIFIED BY PASSWORD 'password' | Specifies a password displayed in ciphertext. |
| with_option | Specifies whether to enable privilege delegation. |
The following table lists the types of privileges that can be granted.
| Privilege | Description |
|---|---|
| ALL PRIVILEGES | All privileges except GRANT OPTION. |
| ALTER | The ALTER TABLE privilege. |
| CREATE | The CREATE TABLE privilege. |
| CREATE USER | The CREATE USER, DROPUSER, RENAME USER, and REVOKE ALL PRIVILEGES privileges. |
| CREATE TABLEGROUP | The global CREATE TABLEGROUP privilege. |
| DELETE | The DELETE privilege. |
| DROP | The DROP privilege. |
| GRANT OPTION | The GRANT OPTION privilege. |
| INSERT | The INSERT privilege. |
| SELECT | The SELECT privilege. |
| UPDATE | The UPDATE privilege. |
| SUPER | The SET GLOBAL privilege for modifying global system parameters. |
| SHOW DATABASES | The global SHOW DATABASES privilege. |
| INDEX | The CREATE INDEX and DROP INDEX privileges. |
| CREATE VIEW | The CREATE VIEW and DROP VIEW privileges. |
| SHOW VIEW | The SHOW CREATE VIEW privilege. |
Note
Currently, all users in the SYS tenant can execute the CHANGE EFFECTIVE TENANT statement to grant privileges to specified users in business tenants.
Examples
Grant all privileges to user obsqluser01.
obclient> GRANT ALL PRIVILEGES ON *.* TO obsqluser01 WITH GRANT OPTION;
Query OK, 0 rows affected