Introduction to IBM DB2
For work, I had to deal with the IBM DB2 DBMS for some time. Because the system is commercial, the Internet does not have much information in Russian, so I decided to describe some of the features of this DBMS.
Let's start from the entry point in the DBMS. In SQL SERVER, the endpoint is an instance, in which of course there may be separate databases, but the configuration and security model are the same for the entire instance. In DB2, the entry point looks like this - an instance (which corresponds to a specific port) - the database. In this case, the configuration is for the whole instance, and for a separate database.
You can see the configuration of an instance either with the help of the db2-command: Where parameters will be indicated, their value and decryption. A shortened version is also possible: Either by using a query: Of the important parameters, one can note:
You can view the settings for a specific database as follows: Either by about the same request as before:
The big difference between DB2 and other DBMSs is the authentication model. There are no internal users, as in SQL Server or MySQL. All authentication is performed by means external to the DBMS (dynamically loaded by plug-ins) - by the operating system or by external plug-ins (Kerberos, GSS API). The authentication type is set in the AUTHENTICATION parameter of the database manager configuration. The default value is SERVER - the username and password are transmitted in clear text and this pair is checked for correctness using the operating system. If the username and password are correct, then the user or the groups in which he is a member (including the special PUBLIC group, which includes all authorized users) has the CONNECT privilege. These privileges can be viewed in the table.SYSCAT.DBAUTH :
A big configuration error is to enable the CLIENT authentication type. In this case, DB2 entrusts authentication to the connected client, and if PUBLIC has the CONNECT privilege, then any user can connect to the database and gain access to all the data that PUBLIC has. The username is taken from the operating system. That is, if we connect through Data Studio as an Administrator user, then all privileges that this user has are granted. And in this case there is no difference from which computer access was made. It is recommended to enable this type of authentication only when there is a secure channel between the server and the client, and other clients cannot connect to the DBMS.
Instance level privileges are specified in the database manager configuration. These are the following privileges:
These privileges are set by specifying the group where the user will be a member. In dbmcfg, these are the SYSADM_GROUP , SYSCTRL_GROUP , SYSMAINT_GROUP, and SYSMON_GROUP parameters , respectively .
Further there are privileges of a specific database. These privileges include access to the database (CONNECTAUTH), creating tables (CREATETABAUTH), creating routines (EXTERNALROUTINEAUTH), etc. These privileges can be viewed in the SYSCAT.DBAUTH view
. Finally, privileges for accessing specific data - tables, routines, etc. Everything here is pretty trivial, but also with some features.
Table access privileges can be viewed in the SYSCAT.TABAUTH view.. 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. In this case, information about this can be viewed in the SYSCAT.COLAUTH view .
Privileges for routines (functions, procedures and methods) can be found in SYSCAT.ROUTINEAUTH . Not everything is trivial here, depending on the SPECIFICNAME and TYPENAME fields, privileges can be granted to all routines of a given scheme.
If readers like the article, I’m ready to talk about data protection in DB2 using Label-Based Access Control
Point of entry
Let's start from the entry point in the DBMS. In SQL SERVER, the endpoint is an instance, in which of course there may be separate databases, but the configuration and security model are the same for the entire instance. In DB2, the entry point looks like this - an instance (which corresponds to a specific port) - the database. In this case, the configuration is for the whole instance, and for a separate database.
You can see the configuration of an instance either with the help of the db2-command: Where parameters will be indicated, their value and decryption. A shortened version is also possible: Either by using a query: Of the important parameters, one can note:
get database manager configurationDatabase Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x0b00
CPU speed (millisec/instruction) (CPUSPEED) = 2.912790e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 8
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk32
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
get dbm cfgselect name, value from sysibmadm.dbmcfg- authentication type (AUTHENTICATION)
- default path for creating new databases (DFTDBPATH)
- network discovery of a server (DISCOVER)
You can view the settings for a specific database as follows: Either by about the same request as before:
connect to sample (sample - имя бд)
get database manager configurationselect name, value from sysibmadm.dbcfgAuthentication
The big difference between DB2 and other DBMSs is the authentication model. There are no internal users, as in SQL Server or MySQL. All authentication is performed by means external to the DBMS (dynamically loaded by plug-ins) - by the operating system or by external plug-ins (Kerberos, GSS API). The authentication type is set in the AUTHENTICATION parameter of the database manager configuration. The default value is SERVER - the username and password are transmitted in clear text and this pair is checked for correctness using the operating system. If the username and password are correct, then the user or the groups in which he is a member (including the special PUBLIC group, which includes all authorized users) has the CONNECT privilege. These privileges can be viewed in the table.SYSCAT.DBAUTH :
select GRANTEE from SYSCAT.DBAUTH where CONNECTAUTH = 'Y'A big configuration error is to enable the CLIENT authentication type. In this case, DB2 entrusts authentication to the connected client, and if PUBLIC has the CONNECT privilege, then any user can connect to the database and gain access to all the data that PUBLIC has. The username is taken from the operating system. That is, if we connect through Data Studio as an Administrator user, then all privileges that this user has are granted. And in this case there is no difference from which computer access was made. It is recommended to enable this type of authentication only when there is a secure channel between the server and the client, and other clients cannot connect to the DBMS.
Login
Instance level privileges are specified in the database manager configuration. These are the following privileges:
- SYSADM
- SYSCTRL
- SYSMAINT
- Sysmon
These privileges are set by specifying the group where the user will be a member. In dbmcfg, these are the SYSADM_GROUP , SYSCTRL_GROUP , SYSMAINT_GROUP, and SYSMON_GROUP parameters , respectively .
Further there are privileges of a specific database. These privileges include access to the database (CONNECTAUTH), creating tables (CREATETABAUTH), creating routines (EXTERNALROUTINEAUTH), etc. These privileges can be viewed in the SYSCAT.DBAUTH view
. Finally, privileges for accessing specific data - tables, routines, etc. Everything here is pretty trivial, but also with some features.
Table access privileges can be viewed in the SYSCAT.TABAUTH view.. 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. In this case, information about this can be viewed in the SYSCAT.COLAUTH view .
Privileges for routines (functions, procedures and methods) can be found in SYSCAT.ROUTINEAUTH . Not everything is trivial here, depending on the SPECIFICNAME and TYPENAME fields, privileges can be granted to all routines of a given scheme.
If readers like the article, I’m ready to talk about data protection in DB2 using Label-Based Access Control
