Introduction to IBM DB2
I had to work with IBM DB2 for some time. Because the system is commercial, then there is not much information on the Internet in Russian, so I decided to describe some features of the operation of this DBMS.
')
Let's start with the entry point in the DBMS. In SQL SERVER, the end point is an instance, in which there can of course be separate databases, but the configuration and security model is the same for the whole instance. In DB2, the entry point looks like this - the instance (which corresponds to a specific port) is the database. In this case, there is a configuration both for the whole instance and for a separate database.
You can view the instance configuration using either the db2 command:
Where will be the parameters, their meaning and decoding. A shortened version is also possible:
Or by using the request:
Of the important parameters include:
You can view the settings of a specific database as follows:
Or about the same query as before:
The big difference between DB2 and other database management systems is the authentication model. There are no internal users like SQL Server or MySQL. All authentication is performed by means external to the DBMS (dynamically loaded plugins) - the operating system tools or external plugins (Kerberos, GSS API). The authentication type is set in the AUTHENTICATION parameter of the database manager configuration. The default setting is SERVER - the username and password are transmitted in the clear and this pair is checked for correctness by means of the operating system. If the username and password are correct, then the CONNECT privilege of the user or groups into which he belongs is checked (including the special PUBLIC group, which includes all authorized users). These privileges can be viewed in the table SYSCAT.DBAUTH :
A big mistake when setting up 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 get access to all the data that PUBLIC has. The username is taken from the operating system. That is, if we connect through Data Studio by the Administrator user, then all the privileges that this user has will be granted. And in this case there is no difference from which computer was accessed. 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 will not be able to connect to the DBMS.
Privileges of the level of a specific instance are recorded in the configuration of the database manager. These privileges are:
These privileges are specified by specifying the group to which the user will be logged. 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 database access (CONNECTAUTH), table creation (CREATETABAUTH), subroutine creation (EXTERNALROUTINEAUTH), and so on. These privileges can be viewed in the SYSCAT.DBAUTH view .
And finally, the privileges to access specific data — tables, subroutines, and so on. Here everything is quite trivial, but also with some features.
Privileges for accessing tables 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 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. In this case, information on this can be viewed in the SYSCAT.COLAUTH view .
The privileges of subroutines (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 subprograms of a given scheme.
If readers like the article, then I’m ready to talk about data protection in DB2 using Label-Based Access Control
Point of entry
')
Let's start with the entry point in the DBMS. In SQL SERVER, the end point is an instance, in which there can of course be separate databases, but the configuration and security model is the same for the whole instance. In DB2, the entry point looks like this - the instance (which corresponds to a specific port) is the database. In this case, there is a configuration both for the whole instance and for a separate database.
You can view the instance configuration using either the db2 command:
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
Where will be the parameters, their meaning and decoding. A shortened version is also possible:
get dbm cfgOr by using the request:
select name, value from sysibmadm.dbmcfgOf the important parameters include:
- authentication type (AUTHENTICATION)
- default path to create new databases (DFTDBPATH)
- network discovery server (DISCOVER)
You can view the settings of a specific database as follows:
connect to sample (sample - )
get database manager configurationOr about the same query as before:
select name, value from sysibmadm.dbcfgAuthentication
The big difference between DB2 and other database management systems is the authentication model. There are no internal users like SQL Server or MySQL. All authentication is performed by means external to the DBMS (dynamically loaded plugins) - the operating system tools or external plugins (Kerberos, GSS API). The authentication type is set in the AUTHENTICATION parameter of the database manager configuration. The default setting is SERVER - the username and password are transmitted in the clear and this pair is checked for correctness by means of the operating system. If the username and password are correct, then the CONNECT privilege of the user or groups into which he belongs is checked (including the special PUBLIC group, which includes all authorized users). These privileges can be viewed in the table SYSCAT.DBAUTH :
select GRANTEE from SYSCAT.DBAUTH where CONNECTAUTH = 'Y'A big mistake when setting up 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 get access to all the data that PUBLIC has. The username is taken from the operating system. That is, if we connect through Data Studio by the Administrator user, then all the privileges that this user has will be granted. And in this case there is no difference from which computer was accessed. 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 will not be able to connect to the DBMS.
Authorization
Privileges of the level of a specific instance are recorded in the configuration of the database manager. These privileges are:
- Sysadm
- SYSCTRL
- SYSMAINT
- Sysmon
These privileges are specified by specifying the group to which the user will be logged. 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 database access (CONNECTAUTH), table creation (CREATETABAUTH), subroutine creation (EXTERNALROUTINEAUTH), and so on. These privileges can be viewed in the SYSCAT.DBAUTH view .
And finally, the privileges to access specific data — tables, subroutines, and so on. Here everything is quite trivial, but also with some features.
Privileges for accessing tables 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 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. In this case, information on this can be viewed in the SYSCAT.COLAUTH view .
The privileges of subroutines (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 subprograms of a given scheme.
If readers like the article, then I’m ready to talk about data protection in DB2 using Label-Based Access Control
Source: https://habr.com/ru/post/128932/
All Articles