System Role Functions
Adaptive Server provides two built-in functions to see what roles a user has:
n
show_role() — Lists a login’s roles
n
proc_role() — Used to see if a login has a specific role
Example:
select show_role()
Lists all of the roles that are currently enabled for a login.
Example:
if (proc_role("sa_role") = 1)
print "The role is SA"
This will print the message “The role is SA” if the sa_role is enabled for the
login.
These can be used to display current roles or to verify that procedures are
executed only by those who have specific roles.
Turning System Roles Off
If you have specific roles, you may disable them for a session by using a set
statement; this might be useful in a testing environment. If you have your own
user ID and wish to test with it (rather than, say, sa), this is a good way to
accomplish that without having to have two separate logins.
Syntax:
set role "role_name" {on | off}
Enables or disables a role immediately.
User-Defined Roles
In addition to the system-defined roles, ASE allows you to define your own
roles. This effectively gives the SSO the ability to create groups at the server
level. For those of you who are interested, this is an ANSI SQL 3 standard
feature.
Chapter 6: Security
145
syslogins sysloginroles syssrvroles
SUID
Name
SRIDSUID SRID
Name
1
...
18
sa
...
Jsmith
1
1
1
...
18
0
1
2
...
0
0
1
2
sa_role
sso_role
oper_role
Figure 6-8
Permissions granted to a role take precedence over permissions granted
to a user/group.
Planning User-Defined Roles
In order to facilitate user-defined roles, it is important to consider:
n
The roles you want to create
n
The responsibilities for each role
n
The position of each in the role hierarchy
n
Which roles in the hierarchy will be mutually exclusive
n
Whether such exclusivity will be at the membership level or activation
level
Creating Roles
Roles are created with the create role statement by users in the master data-
base with the sso_role.
Syntax:
create role role_name [with passwd password”]
role_name is the server-wide name of the role.
Example:
create role clerk_rl
Optionally you can assign a password that must be supplied to enable the role.
Note that the same password will be used by every login that holds the role;
it’s not meant to be secret, it’s only intended to keep a login from automati-
cally (or accidentally) enabling a role.
A server may contain up to 1,024 roles. The first 32 roles are set aside for
system roles, leaving 992 user-defined roles.
Dropping Roles
User-defined roles can be dropped with the drop role command by a login
with the sso_role who is in the master database. You cannot drop system
roles.
Syntax:
drop role role_name [with override]
If the role has permissions assigned, you must use with override to drop the
role, or revoke all permissions first.
You need not drop memberships before dropping a role. Dropping a role
automatically removes any users membership in that role, regardless of
whether you use the with override option.
146 Chapter 6: Security
Mutually Exclusive Roles
You can enforce separation of duties by forcing the roles to be mutually
exclusive. Think of this as declarative referential integrity for roles.
You can configure this separation so that two roles cannot be active at the
same time (activation), or so that two roles cannot be assigned to the same
login (membership).
Exclusive roles are assigned using the alter role command:
alter role role_name1 {add | drop} exclusive {membership | activation}
role_name2
If you configure roles to be membership exclusive and the roles are both
already assigned to a login or role, the alter role command will fail. If you
configure roles to be activation exclusive and a login has both roles active, the
login will receive an error message.
Assigning Passwords to Roles
You can force additional passwords to be role specific. Typically, this is used
only when a login is executed from within an application. The application
user would have to supply the password to enable the role. This also gives you
the ability to, for example, disable a role temporarily by changing its pass-
word. You can assign a role a password at role creation time with the create
role statement, or add or remove one afterward with the alter role statement.
To create a role with a password:
create role hr_rl with passwd ‘HumanResources’
Here, we are creating a role called hr_rl, which will have HumanResources
(passwords are case sensitive) as a password.
To add and remove passwords:
alter role role_name add passwd 'password'
alter role role_name drop passwd
Examples:
alter role test_rl add passwd 'mypassword'
alter role test_rl drop passwd
Passwords may be subject to minimum length, failed attempt, expiration
interval, and required digit limitations just as logins are. The encrypted pass
-
word is stored in syssrvroles.
Chapter 6: Security
147

Get Administrator's Guide to Sybase ASE 15 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.