Role-Based Data Access Control
In corporate IT environments, guiding security principles are frequently included in security models and architectures. However, the problem isn’t merely what story needs to be told; it’s how to tell this story. When it comes to practical implementation of these principles for DBMSes, the picture isn’t encouraging. Data is the lifeblood of today’s enterprises and DBMSes are the heart of IT ecosystems. However, more the norm than the exception are utilization of overprivileged user accounts and system authorities, loss of user identities when connecting to database systems and loss of control over data access. Reasons for this include:
• Historical. Many corporate security efforts focus on establishing perimeter protection rather than protection of data at the source; application and database security often exist as separate silos.
• Functional. Database systems may lack support for the necessary security functionality.
• Operational. There’s a skills gap between security analysts, database administrators and application development teams.
• Cultural. Compliance management and security solutions are treated as separate IT initiatives.
Steps taken to meet compliancy requirements may not necessarily result in implementing widespread database security policies. For example, data subject to compliance requirements can be moved into dedicated, secured databases on isolated networks designed to meet requirements of a specific standard. The cost of this solution is significant, but security breaches may still occur in the remaining, inadequately protected databases and the monetary damage from these breaches can still be substantial (see Figure 1).
In many corporate IT environments, the implemented data access management approach has these shortcomings:
• Data access management is enforced primarily in middle-tier applications. Only coarse-grained access rules are implemented directly in DBMSes. Users, who bypass applications to run ad hoc queries, have overprivileged access to data. System authorities such as SYSADM have unrestricted access to data.
• Accounts with overassigned privileges are used when accessing enterprise data from client applications and for administrative and operational purposes. Most client applications use pooled connections with associated overprivileged accounts to access databases on behalf of users. When a database connection pool is used, the database connection is never established on behalf of the user who performs the transaction and the database never receives the user identity. Database audit records collect the identities of overprivileged users associated with database connection pools instead of the users on whose behalf transactions were executed.
• Access privileges are assigned to specific users or groups, which are collections of users, rather than to roles—collections of entitlements. Groups are static in the sense that group membership is determined based on user identities. Conversely, security roles are granted to users dynamically based on conditions such as user name, origin of request or the time of day and are aligned with job functions.
• In environments where client applications hosted on distributed platforms and DB2 are on z/OS, there’s an inherent gap when controlling access to data in DB2 for z/OS due to the mismatch between distributed and mainframe identities. This mismatch essentially prevents the use of fine-grained access control models such as Row and Column-based Access Control (RCAC). After all, how can you assign fine-grained permissions for data access to a specific row or column in a DBMS if all you have is an arbitrary user identity associated with pooled connections rather than the actual user identity (see Figure 2)?
Access Control Mechanisms
To establish an effective access control mechanism where any type of access to the database is automatically a subject of access control, we must start by discussing the access control mechanisms available:
Standard SQL access control mechanism: SQL language supports the Discretionary Access Control (DAC) mechanism where SELECT, INSERT, UPDATE, and DELETE privileges are granted or revoked with the SQL GRANTS and REVOKE commands:
GRANT privileges ON object TO users [WITH GRANT OPTIONS]
DAC defines object permissions at the discretion of the originator or owner on the table level. Access privileges may be passed on to other users by an owner’s object. Usually, DAC policies are implemented through views. However, complex, fine-grained DAC access policies are difficult to change and tedious to maintain. Lack of proper security assurance, violation of the separation of responsibilities principle, and management overhead are reasons why SQL DAC is inefficient when used on its own.