DB2/LUW Security Tools
28 October 2011
As you know there are many threats to the security of your database data. Fortunately, DB2 provides many ways for you to protect your data. Threats include well known avenues such as unauthorized access to the database or files with data in them and stolen backups, as well newer threats such as mining production data from test databases and internal authorized users using data for roguish purposes. DB2 provides several tools to combat these threats that I will list and briefly describe adding links for further reading.
No matter which IBM Data server you use, I highly recommend that you review our IBM Data Server Security best practice guide and our 12 DB2 Security Best Practices. My article summarizes many aspects of security and briefly describes tools related to those security aspects along with links to further reading, but the best practice guides will help you truly understand overall topic areas.
DB2 has long provided a number of methods for encrypting communication between clients and the database server. However, we now recommend the use of SSL (Secure Sockets Layer) and its successor Transport Layer Security (TLS) for this purpose. SSL has become ubiquitous in the world of Internet communications encryption so using it allows for more standardization of data communications and works very well too. To use SSL or TLS there are some configuration steps that must be done on the server as well as the client. You can read more about this on the DB2 Info Center SSL Page and the DB2 SSL Article on Developerworks. There is no additional cost to use SSL. Older methods such as SERVER_ENCRYPT are still supported, however.
Often much thought is given to how to control access to the data through the database server, but little thought is give to the database data files used by the database server to hold information or to extracted files like backups, log files, exports, copies of production in test databases, etc. Data in these various files can be accessed by hackers who gain access behind the firewall or others who just steal media containing these files such as tapes, flash drives or employee laptops. To cover all of these bases a number of tools exist to help you.
IBM InfoSphere Guardium Encryption Expert is a tool that you can purchase that will encrypt all of your data at rest and manages your encryption keys as well. The DB2 engine is not altered at all. It continues to read and write data to its files normally with Encryption Expert encrypting and decrypting data as it moves into and out of the underlying files. The DB2 engine is a user who has access to the files. Encryption Expert will not decrypt the data for any user who has not been given explicit access to those files. So whether an unauthorized user they hacked his way into the ability to access the files or even stole media containing the database files he will not be able to make sense of the data because it is encrypted. This even applies to data extracted from the database into a protected file system on the database server using the backup utility, SQL or the export utility. Users with access to the file systems will be able to read those files as normal, but those without access will only see encrypted garbage.
DB2 Built-in Encryption Functions
DB2 provides encrypt and decrypt functions to encrypt/decrypt individual columns as they are written or read as part of the built-in functions that come with the database. These functions are good if you only have a few sensitive columns that you need to encrypt. One disadvantage of the encryption functions is that you have to keep track of the encryption keys yourself and possibly include them in every application that accesses the encrypted columns.
Data in Test
I know that you would never do this, but I’m sure that you’ve heard about people who create test databases by just copying data from production. As you know the problem here is that now all the developers, testers, contractors and all their rascally friends now have easy access to the data that you work so hard to secure in production. InfoSphere Optim Test Data Management with the Data Privacy Option can help you have the best of both worlds by making it easy to copy data from production while de-identifying all of your sensitive data as it copies it. It does this masking while keeping data in context and not just scrambling it. It will also make good subsets of your data so that test does not end up becoming bigger than production. So when your test data is created using this method, it doesn’t really matter who sees the data – you will not even care if the developer with a subset of all production data on his laptop looses it because the data is of no value to anyone else!
We spend a lot of time securing our data to make it harder for bad guys to see it, but if we remove data from production databases that is not needed for day-to-day needs than we can make it impossible for them to access that data. That is where InfoSphere Optim Data Growth Solution comes in for archiving data from your relational databases. This tool makes it easy to define what should be archived from your relational databases and added to your library of archived data on a regular basis. Better yet, it provides tools to allow you to access that data using SQL from your favorite report writer or custom application.
DB2 provides a robust set of built in commands for controlling object authorizations. This is the basic foundation of controlling access to tables and other objects in all relational databases. You can read more about how grants, objects and groups work in the DB2 in article, Understanding How Authorities and Privileges are Implemented. Also don’t forget that when creating a database you can specify the RESTRICTIVE option so that DB2 does not grant any privileges to PUBLIC. Another important aspect of object authorization is Object Ownership.
The DB2 Label Based Access Control (LBAC) feature allows you to do fine grained access control and grant access to specific columns and rows within a table. It also allows you to create groups and hierarchies of users who can access these rows and columns – think Top Secret, Secret, Confidential, etc.
Static SQL and pureQuery
One way to avoid the headaches of granting specific authorities on the many database objects to specific users or groups is to grant no authorities on any tables or views. This is done through the use of Static SQL where packages of SQL are bound to the database and applications reference those packages instead of issuing individual dynamic SQL statements. This can also improve performance of the SQL. The Optim pureQuery Runtime tool can be used to easily convert dynamic SQL into static SQL without changing the application. When only static SQL is allowed from the application servers then SQL Injection is thwarted because queries that have not already been bound can not be used because there are no authorities to look at individual tables with new “hacker-created” SQL.
Authentication refers to who you allow to connect to your database. Since DB2 is a database and not a user management system, when it gets a user id and password as part of a connection request it hands them to the security system and asks if they are a valid login and what groups are associated with the user id. Authorities are then assigned based on what has been granted to the user id or groups to which it belongs. By default DB2 hands the user id and password off to the Operating System (OS) to authenticate the user. If the OS has been configured to use a centralized authorization like LDAP then that is how the authentication is done. DB2 also allows you to interface directly with security systems instead of the OS by using Security Plug-ins. Along with LDAP, Kerberos Authentication is one of the most widely used plug-ins. Think about it. User id and password management is one of the most important aspects of security. Do you just want to hope that your various database management systems with different patch levels scattered throughout your organization are good at securing passwords as your hardened security system is?
In 3 tier systems where application servers connect to database servers using a generic user id should use Trusted Context. The generic user id often has too much authority, because every user transaction has the same high authority and there is also a lack of accountability because there is no way of tracking who is doing what to the database with that id. Trusted Context allows the id of the end user to be passed to the database where the authorities of that user id are used instead of the generic application server user. Application servers like WebSphere Application Server can pass the trusted context to the database without modifying the applications.
In this day and age it is often important to see who is connecting to your database (or unsuccessfully trying to many times) and what they are doing once in there especially as far as changing objects or updating data. The free DB2 Audit Facility can capture the level of detail that you wish and provides several functions to easily report that data such as loading the audit data into database tables. This is a great tool for DBA’s in situations where someone is changing things, but no one seems to know who.
The DB2 Audit Facility is a great tool, but it does not do everything that some organizations need. It can not disconnect a user doing bad things, and it can not prevent a highly privileged user such as a DBA from covering his tracks if he does something naughty. It can also have an undesirably large performance impact on database servers that are tight on resources. The answer to this situation is to use our InfoSphere Guardium auditing and security product. Guardium is an appliance that installs in your network and places a software probe in the OS of your database servers. The probes report back to the appliance, so very few resources are used on the database server. This tool can not only observe and report what is going on in the system as far as connections, updates and who is reading what; it can even send alerts as these things are happening. Plus, if you define a policy to do so, it will disconnect users doing things outside of their defined processes. Not only can particular rules about specific actions be defined but patterns can be observed and reports generated about abnormal patterns of activity. Because it is plugged into the OS of the database servers, privileged users can’t manipulate it making this a great tool for complying with regulations like HIPAA and SOX.
I hope that you found this summary of security tools useful and that now you have a fairly comprehensive list of what is available. However, there are probably some aspects that I did not mention so please add any not listed as a comment on my db2Dean Facebook Page.