Purpose
You can use the GRANT 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: Global: Privileges at this level apply to all databases. You can use the GRANT ALL ON *.* statement to grant global privileges. Database level: Privileges at this level apply to all objects in a specified database. You can use the GRANT ALL ON db_name.* statement to grant database privileges. * Table level: Privileges at this level apply to all columns in a specified table. You can use the GRANT ALL ON database_name.table_name statement to grant table privileges. If you replace table_name with an asterisk (*), privileges will be granted to all tables in the database. |
| 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 | 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 user tenants.
Examples
Grant all privileges to user obsqluser01.
obclient> GRANT ALL PRIVILEGES ON *.* TO obsqluser01 WITH GRANT OPTION;
Query OK, 0 rows affected