Purpose
System administrators can use this statement to grant object privileges, system privileges, and roles to users.
Required privileges
To execute the GRANT statement, you need to have the privileges being granted, as well as the privilege to grant those privileges. For example, if you want to grant the SELECT privilege on table tbl1 from user user1 to user user2, make sure that user1 has the SELECT privilege on table tbl1, as well as the privilege to grant the SELECT privilege on table tbl1.
Note
After you grant 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)]
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 extends to dependent users. |
| WITH ADMIN OPTION | Specifies whether to enable admin privilege delegation. When admin privilege delegation is enabled, grant revocation does not extend 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.