The following steps describe how to manage user privileges using roles.
- Define a role.
A CREATE ROLE statement defines a role. A newly created role does not have any associated privileges.
- Add privileges to the role.
Use the GRANT statement to grant privileges to roles on databases, tables, views, macros, columns, triggers, stored procedures, join indexes, hash indexes, and UDFs.
- Grant the role to users or other roles.
Use the GRANT statement to grant a role to users or other roles.
- Assign default roles to users.
Use the DEFAULT ROLE option of the CREATE USER or MODIFY USER statement to specify the default role for a user, where:
DEFAULT ROLE = … Specifies … role_name the name of one role to assign as the default role for a user. NONE that the user does not have a default role. NULL ALL the default role to be all roles that are directly or indirectly granted to the user.
At logon time, the default role of the user becomes the current role for the session.
Privilege validation uses the active roles for a user, which include the current role and all nested roles.
- If necessary, change the current role for a session.
Use the SET ROLE statement to change the current role for a session.
Managing role-based privileges requires sufficient privileges. For example, the CREATE ROLE statement is only authorized to users who have the CREATE ROLE system privilege.