Use the grant role ... where command to restrict role activation according to certain conditions. Only active roles can have access privileges. The grantee of a role with an activation predicate may be a user or a login profile (a login profile is a template of attributes that is applied to assigned users when they log in).
The syntax is:
grant role role_name
[where pred_expression]
to {username | login_profile_name }
The where pred_expression clause is a role-activation predicate or SQL condition that must be satisfied when the named role is activated. Use pred_expression when granting a role to a user or login profile.
You must be in the master database
to grant a role with an activation predicate.
Role activation predicates, like predicated privileges, can access database objects such as tables, views, SQL functions, and built-in functions. These accesses are checked against the permissions and roles of the predicate owner (the grantor of the role) instead of requiring explicit permission on the objects accessed by the predicate by the user who executes the set role statement.
Although you can reference a view with an activation
predicate, you cannot reference a view with a row-filtering predicate.
The following example allows login “Bob” to perform maintenance duties on the server during off-peak hours. If Bob attempts to enable the oper_role between 8 am and 6 pm, Adaptive Server returns an error.
grant role oper_role where datepart(hour, current_time()) not between 8 and 18 to Bob
If an activation predicate is used on a grant role to a user or login profile, the predicate is evaluated either:
During login, if the role is designated as a default role for the user, or
During login, if the role is granted to a login profile and has been added as an auto-activated role, or
When the user attempts to set the role.
Roles granted to a login profile apply to all users assigned to that profile, and can be specified for automatic activation at login, based on evaluation of the predicate. For example, this allows users associated with the admin login profile to assume the sa_role when they use the resource_monitor application:
grant role sa_role
where
(select get_appcontext('SYS_SESSION', 'applname'))
= 'mon_resource' to loginprof_admin
If a role activation predicate evaluates to false, Adaptive Server returns an error for the set role command, or silently does not set a default or automatically activated role during login.
