In Adaptive Server, a System Security Officer can define and create roles as a convenient way to grant and revoke server-wide permissions to several users at a time. For example, clerical staff may need to insert and select from tables in several databases, but they may not need to update them. A System Security Officer could define a role called “clerical_user_role” and grant the role to everyone in the clerical staff. Database object owners could then grant the required privileges to “clerical_user_role”.
Roles can be defined in a role hierarchy, where a role such as “office_manager_role” contains the “clerical_user_role”. Users who are granted roles in a hierarchy automatically have all the permissions of the roles that are lower in the hierarchy. For example, the Office Manager can perform all the actions permitted for the clerical staff. Hierarchies can include either system or user-defined roles.
To find out more about roles assigned to you use:
sp_displayroles – to find out all roles assigned to you, whether or not they are active.
sp_activeroles – to find out which of your assigned roles are active. If you specify the expand_down parameter, Adaptive Server displays any roles contained within your currently active roles.
The syntax is:
sp_displayroles user_name
sp_activeroles expand_down
For more information about roles, see Chapter 13, “Getting Started With Security Administration,” in Adaptive in the System Administration Guide: Volume 1.
