DB2 Basics: Security
This post is not meant to be a comprehensive coverage of security, but an overview such that those newer to DB2 know what areas they may want to research further.
Users, Authentication, and Authorization
I’ve already covered this in some detail in DB2 Basics: Users, Authentication, and Authorization. In general, keep the permissions that you allow any user or group to the minimum that they need to do their job. This is called the principle of least privilege.
SYS Authorization Groups
The parameters in the DBM cfg that are used to set groups for the system levels of DB2 authorization (SYSADM, SYSCTRL, SYSMAINT, and SYSMON) should never be set to STAFF or a group on the server that contains all users.
When you grant a permission within DB2 to “PUBLIC”, you are saying “Anyone who can authenticate has these permissions”. There should be very few permissions that PUBLIC has. I limit it to package permissions, and ALWAYS revoke connect on a database from PUBLIC.
Access to Administer the DB2 Database
It is amazing to me the multitude of ways that various clients have of restricting or controlling access to the highly privileged IDs that we use to administer DB2.
Access to the instance owning ID is required to safely start and stop DB2. This cannot be scheduled, and is often needed for crashes. That means that I am not comfortable fully relinquishing access to the DB2 instance owner. This has come a long way, though – in the early days of db2pd, it could only be used as the DB2 instance owner. I am comfortable with not using the DB2 instance owner except in an emergency. If I don’t access the DB2 instance owner, there are some problems that I have to solve for – such as what ID runs backups and other maintenance scripts that any DBA on a team may have to get to when sharing work and covering issues on-call. For this reason, most clients allow us to access the DB2 instance owner in some way. Some people prefer logging into the ID directly or su’ing from another ID. Some use a script that both allows su’ing to the instance owner and tracks all commands executed. Some allow sudo su to the id specifically or allow sudo to anything for certain IDs. Even for clients that maintain PCI compliance, a “compensating control” for this type of access can involve logging the commands executed and limiting the people who can do this to a very small subset.
For some applications, the ID that the application uses to connect to the database is very highly privileged, and then the application itself limits what any individual can do. This is an easier approach for the DBA, but it scares me a bit how a mistake at the application level or a failure to do the basics like prevent SQL Injection attacks could really cause problems. Also, using a Trusted Context (described below) may be useful in this situation.
If someone knows or can guess the name of a user, group, table, or most database objects, they have an advantage. Because of this, it makes sense to avoid the defaults. Avoid db2inst1 for the instance name. Avoid the default or frequently used ports of 50000, 50001, 60000, and 60001. Don’t name the groups you use for the SYS levels of authorization the same as the group purpose.
Limiting the ports and IP addresses with firewalls ensures that no one from the public internet manages to pound your database server, even if they cannot get authenticated. This should generally include using non-standard port numbers.
Administering DB2 should be done over a VPN or other secure private connection.
When you have to open up the database server more than you would like, or when you have an application ID with very high privileges, you can use a trusted context to only allow connections using that ID from specified servers. This helps to ensure that developers are not randomly using the ID from elsewhere, and also helps if somehow the password gets out to ensure that the ID can only be used from specified servers. There are other uses than this for a trusted context, too.
PCI-DSS, HIPAA and Other Standards
Compliance of any piece of software is dependent on how that software is implemented. The vendor will say that the software can be compliant with a wide range of standards, but it ultimately depends on how the software is used whether compliance is maintained. Even the best DBAs may not implement things that are required for PCI-DSS or HIPAA or other standards by default, because they often cause inconvenience. If you’re working with a consultant, standards compliance needs to be brought up early and often to make sure that the required standards are being met.
This section on auditing refers to tracking users who access one or more objects in specified ways. DB2 has facilities for auditing, and they are so much better than they used to be. But they also have overhead. Enabling auditing is a complicated process to understand what is and is not tracked, to store generated data securely and to ensure that someone is actually reviewing generated data. DB2 is fairly granular in what it does and does not track. In recent versions, you can enable it on a table level to reduce the data that you’re collecting and therefore the performance impact. The absolute minimum of data should be collected when auditing.
To enhance performance, DB2 has an audit buffer. If your security standard will support it, make use of this buffer. Some security standards will not allow this, with the idea that someone could do something nefarious, and then cause a crash of DB2 or the server and then the data in the audit buffer would be lost, while the impact of the nefarious act likely would not be.
SQL Injection is where someone places what is intended to be SQL into a field on a form. An uninformed application then passes that to the database and it wreaks some sort of havoc on the database. I’ve seen several attempts at this in the real world, and all of them were caught at an application or other level before they got to the database. The last was targeted at Postgres, and had no effect on the DB2 database, even if the application hadn’t caught it and prevented it from getting anywhere near the database. In that case, they were trying to invoke a sleep function that does not exist in DB2. The wikipedia entry on SQL Injection is a good place to read more about this. This is one of my favorite comics, and it relates to SQL injection:
Encryption of Data in Transit
Sometimes the application will handle encryption of sensitive data and only pass and store encrypted data for the most sensitive data. Using the dbm cfg AUTHENTICATION parameter setting of DATA_ENCRYPT will do this at the DB2 level. Some applications do not support DATA_ENCRYPT, so test it thoroughly before implementing it.
Encryption of Data at Rest
Data at rest can be encrypted at any of several levels. It can be encrypted by the application before being stored in the database, DB2’s Native Encryption feature can be used to encrypt it, or disk-level data encryption can be used. All of these have performance overhead.
RCAC and LBAC
RCAC is “Row and Column Access Control” while LBAC is “Label-Based Access Control”. Traditional DB2 security means that a user either has access to the data in a table or she doesn’t – the only granularity is which of the 4 permissions – select, update, insert, delete (sometimes abbreviated suid) – the user has. With either RCAC or LBAC, the permissions can be done with more granularity. With LBAC, you define certain labels such as “Secret” “Classified”, etc, and assign it to the data and only users with that same label have access to that data. With RCAC, you can define which columns on a table a user or group has access to, masking the other columns, or define a condition for which rows in a table a user or group can see. Either of these carries overhead with it. The administration of the permissions can also be quite complex, and you should plan ongoing DBA time to spend on it if you are working on this. Check out my more detailed blog entries on DB2 Fine-Grained and Row Access Control (FGAC/RCAC) or LBAC – Label Based Access Control for more details and considerations.
No matter what your security goals or concerns, you should periodically review your system for compliance. Even the best of us misses something, or forgets something, or has multiple people administering a system. It is critical to conduct periodic security reviews, and is best if you can have another skilled DBA conduct a security review to find your own blind spots.