Use the CREATE ROLE statement to create roles. When you create a role, you automatically receive creator privileges, including the DROP ROLE and WITH ADMIN OPTION privileges. Implicit ownership privileges do not apply.
Example: Create a Role and Grant SELECT Privilege
Create a role for general users, grant the role SELECT privileges on the entire Accounting database, and grant role membership to the general users Alana, Rajiv, Carlos, and Ellen:
CREATE ROLE AcctUserRole; GRANT SELECT ON Accounting TO AcctUserRole; GRANT AcctUserRole TO Alana, Rajiv, Carlos, Ellen;
Example: Create a Role and Grant Membership to a User
Create a role for updating the accounts payable table, grant the role data change privileges on the Accounting.AccPay view (an updatable view), and then grant role membership to a single high-level accounting user, Claudia:
CREATE ROLE AcctUpdateRole; GRANT SELECT, UPDATE, INSERT, DELETE ON Accounting.AccPay TO AcctUpdateRole; GRANT AcctEndUserRole, AcctUpdateRole TO Claudia WITH ADMIN OPTION;
Example: Grant CREATE ROLE to Administrators
Grant CREATE ROLE privileges to administrators to perform role administration tasks:
GRANT CREATE ROLE TO Admin2, Admin3;
Assistant administrators Admin2 and Admin3 can now submit the following commands:
CREATE ROLE Role3; GRANT Role1, Role3 TO Francisco; REVOKE Role1 FROM Dani; DROP ROLE Role2;