Example: Creating, Altering, and Dropping a Role using SQL Statements
Here are examples of using the CREATE ROLE, ALTER ROLE, and DROP ROLE statements:
1. Create a role identifier and password for an inventory application for a bookstore:
CREATE ROLE bks_onhand
WITH PASSWORD = ’hgwells’;
Note: Enclose any special characters or blanks in the roll password in single quotes. Blanks are not significant in passwords—for example, ’a b c’ is equivalent to ’abc’. The password can be up to 24 characters in length. (You should choose long passwords that cannot be easily guessed.) If no password is assigned, then all users that have been granted access to that role have access to the role identifier and its associated permissions. (See the GRANT ROLE statement in the SQL Reference Guide.) If a password is assigned, then the user or application must additionally use the password to access the role.
2. Create a role identifier with no password for the daily sales application for a bookstore:
CREATE ROLE dly_sales WITH NOPASSWORD;
3. Create dual role IDs (these function as synonyms) and password for a recommended list application for a bookstore:
CREATE ROLE sclemens, mtwain
WITH PASSWORD = ’goodluck’;
4. Change the password for the existing role identifier new_accounts to eggbasket:
ALTER ROLE new_accounts
WITH PASSWORD = eggbasket;
5. Drop the existing role identifier sales_report:
DROP ROLE sales_report;