create role

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, which must be unique to the server and conform to the rules for identifiers. role_name 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 – password expiration interval specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive. For example, if you create a new login on August 1, 2007 at 10:30 a.m., with a password expiration interval of 30 days, the password expires on August 31, 2007 at 10:30 a.m.
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 passwd expiration to 7 days. The password for the role expires at the time of day that the password was last changed after the specified period has passed (in this example, 7 days):
```
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.
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.

Note: 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, SAP ASE 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.

The restrictions for create role are:

The maximum number of roles that can be created per server session is 1024. However, 32 roles are reserved for system roles, such as sa_role and sso_role. Also, a special user-defined role, sa_serverprivs_role was created by SAP ASE. Therefore, the maximum number of user-defined roles that can be created per server session is 991.
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.

See also sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create role differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with the `manage roles` privilege.
Disabled	With granular permissions disabled, you must be a user with sso_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	85
Audit option	roles
Command or access audited	create role, drop role, alter role, grant role, or revoke role
Information in `extrainfo`	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 is in effect