Roles
A role is an identifier that can be used to associate permissions with applications.
A role is typically associated with one or more applications to grant permissions to those applications.
For example, a company uses a restricted application that performs certain checks before updating the payroll tables to ensure that these tables are updated correctly. The DBA defines a role, for example update_payroll, and later assigns appropriate permissions for the necessary tables. The application developer associates the role with the application.
Note: When defining a role, the DBA normally works with the application developer, so that they can agree on what role identifier and password to use for specific applications.
For further security, a role password can be specified. The role password is optional.
Working with Role Objects
You can perform the following basic operations on roles:
• Create and alter role objects
Note: Passwords can be associated with role objects as well as with user objects. The section
User Password contains more information on passwords.
• View existing role objects, including the detailed properties of each object
• Drop role objects
In SQL, you implement roles with the CREATE ROLE, ALTER ROLE, and DROP ROLE statements when working in a session connected to the iidbdb database.
You can also work with roles using Actian Director and VDBA.
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 Language 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;
Roles and Permissions
After a role is created, you can then associate permissions with it and create grants to it for individual users. For example, for the associated application to execute properly, grant update permission to all payroll tables for the update_payroll role. For details, see Object Permissions.
When you grant an object permission or a subject privilege to a role, you are, in effect, granting that same permission or privilege to any session that is started using that role.
Specifying Role ID at Session Startup
When starting a session, you must specify a role identifier, which puts into effect the associated permissions and subject privileges. For example:
• On the –R flag for many system commands. For details, see the Command Reference.
• With the CONNECT statement as part of an application.
• For an application image as part of the connection profile for an OpenROAD session. For more information, see online help for the Create Connection Profile dialog in OpenROAD.
For the DBA or a user (such as the system administrator) who has the security privilege, neither role identifier nor password is validated. For any other user, the specified role must exist, the user must be granted permission to use the role, and any required password must be specified correctly; otherwise, the connection is refused.