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.
create role role_name [ with passwd "password"
[, {"passwd expiration" | "min passwd length" |
"max failed_logins" } option_value ] ]
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.
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.
specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.
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 the password expiration for intern_role:
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
The create role command creates a role with privileges, permissions, and limitations that you design. For more information on how to use create role, see the System Administration Guide.
For information on monitoring and limiting access to objects, see the set role command.
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 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.
SQL92 – 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.
Commands alter role, drop role, grant, revoke, set
System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect, sp_modifylogin
