SAP Sybase ASE – Keeping Private Data Private With Data Encryption

Data security is one of the hot topics when you ask top executives about what keeps them up at night. Keeping private data private is a never-ending challenge that needs constant attention and dedication. Data encryption is your best defense for protecting sensitive data. Common access control mechanisms and limiting the power of database administrators will make it harder to access the sensitive data, but once this hurdle is overcome the data is freely accessible. Data encryption will supplement and enhance these security measures by protecting sensitive data at rest in backup files and database files that could be exploited by powerful OS users.

SAP Sybase ASE already contains powerful data access control features like granular permissions to enforce the separation of duties. The data encryption option will extend and enhance these features with sophisticated, flexible algorithms and methodologies.

Data Encryption with SAP Sybase ASE

SAP Sybase ASE first introduced data encryption on the column level in release 12.5. In data encryption terminology there are two phases that compose the process. Encryption will obfuscate the data with the help of an encryption key and store the data in the database. The same encryption key is used to read the obfuscated data from the database and decrypt the data back into its original value.

SAP Sybase ASE 15.7 uses Advanced Encryption Standard (AES) with 128, 192, or 256-bit encryption key sizes for its data encryption methodology. The longer the bit string, the more difficult it is for an unauthorized person to decrypt the data.

Data encryption in SAP Sybase ASE uses a symmetric encryption algorithm, which means that the same key is used for encryption and decryption. When you insert or update data in an encrypted column, SAP Sybase ASE transparently encrypts the data immediately before writing the row. The more complicated the encryption, the more CPU resources will be taken up by the encryption algorithm. This methodology is called Transparent Data Encryption (TDE), and is used by Oracle and SQL Server as well. Both Oracle and SQL Server implemented TDE in their data encryption features, using an encryption key to encrypt and decrypt data. The difference is in the way they do the implementation.

Data encryption in SAP Sybase ASE is governed by a system security officer (SSO) or a Key Custodian. Both roles need to have the sso_role granted to their login profiles and no other user, including database administrators, should have this role granted to them.

Data encryption is an internal affair with SAP Sybase ASE. The master encryption key can be stored in the database itself. SAP Sybase ASE features key encryption keys and key encryption values in its data encryption algorithm. The key encryption key (KEK), formerly known as system encryption key (SEK), now has four levels of security. The most secure option requires a password that is not saved in the database. The loss of this password will make it impossible to decrypt the data in any way whatsoever. As a best practice procedure, the Key Custodian should appoint a trusted user as the key recoverer and in case of a lost password; this recovery password can be used to recover master key access.

SAP Sybase ASE 15.7’s data encryption also allows you to encrypt and store passwords from other systems in the database by using a “Service” key. This includes passwords from CIS, LDAP, SSL and Rep Server.

Implementing Data Encryption

Generally, enabling SAP Sybase ASE data encryption consists of a few steps, but requires in-depth planning to ensure that no data loss occurs due to lost passwords.

The steps involved are:

  1. The SSO (assigned sso_role with an admin login or a Key Custodian) enables column encryption with this command:    sp_configure ‘enable encrypted columns’, 1
  2. Depending on the protection method of your column encryption keys, you need to create a database-level master key or set the system encryption password. This is the key encryption key (KEK) that is being used to create the column encryption keys (CEK).
  3. Create one or more column encryption keys (CEK). If additional passwords are assigned to encryption keys, data will be protected, even from database administrators.
  4. Specify the columns in a table to be encrypted.
  5. Grant decrypt permission to users who must see the data. You can choose a default plain text to mask data for unauthorized users.

Once these steps have been completed, existing applications can run against existing tables and columns, but now the data is securely protected against theft and misuse.

The control over the encryption process, when choosing a database-level master key, can be delegated into an application layer. There is no external protection mechanism to be considered, and the application retains full control. This is important to 3rd party applications as the SAP Sybase ASE data encryption is a licensed option. If a client choses to implement data encryption later on, the application must be able to dynamically enable encryption for its data without manual intervention. If one or more encryption keys are located outside the database, the application loses control over the encryption admin process.

SAP Sybase ASE gives applications the authority to take control of data encryption with database-level master keys and SQL statements based column encryption.

Encrypting Data

SAP Sybase ASE is able to encrypt most of its data types. For obvious reasons, encrypting text and image data types is not possible. You also cannot encrypt computed columns or specify encrypted columns in the partition_clause of a table. Other than that, you’re free to apply data encryption to as many columns as you prefer.

In the simple encryption sample below, you can see how to create two encryption keys, and encrypt two columns with two different keys. This is a new table encryption sample.

create encryption key default_key as default for AES

create encryption key cc_key

create table emp_table (ssn char(15) encrypt, ename char(50),…)

create table customer (creditcard char(20) encrypt with cc_key, cc_name char(50),…)

