The article considers the issues of database security of the automated subsystem of water conservation process management of a modern enterprise. The reason for its complexity is that it must also have connections between different data elements and with ensuring information protection. That is why an important task in database development is the development of a conceptual data model (the general logical structure of the database). An approach is proposed to maintain the viability of this database. The final products of biochemical decomposition of organic matter in such water bodies are mineral salts and water. If the treatment facilities operate effectively, then wastewater after appropriate treatment can be used for technological production purposes. This will significantly reduce the consumption of fresh water, as well as reduce emissions of pollutants into the environment. Automated control systems significantly increase the efficiency of technological purification systems. The quality of management of such processes largely depends on the quality of the automated control database. The components of information support for automated control of water conservation processes of a modern enterprise are presented. The core of information support is the database. The theoretical results of the presented study are specified in the development of a database subschema that functions in the PostgreSQL database management system (DBMS).
Water usage after its purification for technological purposes of modern production is advantageous both in terms of environmental protection and economic considerations. If treated wastewater (approximately 90%) is reused for technological purposes, it will be much cheaper for the company than taking fresh water.
Almost always wastewater treatment is a set of methods, among which the functioning of the
technological treatment system in terms of using an automated control system, occupies an
important place [
The correctness of the concept on the basis of which the components of information support are
formed and the validity of the selection of important features and connections are important for the
representativeness of information. The development of information support for the subsystem of
automated control of water conservation processes was carried out on the basis of the methodology
of programming the life cycle of complex objects [
One of the first works where the problem of building a database of automated control of
wastewater treatment was considered. It should be noted that this article did not present a logical
model of the database. The works [
Structural information support of water-saving processes automated control subsystem for modern production (airport) consists of information support of functional subsystems (ISF) and information support of modernization (design) of technological cleaning system (ISM).
The basis of ISF is a database, the control system of which allows you to develop an interface with applications. Applied software based on economic and mathematical methods and models ensures the functioning of water conservation processes in the optimal mode. ISF consists of system-wide (SWIS) and functionally-autonomous information support, which reflects the specifics of each subsystem of automated control.
System-wide information support consists of: - classifiers of problems that contain reference data for solving functional problems; - electronic arrays of these classifiers; - electronic documents common to all subsystems; - means of unambiguous description of data used in all subsystems.
Functionally autonomous information support (FAIS) includes: - information classifiers and corresponding electronic arrays necessary for solving problems in individual subsystems; - means of unambiguous description of data used in individual subsystems.
Information support for modernization and design of technological treatment system is a set of means of identification of object documentation, stages and stages of work on modernization (design) of technological treatment system, which includes: - means of unification of names of tasks of modernization and design; - system of coding of design decisions; - system of designations and coding of stages and stages of modernization (design) of technological cleaning system; - coding system of project documentation.
In the future we will consider the functional and autonomous information support of the subsystem of automated control of water conservation processes of a modern enterprise.
The information support of this subsystem includes: 1. normative and reference data (information on maximum permissible concentrations of pollutants, water consumption limit j-m production process, economic assessment of water in the region, unit price of water j-m production process, cost standards provided by scientific and financial plan, etc.);
2. current information for automated control algorithms (wastewater consumption, physicochemical parameters of the contaminant ingredient, reagent characteristics, water consumption for normal operation of the j-th production process, multiplicity of water dilution by the k-th pollutant, set level of water reuse j-m production process etc.);
3. accounting and archival information for modernization (design) of the technological scheme of wastewater treatment (standard design solutions for typical treatment processes, equipment characteristics, etc.).
As mentioned, the database and management system are the core of information support. The functional use of information support is carried out within the local network, which makes it accessible to all participants in the process. The database administration is responsible for the up-todateness of the information, and the functional responsibilities of its employees should be included in the organizational support. Before entering data into the database, their authenticity is checked by special algorithms.
When developing a logical description of the database of the subsystem of automated control of water conservation processes, the sets of data required for control were identified. The data presented at the logical level of the user reflects his view of the database when solving specific tasks. The introduction of this level of data representation significantly reduces the processing time of queries, increases the security of the database and expands the scope of their use.
The scheme of the database of automated control of cleaning systems includes two tree-like structures (Fig. 1).
The leaf of each tree is an entry that includes the code and name of the treatment (Wastewater Treatment or Gas Emissions Treatment) - the top level of the hierarchy. The next level of the hierarchy, subordinate to the first, is represented by records that reflect the methods of cleaning and their codes. The lower level of the hierarchy consists of records for management tasks according to certain cleaning methods.
The minimum available unit of information in the hierarchical model is a node, ie one row at each level. Searching on the basis of a hierarchical data model is carried out in the directions from top to bottom and from left to right. Therefore, to find any node, it is necessary to identify the node that is looking for and those nodes to which it is subordinate. This is a shortcoming of the hierarchical database. In addition, such a database makes it difficult to adjust, enable and delete all subordinate levels, which also complicates the operation of the database. Presentation of data in the form of trees will hinder the development and significant growth of the database. At a certain stage of its growth, it is possible to disrupt the logical representation of data, which will lead to the need for changes in applications. As a result, one of the main advantages of the database will disappear - data independence. This shortcoming is eliminated in a relational database, in which data is presented in the form of two-dimensional tables - one of the most natural ways to present data to the user.
Since any network structure can be decomposed into a set of tree structures with some redundancy, the hierarchical representation of data can also be reduced to two-dimensional flat files with some redundancy. Relationships between the data shown in Fig. 2 will be presented in the form of twodimensional tables. This was done step by step for each connection between the data, using the normalization process. Tables are built in such a way that information about the relationships between data elements is not lost. The relationship key must contain, in addition to the primary, a secondary (foreign) key, with which the tuples of this relationship will be uniquely identified.
For database management systems that do not support static relations of relational relations, the links between them are established only to solve a specific problem and exist for the period of its solution. Therefore, when mapping a hierarchical model to a relational one, all structural connections are not described explicitly, but only a check is made for the possibility of establishing these connections. A prerequisite for establishing a connection between two relational relations is the presence of at least one common attribute, which is the foreign key on which the connection is performed.
The relationship between PURIFICATION METHODS AND TYPICAL PROCESSES in the first normal form (1NF) is also obtained on the basis of the database subscheme (Table 2).
The relationship will be written as follows: PURIFICATION METHODS AND TYPICAL PROCESSES (code of purification method, name of purification method, code of typical process, name of typical process, code of typical processes parameters, parameters of typical processes, code of pollutant, code of mode parameter).
Table 2
Purification methods and typical processes
Continuing the normalization process, we obtain the ratio of the PURIFICATION METHOD and the ratio of TYPICAL PROCESSES in the third normal form (Table 3).
Table 3 Purification method 3NF
parameter Code of purification method Primary Key
M1 M2 M3 M4
Finally, the ratio TYPICAL PROCESSES in the third normal form will be written (Table 5).
The TYPICAL PROCESS PARAMETERS relationship will be written in the third normal form as follows (Table 6).
The generated ratio POLLUTANTS AND THEIR PARAMETERS in the first normal form based on the database subscheme (fig. 2) will be written as follows: POLLUTANTS AND THEIR PARAMETERS (code of mode parameters, code of pollutant, name of pollutant, physico-chemical parameters of pollutant, pollutant sensor value) (Table 7).
Continuing the process of normalization, the ratio of POLLUTANTS and the ratio of PARAMETERS OF POLLUTANTS in the third normal form were obtained (Table 8-9).
The generated relation REAGENTS AND THEIR PARAMETERS in the first normal form on the basis of the database subscheme (fig. 2) will be written as follows: REAGENTS AND THEIR PARAMETERS (reagent type code, reagent name, reagent physicochemical parameters, reagent concentration sensor value, contaminant code, purification method code) (Table 10).
Continuing the process of normalization, we obtain the ratio of REAGENTS and the ratio of PARAMETERS OF REAGENTS in the third normal form (Table 11-12).
Table 11 Reagents 3NF
Reagent type code Primary Key 200 201
Reagent name
Sodium hydroxide
Lime suspension
The normalization resulted in a relational database model that combines relationship tables.
The implementation of a logical data model is primarily associated with the selection of a specific database for management tasks (Fig. 3). This task is not easy and many factors need to be considered. Here you need to predict the prospects for production development, which creates a choice in terms of expanding functions and tasks, and studies the software market. There are two problems in the assessment of DBMS. The first is related to the choice of the database from the user's point of view, and the other is purely technical and related to system performance.
At the present time there is no generally accepted analytical method of database management system choice. Therefore, experts in data management field for the solution of this task use such methods: - methods of simulation; - experimental studies with using full scale tests; - heuristic method having such points in the comparative evaluation of characteristics as "yesno", "present - absent," "good - satisfactory - bad."
While using simulation modeling techniques there can appear some additional problems in evaluation of database simulation model accuracy. Obtaining comparative characteristics of database management system with the help of full-scale test is associated with significant labor costs. Therefore, the choice of database management system for maintaining a database is based on expert estimates (Table 13-14). Experts i=1,4 1 2 3 4 2 1 1 1
3 9,53 2,81 1,63
4 9,53 8,73 1,9
Factors (promising DBMS), j=1,7
MariaDB 6 4 1,63 4,75 16,38 14 7 4,89 4,75 30,64 9 3 8,15 9,5 29,65 9 6 9,78 13,3 38,08 12 5 6,52 7,6 31,12 4 2 11,41 11,4 28,81
Therefore, based on the results shown in (Table 16), we can conclude that the PostgreSQL database has the smallest number of ranges. Thus, according to experts, this DBMS is the most suitable time database management system for automated management of water-saving processes. An important feature of the PostgreSQL database is the development of capabilities in its application environment for automated control of languages such as PL/Java, C ++, C-compatible modules, Python, PL/pgSQL and others. In addition, the PostgreSQL database has streamlined highperformance transaction and replication mechanisms, as well as an SQL-based query language that allows the client-server LAN to use a client-server architecture. Thus, the PostgreSQL database meets the requirements of efficient operation of the automated control process of water-saving processes for modern production.
Practical work with the presented database involves ensuring three fundamental aspects of security - authentication (who has the right to connect), authorization (what exactly they can do), and protection of the data that is stored and transmitted (Fig.4).
One of the first levels of protection is to verify the client connecting to the database. The PostgreSQL database implements a wide range of authentication methods: trust, password, certificate, as well as integration with external systems - LDAP, GSSAPI/Kerberos, RADIUS, etc. For example, the outdated MD5 method is no longer recommended due to its vulnerability to attacks with "rainbow" tables. Instead, it is recommended to use SCRAM-SHA-256 as a more secure option. The pg_hba.conf file allows you to flexibly determine: where to connect from (IP addresses or local sockets), which user, to which database, and which authentication method is used.
Thus, authentication settings and connection location restrictions reduce the risk of unauthorized access. After confirming the user's authenticity, the next step is to restrict rights: which database objects he can see or change. The PostgreSQL database implements a role and permission model (GRANT/REVOKE) with the principle of "least privilege". In addition, a finer level of control - the so-called Row-Level Security (RLS) - allows you to set policies that control access to individual table rows, depending on the user's role.
The settings of the PUBLIC schema and default privileges also require attention: excessive rights or trust rules in pg_hba.conf can create weak points.
So, authorization is not only about creating roles, but also about regularly auditing rights and reducing rights to the minimum necessary. When it comes to storing and transferring data, the PostgreSQL database has several options, although some of them require external mechanisms.
To protect data during transmission (data in transit), it is recommended to use an SSL/TLS connection, which provides encryption of traffic between the client and the server.
To protect data on disk (data at rest), the PostgreSQL database does not have a full-fledged Transparent Disk Encryption (TDE) feature in the base assembly, so encryption is used at the OS or file system level (for example, LUKS, FileVault) or through an intermediate implementation of TDE based on fork processes. In addition, column or application level encryption can be used (for example, the pgcrypto function), which will allow you to selectively protect sensitive fields. In general, the right strategy is to combine encryption, access restrictions and rights control, which will ensure the integrity, confidentiality and availability of information.
Thus, the structure of information support for automated water conservation process management of modern production is proposed, and the composition and structure of data are determined, as well as the choice of a database management system. An approach to ensuring information protection in the database is considered.
The authors have developed a conceptual model of the database as the main component of information support. For each connection between data, normalization was performed, which allowed obtaining a relational data structure in the third normal form, which has significant advantages over hierarchical and network structures.
The client-server architecture of the database for the subsystem of automated water conservation process management will allow not only to store data on the central computer, but also to perform basic data processing operations. The specificity of this architecture is the use of the SQL query language. The user will not receive files, but only the data selected by him. Protecting information in a database includes measures to ensure its integrity (protection against unauthorized modification), confidentiality (protection against unauthorized disclosure), and availability (ensuring access and recovery from failures). Key techniques include encryption, authentication (including multi-factor authentication), password and privilege management, the use of firewalls and VPNs to create secure connections, and regular backups to recover data in the event of loss.
The presented approaches and the criteria formed on their basis allowed making a choice of a database management system, which allows proceeding to its physical implementation. The choice of a DBMS to support the viability of the database based on the generated indicators is another result of this work. The database will be maintained in the environment of the PostgreSQL relational DBMS, which includes a system of embedded programming languages, the SQL query language, which will allow developing an interface with the application software of the automated control system (ACS) for wastewater treatment processes on a single information basis.
Further development of the database will be associated with the accumulation of information on the statistical and dynamic characteristics of typical treatment processes. Information of the third lower level of the database subscheme will be recorded in the form of sections of the actual database. In the future, an Automated Information System for Managing Water-Saving Processes in Modern Production should function.
The authors have not employed any Generative AI tools. 2020.