Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

alter role

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.

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

is one role in a mutually exclusive relationship.

adds a role in a mutually exclusive relationship; adds a password to a role.

drops a role in a mutually exclusive relationship; drops a password from a role.

makes both named roles mutually exclusive.

does not allow you to grant users both roles at the same time.

allows you to grant a user both roles at the same time, but does not allow the user to activate both roles at the same time.

is the other role in a mutually exclusive relationship.

is the name of the role for which you want to add, drop, or change a password.

adds a password to a role.

is the password to add to a role. Passwords must be at least 6 characters in length and must conform to the rules for identifiers. You cannot use variables for passwords.

locks the specified role.

unlocks the specified role.

applies the setting that follows to the entire server rather than to a specific role.

activates the option that follows it.

specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.

specifies the minimum length allowed for the specified password.

specifies the maximum number of failed login attempts allowed for the specified password.

specifies the value for passwd expiration, min passwd length, or max failed_logins. To set all overrides, set the value of option_value to -1.

Examples

Example 1

Defines intern_role and specialist_role as mutually exclusive:

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 the role physician_role:

alter role physician_role lock

Example 6

Unlocks the role 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.

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

• 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 having mutually exclusive after granting both roles to users or roles. Revoke either granted role from existing grantees before attempting to define the roles as mutually exclusive on 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"

  Passwords attached to user-defined roles do not expire.

Standards

ANSI SQL - Compliance level: Transact-SQL extension.

Permissions

Only a System Security Officer can execute alter role.

See also

Documents

For more information on altering roles, see the System Administration Guide.

Commands

create role, drop role, grant, revoke, set

Functions

mut_excl_roles, proc_role, role_contain, role_id, role_name

System procedures

sp_activeroles, sp_displaylogin, sp_displayroles, sp_modifylogin