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.
create role role_name [with passwd "password" [, {passwd expiration | min passwd length | max failed_logins} option_value]]
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.
attaches a password the user must enter to activate the role.
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.
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.
specifies the minimum password length required for the specified role.
specifies the number of allowable failed login attempts for the specified login.
specifies the value for passwd expiration, min passwd length, or max failed_logins.
Creates a role named doctor_role:
create role doctor_role
Creates a role named doctor_role with the password “physician”:
create role doctor_role with passwd "physician"
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
Sets the maximum number of failed logins allowed for intern_role:
create role intern_role with passwd "temp244" max failed_logins 20
Sets the minimum password length for intern_role:
create role intern_role with passwd "temp244", min passwd length 0
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.
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.
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.
ANSI SQL – Compliance level: Transact-SQL extension.
You must be a system security officer to use create role.
create role permission is not included in the grant all command.
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 |
|
Commands alter role, drop role, grant, revoke, set
System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect, sp_modifylogin