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 is also used to lock and unlock 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

role1

is one role in a mutually exclusive relationship.

add

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

drop

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

exclusive

makes both named roles mutually exclusive.

membership

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

activation

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.

role2

is the other role in a mutually exclusive relationship.

role_name

is the name of the role for which you want to add, drop, or change a password. Use role_name to specify the password expiration interval, the minimum password length, and the maximum number of failed logins.

passwd

adds or drops a password to a role.

password

is the password to add to a role. You cannot use variables for passwords. For rules on passwords, see Chapter 14, “Managing Adaptive Server Logins, Database Users, and Client Connections,” in the System Administration Guide, Volume 1.

lock

locks the specified role.

unlock

unlocks the specified role.

all overrides

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

set

activates the option that follows it.

passwd expiration

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

min passwd length

specifies the minimum length allowed for the specified password.

max failed_logins

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

option_value

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 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 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


Mutually exclusive roles


Changing passwords for roles

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:

Event

Audit option

Command or access audited

Information in extrainfo

85

roles

create role, drop role, alter role, grant role, or revoke role

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

See also

Commands create role, drop role, grant, revoke, set

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

Functions mut_excl_roles, proc_role, role_contain, role_id, role_name

System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_modifylogin