GRANT|V4.4.2| docs|Distributed Database

Purpose

This statement is used by system administrators to grant users object privileges, system privileges, and roles.

Privilege requirements

When executing the GRANT statement, the current user must have the privileges being granted and the privileges to grant those privileges. For example, if user user1 wants to grant the SELECT privilege on table tbl1 to user user2, user user1 must have the SELECT privilege on table tbl1 and the privilege to grant the SELECT privilege on table tbl1.

Note

After granting privileges to a user, the user needs to reconnect to OceanBase Database for the privileges to take effect.

Syntax

/* Grant object privileges */
GRANT {obj_all_col_priv [, obj_all_col_priv...]}
    ON obj_clause
    TO {grant_user [, grant_user...]}
    [WITH GRANT OPTION]

/* Grant system privileges or roles */
GRANT obj_all_col_priv [, obj_all_col_priv...]
    TO grantee_clause
    [WITH ADMIN OPTION]

grantee_clause:
      grant_user [, grant_user...]
    | grant_user IDENTIFIED BY password

obj_all_col_priv:
      role
    | sys_and_obj_priv [(column_list)]
    | ALL [PRIVILEGES] [(column_list)]
    | READ
    | WRITE

obj_clause:
    schema_name.relation_name
    | LOCATION location_name

Parameters

Parameter	Description
obj_all_col_priv	Specifies the privileges to be granted. You can directly or indirectly grant privileges to users by granting privileges or roles. When granting multiple privileges to a user, separate the privilege types with commas (`,`). For more information about the privilege types, see Privilege types in Oracle-compatible mode.
obj_clause	Specifies the object to which privileges are granted. For more information, see obj_clause.
grant_user	Specifies the user or role to which privileges are granted. Valid values: `user [USER_VARIABLE]`: grants privileges to a specific user. `CONNECT`: grants privileges to the CONNECT role. `RESOURCE`: grants privileges to the RESOURCE role. `PUBLIC`: grants privileges to the public role.
IDENTIFIED BY password	Specifies a password for the user to whom privileges are granted. The password is stored in the `dba_users` table as ciphertext. If the password contains special characters ~!@#%^&*_-+=`\|(){}[]:;',.?/, enclose it in double quotation marks (`""`).
WITH GRANT OPTION	Specifies whether the privileges can be granted to other users. If this option is specified, the privileges are cascaded when revoked.
WITH ADMIN OPTION	Specifies whether the privileges can be granted to other users. If this option is specified, the privileges are not cascaded when revoked.
role	Specifies the role to be granted. Valid values: `role_name`: the name of a custom role. `DBA`: the database administrator role, which has full database management privileges. Users granted the DBA role can perform any database operation. `RESOURCE`: the RESOURCE role. `CONNECT`: the CONNECT role. `PUBLIC`: the public role.

obj_clause

schema_name.relation_name: specifies that the privileges apply to database objects such as tables, views, and sequences.
LOCATION location_name: specifies that the privileges apply to a location. You can grant the following privileges to a user on a location object:
- READ: the read privilege on a location object.
- WRITE: the write privilege on a location object.
Note

Starting from OceanBase Database V4.4.1, the GRANT statement supports the LOCATION parameter and the corresponding READ and WRITE privileges in OceanBase Database V4.4.x.

Examples

Grant the CREATE VIEW privilege to user user1 and allow the privilege to be granted to other users.
```
obclient> GRANT CREATE VIEW TO user1 WITH ADMIN OPTION;
```
Grant the CONNECT role to user user1 and change the password of user1.
```
obclient> GRANT CONNECT TO user1 IDENTIFIED by '********';
```
After execution, check the password of user1 in the dba_users table. You will see that it has been updated to the latest password.
Grant the COMMENT ANY TABLE privilege to role role1.
```
obclient> GRANT COMMENT ANY TABLE TO role1;
```
Grant the read privilege on the location object local_t to user user006.
```
obclient> GRANT READ ON LOCATION local_t TO user006;
```

References

For information about how to view user privileges, see View user privileges.
For information about how to view roles and the privileges of roles, see View roles.
You can query the dba_users view to obtain information about the created users. For more information about the dba_users view, see DBA_USERS.