IBM DB2 database management system was developed back in the 70s and has taken a foothold on the market of industrial DBMSs, meeting the highest requirements to its performance, reliability, security and scalability. However, in the private sector, the system hasn’t gained that wide acceptance even with its free IBM DB2 Express version. This might be the reason, why there are rather few articles on the DB2 use and configuration on the Internet.
DB2 has a wide range of features that allow both protecting data from external actions and distributing permissions for the internal users relying only on the database’s own tools.
Yet, a novice can find it difficult to get a sense of all these various functions. This article is meant to give you a closer look at some of the major aspects of the system.
DB2 entry point has the following address: DBMS -> an instance that can be linked to a specific port -> the name of a specific database. The security configuration can be changed both for a specific instance and for a specific database.
Authentication is a primary protection mechanism that is triggered every time you are connecting to the DB2 server. Authentication validates the credentials you enter. DB2’s authentication is peculiar: it is carried out only by external plug-ins. Unlike Oracle and MS SQL Server, there are no internal users in DB2. Even the CREATE USER function, which is provided in IBM Data Studio, does not create a user as such but assigns a user with a privilege to connect to the database.
There are several authentication types in the system. The type you need is controlled by the AUTHENTICATION parameter of the database manager. The value of the parameter appoints a site for authentication (at the server or at the client) and determines whether the data should be encrypted (values ended with _ENCRYPT).
The manager configuration can be viewed by sending a request to the sysibmadm.dbmcfg table, provided that you have access permissions for any database, which is not always possible. If you have a local access to the server, you can open the command line processor (db2 or db2.exe in Windows OS), connect to the instance and execute the following commands:
db2 => attach to db2inst1
db2 => get database manager configuration
The default value for AUTHENTICATION is SERVER. User names and passwords are validated at the server by the operating system. However, all data are transferred in plain text and can be intercepted by an attacker.
If the authentication type is changed to SERVER_ENCRYPT, the user name and password are encrypted and then validated at the server side.
However, the request text and the results will be still transmitted in plain text.
If AUTHENTICATION value is DATA_ENCRYPT, it ensures encryption not only for user data, but for the data transferred between the client and the server as well.
A couple of words should be said about the CLIENT authentication type. This authentication type is believed to provide a secure connection channel between the client and the server, so if a user gets access to the client, he/she can access the server as well without credential validation. In other words, the authentication as such is always carried out at the client side, not the server one. Even if a user, who is trying to connect to a server, has no access rights, he/she still receives all privileges assigned to the PUBLIC group. So, it’s better not to use this authentication type because it can provide an attacker with an effortless access to the server.
If the authentication is successful, the user ID is matched to a DB2 identifier. Usually, the identifier coincides with the user name but uses upper case symbols.
Authorization validates whether the user has the rights (authorities) to perform the actions he/she is trying to perform. In the system, there are authorities for DBMS and for database instances.
Instance-level authorities are assigned in the DB manager configurations. These are the following authorities:
- SYSADM (system administrator authorities)
- SYSCTRL (authorities for system control)
- SYSMAINT (authorities for system maintenance)
- SYSMON (authorities for system monitoring)
These authorities are set by specifying the group that will be assigned for the user. To do so, use the following parameters of the dbmcfg file (respective to the above authorities):
There is no easy way to get the list of users that belong to a certain group by means of the DB2 tools. This can be done either by means of the operating system itself, or by analyzing the groups to which a specific user belongs (for the request see ‘useful requests’).
When configuring DB2, it is essential to check the list of users with the SYSADM authority. This authority allows controlling all database objects.
Authorities of a specific database can be viewed at SYSCAT.DBAUTH. Pay a special attention to the following two authorities: CONNECTAUTH, which determines whether the user will be granted access to the database, and NOFENCEAUTH, which is responsible for creating not-fenced functions and procedures. Such procedures are executed in the address space of the database. If error occurs, they can violate the integrity of the database and its tables.
DB2 can grant privileges on various objects. Privileges on tables can be viewed at SYSCAT.TABAUTH. Data on the type of the granted privilege are stored in separate columns depending on the privilege itself (SELECTAUTH, DELETEAUTH, etc.). When granting the REFERENCES and UPDATE privileges with the GRANT command, you can specify names for the columns to which the privileges apply. For more information, see SYSCAT.COLAUTH.
Privileges on routines (functions, procedures and methods) can be viewed at SYSCAT.ROUTINEAUTH. This one is a bit unusual because, depending on the SPECIFICNAME and TYPENAME value, privileges can be granted on all routines in a particular schema.
Users, Groups, Roles
All database authorities and privileges can be granted to users, groups and roles. Users, groups and group membership are controlled outside the database. In this connection, it would be useful to consider some recommendations and be aware of some peculiarities of the granting procedure. It’s not advisable to grant database privileges and authorities, in particular, the database connection ones (CONNECTAUTH), to groups. Privileges should be granted only to those users or roles which require the privileges for particular purposes. Roles are supported in DB2 9.5 and later. Role membership is contr