|
Category: ASE: Users, Roles und Permissions
Role, Roles
Roles,
in
ASE,
are a technique to assign permissions or privileges to one or more
logins
on a serverwide basis.
Each login can be granted one or more roles. Additionally, roles can also be granted to
other roles, thereby creating a role hierarchy.
This means that a login which was granted a role, also inherits all privileges of the roles
which are below the granted role in the role hierarchy. For example, a company could create a role,
which allows a manager to view salary information for all employees in its Sidney branch.
Another role grants similar privileges on the same information for the employees in New York.
This two roles are granted to a third role, which was assigned to the "Chief Financial Officer"
of the company.
This way the CFO has full access to the salary information for the employees in both locations.
Roles can either be active or inactive, i.e. inactive roles have to be switched on by the user
explicitly, using the set role "rolename" on, to give the login access to the
privileges granted to the role. System roles by default are active. In ASE terms this is called
default on for "active" and default off for "inactive".
There are two kinds of roles in
ASE:
"system roles", created during installation of ASE and
"
user
defined roles", created by administrative database users after installation.
Depending on the version of ASE, there are up to 12 system roles. Most commonly used are the first three roles:
-
sa_role - circumvents the most security checks and contains
database owner (dbo)
privileges for all databases
-
sso_role - required for almost all security relevant tasks
-
oper_role - gives access to
dump
and
load
operations for databases and
transaction logs.
-
sybase_ts_role - required for the majority of
dbcc
commands.
-
replication_role - for
replication server
related tasks.
-
mon_role - needed for task regarding server monitoring , like for example the
monitoring server
or
MDA Tabellen.
-
dtm_tm_role - required for task related to "
Distributed Transaction Management
".
-
ha_role - for configuring "
High Availability
" features
-
js_admin_role, js_client_role, js_user_role - from ASE version 12.5.1 required for the "
Job Scheduler
"
-
messaging_role - starting in version 12.5.2 required for tasks in the area of "Real Time Data Services"
-
webservices_role - from version 15.0 onwards required for "Webservices" tasks
-
navigator_role - up to version 11.0, now no longer required. Was used for Sybase MPP
("Massive Parallel Processing"), a data warehouse flavour
By default, the login "sa" has been granted the roles sa_role, sso_role and oper_role.
User defined roles are created using the command create role.
Where is role information stored?
All defined roles are stored in the
master
database
in the
system table
syssrvroles.
Roles assigned to individual logins can be found in the table
sysloginroles,
also located in the master database.
Information on personal roles
Further information on roles granted to ones own login can be retrieved using the
stored procedures:
-
sp_displayroles
- lists all roles granted to the login, independent if they are active or inactive
-
sp_activeroles
- lists all active roles
|
