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

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.

passwd

adds a password to a role.

password

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.

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:

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.

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