This topic describes how to modify a role, which mainly involves adding privileges to a role.
Modify a role by using SQL statements
Use the GRANT statement to add privileges to a role.
Prerequisites
To grant system privileges, object privileges, or roles to a role, you must have the privileges or roles to be granted and any of the following privileges: GRANT OPTION, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, or GRANT ANY ROLE. For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For information about how to grant privileges to a user, see Modify user privileges. For information about how to view your roles, see View roles.
Syntax
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
where
obj_privilege: specifies the object privileges to be granted. When granting multiple privileges to a user, separate them 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 granting multiple privileges to a user, separate them with commas (,).WITH GRANT OPTION: specifies whether to enable privilege delegation. When privilege delegation is enabled, revoking privileges will also revoke delegated privileges.
Examples
Grant the SELECT privilege on the test1.tbl1 table to the role1 role:
obclient> GRANT SELECT ON test1.tbl1 TO role1;
Query OK, 0 rows affected
In addition, you can add privileges to the PUBLIC role by using the following SQL statement:
Notice
To ensure database security, grant this role only when it is 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 in the tenant 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.