You can grant privileges to or revoke privileges from a role.
Grant privileges to a role
Prerequisites
You have the privileges to be granted and the GRANT OPTION, GRANT ANY OBJECT PRIVILEGE, or GRANT ANY PRIVILEGE privilege.
For information about how to view your privileges, see View user privileges. If you do not have the required privilege, contact the administrator to obtain the privilege. For more information about how to grant privileges to a user, see Grant direct privileges. For more information about how to view your roles, see View roles.
Syntax and examples
The syntax for granting object privileges to a role is as follows:
GRANT obj_privilege ON obj_clause TO role_name;
obj_clause:
relation_name
| relation_name '.' relation_name
The syntax for granting system privileges to a role is as follows:
GRANT {system_privilege | ALL PRIVILEGES}
TO role_name [WITH ADMID OPTION];
where:
obj_privilegespecifies the object privileges to be granted. When you grant multiple privileges to a user, separate the privileges with commas (,).For more information about the object privilege types supported in Oracle mode, see Privilege types in Oracle mode.
obj_clausespecifies the objects related to the object privileges to be granted.system_privilegespecifies the system privileges to be granted. When you grant multiple privileges to a user, separate the privileges with commas (,).For more information about the system privilege types supported in Oracle mode, see Privilege types in Oracle mode.
WITH ADMIN OPTIONspecifies that the granted privilege can be granted to other users, and privilege revocation does not extend to other users..
Here is an example:
Grant the role1 role the SELECT privilege on the test1.tbl1 table.
obclient> GRANT SELECT ON test1.tbl1 TO role1;
You can also add privileges to the PUBLIC role by using the following SQL statement.
Notice
To ensure database security, grant this role only when necessary.
obclient> GRANT [privilege_list] TO PUBLIC;
After you add a privilege to the PUBLIC role, all users in the tenant have the privilege. This means that all users can immediately perform operations that are authorized by the privilege. For more information about the PUBLIC role, see Overview.
For more information about the GRANT statement, see GRANT.
Revoke privileges from a role
Prerequisites
You have the privileges or roles to be revoked and any of the following privileges: GRANT OPTION, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, and GRANT ANY ROLE.
For information about how to view your privileges, see View user privileges. If you do not have the required privilege, contact the administrator to obtain the privilege. For more information about how to grant privileges to a user, see Grant direct privileges. For more information about how to view your roles, see View roles.
Considerations
Notice
Revoking privileges from the default role STANDBY_REPLICATION in the system can affect log synchronization in the network-based Physical Standby Database solution. Proceed with caution.
Syntax and examples
The syntax for revoking object privileges from a role is as follows:
REVOKE obj_privilege ON obj_clause FROM role_name;
obj_clause:
relation_name
| relation_name '.' relation_name
The syntax for revoking system privileges from a role is as follows:
REVOKE {system_privilege | ALL PRIVILEGES}
FROM role_name;
where:
obj_privilegespecifies the object privileges to be revoked. When you revoke multiple privileges from a user at a time, separate the privileges with commas (,).obj_clausespecifies the objects related to the object privileges to be revoked.system_privilegespecifies the system privileges to be revoked. When you revoke multiple privileges from a user at a time, separate the privileges with commas (,).
The following example shows how to revoke the CREATE SESSION system privilege from the role1 role:
obclient> REVOKE CREATE SESSION FROM role1;