The System Roles
There are a few predefined server roles.
Table 6-5: Server roles
Role Definition
sa_role Performs system administration
sso_role Performs security administration
oper_role Performs operator functions
replication_role Used by replication processes
sybase_ts_role Used to perform undocumented maintenance tasks
dtm_tm_role Used in externally coordinated XA transactions
ha_role Controls High Availability Companion Server actions
mon_role Provides access to monitoring tables
js_admin_role Allows for administration of the Job Scheduler
js_client_role Executes Job Scheduler tasks
js_user_role Creates and runs jobs in the Job Scheduler
messaging_role Administers and executes Real Time Messaging
navigator_role Used if you have Navigation Server
web_services Administers Web Services
You probably will not grant replication_role or navigator_role to a login. I
will often give myself the sybase_ts_role when I go into a new shop, as it
allows you to perform a variety of (officially) undocumented dbcc tasks, such
as dbcc help (‘command’) to get the syntax for other undocumented dbcc
commands. The dtm_tm_role and ha_role are not necessary unless you are
performing distributed transactions or have separately licensed the HA fea
-
ture for warm standby.
Initially, the sa login has the following roles: sa_role, sso_role, oper_role,
and sybase_ts_role. These may be assigned to other users, and the sa login
subsequently locked.
System Role Definitions
The sp_role command can be used to grant roles to and revoke roles from
logins (though grant and revoke are recommended).
Syntax:
sp_role {"grant" | "revoke"}, role_name, login_name
Example:
sp_role "grant", sa_role, jgarbus
The sso_role and oper_role are granted by the SSO; the sa_role is granted by
the SA. The last remaining SA and SSO roles cannot be revoked. Roles
granted will take effect the next time the user logs in, or the user can invoke it
Chapter 6: Security
143
immediately using the set role command. You cannot revoke a role from a
login when it is currently logged in.
Alternately, you may use the same grant and revoke commands discussed
previously to assign or remove roles; the chief advantage to grant/revoke is
that you may use lists of roles and logins.
Syntax:
grant role role_name [, role_name ...] to grantee [, grantee ...]
revoke role role_name [, role_name ...] from grantee [, grantee ...]
Example:
grant role sa_role, sso_role to jgarbus, gtyrrell, achang
revoke role sa_role, sso_role, oper_role from blefevre
syslogins, sysloginroles, and syssrvroles
Roles (and new roles that you create) are added to the syssrvroles table. Since
a login may have many roles, the sysloginroles resolves the many-to-many
relationship, and manages the instances of roles for each login.
You can see the relationship in Figure 6-8. The sa login, which has a server
user ID of 1, is assigned the sa_role, sso_role, and oper_role. Jsmith, who has
an SUID of 18, also has the sa_role.
144 Chapter 6: Security
syslogins
suid
status
accdate
totcpu
totio
spacelimit
timelimit
resultlimit
dbname
name
password
language
pwdate
audflags
fullname
srvrname
sysloginroles syssrvroles
srid
name
password
suid
srid
status
1N N1
Figure 6-7

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.