alter role

Description

Defines mutually exclusive relationships between roles; adds, drops, and changes passwords for roles; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role. alter role also locks and unlocks roles.

Syntax

alter role role1 {add | drop} exclusive 
	{membership | activation} role2 

alter role role_name [add passwd "password" | 
	drop passwd] [lock | unlock]

alter role {role_name | "all overrides"} 
	set {passwd expiration | min passwd length | 
	max failed_logins} option_value 

Parameters

Examples

Example 1

Defines intern_role and specialist_role as mutually exclusive at the membership level:

alter role intern_role add exclusive membership 
    specialist_role

Example 2

Defines roles as mutually exclusive at the membership level and at the activation level:

alter role specialist_role add exclusive membership 
    intern_role
alter role intern_role add exclusive activation 
    surgeon_role

Example 3

Adds a password to an existing role:

alter role doctor_role add passwd "physician"

Example 4

Drops a password from an existing role:

alter role doctor_role drop passwd

Example 5

Locks physician_role:

alter role physician_role lock

Example 6

Unlocks physician_role:

alter role physician_role unlock

Example 7

Changes the maximum number of failed logins allowed for physician_role to 5:

alter role physician_role set max failed_logins 5

Example 8

Sets the minimum password length for physician_role, an existing role, to five characters:

alter role physician_role set min passwd length 5

Example 9

Overrides the minimum password length of all roles:

alter role "all overrides" set min passwd length -1

Example 10

Removes the overrides for the maximum failed logins for all roles:

alter role "all overrides" set max failed_logins -1

Usage

• The alter role command defines mutually exclusive relationships between roles, and adds, drops, and changes passwords for roles.

• The all overrides parameter removes the system overrides that were set using sp_configure with any of the following parameters:

  • passwd expiration

  • max failed_logins

  • min passwd length

  Dropping the role password removes the overrides for the password expiration and the maximum failed logins options.

• When you use alter role to lock or unlock roles, you set (or unset) the locksuid, lockdate, and lockreason columns that are added to syssrvroles.

• You need not use any particular order to specify the roles in a mutually exclusive relationship or role hierarchy.

• You can use mutual exclusivity with role hierarchy to impose constraints on user-defined roles.

• Mutually exclusive membership is a stronger restriction than mutually exclusive activation. If you define two roles as mutually exclusive at membership, they are implicitly mutually exclusive at activation.

• If you define two roles as mutually exclusive at membership, defining them as mutually exclusive at activation has no effect on the membership definitions. Mutual exclusivity at activation is added and dropped independently of mutual exclusivity at membership.

• You cannot define two roles as mutually exclusive property after granting both roles to users or roles. Revoke either granted role from existing grantees before attempting to define the roles as mutually exclusive at the membership level.

• If two roles are defined as mutually exclusive at activation, the system security officer can assign both roles to the same user, but the user cannot activate both roles at the same time.

• If the system security officer defines two roles as mutually exclusive at activation, and users have already activated both roles or, by default, have set both roles to activate at login, Adaptive Server makes the roles mutually exclusive, but issues a warning message naming specific users with conflicting roles. The users’ activated roles do not change.

To change the password for a role, first drop the existing password, then add the new password, as follows:

alter role doctor_role drop passwd

alter role doctor_role add passwd "physician"

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only a system security officer can execute alter role.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also