Purpose
System administrators can use this statement to grant object privileges, system privileges, and roles to users.
Required privileges
You must have the privileges that you want to grant, and privilege delegation must be enabled. For example, to grant the SELECT privilege on the tbl1 table to user2 as user1, user1 must have the SELECT privilege on the tbl1 table, and the privilege delegation of the SELECT privilege on tbl1 must be enabled.
Note
After a user is granted a privilege, the privilege takes effect only after the user is reconnected to OceanBase Database.
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)]
Parameters
| Parameter | Description |
|---|---|
| obj_all_col_priv | The privilege to be granted. You can grant privileges to users or roles. To grant multiple privileges to a user, separate the privileges with commas (,). For more information about the privilege types, see Privilege types in Oracle mode. |
| obj_clause | The object to be authorized. You can specify the object to be authorized in the following ways:
|
| grant_user | The user or role to which the privilege is to be granted. Valid values:
|
| IDENTIFIED BY password | The password for the user to be authorized. The password is in plaintext and is saved in ciphertext on the server after it is saved to the dba_users table. Enclose special characters in the password in double quotation marks (""). Special characters include the following ones: ~!@#%^&*_-+=`|(){}[]:;’,.?/. |
| WITH GRANT OPTION | Specifies whether to enable privilege delegation. When privilege delegation is enabled, grant revocation cascades to dependent users. |
| WITH ADMIN OPTION | Specifies whether to enable admin privilege delegation. When admin privilege delegation is enabled, grant revocation does not cascade to dependent users. |
| role | The role to be granted. Valid values:
|
Examples
Grant the
CREATE VIEWprivilege touser1, and enable privilege delegation for the privilege.obclient> GRANT CREATE VIEW TO user1 WITH ADMIN OPTION;Grant the
CONNECTrole touser1, and change the password foruser1.obclient> GRANT CONNECT TO user1 IDENTIFIED by '********';Check the password of
user1in thedba_userstable. The password is updated to the new one.Grant the
COMMENT ANY TABLEprivilege torole1.GRANT COMMENT ANY TABLE TO role1;
References
For more information about how to view user privileges, see View user privileges.
For more information about how to view roles and role privileges, see View roles.
You can query the information about the created user in the
dba_userstable. For more information about thedba_userstable, see DBA_USERS.