IBM DB2 Database Security Model

The IBM DB2 database management system begins its development in the distant 70s and now occupies a strong position in the market of corporate DBMS, meeting the high requirements for performance, reliability, security and scalability. In the private sector, the DB2 system is not widely used, despite the availability of a free version of IBM DB2 Express. Perhaps it is because of this that there are not many articles about tuning and using DB2 on the Internet.

The DB2 security model has broad functionality and allows you to protect data from both external influences and to differentiate access rights for internal users by means of the DBMS itself.

However, it is difficult for an unprepared user to understand all this diversity from scratch, so some important aspects will be discussed in this article.

')

Point of entry

The entry point to DB2 looks like this: DBMS -> instance, which can be tied to a specific port -> name of a specific database. Security settings can be changed both in a specific instance and in a specific database.

Authentication

Authentication is the primary security mechanism that is used when you try to connect to a DB2 server. Authentication verifies the correctness of the credentials provided. The main feature in DB2 is that user authentication is performed only by external plugins. Internal users, unlike Oracle or MS SQL Server, do not exist here. Even the create user function that is in the IBM Data Studio program does not actually create the user, but assigns the specified user the privilege to connect to the database.

There are several authentication options, the desired option is governed by the AUTHENTICATION parameter in the database manager. The value of this parameter affects where clients will be authenticated (on the server side or on the client side) and whether data will be transmitted in encrypted form (values ​​ending in _ENCRYPT). The supported values ​​for this parameter are available at the following address.

You can view the database manager configuration by querying the sysibmadm.dbmcfg table, but you need to have access to any of the databases, which is not always possible. If you have local access to the server, you can open a command line processor (db2 or db2.exe on Windows), connect to the instance and execute the following commands:

db2 => attach to db2inst1
db2 => get database manager configuration

The default value for the AUTHENTICATION parameter is SERVER. Verification of the provided user credentials is made on the server side by means of the operating system, but all the data is transmitted in the clear and can be intercepted by an attacker.

Consider how the intercepted information looks in Wireshark.


The login and password transmitted from the client are visible in the package when viewing EBCDIC.

If the authentication type is changed to SERVER_ENCRYPT, the login and password will be transmitted in encrypted form and verified on the server side.

The value changes as follows:

db2 => attach to db2inst1
db2 => update database manager configuration using authentication server_encrypt
db2 => db2stop force
db2 => db2start

The authentication package will look like this:


However, the text of the requests and the result will still be transmitted in the clear.

Package request in Wireshark:


The packet with the answer in Wireshark:


If the AUTHENTICATION parameter is set to DATA_ENCRYPT, the user credentials are encrypted, as well as information transmitted between the client and the server.

The value changes as in the above example:

db2 => attach to db2inst1
db2 => update database manager configuration using authentication data_encrypt
db2 => db2stop force
db2 => db2start

After that, the transmitted data will also be encrypted:


Also, you need to pay attention to the type of authentication CLIENT. With this type of authentication, it is assumed that there is a secure communication channel between the client and the server, and if the user has access to the client, he can access the server without checking the correctness of the credentials. That is, authentication as such occurs on the client side, server-side checks are not performed. Even if the user who connects to the server does not have access rights, he still gets all the privileges that are assigned to the PUBLIC group. Therefore, you should not use this type of authentication, it will give attackers the opportunity to gain access to the server with little effort.

If, for some reason, this type of authentication is necessary, then you need to consider that there are two additional parameters that ultimately affect how the user credentials will be checked. These are the trust_allclnts parameter, with which you can specify which clients are considered trusted, and the trust_clntauth parameter, which determines where to check the login and password if they were transferred during the connection. Both of these parameters affect authentication only if the AUTHENTICATION parameter is set to CLIENT.

If authentication is successful, the user ID is associated with the DB2 ID. Typically, the ID is the same as the user name, but it uses uppercase characters.

Authorization

The authorization process checks if the user has the necessary rights for the requested actions. There are authorities (authorities) of a copy of a DBMS and a database.

Authority level of a specific instance is written in the database manager configuration. These powers are as follows:

• SYSADM (system administrator authority)
• SYSCTRL (system control authority)
• SYSMAINT (system maintenance authority)
• SYSMON (system monitoring authority)

These privileges are specified by specifying the group in which the user will be included. To do this, use the following parameters of the dbmcfg file (according to the above permissions):

• SYSADM_GROUP
• SYSCTRL_GROUP
• SYSMAINT_GROUP
• SYSMON_GROUP

