In-Depth

Security Basics: Database Access Controls

How privileges, roles, views, and row-level security are used to protect your data

• By Robert Stackowiak
• 06/18/2003

Typically, “security” efforts are targeted at transactional systems. But security for data warehousing is just as important – if not more so. Unlike a transactional database, a data warehouse stores current and historical records consolidated from multiple transactional systems. This valuable information about customers, sales, and product development makes your data warehouse a more tempting target for an attack. Why steal a day's worth of data from a transactional database when you could get your hands on a company's financial history or list of customers from a data warehouse?

The results of security breaches in a data warehouse can be extremely serious, ranging from financial exposure to disrupted operations. So it’s no wonder that IT departments today are taking a much harder look at securing data warehouses. Technology analysts concur. Gartner’s Kevin Strange stresses the importance of taking security into account when building a data warehouse: "Just as an enterprise should ensure the security of its online transaction processing applications, so must an enterprise perform comprehensive planning for, and evaluation of, security issues for a data warehouse." (“CIO Alert: Many Data Warehouses Are Security Time Bombs Waiting to Go Off,” September 4, 2002)

How do you ensure users can only access information pertinent to them – and that all your sensitive information stays private? While basic security can ensure that user identity is established through authentication, access control is the most important and necessary security layer to ensure users can see only the information they need to do their jobs or perform authorized functions, and no more.

Database Access Control

Database access control is recognized as the best means of protecting data in the data warehouse since security is implemented closest to the data and cannot be bypassed. Databases without flexible and granular access control can force IT to build complex front-end applications, a costly and risk-prone endeavor.

Access control is based on the notion of privileges -- the authorization to perform a particular operation. Privileges are required to gain access to information in the database. A database can enable implementation of “least privileges,” that is, granting a user only those privileges required to do his or her job. While privileges enable restriction of the types of operations a user can perform in the database, managing privileges can be complex.

Roles address the complexity of privilege management by providing user-defined collections of privileges that can be granted to (and revoked from) users and from other roles. One could create a MARKETING MANAGER role, grant it all privileges needed by marketing managers to perform their jobs, and then simply grant this role to all marketing managers. A role can be a foundation for other roles. For example, a VP MARKETING role could be granted the MARKETING MANAGER role plus any other necessary privileges needed by all vice presidents in Marketing.

Granting an additional privilege to the MARKETING MANAGER role would grant an additional privilege to all marketing managers and their vice presidents. Revoking a privilege from that role would revoke that privilege from all marketing managers and VPs. Note also that a role can be defined to prompt the user for a password when invoked, providing another layer of security.

While privileges allow control of the operations that a user can perform on database objects (such as tables), views enable further data access limitations. A view is a content- or context-dependent subset of one or more tables (or views). A view might be created to allow a sales manager to view only the information in a customer table that is relevant to customers in their own territory. The view can contain selected columns from the base table(s) in which a subset of customer information is contained. A view can also be limited to a subset of the rows in the base table, such as a view of a customer table containing records of customers located in a certain territory.

A More Granular Solution: Row-level Security

Row-level security provides database access control with granularity at the level of individual rows in a table based on user identity. Row-level security is not standard in all databases today. Databases providing access control at an object level (such as a table), but not at the row level, force building of more complex solutions—either in a front-end application or through database views.

One method of providing row-level security is through data labeling technology whereby sensitivity labels are assigned to individual rows in a table. Row level access mediation is then based on the user’s label authorizations.

Suppose the sales department in a company needs to access the enterprise data warehouse to determine top customers, where each row in a table represents a customer. Row-level security can easily be used to restrict a sales representative to see only rows of data (that is, customers) in an assigned territory, while IT can grant the VP of Sales permission to see rows of data (customers) in all territories. Row-level security provides the following benefits:

• Lower Cost of Ownership. Security is built once in the database. Avoiding repetition of the same security implementation in each and every application can result in huge cost savings.

• Elimination of the “Application Security Problem.” Users can no longer bypass security policies embedded in applications. Security policies are associated directly with data and automatically enforced by the database.

• Consolidation of data marts becomes practical —Instead of deploying data marts to physically separate data for security, row-level security enables consolidation of data into a single enterprise data warehouse or a single table while maintaining physical data separation between departments and users.

Additional Security Layers Minimize Risk

In addition to database access control, encryption and auditing help provide a secure environment. Network encryption can ensure that data moving to and from a data warehouse over a network is hidden from unauthorized users. Encryption of stored data fields protects very sensitive data that might otherwise be viewed. Database auditing can deter users from abusing privileges by holding them accountable for their actions. The number of security layers employed depends on the data sensitivity and types of users.

Security evaluations from independent standards such as the U.S. Department of Defense's Trusted Computer Security Evaluation Criteria and the European Union's Information Technology Security Evaluation Criteria, assure that a database product is capable of enabling needed security. The number of security evaluations a database product has received is an indication of the flexibility and completeness offered to meet security requirements.

Summary

An enterprise business intelligence solution or a data warehouse can provide an accurate picture of the business enabling strategic and tactical decision-making. There can be substantial security risks where a single database contains valuable information for an entire company. Leveraging standard security features can help protect your data. Advanced features such as built–in row-level security for strict access control and other layers of security may also be necessary to minimize risk.