IBM DB2 Database Security Model
The IBM DB2 database management system began its development in the distant 70s and now occupies a strong position in the corporate DBMS market, meeting the high requirements for performance, reliability, security and scalability. In the private sector, the DB2 system is not widespread, despite the availability of a free version of IBM DB2 Express. Perhaps it is because of this that there are not many articles on the Internet about setting up and using DB2.
The DB2 security model has extensive functionality and allows you to protect data from both external influences and to differentiate access rights for internal users using 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 is as follows: DBMS -> instance, which can be bound 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 applies when you try to connect to a DB2 server. Authentication verifies that the credentials provided are correct. The main feature in DB2 is that user authentication is done only by external plugins. Internal users, unlike Oracle or MS SQL Server, do not exist here. Even the user creation function, which 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 regulated by the AUTHENTICATION parameter in the database manager. The value of this parameter affects where the client authentication will be performed (on the server side or on the client side) and whether the data will be transmitted in encrypted form (values with the end of _ENCRYPT). 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 for this 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 the command line processor (db2 or db2.exe in Windows), connect to the instance and run the following commands:
db2 => attach to db2inst1
db2 => get database manager configurationThe default value for AUTHENTICATION is SERVER. Validation of the provided user credentials is performed on the server side using the operating system, but all data is transmitted in the clear and can be intercepted by an attacker.
Let's see how the intercepted information in Wireshark looks like.
Login and password transmitted from the client are visible in the package when viewing EBCDIC.
When changing the authentication type to SERVER_ENCRYPT, the login and password will be transmitted in encrypted form and checked on the server side.
The value changes as follows: The authentication package will look like this: However, the request text and the result will still be transmitted in clear text.
db2 => attach to db2inst1
db2 => update database manager configuration using authentication server_encrypt
db2 => db2stop force
db2 => db2start
Request packet in Wireshark:
Answer packet in Wireshark:
If the AUTHENTICATION parameter is set to DATA_ENCRYPT, then the user credentials are encrypted, as well as information transmitted between the client and the server.
The value changes similarly to the above example: After that, the transmitted data will also be encrypted:
db2 => attach to db2inst1
db2 => update database manager configuration using authentication data_encrypt
db2 => db2stop force
db2 => db2start
Also, pay attention to the CLIENT authentication type. With this type of authentication, it is believed that a secure communication channel exists between the client and the server, and if the user has access to the client, he can gain access to the server without checking the credentials. That is, authentication as such occurs on the client side, verification on the server side is 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, this will provide attackers with the opportunity to gain access to the server without much effort.
If, for some reason, this type of authentication is suddenly needed, then you need to consider that there are two additional parameters that ultimately affect how the user credentials will be verified. This is 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 succeeds, the user ID matches the DB2 ID. Usually the identifier matches the username, but it uses uppercase characters.
Login
During the authorization process, it is checked whether the user has the necessary rights for the actions requested by him. There are authorities of the DBMS instance and database.
The authority level of a specific instance is specified in the database manager configuration. These are the following authorities:
- SYSADM (system administrator privileges)
- SYSCTRL (system management authority)
- SYSMAINT (System Maintenance Authorization)
- SYSMON (system monitoring authority)
These privileges are set by specifying the group to which the user will belong. To do this, use the following parameters of the dbmcfg file (according to the above permissions):
It is easy to get a list of users that are part of a group using DB2 means, you need to do this in the operating system itself or analyze which groups a particular user belongs to (see the “Useful Queries” for the query).
When configuring DB2, it is imperative to check the list of users who are assigned the SYSADM authority. This permission allows you to manage all database objects.
The credentials of a specific database can be viewed in the SYSCAT.DBAUTH view. It is necessary to pay attention to the CONNECTAUTH privilege, which determines whether the user will have access to the database or not, and the NOFENCEAUTH privilege, which is responsible for creating not fenced procedures and functions. Such procedures are performed in the address space of the database and, in case of an error, can violate the integrity of the database and the tables in it.
Privilege
Privileges in DB2 can be granted to various objects. Table access privileges can be viewed in the SYSCAT.TABAUTH view . Data on the type of privilege granted is stored in separate columns, depending on the privilege itself (SELECTAUTH, DELETEAUTH, etc.). When granting privileges 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 found in the SYSCAT.COLAUTH view .
Privileges for routines (functions, procedures and methods) can be found in the SYSCAT.ROUTINEAUTH view . Everything here is not entirely trivial, depending on the SPECIFICNAME and TYPENAME fields, privileges can be granted to all routines of a given scheme.
Users, Groups, Roles
All database permissions and various privileges can be granted 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 advisable to take into account certain recommendations and to know some subtleties when issuing powers and privileges. It is not recommended to grant privileges and authority to the database, in particular the ability to connect to the database (CONNECTAUTH), to groups. Privileges should be granted to specific users or roles who need it. Role support has been introduced in DB2 since version 9.5. Role membership management is done inside the database itself.
Also, DB2 has a built-in PUBLIC role. The database user does not need to provide the PUBLIC role: it is not possible to withdraw the PUBLIC role from the user. When a privilege is granted to the PUBLIC role, the privilege is actually granted to all database users. Do not grant any PUBLIC role database credentials. Privileges on 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, privileges are not checked for the existence of a user or group in the system. As a result, authentication users may appear in the system without being tied to real users of the system. You can find these 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)
A similar query is used to search for such groups, but the query indicates that there is no need to display PUBLIC data:
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 Label-based access control for accessing data in tables. 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 know about their existence. It makes no sense to talk in detail about the methods of implementing LBAC, since the manufacturer has a training manual on this topic:
www.ibm.com/developerworks/en/edu/dm0605wong/index.html
Automated Scan Tools
When setting up IBM DB2 server security, the important point is the use of any security scanners (for example, NGS SQuirreL for DB2, MaxPatrol, etc.). Scanners will explicitly indicate vulnerabilities in settings that you might have missed, or 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.dbmcfgeither
db2 => get dbm cfgChange 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('')) as t
Список всех установленных СУБД:
$ db2ls
Список всех экземпляров в СУБД:
$ db2ilist
Ограничить количество выводимых строк:
select * from tabname fetch first 5 rows only