It is easy to get a list of users that belong to a group using DB2 tools, you need to do this in the operating system itself or analyze which groups include a specific user (see the query in “useful queries”).

When setting up DB2, it is imperative that you check the list of users that have been granted SYSADM authority. This privilege allows you to manage all database objects.

The authority of a specific database can be viewed in the SYSCAT.DBAUTH view . Attention is drawn to the privilege of CONNECTAUTH, which determines whether the user will have access to the database or not, and the privilege NOFENCEAUTH, which is responsible for creating non-isolated procedures and functions. Such procedures are performed in the address space of the database and, in case of an error, may violate the integrity of the database and the tables in it.

Privilege

DB2 privileges can be granted to various objects. Privileges for accessing tables can be viewed in the SYSCAT.TABAUTH view. Data on the type of privilege issued is stored in separate columns, depending on the privilege itself (SELECTAUTH, DELETEAUTH, etc.). When granting a privilege using the GRANT command for the REFERENCES and UPDATE privileges, you can also specify the names of the columns to which these privileges will apply. Information on this can be viewed in the SYSCAT.COLAUTH view .
The privileges of the subroutines (functions, procedures, and methods) can be found in the SYSCAT.ROUTINEAUTH view . Everything is not quite trivial here, depending on the SPECIFICNAME and TYPENAME fields, privileges can be granted to all subprograms of a given scheme.

Users, groups, roles

All database permissions and various privileges can be given to users, groups or roles. The existence of users, groups and user membership in groups is regulated outside the database itself. In this regard, it is desirable to take into account certain recommendations and know some subtleties in the issuance of powers and privileges. It is not recommended to issue database privileges and privileges, in particular database connectivity (CONNECTAUTH), to groups. Privileges should be granted to specific users or roles that need it. Role support has been introduced in DB2 since version 9.5. Role membership management is done within the database itself.

Also, in DB2, there is a built-in role PUBLIC. The database user does not need to provide the PUBLIC role: it is impossible to revoke the PUBLIC role from the user. When a privilege is granted to the PUBLIC role, the privilege is actually granted to all users of the database. You should not issue any authority to the PUBLIC role database. Privileges to tables and views should be issued with extreme caution, only for viewing and without the possibility of reassignment (WITH GRANT OPTION).

Due to the nature of authentication when issuing privileges, the existence of a user or group in the system is not checked. As a result, authentication users may appear in the system without being tied to real users of the system. You can find such users using the following SQL query:

SELECT authid FROM sysibmadm.authorizationids WHERE authidtype = 'U' AND authid NOT IN (SELECT username FROM TABLE(sysfun.USERS()) AS W)

To search for such groups, a similar query is used, but the query indicates that it is not necessary to output the data on PUBLIC:

SELECT authid FROM sysibmadm.authorizationids WHERE authidtype = 'G' AND authid NOT IN (SELECT groupname FROM TABLE(sysfun.groups()) AS W) AND authid != 'PUBLIC'

Lbac

DB2 has a powerful mechanism for delimiting access to data in tables based on labels (Label-based access control). The mechanism allows you to set security labels on specific rows or columns in such a way that a user who does not have access to protected data will not even be aware of their existence. It makes no sense to talk in detail about the methods of implementing LBAC, since the manufacturer has a tutorial on this topic:

www.ibm.com/developerworks/ru/edu/dm0605wong/index.html

Auto Scan Tools

When setting up security for an IBM DB2 server, an important point is to use any security scanners (for example, NGS SQuirreL for DB2, MaxPatrol, etc.). Scanners will clearly indicate vulnerabilities in settings that you might have missed, or will display information in a form convenient for analysis:
NGS SQuirreL for DB2:

MaxPatrol:

Useful queries and commands

Get database manager settings:
select name, value from sysibmadm.dbmcfg
or
db2 => get dbm cfg

Change the database manager parameter:
db2 => update database manager configuration using
:
db2 => db2stop force
db2 => db2start

:
select name, value from sysibmadm.dbcfg

db2 => get db cfg for

:
select username from table(sysfun.USERS()) AS t

:
select groupname from table(sysfun.GROUPS()) AS t

(, , ):
select AUTHID, AUTHIDTYPE from sysibmadm.AUTHORIZATIONIDS

:
select current server from sysibm.sysdummy1

:
select user from sysibm.sysdummy1

, :
select GROUPNAME from table(sysfun.groups_for_user('<username>')) as t

:
$ db2ls

:
$ db2ilist

:
select * from tabname fetch first 5 rows only