Purpose
This statement is used by system administrators to grant users various types of permissions, including object permissions, system permissions, and roles.
Privilege requirements
When executing the GRANT statement, the current user must have the privilege being granted and the privilege to grant that privilege. 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)]
obj_clause:
schema_name.relation_name
Parameters
| Parameter | Description |
|---|---|
| obj_all_col_priv | Specifies the privilege to be granted. You can directly or indirectly grant privileges to users by granting privileges or roles. When multiple privileges are granted to a user, the privileges are separated by commas (,). For more information about the privilege types, see Privilege types in Oracle mode. |
| obj_clause | Specifies the object to which the privilege is granted. Valid value: schema_name.relation_name. |
| grant_user | Specifies the user or role to which the privilege is granted. Valid values:
|
| IDENTIFIED BY password | Specifies a password for the user to whom the privilege is granted. The password is stored in plaintext in the dba_users table and is then encrypted by the server. If the password contains special characters ~!@#%^&*_-+=`|(){}[]:;',.?/, enclose it in English double quotation marks (""). |
| WITH GRANT OPTION | Specifies whether the privilege can be granted to other users. If this option is specified, revoking the privilege will also revoke it from all users who have been granted the privilege. |
| WITH ADMIN OPTION | Specifies whether the privilege can be granted to other users. If this option is specified, revoking the privilege will not revoke it from users who have been granted the privilege. |
| role | Specifies the role to be granted. Valid values:
|
Examples
Grant the
CREATE VIEWprivilege to useruser1and allow the privilege to be granted to other users.obclient> GRANT CREATE VIEW TO user1 WITH ADMIN OPTION;Grant the
CONNECTrole to useruser1and change the password ofuser1.obclient> GRANT CONNECT TO user1 IDENTIFIED by '********';After execution, check the password of
user1in thedba_userstable. You will see that it has been updated to the new password.Grant the COMMENT ANY TABLE privilege to role
role1.GRANT COMMENT ANY TABLE TO role1;
References
For information about how to view user privileges, see View user privileges.
For information about how to view roles and the privileges in roles, see View roles.
You can view the information about created users from the
dba_userstable. For more information about thedba_userstable, see DBA_USERS.