Chapter 1: Commands

create role

Description

Creates a user-defined role; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role at creation. You can also associate a password with the role at the time that the role is created.

Syntax

create role role_name [ with passwd "password" 
	[, {passwd expiration | min passwd length | 
	max failed_logins } option_value ] ]

Parameters

role_name: is the name of the new role. It must be unique to the server and conform to the rules for identifiers. It cannot be a variable.

with passwd: attaches a password the user must enter to activate the role.

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

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 password length required for the specified role.

max failed_logins: specifies the number of allowable failed login attempts for the specified login.

option_value: specifies the value for passwd expiration, min passwd length, or max failed_logins.

Examples

Example 1

Creates a role named doctor_role:

create role doctor_role

Example 2

Creates a role named doctor_role with the password “physician”:

create role doctor_role with passwd "physician"

Example 3

Sets the password expiration for intern_role:

create role intern_role with passwd "temp244", 
passwd expiration 7

Example 4

Sets the maximum number of failed logins allowed for intern_role:

create role intern_role with passwd "temp244" 
max failed_logins 20

Example 5

Sets the minimum password length for intern_role:

create role intern_role with passwd "temp244", 
min passwd length 0

Usage

Use create role from the master database.
Use the with passwd password clause to attach a password to a role at creation. If you attach a password to the role, the user granted this role must specify the password to activate the role.

For information on adding a password to a role after creation, see the alter role command.

Passwords created in versions before 12.x that are attached to user-defined roles do not expire.
Role names must be unique to the server.
Role names cannot be the same as user names. You can create a role with the same name as a user, but when you grant privileges, Adaptive Server resolves naming conflicts by making the grant to the user instead of the role.

For more information on naming conflicts, see the grant role command.

Restrictions

The maximum number of roles that can be created per server session is 1024. However, 32 roles are reserved for Sybase system roles, such as sa_role and sso_role. Therefore, the maximum number of user-defined roles that can be created per server session is 992.
If you create a role with an attached password, a user cannot activate that role by default at login. Do not create a role with an attached password if the user to whom you grant that role needs to activate the role by default at login.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be a System Security Officer to use create role.

create role permission is not included in the grant all command.

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