SAP Sybase ASE 15.7 ESD#2 – Separation of Duties

Protecting sensitive data from prying eyes is a hot topic these days. According to Gartner research results, 70% of high-cost security incidents occur when data from inside the organization gets out. Most data leakage occurs either by accident, or because of poor business processes.

SAP Sybase ASE 15.7 ESD#2 has introduced a new security feature called Granular Permissions. This is a new feature that enables database administrators to fine tune the “separation of duties” that were in place since the introduction of role based security. Granular Permissions offer organizations the path to avoid security breaches, and have tighter control over which users can access sensitive data.

Granular Permissions are grantable system privileges that allow you to enforce “separation of duties.” For tight access control, all users in a database environment should be granted the least amount of privileges possible required doing their job. Granular Permissions reconstruct system-defined roles (sa_role, sso_role, oper_role and application_role) to a set of explicitly granted privileges. In other words, Granular Permissions divide role-based security into individual, grantable privileges that can be assigned to and revoked from any database user. System privileges can overlap. Some privileges imply another, more granular privileges. In this case, it is important to understand the hierarchy of the privileges to grant exactly the right ones.

Improving Data Security

To better understand the role of data security and implementing Granular Permissions into an existing environment, it is important to see the bigger picture of data security tasks. The level of data security needs depend largely on the size and industry of your company. Industry and government regulations dictate your data security requirements. Larger corporations have dedicated teams performing specific tasks for individual security needs.

Data security is typically separated into the following categories:

Access Management
Manage Authorization (Roles)
Manage Authentication (Users)
Admin Control
Oversee Separation of Duties (SoD)
Oversee Configuration and Change Management
Auditing / Monitoring
Identify who is Accessing What, When, Where, and How
Identify Unusual User Behaviors
Protect Stored Data (Backups, Drives)
Protect Data in Transit (Network, Wireless)
Data Masking
Protect Test Data
Protect Production Data

Understanding Granular Permissions

SAP Sybase ASE’s Granular Permissions cover the Access Management and the Admin Control categories, two important building blocks in a comprehensive security strategy. These two building blocks are the foundation and first line of protection to control whom can access which information. Controlling access is only effective as long as the system is actively managing the access. Once database server engines are no longer in control over the user access layer, you need to use data encryption as your last line of defense to effectively protect sensitive data. For example, access to the backup files is controlled by the OS layer and it is outside the database engine. This is a topic that will be discussed in another article.

The basic concept behind Granular Permissions is grantable system privileges. This concept allows the grantor of these permissions to very selectively grant or revoke system privileges, separating security and admin duties of a database administrator into multiple user logins. This implements a much more refined access control by expanding the more generic system roles of sa_role, sso_role, and oper_role with Granular Permissions.

Implementing Granular Permissions

There are two fundamentally different approaches to implement security measures. One approach is to implement security layers outside, and around the core database engine. This allows for more flexibility and scalability for extending security to other systems. But, it also opens opportunities to circumvent the security layer. This approach requires additional counter-measures to close the gaps. These security layers tend to make systems more complex and more difficult to manage.

The second approach is to embed the security protection inside the database engine. This is the method SAP Sybase ASE offers. The advantage is there are no exposures that allow for any circumvention of the protection layer.

Granular Permissions are a licensed feature of the SAP Sybase ASE Security and Directory Services option. A database administrator is required to have the sso_role assigned to enable Granular Permissions.
sp_configure “enable granular permissions”, 1
This is a dynamic parameter and does not require a reboot of the server. This will add the new system-defined role “sa_serverprivs_role”, which allows the database administrator to explicitly manage the Granular Permissions. This role can be revoked from the database administrator and assigned to the security administrator. This will add an extra security layer by restricting the ability to override privileges.

There is also a new default database user usedb_user in each database. When Granular Permissions are enabled, users accessing a database will assume the user name userdb_user if the user is granted the use database privilege on the database. This will allow database administrators to grant system privileges to a database user who is not a valid user in the database, performing system related activities without gaining access to the data in this database. In order for this action to be effective, the guest user access must be disabled in this database.

Granular Permissions vs. Data Encryption

A multi-layered approach using both Granular Permissions and data encryption is your best defense against unauthorized access to sensitive data. Here is how they work in concert with one another. Granular Permissions actively protect the data from unauthorized access by implementing effective identity management and admin control. These permissions are part of the security layer that is active once a user connects to the database. Outside the database engine, the permission security is ineffective.

That’s when data encryption takes over. Encryption is commonly used to secure data transfer between two end points. It is also effective in storing data on a disk media in encrypted form. Everybody who knows what a hex editor is knows how easy it is to spy on files, regardless of their format. The data encryption on disk media storage will protect database data and backup files from getting into the wrong hands. The database engine’s role is to decrypt the data and make it available to anybody who has access to the data. This is governed by the access control and, optionally, by Granular Permissions.

These two security methods are the first line of defense, and provide excellent protection. Together, they provide a security blanket that spawns from the physical files to database access control.

The Value of the Separation of Duties

The phrase ‘separation of duties’ is nothing new, and has been a top concern by the user community for years. Don’t get me wrong, separation of duties existed before the introduction of Granular Permissions, but at a much higher level.

Granular Permissions allow organizations to empower regular users to do more, protect sensitive data by limiting the access of database administrators, and separate security tasks from administrative tasks. Using these permissions, companies can safely delegate routine administrative tasks to other departments within the organization, and free up valuable database administrator resources.

