There is no organization that can claim that they are 100% secure.  In the light of increasing attacks on data, it is essential that all organizations take precaution to protect their data in all possible ways. Organizations of all sizes do their due diligence to make sure that their data is well protected.  Even with all these precautions in place, Verizon Data Breach Investigations Report of 2014 stated that in 2013 alone there were 63,437 (an increase from 47,000) security related incidents of which 1,367 (an increase from 621) of them were confirmed to be data breaches.  Most of these incidents were opportunistic attacks which means that they could have been prevented.    The most difficult of these breaches occur from internal sources. Organizations need to be fully cognizant of this and take necessary steps to protect the data from reaching the wrong hands.

Perimeter protection is the first step any organization should take when it comes to security. Entry points like the network should be robust in terms of both in-coming as well as outgoing traffic. Firewall protection and policy protection combined with login authentication requirements should be extremely rigid.  The next step is to protect the database environment.  Compliance regulations such as the US Sarbanes-Oxley (SOX), US Healthcare Insurance Portability and Accountability Act (HIPAA), Japan Privacy Law, EU Privacy Directive, and the US California Senate Bill 1386 require tighter internal controls to prevent unauthorized disclosure or modification of sensitive information which could lead to identity theft and financial irregularities.

SAP ASE ensures that data is protected from all angles right from the time a user requests login permissions to when the database is being queried and even when the data is at rest. SAP ASE allows both internal and external mechanisms for authentication.  A ‘login’ is a means of providing a name and a password to request access to SAP ASE.  SAP ASE looks in a system table called syslogins to verify if the username and password provided are valid.  SAP ASE also supports a variety of external mechanisms as well.  Some of the mechanisms are Kerberos which provides a centralized and secure authentication mechanism where the authentication occurs with a trusted, third-party server called a key distribution center to verify both the client and the server, LDAP which provides a centralized authentication mechanism using login name and password, and finally PAM user authentication which provides a centralized authentication mechanism that uses operating system interfaces for administration and run-time application operations.   After the initial login requirements are met, SAP ASE manages access through a set of pre-defined system roles.  Each of these roles are associated with a pre-defined privileges which may not be taken away from users.   Each activity that gets executed in SAP ASE requires the user to possess specific roles.  In SAP ASE versions prior to 15.7 SP100, the system administrator had access to everything in the database and could assume Database Owner title of any database they wanted to access. 

SAP ASE has taken security to the next level by introducing granular permission control functionality which helps construct site-specific roles with privileges to match site job requirements.  ASE allows customers to revoke permissions from out-of-box system defined roles by enabling a new configuration option called “enable granular permissions”.  The nice thing is that it is possible to restrict DBO’s and SA’s from accessing specific user data as well.  In earlier release of ASE, a system admin and database owner had “unlimited” power with the data.  An user with sa_role/sso_role can almost do anything in the server and a database owner could access any user table via the setuser command.    With ASE 15.7 SP100 and later releases, system admin and database owner’s power can be limited if configured and implicit permission vested in sa_role/sso_role/dbo can be revoked.  This means that by default, sa_role would not have permissions on objects owned by dbo. Here is a quick look into what was available in the previous releases of ASE prior to 15.7 ESD#2  and what is now available:

Pre-ASE 15.7 ESD#2

ASE 15.7 ESD#2 and later

Limited number of grantable commands (system privileges), 12 in total

Huge increase of grantable system privileges…> 100 in total

4 server-wide commands: create database, connect, set tracing, set proxy

50 server-wide privileges: change password, shutdown, dump database, etc

8 database-wide commands: create table, create encryption key, create default, create function, create procedure, create rule, create trigger, create view

58 database-wide privileges: setuser, create any table, select any table, etc

Immutable system-defined roles + user defined roles with limited flexibility :

13 immutable system-defined roles: sa_role, sso_role, oper_role, etc.

User-defined roles has limited flexibility: grantable commands is limited

Mutable system-defined roles + user-defined roles with unlimited flexibility:

5 commonly used system-defined roles are made mutable: sa_role, sso_role, oper_role, replication_role, keycustodian_role

More useful user-defined roles can be constructed: huge number of grantable privileges/permissions

SAP ASE 15.7 ESD#2 introduced additional security measures to protect data.  New granular, flexible and robust access control mechanism called Predicated Privileges was introduced to enforce policies that would allow users to read and write data.  This functionality allows the administrator to grant access to sensitive data based on the content of the data’s access tag.  SAP ASE qualifies all access to the sensitive table/column by adding one or more customized predicates that serve as a data filter for the table.   This data privacy protection mechanism is based on row-level access controls that dynamically grants privileges to a user based on data content and/or context information.   Predicates for role activation are a pre-requisite for time-based role activation which would be automatically set for a given user at some particular time (or other condition) and automatically revoked at some other time (or condition).

It is quite flexible in that access can be granted to users based on attributes of their session’s profile. This can be done directly, or through granting roles to login profiles.  If there is a need to restrict access to data to preserve its secrecy against fraud or theft, and also to protect the privacy of the owner of the data. Predicated Privileges are an enhancement to the ASE access control system where the “grant” statement is extended to grant row-level access control on a given object based on conditions expressed through a general SQL “where” clause. The where clause is applied when a query against the object is processed and the conditions are evaluated when a query is executed.  The important thing is that the “grant role” statement is extended to support role activation based on dynamic predicate evaluation, allowing user connections to associate with a defined security domain without the overhead of login triggers.

In closing, deploying technology that reliably keeps your enterprise data secure has become a necessary best practice to comply with security regulations.  And SAP ASE does it once again with adhering to security using access control to ensure that data is protected from both external as well as internal sources.

To report this post you need to login first.


You must be Logged on to comment or reply to a post.

  1. Jeroen Rijnbergen

    Granular permissions brings a lot of possibilities in restricting system roles.

    Unfortunately it also reduces security…

    For example permission ‘Manage Roles’, with this permission you can grant any role to any login or other role. Including sa_role and sso_role. There’s no restriction anymore for having sa_role to be able to grant sa_role. Same for sso_role. You can grant this role without having the role.

    It’s quite difficult (maybe even impossible?) to implement separation of duties with permission ‘Manage Roles’

    1. Jason Froebe

      It definitely pushes the security responsibility more heavily on the DBA / Security Officers.  The increased granularity has the potential of being more secure but in practice you’re probably correct Jeroen.  The sheer number of roles can become confusing and/or misused especially when you’re dealing with a heterogeneous DBMS environment.  Every DBMS vendor seems to implement security differently and often with different meanings for similarily named ‘roles’. 


Leave a Reply