Database Security: DB2 vs MySQL and how to make a million ...

I recently blogged about Oracle killing one of its recent acquisitions a company called Virtual Iron. I posed a question if the same fate awaits MySQL. This proved to be a popular post and there was no shortage of comments. One particular comment identified what the person believes is a shortcoming of DB2:

The way DB2 manages users — via the OS — is a large hurdle for people coming from MySql (or any other DBMS for what that matters). DB2 express will never flourish before they provide user management like MySql and MS Sql have.

Brad Perkins replied that he considers this MySQL feature to be the weakest point of MySQL and that he actually appreciates the DB2 way of authenticating users:

I would have to strongly disagree with you on user mgmt and mysql. This is a strong plus for db2 not a detractor. If I have 80 nodes for mysql, which I do, I have to create the user on each instance where as in db2 I can do it once in NIS or LDAP. DB2 is way ahead of the curve in that regard. Also I believe M$ does this too… so this isn’t really that novel of a concept. From a security perspective mysql is a nightmare to manage as users are by default allowed to have no pass. This makes the auditors very nervous indeed. Having done extensive work with both databases I can tell you this is one of MySQL weakest features.

This back-and-forth in the comment stream made me think that there is a need to do some simple comparisons as to how different DBMS handle specific tasks. There is no way I can do a comprehensive treatment comparing all of the DBMS on all areas but I figured that once in a while I could post short articles on narrow topics. Since the conversation in the comment stream was around the differences in user authentication in DB2 and MySQL I figured this would be as good a place to start as any.

First thing first, let’s define what the term authentication really means. To ensure that data managed by the DBMS is protected from the prying eyes of the evil doers all DBMS manage assigning privileges and permissions to the users of the DBMS. However, before you can allow someone to access data or to perform an operation you must ensure that the user is who she claims she is. Authentication is the process of validating user’s identity. We are all familiar with providing user id and password when we log in to different systems and applications. In the simplest authentication scenarios the id+password pair is compared to what is stored in the system and a user is let in if there is a match or access is denied if the provided credentials do not match those stored in the system. Using id+password pair is only one example of user credential. Other examples include biometric information such as fingerprint or a retina scan, voice print, public key certificates like those described by the X.509 standard etc.

As I mentioned before, to ensure that the user is who she claims she is DBMS must compare the credentials provided with those that are known to be properly authenticating the user. Here is the dilemma: “how does the DBMS manage the credentials for the authentication process?”. Well, DBMS are all about managing data and these credentials (e.g. id+password) are just data so why not just store them in the database? Indeed, this is exactly what MySQL, Oracle and many other DBMS do. MySQL has a set of grant tables in a database (schema for DB2 people) called mysql.  Specifically, there are columns in the table called user that contain hostname, user id and an encrypted password for every user account.

mysql> select Host, User, Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *595E8DB4739AB8DC9771B264DF11A40995FD16A2 |
| %         | root | *595E8DB4739AB8DC9771B264DF11A40995FD16A2 |
+-----------+------+-------------------------------------------+

If you describe this table you will also notice that it contains many more columns that store user privileges  and there are columns that deal with x.509 certificates. Keeping account information in the database itself can be very convenient. For example, for an ISV having the application self contained and to be able to easily create and destroy accounts through the application itself is very attractive. You can also use database backups to back up account information and restore it if disaster strikes. However, in a typical enterprise environment, keeping account information in the same database as the application data can be very inconvenient. In a typical large organization, users need to use many applications located on a variety of systems. When each one of these applications and systems demands that a users create individual accounts with unique credentials users get overwhelmed and confused; “what was my id and password for the accounting system?”. Surveys of corporate IT Help Desks indicate that over 80% of the help desk calls are related to user identity issues. This becomes an even bigger headache when you consider the processes that are required to accommodate job changes or employee resignations. Say you had an employee leave the company. You now have to go every single system and application in your company and remove all of the accounts this employee may have had. Do you even know which systems and applications are affected? This is the reason many organizations prefer to use corporate directory systems (LDAP, Windows Active Directory i.e. Kerberos etc.) to manage all user accounts. At IBM, all employees get a single “intranet id” that we use to sign on to pretty much any application that we are authorized to use. I absolutely love it as I now only have to remember a single id and password. Gone are the days of writing up lists of ids and passwords and hiding them in that very secure place … under the keyboard. This issue is not unique to large enterprises. Out on the web, there is a big push behind using single sign-on solutions like OpenID to spare all of us the pain of trying to keep hundreds of accounts on various web sites.

Another common issue associated with individual applications managing accounts is inability to consistently apply organizational security standards. For example, your organization may mandate rules for non-trivial passwords (must be at least 8 characters, can’t repeat, must contain at least 2 digits, can’t have a name of your cat etc.)  and if it does you can bet there will be a security audit that will nail you if any of the accounts don’t comply. MySQL does not provide any facility to help you enforce corporate password rules so you end up coding them in each application. This is not fun especially if you consider the fact that you have to maintain and change this code as the rules change … and they do change. And what about if this application is written by someone else and you don’t have access to source?

These are some of the reasons DB2 and several other DBMS like Microsoft SQL Server try to persuade DBAs not manage accounts in the database. Just like MySQL provides ability to plug in different storage engines, DB2 provides a plug-in architecture for managing user accounts and group membership. DB2 ships with a number of plug-ins and provides a documented open API for creating new plug-ins. This way an administrator can choose to use the security subsystem provided by the operating system (this is the default for DB2) or use corporate LDAP directories or Windows Active Directory (Kerberos). If you don’t mind writing a few lines of code you could go and create a plug-in that will authenticate your users by their retina scan or their iPhone. Say 3 full turns to the right, one full turn back, quarter turn to the right again; like a combination lock. I can see you are already thinking: “this would make a cool iLock app … almost as cool as the iFart which sold over 350000 copies … I can be the next internet millionaire!”. If you want to do something more prosaic, like use the same authentication like MySQL does i.e. keep account information in the database, you could use DB2 security plug-in to do that. Just look at this article by Gene Kligerman and follow his simple explanations.

It was not my intention to provide a complete essay on database security in this post. This is a topic that requires a book. For those interested in DB2 security I recommend Understanding DB2 9 Security. Also take a look at the videos on ChannelDB2.com like this on-line lesson on DB2 security. And as usual, I recommend the free Getting Started with DB2 Express-C book which is available in several languages (chapter 10 deals with security). If you are interested in understanding the basics of MySQL security I recommend Chapter 12 of my all time favorite MySQL book High Performance MySQL: Optimization, Backups, Replication, and More.

Thanks for stopping by. If you enjoyed reading this post, and may want to read more in the future subscribe to this blog and leave a comment.