The ssn column will be encrypted with the default key, and the creditcard column will be encrypted with the cc_key. The column encryption keys (CEK) are being created using the key encryption keys (KEK).  If a password is required you will be prompted to enter the password to create the CEK.

To apply encryption to a column in an existing table, use the alter table / modify command to modify the column. You will use either the default key or a named encryption created in the current database or another database. The exception for this encryption is that you cannot modify a column if there is a trigger in place, or the column is a key in a clustered or placement index. In this case, the trigger / index has to be dropped, the column encrypted, and the trigger / index re-created.

Understanding Decryption

One important part of encrypting data in a column is to ensure that the values can be decrypted. The decrypt permission can be granted either to a role or individually to a user on specific tables. It is advisable to combine select and decrypt permissions at the same time to a user or role. Be careful when granting DML (update, insert and delete) permissions without the decrypt permission to a user or role. This can cause unintended consequences and potentially lead to data loss. Encrypted data can be overwritten with trash so that it is impossible to decrypt the data. In this case, encryption constraints or planning carefully for data access permissions are the best solutions to prevent this scenario.

Data Encryption vs. Data Masking

One of the main distinctions between data encryption and data masking is that encryption has to be reversible and a decryption method has to ensure that encrypted data can be successfully decrypted into the correct value.  Data masking applies data scrambling or obfuscation methods to render sensitive data unusable. One of the most famous data masking examples is displaying only the last four digits of a social security number. All other digits have been replaced by the letter x.

Data masking is divided into static and dynamic masking. Obfuscation either happens in real time when accessing the data, or the data has been store in an obfuscated form into the database. Static data masking is not possible in production databases; it is commonly used to protect sensitive data in test and development environments.

As an added bonus with data encryption, basic data masking can be achieved via the default value setting when encrypting data columns. If a user has not been granted decrypt permission, a default value will be displayed instead.

Data Encryption Consequences

When implementing data encryption with SAP Sybase ASE, special attention to index strategies is required to offset negative performance impacts. That’s why it is important to choose the encrypted columns wisely. SAP Sybase ASE has built-in optimization indexing encrypted columns.  You can access data effectively via an index, but there are certain rules that need to be followed. The same holds true when encrypting constraints.

The biggest consequence when implementing data encryption is the potential loss of data if passwords are lost. SAP Sybase ASE offers four options to manage the key encryption keys (KEK), each one of them with a larger degree of protection. There is still the standard system encryption key (SEK) option available, but all other options require one or more passwords, and these passwords are not stored in the database. Keeping track of these passwords is one of the duties of the SSO or the Key Custodian.

Performance Impacts

Based on independent performance benchmarks, the impact of data encryption on performance largely depends on how many columns are being encrypted. There is a storage increase associated with data encryption, which gets easily overlooked in encryption rollout projects, but could create unintended bottlenecks.

Creating or re-creating indexes will take a performance hit. This is expected behavior; however SAP Sybase ASE implemented performance improvements in its latest version that will mitigate this performance impact.

If encrypted columns are part of a table join in a query, the largest performance impact will be noted, especially if non-encrypted columns are being joined with encrypted columns. Be sure to thoroughly check your encryption plan with the data model and the foreign key reference model.

Expanding Data Encryption beyond Data

SAP Sybase ASE provides strong data encryption methodologies for external login passwords and hidden text, using the AES-256 symmetric encryption algorithm. By using this option, you will be able to apply strong data encryption for external passwords to:

  • Replication Agents and replicated databases
  • CIS for remote descriptors and logins
  • The job scheduler agent
  • Real Time Messaging (RTMS)
  • Secure Socket Layers (SSL) and Lightweight Directory Access Protocol (LDAP) protecting SSL and LDAP access accounts. Passwords are being managed by the stored procedures sp_ldapadmin and sp_ssladmin, and can be secured.

Securing external passwords and hidden text requires the use of a service key. Service keys are 256-bit, persistent encryption keys and are stored in sysencryptkeys. Service keys are encrypted by either using a static key or the master key. Using the master key requires the password to decrypt the database-specific master key.

Conclusion

Encrypting data will protect data at rest from unauthorized access. In case of backup files getting stolen or privileged OS users gaining access to the database files, data encryption renders these security breach attempts useless. Data encryption is required to comply with certain government and industry regulations such as PCI, HIPPA, etc. SAP Sybase ASE implemented data encryption in a secure and flexible way that enables applications to take control over the data encryption process.

SAP Sybase ASE enables data encryption by storing the key encryption keys inside the database. It also added an additional security layer requiring granting special decrypt permission to users in order to make the data readable again. This effectively blocks database administrators from accessing sensitive data in the database and on the backup files.

Based on benchmark tests, data encryption adds very limited overhead to the data processing if used for selective columns in selective tables. The CPU overhead logically increases when encrypting more columns. But as a tradeoff for stellar security, this is a small price to pay.

Need additional support? Sybase ASE third-party support may be for you. Contact us today.

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
  • Encryption
    • 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

Conclusion

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.