This topic describes how to add privileges to a role.
Modify a role by using SQL statements
You can use the GRANT statement to add privileges to a role.
Prerequisites
You must have the privileges or roles to be added 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 an administrator. For more information, see Modify user privileges. For more information about how to view your roles, see View roles.
Syntax for the SQL statements
Add object privileges to a role:
GRANT obj_privilege ON obj_clause TO role_name [WITH GRANT OPTION];
obj_privilege:
ALTER
| DELETE
| INSERT
| SELECT
| UPDATE
| EXECUTE
obj_clause:
relation_name
| relation_name '.' relation_name
| DIRECTORY relation_name
Add system privileges to a role:
GRANT {system_privilege_list | ALL PRIVILEGES}
TO role_name [WITH {GRANT | ADMID} OPTION];
system_privilege:
CREATE SESSION
| EXEMPT REDACTION POLICY
| SYSDBA
| SYSOPER
| SYSBACKUP
| CREATE TABLE
| CREATE ANY TABLE
| ALTER ANY TABLE
| BACKUP ANY TABLE
| DROP ANY TABLE
| LOCK ANY TABLE
| COMMENT ANY TABLE
| SELECT ANY TABLE
| INSERT ANY TABLE
| UPDATE ANY TABLE
| DELETE ANY TABLE
| FLASHBACK ANY TABLE
| CREATE ROLE
| DROP ANY ROLE
| GRANT ANY ROLE
| ALTER ANY ROLE
| AUDIT ANY
| GRANT ANY PRIVILEGE
| GRANT ANY OBJECT PRIVILEGE
| CREATE ANY INDEX
| ALTER ANY INDEX
| DROP ANY INDEX
| CREATE ANY VIEW
| DROP ANY VIEW
| CREATE VIEW
| SELECT ANY DICTIONARY
| CREATE PROCEDURE
| CREATE ANY PROCEDURE
| ALTER ANY PROCEDURE
| DROP ANY PROCEDURE
| EXECUTE ANY PROCEDURE
| CREATE SYNONYM
| CREATE ANY SYNONYM
| DROP ANY SYNONYM
| CREATE PUBLIC SYNONYM
| DROP PUBLIC SYNONYM
| CREATE SEQUENCE
| CREATE ANY SEQUENCE
| ALTER ANY SEQUENCE
| DROP ANY SEQUENCE
| SELECT ANY SEQUENCE
| CREATE TRIGGER
| CREATE ANY TRIGGER
| ALTER ANY TRIGGER
| DROP ANY TRIGGER
| CREATE PROFILE
| ALTER PROFILE
| DROP PROFILE
| CREATE USER
| ALTER USER
| DROP USER
| CREATE TYPE
| CREATE ANY TYPE
| ALTER ANY TYPE
| DROP ANY TYPE
| EXECUTE ANY TYPE
| UNDER ANY TYPE
| PURGE DBA_RECYCLEBIN
| CREATE ANY OUTLINE
| ALTER ANY OUTLINE
| DROP ANY OUTLINE
| SYSKM
| CREATE TABLESPACE
| ALTER TABLESPACE
| DROP TABLESPACE
| SHOW PROCESS
| ALTER SYSTEM
| CREATE DATABASE LINK
| CREATE PUBLIC DATABASE LINK
| DROP DATABASE LINK
| ALTER SESSION
| ALTER DATABASE
Notes:
obj_privilege: specifies the object privileges to be granted. When you grant multiple privileges to a user, the privileges must be separated with commas (,).obj_clause: specifies the objects related to the object privileges to be granted.system_privilege: specifies the system privileges to be granted. When you grant multiple privileges to a user, the privileges must be separated with commas (,).WITH GRANT OPTION: specifies whether to enable privilege delegation. When privilege delegation is enabled, grant revocation extends to dependent users.
Examples
Grant the role1 role the SELECT privilege on the test1.tbl1 table:
obclient> GRANT SELECT ON test1.tbl1 TO role1;
Query OK, 0 rows affected
You can also add privileges to the PUBLIC role by using the following SQL statement:
Notice
To ensure the security of OceanBase Database, we recommend that you assign this role to a user only when the role is necessary for your business.
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.