Database administrators will attest that, for the most part, their job duties do not require access to user data, except for one critical exception. In a performance triage situation, database administrators need access to restricted data to evaluate the situation, and apply solutions. This directly conflicts with the need to limit access to the data owners.

During an exception situation like this, temporary access can be granted to database administrators. The value of temporary access is that the exception can be controlled and closely monitored to further reduce the exposure.

Granular Permissions Require Planning

Yes, the implementation of Granular Permissions requires some planning. It largely depends on what methodology an organization wants to implement. Careful planning to empower users to do more can free up valuable database administrator resources. Granular Permissions are an extension of the existing security model of SAP Sybase ASE.

Limiting the access of the database administrator to user data requires more planning. It requires the implementation of a system security operator role, and spreading the database administrator roles to multiple login profiles. This might make database administrators nervous about their effectiveness to administer the database. The right planning, however, ensures success with a clear list of responsibilities and hand offs for each role for database administrators.

Use Cases for Granular Permissions

The best way to demonstrate the usability of Granular Permissions is through use cases. These are just a few examples.

Enabling developers to manage their own development database
Before Granular Permissions were available, database administrators needed to assign the sa_role to developers if they wanted to grant them the rightto backup and restore their development database. This was way more than the developer needed, but the only way to ease the workload on the database administrator. With Granular Permissions, a database administrator can assign the database backup, and restore privileges to the developer with just a couple of commands. This enables more independence for the development team, and frees up valuable database administrator resources, while preserving the system integrity.For example: Mike is a developer of the db1 database. To enable Mike to backup and restore his development database, Mike must first be a user in the master database. The following commands allow Mike to take control over the backup of his database, without giving Mike full control over the database server:use master
grant dump database on db1 to mike
grant load database on db1 to mike
grant checkpoint database on db1 to mike
Limiting user data access for database administrator(sa) and database owners (dbo)
Limiting access to sensitive user data is the top concern for data owners. SAP Sybase ASE with Granular

Permissions allows you to do exactly that. By default, the sa_role has all the power to do all the administrative tasks and has access to data in the database. There are other roles the default database administrator login has been assigned. To effectively implement the “separation of duties”, the first step is to move the sso_role into another login owned by a security administrator.

This will prevent the database administrator from gaining access to restricted data by simply changing passwords on data owner logins.The next step is to remove access privileges to all, or specific user databases from the sa_role. Once this is in place, the database administrator can no longer access user databases. To further tighten the control over access to the user data, it is a key step to restrict database owners’ (dbo) ability to assume other database users’ identities. By revoking thesetuser privilege from thedbo user, this functionality is no longer available to database owners.Implementing all these steps with Granular Permissions will limit the access of the database administrator, and database owners to user data.To limit a database administrator’s access to a user database, the following commands must be issued:

use master
revoke own any databases from sa_role
revoke manage server permissions from sa_role

In addition you need to make sure that there are no dbo aliases for the sa user in these user databases.

These commands will prevent database owners from assuming other users’ identity:

use db1
revoke setuser from dbo

Enabling help desk operators

There are certain tasks that rank in the top five activities of help desk operators. Resetting user’s passwords, and stopping hanging or inactive user connections from taking over the entire system are at the top of the activity list. Without Granular Permissions, the only way to extend these privileges to the help desk is to grant sa_role and sso_role to these users, and essentially upgrade them to database administrators. Now they can perform their duties, but they have the keys to the kingdom in the process. This is probably not what you want.
A more functional and secure method is to use Granular Permissions, grant the change password, and kill any process privileges to the help desk user logins. With this simple step, the help desk is more effective and can reduce turnaround times dramatically. Best of all, valuable database administrator resources are freed up again.

With these simple commands, Alice, a help desk operator, is enabled to be more effective in her job without gaining full control over the database server.

use master
grant change password to alice
grant kill any process to alice


Data security is a hot topic these days and SAP Sybase ASE offers the right tool to enable clients to protect their data assets on every level of the database access control layer. The Granular Permissions functionality effectively replaces the hammer with a scalpel to fine tune data access. This functionality enables developers and helpdesk operators to perform their duties more independently. It gives organizations a powerful method to implement separation of duties to more closely align to regulatory requirements.

SAP Sybase ASE stays true to its core by implementing Granular Permissions via system configuration steps. The heavy lifting is done behind the scenes and enables organizations to implement separation of duties without a steep learning curve. One single command will enable the Granular Permissions and a few more simple commands fully implement a fine-tuned access control mechanism.

Granular Permissions are a licensed option, but worth the investment to free up valuable database administrator resources. Database administrators can delegate routine tasks to other professionals in their organization with Granular Permissions, giving them precious time for added productivity in other important areas.


About the Author

Peter Dobler is an accomplished IT database professional who delivers results by building sophisticated data systems, by implementing advanced application software, and by improving efficiencies and reducing costs. He founded Dobler Consulting, ( a Tampa, Florida consulting firm that delivers implementation expertise for Oracle, SAP Sybase, and MS SQL Server in 2000. His 25 plus years in technology started in Basle, Switzerland in 1985. Peter uses his extensive experience to hone his talent as a proven resource for producing streamlined IT solutions. He currently engages in strategic alliances and special projects with SAP Sybase, the enterprise software and services company. He can be reached at: or 813-322-3240.


Leave a Comment

Your email address will not be published. Required fields are marked *