DB2 Basics: Users, Authentication, and Authorization

This is clearly a broad topic, and this post is intended to serve as an introduction.

Authentication

The first step in accessing a db2 database is Authentication. Authentication is what tells DB2 that you are who you say you are. Simply put, DB2 does not do authentication. It relies on some other facility to perform authentication.

Traditional Authentication

Traditionally, DB2 has left authentication up to the OS. This differs from Oracle and MYSQL and many other relational databases, which at least offer the option of storing the users in the database itself. In some ways, this method simplifies things. It works out of the box, and all you have to do is create a user at the OS level and then grant them privileges at the database level and they’re ready to go.

This method requires a bit of an odd twist, though – in DB2, you can grant privileges to users that do not exist at the OS level. When you think about it, this is almost required, as if you’re backing up and restoring a database between servers, you would otherwise have to have some kind of verification that all the users with permissions in the database exist at the OS level – a cumbersome requirement.

This is also a potential security loophole – if you have some user who has permissions on another system, and you backup and restore a database between systems, and the user id does not exist on the target system, all you have to do to get permissions on the target system is to request the ID on that system. For this reason, it is a security best practice in most situations to make sure that you don’t leave permissions “hanging” out there – granted to IDs that do not exist.

Transparent LDAP

I have seen more and more of this method in the last few years. Maintaining IDs across systems can be difficult and time consuming. So if you have LDAP for the purposes of easier logins to your systems anyway, then allowing your users to use their ldap ids for authentication and authorization with db2 can be a good thing.

You can only use transparent LDAP with the following operating systems:

• AIX
• HP-UX
• Linux
• Solaris

I believe DB2 on Windows has been more supportive of LDAP integration in the form of Active Directory, though DB2 on Windows in not my specialty, and I haven’t had a client use it for a couple of years.

Other Authentication Methods

I give the above two methods the most detail because they’re the ones I have used the most. DB2 comes with plugins for Kerberos and LDAP(different method than the transparent ldap above). Plugins also allow you to even code your own method, with the details required of plugins in the DB2 Information Center.

DB2’s Hard to Find User ID Restrictions

If you really look in the info center, you can find this information, but it isn’t all in one easy to find place. DB2 user ids and groups must be:

• 8 characters or less
• All lower case – no mixed case
• Contain no special characters, including ‘.’

If a user id does not meet these standards, DB2 is not terribly helpful. Usually I love the detail and specificity of DB2 error messages, but in this case, all you’ll get is a very standard looking permissions denial:

So if you’re sure you’ve granted permissions and are still getting this error, double check to make sure the ID matches DB2’s naming standards.

My company’s naming standards don’t match up with this at all. But fortuneately, LDAP will let you set up an alias for each id that DOES meet db2’s naming standards. This makes it so that even if a user logs in with ‘John.Smith’, when they go to connect to the database, LDAP will pass ‘jsmith’ to DB2.

Authorization

Authorization is what matters once a user has been authenticated. Authorization is the set of privileges that tells DB2 what you have the right to do. If you have no privileges whatsoever, you won’t be able to connect to the database, even if authentication was correct.

DB2 manages authorizations at three different levels:

1. Instance
2. Database
3. Object

9.7 (and 9.1) changed some of the ways that DB2 handles permissions. Each of these versions watered down exactly what was included with DBADM, and offered some separation. With 9.7 they separated out access to the data, so that someone with DBADM doesn’t necessarily have access to the data in a database.

Because of the changes in DB2 9.7, it is easiest to represent the permissions in multiple diagrams. First, the Permissions at the instance level for SYSADM, SYSCTRL, SYSMAINT, and SYSMON:

Now, the database level permissions:

Watch Out When Revoking DBADM

One interesting thing about the changes to DBADM in db2 9.7 is that IBM made it so that when you grant dbadm, you get basically the same effect as in previous versions. This is accomplished through DB2 also granting ACCESSCTRL and DATAACCESS whenever DBADM is granted. This seems like a great idea until you try to revoke it.

Granting DBADM:

Revoking DBADM:

Notice that on revoke, you must not only revoke DBADM, but also ACCESSCTRL and DATAACCESS to get back to the level of permissions the ID had before being granted DBADM.

Watch Out When Restoring From One Server to Another

The other thing you have to watch out for now that SYSADM doesn’t automatically get DBADM is when you restore from one server to another with different instance ids. I’m used to depending on the same instance owning IDs, but there are some situations even on the clients I support where they differ. And when they differ, we have to use the registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES. This variable