DB2/LUW Security – Inside the Perimeter

28 August 2010


For DB2 on Linux, UNIX and Windows (DB2/LUW) and to some extent other databases, IBM has a complete set of tooling to allow you to ensure that your data assets are protected.  It is no longer enough to guard your perimeter – these days you have to expect that your firewall will be breached sometime.  Using the right combination of tools that I highlight in this article you can not only keep your data safe, but you can also easily provide standardized reports for a number of government compliance mandates should they be needed.  I provide a high level summary of each feature and a link for more detailed information for ones that seem promising to you.


New Authorities

As with any database, it is important to give each userid only the minimum authority to access database objects and data that is really needed.  DB2 now has a new SECADM authorization that allows users with this privilege to grant and revoke authorities to users and groups.  Users with this authority do not need to be granted permission to view any data in the database and SECADM can be revoked from the DBADM and SYSADM authorities.  This means that you can separate the duties of database and system administration from security administration.  This will make certain auditors very happy. 


Further, you can grant database and system administration rights to users without granting the privilege to select or update data.  Some security regulations prohibit the DBAs from being about to see your sensitive production data.  There are also several other new authorities that can be used for separation of duties. 


Trusted Context

In many 3-tier applications, the the middle-tier applications will all connect to the database using one generic user id.  This means that the database has no visibility to who is actually is issuing queries.  With the DB2 Trusted Context, the actual id of the end user is passed to the database for each transaction, with security for each transaction being evaluated for the end-user or just tracked for auditing.  This is done by sending additional information with the queries.  With newer versions of WebSphere Application Server and other web servers, this trusted context can be sent without modifying the application.



In DB2 you can enable DB2 auditing to track who is doing what in your system.  The audit records are put into audit log files on your database.  You can turn on various levels of auditing that provide the right tradeoff between collecting enough information to satisfy your requirements and the amount of audit data that you have to store.  DB2 also has built-in facilities to allow you to report from these logs and to put the audit information into database tables to do custom reporting. 



Our Infosphere Gaurdium product takes auditing a step further by removing the configuration of auditing (including stopping and starting auditing) and the management of the audit log files from the database administrator.  This is necessary to pass certain government and industry standard audits.  Guardium works by plugging light-weight agents into the operating system of the monitored database servers that report back to a Guardium appliance.  The Guardium appliance is a small, special-purpose server that collects the audit information in a relational database.  Guardium comes with a number of auditing reports that you can use out of the box, some of which are in formats that external auditors are looking for already.  You can also create your own customized reports.  In addition to reporting, Guardium also allows you to set policies so that you can be alerted immediately when someone is doing something out of the ordinary.  It can also be configured to prevent access that is now allowed.   Guardium not only works with DB2/LUW, DB2/zOS and Informix, but it also works with several non-IBM databases like Oracle and SQL Server. 


Optim Data Privacy

You often need a copy of production data or a subset of it in development for adequate testing, but it contains very sensitive information about customers, employees, or your business.  However, auditors will not allow developers and testers to see production data and testing becomes impossible if you have production security controls in place in non-production environments.  The answer to this quandary is Optim Data Privacy with the Optim Test Data Management tool that allows you to create secure, relevant subsets of data for testing.  Data Privacy allows you to create test databases where the sensitive data is changed and unusable for sneaky purposes, but in context for your testing.  For example, it has a list of thousands of real first and last names that it uses to replace the names from production.  It also as many built in functions to mask things like social security numbers and credit card numbers that have logic built-in.  These numbers are still in context, but are different than the source database.  For example, a Visa card number will still be Visa, but since you used Optim Data Privacy to change the name, address and credit card number, the information is useless to rascals.  Data Privacy works with most vendors' relational databases and not just DB2.



Label Based Access Control allows you to grant access to data by row, column or both and authorizes users to see data by a hierarchy, group membership or various other categories.  LBAC is available for DB2/LUW and Informix.


Static SQL

DB2 has had a feature since it was released that few other database vendors have implemented even to this day.  That feature is the use of Static SQL.  With static SQL you bind sets  of queries into the database at compile time.  The objects created in the database for these sets of SQL are called a packages.  Users who run certain applications are granted execute authority on those applications and do NOT need to be granted any SELECT, UPDATE, etc authorities on individual tables.  Therefore, any stray queries issued by hackers or employees will not be able to run.  No one can access any data except through the static packages.  This can make your database very secure and prevent SQL Injection hacks.


Optim pureQuery Runtime

For Java applications it is often difficult to use static SQL, but Optim pureQuery Runtime solves the SQL Injection problem without changing your JDBC application that access DB2.  Using just pureQuery on your application server, you can set a flag that tells pureQuery to capture one copy of each unique query that is submitted to DB2 through the IBM type-4 JCC (JDBC) driver.  Once all SQL has been captured you can set another flag for pureQuery so that it will block any SQL submitted through the JDBC driver that is not in the file of previously captured SQL.  Again this prevents SQL Injection because new SQL can't be submitted until you put it into the capture file.  Further you can bind the file of captured SQL to the DB2 database and and then set a flag in pureQuery to substitute dynamic SQL from the application into calls to the static package in DB2.  You can then revoke all privileges from all users against tables and views in the database so that access is only allowed through the static packages.  This tool works with DB2 on all platforms.  If you have the source code, you can use the Optim Development Studio to just gather the SQL out of your Java Applications instead of capturing executing SQL. 


Database Encryption Expert

With Database Encryption Expert (DEE)  you can protect DB2 or Informix database data on your physical disk, backups on tape, and export files that need to be transported.  Yours truly, db2Dean, and Mrs. Db2Dean got a free year of credit monitoring because an un-encrypted database backup got stolen on the way to the offsite vault from a local health care provider.  This sort of thing happens all of the time, but it doesn't have to.   DEE plugs into the file system of the server where it is running and encrypts data being written to that file system.  This means that the database does does not have to be configured for DEE or even know it exists.  DEE knows about the database though, and will encrypt the data being extracted for backups or exports.  However, data being queried through the database engine is automatically decrypted without the user knowing anything about the encryption.  Users with the right authorities can view the extracted data or use it to recovery the database, but others can't.




I hope this has been a useful list.  There are several other features in DB2 that can be used to improve security, but the list is too long to enumerate here.  You can also see our DB2/LUW Security Best Practices



HOME | Search