CREATE ROLE —define a new database role


CREATE ROLE [schema.]name


A ROLE is an entity that can own database objects and be assigned database privileges. Any user can create a ROLE. Use DROP ROLE to drop a ROLE. Use GRANT and REVOKE to assign and remove privileges from roles. (See DROP ROLE, GRANT, REVOKE.) The user granting privileges on database objects to a ROLE must own both the ROLE and that database object for which they are granting privileges. It is recommended that you create roles, assign privileges to those roles, and then assign those roles to users, rather than assigning privileges directly to users. Users can assigned any number of roles.



Create a ROLE. Assign SELECT on a table to the ROLE. Assign the ROLE to a USER.

CREATE ROLE account_manager;
CREATE TABLE accounts_payable (id INTEGER, account_no STRING, amount NUMBER);
GRANT SELECT ON accounts_payable TO ROLE account_manager;
CREATE USER payroll_clerk password 'jd&dtk9';
GRANT account_manager TO payroll_clerk;