Purpose
This statement allows a system administrator to grant privileges to users, including object privileges, system privileges, and roles.
Privilege requirements
To execute the GRANT statement, the current user must have the privileges to be granted and the privileges to grant the specified privileges. For example, to grant the SELECT privilege on table tbl1 to user user2 by using user user1, user user1 must have the SELECT privilege on table tbl1 and the privilege to grant the SELECT privilege on table tbl1 to other users.
Note
After you grant privileges to a user, the user must 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)]
obj_clause:
schema_name.relation_name
| CATALOG external_catalog_name
Parameters
| Parameter | Description |
|---|---|
| obj_all_col_priv | The privileges to be granted. You can grant privileges or roles to users directly or indirectly, and grant multiple privileges to a user at the same time by separating the privilege types with commas (,). For more information about the privilege types, see Privilege types in Oracle mode. |
| obj_clause | The authorization object. Valid values:
|
| grant_user | The user or role to which privileges are granted. Valid values:
|
| IDENTIFIED BY password | The password of the user to which privileges are to be granted. In this example, the password is specified in plaintext. After the password is stored in the dba_users table, the server encrypts it. If the password contains special characters ~!@#%^&*_-+=`|(){}[]:;',.?/, enclose it in double quotation marks ( "" ). |
| WITH GRANT OPTION | Specifies whether to allow the granted privilege to be granted again. If you revoke a privilege that is granted with this clause, the privilege granted again based on this privilege is also revoked. |
| WITH ADMIN OPTION | Specifies whether to allow the granted role to be granted again. If you revoke a role that is granted with this clause, the role granted again based on this role is not revoked. |
| role | The role to be granted. Valid values:
|
Examples
Grant the
CREATE VIEWprivilege to theuser1user and allow this privilege to be granted to other users.obclient> GRANT CREATE VIEW TO user1 WITH ADMIN OPTION;Grant the
CONNECTrole to theuser1user and change the password of theuser1user.obclient> GRANT CONNECT TO user1 IDENTIFIED by '********';After the operation is performed, check the password of the
user1user in thedba_userstable. The password has been updated to the newly set one.Grant the COMMENT ANY TABLE privilege to the
role1role.GRANT COMMENT ANY TABLE TO role1;Grant the
USE CATALOGprivilege on thetest_odps_catalogobject in the catalog to theuser004user.GRANT USE CATALOG ON CATALOG test_odps_catalog TO user004;Grant the
CREATE CATALOGandUSE ANY CATALOGsystem privileges to theuser005user.GRANT CREATE CATALOG, USE ANY CATALOG TO user005;
References
To view the privileges of a user, follow the steps in View user privileges.
To view the privileges of a role, follow the steps in View a role.
You can query the
dba_userstable for information about the created users. For more information about thedba_userstable, see DBA_USERS.