Keeping data confidential

See correction at end of article

Many companies that were doing business in a relatively carefree fashion a year ago now find their customers insisting on high levels of accountability. Of course, health-care organizations have been under the watchful eye of the Joint Commission on Accreditation of Healthcare Organizations (JCAHO) and the Health Insurance Portability and Accountability Act (HIPAA) for quite some time. But public companies outside the health-care industry have woken up to find themselves facing Sarbanes-Oxley and other mandates.

Each of these regulations requires enterprises to protect and control the flow of information. Strong, identity-based access-control systems accomplish this for applications and resources, but when it comes to databases -- against which employees may have become accustomed to performing ad-hoc queries -- the situation can be more difficult. In those cases, one way to protect data from prying eyes is to obscure it.

Obscuring data for compliance -- at its most basic level -- just means keeping people from seeing what they're not supposed to see. There are two parts to obscuring data: security and privacy. Threats range from analysts clicking on the wrong database table and accidentally seeing something they shouldn't, to a disgruntled employee looking to get back at the company. So, whether you lock everyone out of your database or just arrange the data so that no personal information can be viewed by nonessential personnel, there are time-tested techniques for protecting your company and your clients against privacy invasion.

There are many ways to obscure data from wandering eyes -- and sometimes DBAs just have to get creative if they are going to achieve their goals. The methods used range in difficulty based not only on the level of sensitivity of the data but also on the skill of the DBA, the company's level of commitment, and the amount of time afforded for the project.

Basic Techniques

Stored procedures are the most flexible tool DBAs have for obscuring data. Stored procedures are snippets of SQL code that have been compiled and saved in the database itself, resulting in better performance than that provided by noncompiled SQL. Stored procedures can also call other procedures, views, and functions and can perform any type of math available to the SQL language. Using well-designed procedures, a DBA can alter data in almost any way necessary, including encrypting it.

Views, another way to obscure data, are a logical representation of data that can join several tables at a time while maintaining a good level of security. Sensitive columns can be filtered out -- or even modified -- using simple conditional statements. Because they are less flexible than stored procedures and can't make major modifications to data, however, views are only useful for protecting against minor threats. Nonetheless, they are still useful for obscuring data as well as the underlying schema.

UDFs (user defined functions) are the next level down in data obscurity. UDFs are similar to stored procedures that can be applied to individual columns. UDFs are implemented at the database level but can be called directly from queries -- unlike stored procedures, which replace the query. Because they are more granular than stored procedures, UDFs can actually be more flexible.