Purpose
This statement is used to grant corresponding privileges to a specified user or role, or to grant a role to a user or another role.
Limitations and considerations
Circular GRANT operations are not supported. For example, if role A grants privileges to role B, and role B grants privileges to role C, then attempting for role C to grant privileges back to role A will result in an error.
Privilege requirements
User privileges required to grant object privileges
When executing the
GRANTstatement, the current user must have the privilege being granted. For example, to grant theSELECTprivilege on tabletbl1to useruser2using useruser1, useruser1must have theSELECTprivilege on tabletbl1.The current user must have the
GRANT OPTIONprivilege when executing theGRANTstatement. For more information about OceanBase AI Database privileges, see Permission classification.
Note
After a user is granted privileges, the user must reconnect to OceanBase AI Database for the privileges to take effect.
Grant roles to users or role permissions requirements
- The current user can grant all roles if the
SUPERprivilege is granted. - When granting a role to another user or role, the current user must possess the role being granted and have the
ADMIN OPTIONprivilege for the grant to succeed. For operations related to viewing the privileges of the current user, see View user privileges.
Syntax
Grant object privileges to a user or role
GRANT priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]
ON [object_type] priv_level
TO {user [, user...]}
[WITH GRANT OPTION]
priv_type:
ALTER
| CREATE
| CREATE USER
| CREATE VIEW
| DELETE
| DROP
| GRANT OPTION
| INSERT
| SELECT
| UPDATE
| INDEX
| EXECUTE
| SUPER
| BYPASSRLS
| ALL [PRIVILEGES]
| READ
| WRITE
| ...
column_name_list:
column_name [, column_name ...]
object_type:
TABLE
| FUNCTION
| PROCEDURE
| CATALOG
| LOCATION
user:
user_or_role
| user_name IDENTIFIED [WITH auth_plugin] BY password
| user_name IDENTIFIED [WITH auth_plugin] BY PASSWORD password
user_or_role:
user_name | role_name
Grant roles to users or other roles
GRANT role_name [, role_name ...]
TO user_or_role [, user_or_role ...]
[WITH ADMIN OPTION];
user_or_role:
user_name | role_name
Parameters
Parameters |
Description |
|---|---|
| priv_type | Specify the type of privilege to grant. Multiple privileges can be granted, separated by commas (,). Separate multiple entries with commas (,). For more information about the specific privilege types and their descriptions, see [Privilege types](../../../../../600.manage/500.security-and-permissions/300.access-control/200.user-and-permission/200.permission-of-mysql-mode/100.permission-classification-of-mysql.mdNote: The PROXY parameter is only documented in the syntax of this version and does not take effect. |
| column_name_list | Optional. Specifies the list of columns to which column-level privileges are granted. The privileges apply only to the specified columns. If this parameter is omitted, the privileges apply to the entire table or object. For more information, see Grant privileges directly. |
| object_type | Optional. Specifies the type of object to which the privilege is granted. For more information, see object_type. |
| priv_level | Specifies the level to which the privilege is granted. The format is as follows:
|
| user | Specify the user or users to whom the privilege is granted. Multiple users can be specified, separated by commas (,). If the user does not exist, this statement will create the user directly. |
| auth_plugin | Specifies the user authentication method. Currently, only mysql_native_password Authentication plugin. |
| BY password | Specify a password for the user to be authorized. The password is stored in plaintext. mysql.user After the table, the server side will store it as ciphertext. If the password contains special characters ~!@#%^&*_-+=`| (){}[]:;',.?/ must be enclosed in double quotation marks ('' or ""). |
| BY PASSWORD password | Specify a password for the user to be authorized. The password is stored in ciphertext format and will be directly stored in the database. mysql.user table. |
| WITH GRANT OPTION | Specifies whether the permission can be granted to others and whether revocation will cascade. |
| role_name | Specifies the role name. |
| WITH ADMIN OPTION | Specifies whether role privileges can be granted to others. Revocation does not cascade. |
object_type
Notice
When using the object_type clause, you must ensure that the specified object is indeed of the corresponding type; otherwise, a syntax error will occur.
TABLE: Indicates that the object to which the privilege is applied is a table.FUNCTION: Indicates that the object type to which the privilege is applied is a function.PROCEDURE: Indicates that the object type for which privileges are being applied is a stored procedure.CATALOG: Indicates that the object type for which privileges are being applied is a catalog.LOCATION: Indicates that the object type for which privileges are being granted is a location. The following privileges can be granted on a location:READ: indicates the read permission for the location object.WRITE: indicates the write permission for the Location object.
Examples
Example 1: Grant object privileges
Grant the
CREATE VIEWprivilege on thesales_dbdatabase to the existing userdeveloper_user, and allow them to grant the same privilege to other users.obclient> GRANT CREATE VIEW ON sales_db.* TO developer_user WITH GRANT OPTION;Grant the
CREATEprivilege on thesales_dbdatabase to the existing useradmin_user, and change the password ofadmin_user.obclient> GRANT CREATE ON sales_db.* TO admin_user IDENTIFIED BY '********';After execution, check the password of the
admin_useraccount in themysql.usertable. You will see that it has been updated to the newly set password.Grant the
CREATEprivilege on thesales_dbdatabase to the non-existent userreport_user, and set a password forreport_user.obclient> GRANT CREATE ON sales_db.* TO report_user IDENTIFIED BY '********';Grant the
SELECTprivilege on thesalarycolumn of theemployee_infotable in thehr_dbdatabase to the existing useranalyst_user.obclient> GRANT SELECT(salary) ON hr_db.employee_info TO analyst_user;Grant the user
data_adminglobal-levelCREATE CATALOGandUSE CATALOGprivileges.obclient> GRANT CREATE CATALOG, USE CATALOG ON *.* TO data_admin WITH GRANT OPTION;Grant the global
BYPASSRLSprivilege to the management account, allowing it to bypass row-level security (RLS). This privilege can only be granted at the*.*level (the field afterONdoes not support other syntax).obclient> GRANT BYPASSRLS ON *.* TO 'security_admin'@'%';
Example 2: Grant privileges using object_type
Grant the
SELECTprivilege on theorderstable in thesales_dbdatabase toquery_user.obclient> GRANT SELECT ON TABLE sales_db.orders TO query_user;Grant the
executeprivilege on thecalculate_bonusfunction in thehr_dbdatabase todeveloper_user.obclient> GRANT EXECUTE ON FUNCTION hr_db.calculate_bonus TO developer_user;Grant the
dba_useruser the execute privilege on the stored procedureupdate_employee_infoin thehr_dbdatabase.obclient> GRANT EXECUTE ON PROCEDURE hr_db.update_employee_info TO dba_user;Grant the
SELECTandUSE CATALOGprivileges on the Catalog objectexternal_data_catalogto the userdata_engineer.obclient> GRANT SELECT, USE CATALOG ON CATALOG external_data_catalog TO data_engineer WITH GRANT OPTION;Grant the
etl_useruser read permission on theoss_data_locationlocation object.obclient> GRANT READ ON LOCATION oss_data_location TO etl_user WITH GRANT OPTION;
Example 3: Grant roles to users or other roles
Grant the permissions of role
app_admin_roleto roledepartment_admin_role, and allow roledepartment_admin_roleto grant them further.obclient> GRANT app_admin_role TO department_admin_role WITH ADMIN OPTION;Grant the
read_only_rolerole to the useranalyst_userand allow role assignment.obclient> GRANT read_only_role TO analyst_user WITH ADMIN OPTION;
References
- For more information about how to grant privileges, see Grant privileges directly.
- For more information about how to view user privileges, see View user privileges.
- You can view the information about the created user in the
mysql.usertable. For more information about themysql.usertable, see mysql.user. - Add privileges to a role
- Grant roles to users or other roles
- Activate a role
- Indirect authorization