RU2682010C1 - Data in the database access separation method - Google Patents

Abstract

FIELD: data processing.SUBSTANCE: invention relates to the data in the database access restricting method. In the method, in the database creating the stored in one table data layers directory, creating the data layers users linking table, creating the child data layers linking table, creating the security policy at each of the tables row level, setting the data layer identifier for the added data set and saving the added data set while maintaining the specified layer identifier, with the data set adding to the linking table, setting the data layer identifier for the added data set and saving the added data set, setting the data layer identifier for one added data set, saving the added document and the document one data set, saving the added data set, providing the user with access to the data sets, whose data layers identifiers are associated with the user ID in the users linking table.EFFECT: technical result consists in increase in the users access to data separation reliability through the use of data layers and security policy at the rows level.3 cl, 9 dwg, 3 tbl

Description

FIELD OF TECHNOLOGY

The invention relates to the field of information technology and computer technology, and in particular to a method for delimiting access to data in a database.

BACKGROUND

Recently, due to the growth in the volume of data stored and used by users on computing devices and in various systems, technical solutions are required that allow users to access only those database data that users need to carry out their activities, in particular for work. Providing users using computing devices access only to the data they need, in particular, without providing access to the data of other users or providing limited access to the data of other users, for example, for viewing (reading) and modification (for example, changing, deleting, etc. .d.) of data, allows to speed up the work of users with the volumes of data provided to them, and also increases the security of receiving data for users who do not have permission to view and / or modify ation of such data. Technical solutions are also required that allow certain users to restrict access to data stored in databases, in particular, databases common to many users, for example, users of various enterprises. Technical solutions are also required that allow users to use the data stored in the databases, common, in the particular case, the same for all users or at least one user group. Traditional methods of working with data, including methods of delimiting data in databases, over time become less effective or ineffective, which leads to a decrease in the efficiency of users and enterprises.

The prior art system and method for ensuring procurement between companies and groups of companies (see US 7155403B2, publ. 12/26/2006), and a method for ensuring procurement between many companies with many complex accounting rules to ensure safe viewing of information by companies, and companies are organized into groups of companies and purchasing associations to receive discounts and access to general supplier contracts.

The disadvantages of this technical solution is the lack of the use of data layers and the use of row-level security policies to ensure that users have differentiated access to data stored in the database. Another drawback of this solution is the lack of using a hierarchy of data layers (in particular, parent and child data layers) to provide differentiation of access to data stored in the database.

The prior art also knows a system and method for collecting and storing casino information in a relational database system (see US 20090172035A1, published 02.07.2009), the method including creating a relational database for storing and organizing information, as well as creating a logical data model defining how information is stored and organized in a database, and the logical data model includes a subject area that includes many entities and relationships that determine how information is stored in the specified database.

The disadvantages of this technical solution is the lack of the use of data layers and the use of row-level security policies to ensure that users have differentiated access to data stored in the database. Another drawback of this solution is the lack of using a hierarchy of data layers (in particular, parent and child data layers) to provide differentiation of access to data stored in the database.

Integrated communication systems and methods for presenting products and direct sales are also known from the prior art (see US 20080052193A1, publ. 02.28.2008), moreover, the method describes providing a company interface for an ERP system associated with many companies, the interface providing an access channel to the goods of each company, and companies can selectively download content from their ERP system and provide a user interface for displaying content about the product, and allowing the user to to acquire goods, and transactions are sent directly to the seller’s ERP system through the company’s interface.

The disadvantages of this technical solution is the lack of the use of data layers and the use of row-level security policies to ensure that users have differentiated access to data stored in the database. Another drawback of this solution is the lack of using a hierarchy of data layers (in particular, parent and child data layers) to provide differentiation of access to data stored in the database.

The proposed method allows to overcome at least part of the above disadvantages or all of these disadvantages, as well as realize the advantages of the present invention, as described in the framework of the present technical invention.

SUMMARY OF THE INVENTION

The technical result is to increase the reliability of restricting user access to data by using data layers and row-level security policies.

According to one implementation option, a method is proposed for delimiting access to data in a database, in which a directory of data layers stored in at least one table is stored in a database of a database server, wherein the directory of data layers contains at least least the names of all data layers and contains the identifiers of data layers for data sets stored in the metadata objects of the business logic of the application server of the computer system server, and the metadata objects are directories, documents, tabular parts of documents, totals, and linking reference tables stored in the database tables of the database server associated with the computer system server, where the identifier of the data layer determines whether the data set belongs to the corresponding data layer; creating a linking table of users of data layers in the database of the database server, and in the linking table of users of data layers, the relationships between users and data layers are stored in the form of stored associated user identifiers and corresponding data layer identifiers from available data layer identifiers of the data layer directory, relations between users and data layers determine the availability of data sets for reading and modification to users associated with it data layers; creating a linking table of the child data layers in the database database server, and the hierarchy between the data layers is stored in the linking table of the child data layers in the form of stored linked identifiers for the child data layers and identifiers for the parent data layers, and the relationship between the parent layers and child layers determines the availability of data sets of child data layers for reading by users of the parent data layers; the security policy is created at the row level (RLS policies) for each of the database server database tables, which store directories, documents, tabular parts of documents, totals and linking reference tables, and RLS policies are saved in the database of the database server data, moreover, the RLS policy contains at least one expression that returns a logical result that determines the availability of data sets from the database tables of the database server to the user, depending on the identifiers Loew data for these data sets; the application server when adding a data set to the directory sets the identifier of the data layer for the added data set and saves the added data set to the database of the database server with saving the set layer identifier for such a data set; the application server when adding a data set to the linking table sets the identifier of the data layer for the data set to be added and saves the added data set to the database of the database server with saving the set layer identifier for such a data set; when adding a document, the application server establishes the identifier of the data layer for at least one added data set contained in the added document and saves the added document and at least one document data set in the database of the database server with saving for a stored document dataset of the set layer identifier; when adding a data set to the tabular part of the document, the application server sets the data layer identifier for the data set to be added and saves the data set to be added to the database server database, preserving the set layer identifier for such a data set; the computer system server by applying the RLS policy executed by the database server to requests made to data sets from the database of the database server, the user is granted access to the data sets and data sets for reading and modification, the identifiers of the data layers of which are associated with the identifier the user in the user data linking table of data layers when querying data from the database server database by the user using the client application I installed on a user's computing device associated with the application server, the server computer system; the computer system server provides access to the user of the parent layer to the data sets and data sets of the child data layer for reading, without the possibility of their modification, by applying the RLS policy executed by the database server to requests made to data sets from the database of the database server using data layer identifiers for child data layers and data layer identifiers for parent data layers from a linking table of child data layers for setting detecting communication data between the parent layer and at least one layer of subsidiary data by sending the request data from the user database server data using the client application installed on a computing device user associated with the application server the server computer system.

In one particular embodiment, a directory of directory exclusions is created containing directories whose data sets do not contain identifiers of data layers, and when at least one data set is added, at least one decoupling table of the directory and / or at least , in one other directory, different from the directory of directory exclusions, the following is carried out: checking the availability of the directory to which the data set is added in the directory of directory exclusions; checking the presence of a data layer identifier in the data set to be added for the data set to be added when the absence of the directory is found in the directory of directory exceptions; obtaining a data layer identifier from the linking table of data layer users and adding the obtained data layer identifier to the data set to be added in the directory when it is found that the data layer identifier is not in the data set to be added for the data being added; checking whether a data set is added to the database by a user or a task executed by program code; assignment of the identifier of the data layer obtained from the data linking user table to the unsaved data sets of the linking tables associated with the data set to be added when the user data set is added; assignment of the identifier of the data layer obtained from the added data set to the unsaved data sets of the linking tables associated with the added data set when adding the data set to a task executed by program code.

In another particular embodiment, a document type exception directory is created containing documents whose data sets do not contain data layer identifiers, and when a document is added to the database server database, the following is performed: checking whether the type of the added document is added to the document type exception directory; checking the presence in the added document of a data layer identifier for the data of the added document when establishing the absence of the type of the added document in the directory of exceptions of document types; obtaining a data layer identifier from a linking table of data layer users and adding the obtained identifier to the added document when it is determined that the data layer has no identifier in the added document; checking the implementation of adding a document to the database by a user or a task executed by program code; assignment of the identifier of the data layer obtained from the added document to unsaved datasets of the table parts associated with the added document when the user adds the document to the database; assignment of the identifier of the data layer added to the document from the data layers obtained from the user linking table to the unsaved data sets of the table parts associated with the added document when the document is added to the database by a task executed by program code.

BRIEF DESCRIPTION OF THE DRAWINGS

Additional objectives, features and advantages of the present invention will be apparent from reading the following description of an embodiment of the invention with reference to the accompanying drawings, in which:

FIG. 1 illustrates an exemplary embodiment of a system that implements the method described in the framework of the present invention;

FIG. 2 illustrates an exemplary embodiment of the components of an application server, the components of a client application, as well as the interaction between them and the database;

FIG. Figure 3 illustrates an example embodiment of restricting user (and user group) access to data using the RLS policy.

FIG. 4 illustrates a flowchart of an exemplary embodiment of a process for creating a new entry in a directory and / or directory decoupling table;

FIG. 5 illustrates a flowchart of an example embodiment of a process for saving a new document;

FIG. 6 illustrates an exemplary embodiment of virtual database generation according to the present invention;

FIG. 7 illustrates an exemplary embodiment of a virtual database hierarchy according to the present invention;

FIG. 8 illustrates a method of implementing the present invention;

FIG. 9 illustrates an example of a general purpose computer system.

DETAILED DESCRIPTION

The objects and features of the present invention, methods for achieving these objects and features will become apparent by reference to exemplary embodiments. However, the present invention is not limited to the exemplary embodiments disclosed below, it can be embodied in various forms. The essence described in the description is nothing more than the specific details provided to assist the specialist in the field of technology in a comprehensive understanding of the invention, and the present invention is defined only in the scope of the attached claims.

Used in the present description of the invention, the terms “component”, “element”, “system”, “module”, “part”, “block”, in particular, “component”, and the like, are used to refer to computer entities (for example, objects related to a computer, computing entities), which may be hardware, in particular equipment (for example, a device, tool, apparatus, equipment, part of a device, in particular a processor, microprocessor, printed circuit board, etc.), software m (for example, executable program code, a compiled application, software module, part of the software and / or code, etc.) or firmware (firmware / firmware). So, for example, a component can be a process running (running) on a processor, processor, object, executable file, program, function, method, library, subprogram and / or computing device (for example, microcomputer or computer) or a combination of software or hardware .

In the particular case, the present invention allows the maintenance and formation of (management) statements of the enterprise (in the particular case, consisting of (including) at least one company, which is a legal entity, for example, a company, individual entrepreneur, etc.) , in particular, users of enterprises (customers) using the system described in the framework of the present invention. It is worth noting that the implementation of reporting can be carried out in real time (online, from English online, on-line) or upon request (on demand, from English on-demand) with guaranteed reliability, in particular, the reliability of the figures that managers and owners of companies see, for example, figures in the balance sheet, income statement (profit and loss statement), cash flow statement (DDS), etc. In the general case, the reporting may contain false data (it may be false) when users enter data from incomplete information (data), in particular, about completed transactions, or the information (data) entered or entered by users contains errors. It is worth noting that in the present invention, when creating postings, the double entry rule is guaranteed to be observed (in particular, the reflection of a business transaction in the debit of one account and in the credit of another in the same amount), i.e. in a particular case, no data appears from nowhere and does not disappear to nowhere. In the particular case, enterprise accounting is carried out in a single physical database (in particular, in the database (FIG. 1, 145)). In the particular case, users use a standard set of operations that are reported in a specified way that users cannot change. Management reporting in a particular case is a system of indicators characterizing the activities of the enterprise (which is the subject of budget accounting, in particular budget accounting), different from budget reporting compiled on the basis of management accounting for the enterprise (and / or in the particular case, at least , one company of such an enterprise).

Currently, many enterprises use several systems for management accounting, for example, partially in spreadsheets and systems (in particular software) for creating and editing spreadsheets, spreadsheets (for example, Microsoft Excel / Microsoft Office Excel, OpenOffice Calc / Apache OpenOffice.org Calc, WPS spreadsheets, etc.) and partially in accounting systems. In this case, to obtain management reporting, for example, balance sheet, users must manually “consolidate” (combine, consolidate) data from various sources (in particular, from spreadsheets and accounting systems), which takes considerable time for users and often leads to errors in the resulting summary data. Moreover, this approach does not allow for management accounting in real time.

The described invention allows to implement the application of changes made by at least one user of at least one enterprise to the accounting policy (in particular, the user performs an operation that only changes the virtual database that such user has access to, as described in more detail below) and the principles of the formation of management reporting for all (or only certain) enterprises. The mentioned changes (in particular, resulting from the user performing at least one operation) can include any form of management reporting, and in the particular case, users cannot make changes to them at their discretion. It is worth noting that changes made by the user, for example, changes (editing) in documents, addition of new documents and deletion of existing documents, etc., are determined according to the rules that are set, and such rules determine exactly how user operations get into reporting and special case are the same for all users. So, for example, if the management report “Balance” contains the section “Accounts receivable from customers”, then an application server (described later) can be created (for example, by the administrator of the system described in the present invention or, at least, part of it, for example, by the administrator application server) the rule is the separation of the said receivables of buyers into normal receivables and overdue receivables, then this rule will be fulfilled for all users, i.e., at least at least one part (in particular, a module) of the described system will divide the said receivables of buyers into normal receivables and overdue receivables from all users (of the described system). Regarding the mentioned principles of formation of management reporting, it is worth noting that the daily work of the company in a particular case consists of a set of (elementary) operations performed by users. Each operation can lead to a change in the state of assets and liabilities of the company. An accounting policy determines how a particular transaction changes a liability or an asset, and how this change enters into statements such as balance sheet, income statement, cash flow statement, etc. So, for example, if the company sells the goods to the buyer with delivery, then the profit from this operation is not fixed at the time of shipment of the goods from the warehouse, but at the moment when the driver of the vehicle that delivered the goods confirms the fact of transferring the goods to the buyer.

In the particular case, the described invention allows users to carry out the above operations according to unified preset accounting rules in one database. It is worth noting that for each operation, at least one algorithm is specified and executed that determines at what time such an operation posts to the registers (for example, information register, accumulation register, accounting register, calculation register, etc. ) of the described system. Based on the data from the registers, reporting is carried out and from the observance of the aforementioned double entry rule, the total amount for all registers is always zero. It is also worth noting that the basis of accounting is also the directories (in particular, catalogs) of articles of mutual settlements with counterparties, articles of VAT, cost items, etc. A set of such articles is stored (set, for example, by the mentioned administrator) in virtual database 0 (FIG. 7, 710) in the form of at least one table and is available to users for use (in particular, for viewing, for example, reading client application (FIG. 1, 130; FIG. 1, 136), etc.), but is not available for change, as described in more detail below. As an example, to describe the present invention, an ERP system is used (ERP, from Enterprise Resource Planning, enterprise resource planning - an organizational strategy for integrating production and operations, human resources management, financial management and asset management, focused on continuous balancing and optimization of enterprise resources through software that provides a common data and process model for all areas of activity, in particular the ERP system is a (software) module, real which describes the ERP strategy and, in a particular case, is an enterprise resource planning system), but it is worthwhile to understand that any computer system can be used, for example, an approximate version of which is shown in FIG. 9. In the particular case, the ERP system described in the framework of the present invention includes such a database and predefined (predefined) accounting rules, the interaction with which is carried out by the interaction (with which) users of enterprises. So, for example, in such an ERP system there is a catalog (directory) of goods stored (stored) in the database (DB) of such an ERP system, in particular in the database (FIG. 1, 145) of the main database server (FIG. 1, 110), in at least one table (in which data on goods from the said directory (catalog) of goods is stored). So, for example, one enterprise can sell computer equipment, the second enterprise can sell household chemicals, and both enterprises (in particular, users of enterprises, for example, accountants, database operators, etc.) store data about their goods in one and the same catalog (directory) of goods, in particular, to said database table. Thus, the catalog (directory) of goods will contain such goods from the first enterprise (selling / selling computer equipment) as laptops, monitors, etc., which are computer equipment, and such goods from the second company (selling / selling household chemicals) ), like soap, detergents, cleaning products, etc., which are household chemicals. It is worth noting that in directories (catalogs) the first and second (third, etc.) enterprises can store (save) data on suppliers, companies (company companies), settlement accounts, etc., i.e. in the particular case, such directories (directories) are common for both companies (users (employees) of the companies), so that the data of one enterprise can be displayed (presented) by users of another enterprise and / or modified by users of another enterprise, which in a particular case can be confusing and errors in data between enterprises, and may interfere with the work of users with such data (for example, users of enterprises may erroneously change data from other enterprises or use data from other enterprises yaty instead of their own and so on). It is worth noting that, in a particular case, users (of one enterprise) cannot save data common to all users, for example, users of the first enterprise and users of the second enterprise, for example, information (data) about banks (for example, bank identification codes (BICs) ), bank name, correspondent account, address, etc.), exchange rates, etc. Such general data in a particular case should be available, both for users of the first enterprise, and for users of the second enterprise, i.e. can be accessed (in particular, can be viewed) by all users or at least one part of users - a group of users. Thus, it is necessary to realize access (in particular, displaying to users and / or saving by users of data, in particular, to directories (directories) and / or database tables) to a certain group of users (the first group of users) of data stored by the first group of users (client data of the first user group), as well as to the part of the data (common data for two user groups) stored by another user group (second user group), and also to provide access to the second user group It refers to data stored by the second user group (client data of the second user group) and to a part of the data (common data for two user groups) stored by the first user group. So, for example, users of the first enterprise (the first group of users) can be granted access to data that was saved by such users of the first enterprise (the first group of users), and users of the second enterprise (second group of users) can be granted access to the data that saved by such users of the second enterprise (the second group of users), and in the particular case the second group of users has access to some of the data stored by the first user group lei, and the first group of users has access to the portion of the data stored second group of users. It is worth noting that the users of each enterprise can comprise at least one user group, for example, a group of users who are employees of the accounting department of the first enterprise, a group of users who are employees of the sales department of the first enterprise, a group of users who are employees of the accounting department of the second enterprise, users who are employees of the sales department of the second enterprise, etc. The above is an example for two user groups of two enterprises, although it is worth noting that the present invention is not limited to two enterprises and one or two user groups for an enterprise, for example, enterprises can exist - two, three, four, ten, one hundred, one thousand etc., and each enterprise may have one, two, three, four, etc. user groups. In the particular case, certain users or user groups can be granted access (can be displayed, edited, in particular, modified and deleted) to all records of other users or user groups, as described in the framework of the present invention.

In FIG. 1 shows an exemplary embodiment of a system that implements the method described in the framework of the present invention.

The system by which the implementation of the method described in the framework of the present invention, shown in FIG. 2, comprises a server (in particular, a remote server (computer system)), for example, an ERP system server 112, which includes at least an application server 120. Also, the ERP system server 112 may include an export server 125.

Also, the system depicted in FIG. 2, contains an application server 120, a main database server 110, a backup database server 115, an export server 125, user computing devices (132, 134) at enterprise 1, enterprise 2, etc. with installed client applications (130, 136), print servers (135) and printers (140) at the mentioned enterprises (enterprise 1, enterprise 2 ... enterprise N).

It is worth noting that the elements (in a particular case, the components of an exemplary embodiment of a system that implements the method described in the framework of the present invention), for example, a primary database server 110, a backup database server 115, an application server 120, an export server 125, a print server 135, computing devices of users (132, 134), etc., shown in FIG. 1, can be interconnected via a local area network (LAN), the Internet, mobile communications and / or through any other type (method) of wired communication (for example, via a USB interface, an RS-232 / COM port interface, etc.) etc.) and / or wireless communications (e.g. Bluetooth, Wi-Fi, mobile cellular (GSM), in particular in the 850/900/1800/1900 MHz bands, satellite communications, trunking and ultra-low data channels power consumption, forming complex wireless networks with mesh topology (ZigBee), etc.).

It should be noted that computing devices, in particular, computing devices of users, can be smartphones, laptops, tablets, personal computers, electronic computers (computers), servers, workstations, monoblocks, computer stands, desktop computers, test benches, demonstration computers stands, mobile (cellular) devices, in particular telephones, etc.

The main database server 110 carries out at least data management and contains at least business logic (business logic space, business logic components, business logic elements (components) of the business logic) described below. The main database server 110 in a particular case contains a DBMS (database management system) and provides the DBMS functions for maintaining and maintaining the database 145. In the particular case, the main database server 110 can be a (software) database server, in particular, supporting the described RLS technology, for example, “Oracle 11gR2 Enterprise Edition”, “Oracle 12c Enterprise Edition”, “PostgreSQL 9.6”, etc., installed on the storage device of at least one computing device, for example, a computer ( or special computer m equipment) isolated and / or specialized to perform specific service functions. It is worth noting that the data storage device (data storage) mentioned can be an integral part of at least one main database server 110 and / or can be connected with it (wired or wireless communication types), and is a device for storing information ( data) and can be implemented by at least one hard disk drive (HDD), solid-state drive (SSD, solid-state drive), hybrid hard drive (SSHD solid-state hybrid drive), a storage area network (SAN / SAN) L. Storage Area Network), a network storage system / network storage (NAS, English Network Attached Storage) and / or any other device that allows at least writing to a device, reading from a device and / or storing data on device. Data on data storage devices (in data warehouses) can be stored in any known format, for example, in a database (DB) 145, for example, in the form of at least one table or a set of related or unrelated database tables. It is worth noting that at least one of the mentioned database 145 may be a hierarchical, object, object-oriented, object-relational, relational, network and / or functional database, each of which may be centralized, concentrated, distributed, heterogeneous homogeneous, fragmented / partitioned, replicated, spatial, temporal, spatio-temporal, cyclical, super-large database, etc., and for the management, creation and use of databases can use various database management systems (DBMS). Also, data on data storage devices (in data warehouses) can be stored in at least one file, in particular, in the form of a text file, or data can be stored in any at least one other storage format currently known data (information) or in a data format invented later.

The export server (from the English. Export server) 125 converts printed forms into files.

Print server 135 receives print jobs for documents from at least one application server 120. Typically, print server 135 is located in an office where client applications are used (130, 136), which can significantly reduce the load on communication channels due to transmission a much smaller amount of data (in particular, between the application server 120 located in the data center ((from the English data center), or the data storage center (storage and)) and the office of the enterprise in which the computing user device (132, 134 and etc.) with the client application installed on it (130, 136)).

The backup database server 115 is used to provide fault tolerance of the primary database server 110 and stores backup copies of data stored in the database 145.

It is worth noting that the main database server 110, the backup database server 115, the application server 120, the export server 125, and the print server 135 can be implemented by at least one specialized computer and / or specialized equipment (to run the service software on it providing, in particular, without direct human involvement) and / or, at least, one software component (in particular, an application) of a computing system (in particular, at least one specialized computer A and / or specialized equipment), performing service (serving) function on demand, providing access to certain resources or services. In a particular case, the main database server 110 or the backup database server 115, or the application server 120, or the export server 125, or the print server 135 can be implemented by software, in particular, a software component (application) running on hardware, in particular, a (universal) computing device, such as a server, computer, etc. In the particular case, the primary database server 110 or the standby database server 115, or the application server 120, or the export server 125, or the print server 135 may be remote servers, cloud servers, etc.

In the particular case, client applications (130, 136, etc.) installed on user computing devices (WU), for example, WU 1 (132), WU 2 (134), WU N, etc., are implemented at least, the presentation of data (depending on the operations described, for example, if the operation is an operation to purchase goods, then the user displays (presents) a list of suppliers, goods, stocks of goods in warehouses, etc., in particular, in at least one directory) and functional processing (which in a particular case includes changing data in directories, for example, the quantity of goods in an order to a supplier and in a customer’s order, i.e., in a particular case, the client application 130 receives data from the application server (which in turn requests them from the main database server 110) 120, editing (where it is allowed by the logic of the described system) or adding new data by users).

It is worth noting that, in the particular case, the system that implements the method described in the present invention can be implemented by a software and hardware complex of three-level architecture (three-tier architecture scheme), in particular, one level of such a scheme is the main database server 110 and optionally standby database server 115, another level is the application server 120 and another level is at least one client application, for example, a client application (client (software) module) 130, client th application (client (software) module) 136 and other client applications (client (software) modules). In the particular case, a system that implements the method described in the present invention, and shown in FIG. 1, including that implemented by a three-tier architecture, allows centralized management of business logics (and) and, in case of its (their) changes, allows not to carry out its (their) replication in the mentioned client applications. It is worth noting that in a particular case, business logic is the implementation of the rules and restrictions of automated operations and is a combination of rules, principles, and dependencies of the behavior of objects of the subject area supported by the described system. In the particular case, a system that implements the method described in the present invention, and shown in FIG. 1 avoids the need to install users on computing devices (client computers, client computers, computing devices of enterprise users, in particular companies), for example, on computing device (WU) of user 1 (132), on computing device (WU) of user 2 (134) etc., components of (software) data access control. Also, in the particular case, the system that implements the method described in the present invention, and shown in FIG. 1, including that implemented by a three-tier architecture, allows the possibility of delayed (in time) updating of the database 145 in the event of a change in the data requested from the main server of the database 110, offline. In the particular case, the data update will be carried out in the database 145 after the next connection of the client application (client program, client software) 130, client application 136, etc. with application server 120.

In FIG. 2 shows an exemplary embodiment of the components of the application server 120, the components of the client application 130, as well as a variant of the interaction between them and the database. Application server 120 includes a core 210 and a business logic space (business logic). The business logic space (business logic) includes types of documents 212, processors (in particular, implemented by scripts) 214, subtypes of documents 216, printed forms 218, results 220, reports 222, directories (directories) 224, metadata 226, server modules 228 , client application modules 230, and may also include business rule processing scripts, business rules, screen forms, etc., with handlers 214 describing the interaction logic of business objects being business rules and processing scenarios.

In the particular case, the server modules 228 are software libraries (a collection of routines or objects used for software development) used by the described scripts, in particular, the application server scripts 120, and, in the particular case, loaded at boot (in particular, at startup ) application server 120.

In the particular case, the client application modules 230 are a set of software libraries, for example, for creating and, in particular, the functioning of client applications (for example, 130, 136). For example, such client application modules 230 may contain a set of forms for editing configuration items, etc.

Client application 130 includes a kernel 210 (in a particular case, which is a copy of the core of the application server 120), metadata 226 (in a particular case, which is a copy of the metadata of the application server 120), client application modules 230 (in the particular case, a copy of the client application server application modules 120) etc.

In the particular case, the core 210 (application server 120 or client application 130) changes much less frequently than the constituent parts (elements) of the business logic space. The core 210 provides the functioning of the components of the business logic space (for example, document types 212, handlers 214, subtypes of documents 216, printable forms 218, totals 220, reports 222, directories (directories) 224, metadata 226, server modules 228, client application modules 230), and such said constituent parts are stored in the tables (in the form of tables) of the database 145 of the main database server 110.

The kernel 210, in the particular case, is a (software) module that is an invariant part of the described system and ensures the functioning of the second, variable part - the business logic space. The kernel 210 provides the functioning of the described system and provides the following tools and mechanisms:

- storage of users, their authorization and verification of rights;

- communication between applications;

- access to the DBMS (saving system objects to the database 145);

- management of configuration changes (versioning mechanism);

- conversion of "raw data" from the database 145 into the objects of the described system;

- integration with other systems, for example, SOAP (from the English Simple Object Access Protocol - a simple protocol for accessing objects), JSON (from the English JavaScript Object Notation - a text-based data exchange format based on JavaScript), REST (from the English Representational State Transfer - “transfer of view state”), XML (from the English eXtensible Markup Language) - an extensible markup language), etc .;

- development environment and configuration changes;

- transformation of objects, their unloading and saving to the database;

- communication between parts (software and hardware modules) of the described system.

As mentioned above, in the particular case, the business logic space contains business rules, business rule processing scenarios, screen forms, etc. A specific filling of the business logic space, for example, corresponding to a specific client (in particular, an enterprise, for example, a user of an enterprise or a group of users of at least one enterprise, in particular, a user of an enterprise company) or a specific business option (in particular, an enterprise or company enterprises) is the configuration of the business logic space (configuration). The configuration is stored in database 145.

In a particular case, the 210 kernel provides an interface and tools for implementing business logic. In the particular case, the business logic executed on the application server 120 is implemented by classes in one of the well-known programming languages (for example, C #) inherited from the corresponding kernel classes (scripts).

One of the types of scripts are transactional scripts associated with a document subtype and are executed (executed) when a document of this subtype is saved, and transactional scripts form movements based on the results (postings).

It is worth noting that scripts may be handlers 214:

- a directory event handler - is a script that is automatically executed in the case of a series of events occurring (carried out) with the directory entries, and for each directory its own event handler can be created;

- a document event handler - is a script that is automatically executed in the case of a series of events occurring (carried out) with a document, and for each type of document (documents) its own event handler can be created.

It is also worth noting that the on-screen logic runs on the client application 130 and is implemented in the form of modules, in particular, modules of the client applications 230, for example, in a .NET compatible programming language.

It is worth noting that the business logic space (business logic, configuration) may contain such metadata objects (being data entities, data structures), such as directories (directories) 224, reference decoupling tables (linking reference tables, linking reference tables, link tables reference book, types of decoupling tables), documents, tabular parts of documents, results 220, etc. It should be noted that the data of metadata objects in computer information systems can be processed as data entities (entities) that have constant or variable sets of values (in particular, data). Currently, it is widely used in computer information systems to store data in the form of data entities, in particular cases stored in database tables, for storing, transmitting, presenting (displaying) and other purposes of working with data. The user can view, transfer and edit data entities, in particular, the data contained in such data entities. It is worth noting that the directories (directories) 224, decoupling tables of the reference book (linking reference tables, linking reference tables, reference book tables, types of decoupling tables), documents, tabular parts of documents, results 220 are given in the present description as an example, and it is worth understanding that the present invention is not limited to such directories 224, decoupling tables of the directory, documents, tabular parts of documents, results 220. Directories (directories) 224 contain information about the objects participating them in the business processes of companies (for example, suppliers or customers or goods or warehouses). In the particular case, directories (directories) 224 are used by the described system (an exemplary embodiment, which is shown in FIG. 1), for example, by the application server 120 and / or client application 130, in cases where it is necessary to exclude the ambiguous input of data (information) by users (enterprises), for example, so that each user (for example, buyer, seller, storekeeper, accountant, etc.) understands what kind of product (data about which is stored in database 145), each such user should call such a product about otherness, i.e., in the particular case, in the database 145 is done storage, e.g., the same title for each item, one unit for each item of goods, etc. It is worth noting that directories can include regular (non-nuclear) directories (included in the business logic space) and directories related to the kernel ("nuclear" directories, kernel 210 directories), in the particular case, directories that are kernel 210 directories, in which , in a particular case, users (in particular, user groups) cannot add new fields and / or in which existing fields cannot be changed, in particular the composition of the fields (in a particular case, new fields cannot be added or changed or deleted essentially vuyuschie field). In a particular case, kernel directories (nuclear directories, system directories) can be user directories, user groups, role directories (in particular, intended for maintaining a list of roles in the warehouse accounting system and setting rights for them), document directories, etc.

Reports 222 are (applied) configuration objects (business logic) and are used to process accumulated information and obtain summary data in a convenient form for viewing and analysis. It is worth noting that reports 222 can exist in three types:

- reports on the results, the functionality of which is fully implemented by the 210 kernel;

- virtual totals, with the help of which a report can be created on the summary data of several totals;

- user reports implemented by the administrator (programmer, developer).

The referenced reference tables (stored in the database tables 145 of the main database server 110) are used (in particular, by the components of the system, an example of which is shown in FIG. 1 and FIG. 2, for example, the application server 120 and / or client application 130 , and components of the application server 120 and / or client application 130) for creating links (in particular, include links, for example, links) between at least one element of at least one directory, at least with one email ment, at least one other directory. So, for example, one decoupling table (linking table) may contain relationships between one element of one directory and several elements of another directory. In the particular case, a link between an element of one directory and several links is a link.

The documents mentioned above (stored in the database tables 145 of the main database server 110, in particular, in the form of at least one record of at least one table of at least one of the mentioned databases) contain information about what happened in company events (for example, sale of goods, receipt of funds in the current account from the buyer, etc.). In the particular case, the document consists of (contains) the headers ("caps") of the document, a set of fields inherent in all subtypes of this type of document, in particular, fields that may be contained in subtypes of this type of document. Also, a document may include a set of tabular parts, in a particular case being an array of data belonging to such a document. So, for example, for the arrival of goods, the document headers can be information about the date of arrival of the goods, by whom (which company, in particular, the company of the company) the goods were delivered, to which warehouse the goods were delivered, etc. It is also worth noting that the tabular part of the document, in the particular case, is the list of goods ordered by the enterprise (in particular, the company’s company), the number of goods ordered, the cost of goods ordered, etc.

In the particular case, each document consists of a common “header” (a set of fields that exists in any document of the described system), a “header” (a set of fields that exists only in this document, and a set of tabular parts, and the number of tabular parts for one the document is not limited, which greatly simplifies the formulation of complex business logic). The tabular section allows you to save the list of records within one document (for example, product lines).

In the particular case, the document contains information (data) about one event.

It should be noted that a record (a record of a directory) is a specific line (or a specific object of the system, in particular, business logic) of a directory.

It is also worth noting that, in the particular case, directories and decoupling tables (directories) are used to store static, rarely changing data. So, for example, a list of goods sold or manufactured by a company can be implemented by a directory.

The results of 220 mentioned above (stored in the database tables 145 of the main database server 110) contain information (data) about the current state of the measured indicators of the company and the history of their changes. So, for example, Results 220 may contain information on the remaining goods (at the enterprise, in particular, at the company’s company, for example, at the warehouse). Each data change in Outcome 220 is called a movement. So, for example, each income or expenditure of goods (for example, the remains of the goods) are movements. Totals 220 are made up of measurements and variables. Each measurement of the data in Results 220, for example, a specific total (income or expense of the goods), which is a measured indicator of the company, is a link to at least one directory from the Directories 224, and the history of their changes is stored in variables. Results 220 may have several dimensions, for example, two or more. To summarize, the remaining goods in the warehouse are the measurements of the goods and warehouses themselves, and the quantity and value of these goods are variables. Movements are formed on the basis of documents (when they are saved) and, being recorded in the results 220, they change the values of their variables for certain sets of measurements. The arrival of goods increases the balances of the capitalized goods in the warehouse indicated in the document of arrival. The expense document, on the contrary, reduces them.

Results (total) 220 is the object of the described system (in particular, business logic), which allows you to describe the measured indicator of the subject area and store the history of its change. Results 220 can also be represented as a multidimensional cube. Thus, the document saves information about the event and converts its data into a set of totals changes. So, for example, an example of totals 220 are the remains of goods in warehouses, and in totals 220, information is stored on how much goods are left in each of the warehouses. Accordingly, the sales document reduces the value of the total remaining goods in warehouses (for goods from the document).

Printed forms 218 are used to print system objects to a printer (to a printer or for export to a file). A printable form is a template and a script that provides data to fill out a template.

It is worth noting that the attributes of documents, in the particular case, are document types 212, which (document types) are determined by business logic. A document type is a description of a set of fields in the document header and other (its) properties. So, the type of documents (212) may be Sale (of goods), Coming (of goods), etc. An event, the information about which is contained (stored) in at least one document, can be “stretched” in time, for example, a Sale contains the stages of Order, Set (in particular, picking) in a warehouse, Payment, Issue, etc. .d., in connection with which, the types of documents (212) have corresponding (sub-events) subtypes of documents 216 that describe the state of documents at a certain (including past, present, etc.) time (or period) of time and related to it.

It is worth noting that directories contain a directory of exceptions to directories containing directories whose datasets do not contain identifiers of data layers.

It is worth noting that the directories contain a document type exception directory containing document types whose data sets do not contain data layer identifiers.

It is worth noting that, within the framework of the present invention, directories contain an additional directory of data layers (“Data Layers”, DataLayer). In the particular case, the creation (for example, by the administrator of the described system) of the directory “Data Layers” is carried out, an exemplary version of which is shown in Table 1.

Table 1.

System-
first name Title
(Caption) Field type in terms of database (Property type.Database type) Field type in terms of the system (Property type..NET Type) Obligation
regarding filling Unique
code (identical
Kator, ID)
data layer Identity
data layer fixer (unique
code, value) of the data layer NUMBER (NUMBER (18)) Number (System.Int64) ISTI-
ON Name of the data layer (Name) Title DATA SYMBOL (VARCHAR2 (2048)) String (System.String) ISTI-
ON Is-
x folder Folder NUMBER (NUMBER (1)) Logic type (System.Boolean) ISTI-
ON

It is worth noting that decoupling (linking) tables (spaces) of business logic contain decoupling tables of child data layers (“Child data layers”) and users of data layers (“Data layer users”), in particular, creation of decoupling tables “Child layers” data ”(an example of which is shown in Table 2) and“ Users of the data layer ”(an example of which is shown in Table 3). In the particular case, all data that is stored in (non-nuclear) directories, decoupling tables of directories, documents, tabular parts and totals 220 contain (have) a sign of relationship (belonging) to a particular data layer (in particular, have a sign (in particular, have a link to a specific data layer from the aforementioned directory of data layers), to which data layer they (data) belong), for which all of the mentioned objects ((non-nuclear) directories, decoupling tables of documents, documents, tabular parts and totals 220) will adding a wish to set up links to the data layer.

Table 2 shows an example variant of the decoupling table (which defines, in particular, the relationship between the data layers, in particular, between the parent data layer and the child data layer), in particular, the decoupling table of the Child data layers (ChildrenLayers, “Child layers ") of the type of Child Data Layers (DataLayerTreeNode) with a link by the identifier of the parent data layer (ParentID), in which the identifier of the parent data layer (ParentID) and the identifier of the child data layer (ChildID), in a particular case, are links to help IR data layers ( "Data Layers» (DataLayer)).

Table 2.

System Name (Name) Title (Caption) Type of field in terms of database
(Property type..NET Type) Type of field in terms of the system
(Property type.Database type) Obligation
specifically to
fill in
opinion Parent Identifier
sky
data layer (ParentID) Parent data layer, code
(ident
fixture of the (parent) data layer) Number (System.Int64) NUMBER
(NUMBER (18)) TRUE Child Data Layer Identifier (ChildID) Child data layer, code (identical
fixture (child) layer
data) Number (System.Int64) NUMBER
(NUMBER (18)) TRUE

It is worth noting that, for example, for two enterprises, a Basic Data Layer can be created (in particular, by the administrator of the described system) and a separate Data Layer can be created for each of the two enterprises, for example, Data Layer 1 for Enterprise 1 and Data Layer 2 for Enterprises 2. The base data layer is dependent (Data Layer 1 and Data Layer 2 inherit, in particular, contain all the data from the base layer), in particular, a child of Data Layer 1 and a child of Data Layer 2, and this dependence ( communication) is carried out by p the referenced table of the child data layers (“Child data layers”) above, so that users who have access to Data Layer 1 (data from Layer 1) at least see all the data from Layer 1 and the Base data layer and , in a particular case, can edit them. The users of Enterprise 1 and the users of Enterprise 2 are added (started up), in particular, by the administrator of the described system, to the user directory (in particular, which is the nuclear directory listing (all) users of the described system), in particular, the administrator of the system described in the present invention or at least parts of it, for example, the application server administrator. In the interchange table of users of data layers (“Data layer users”), an example option is given below, for Data Layer 1, users (in particular, user identifiers) of Enterprise 1 are added. In the interchange table of users of data layers (“Users of data layer”) for Data Layer 2 is added by Enterprise 1 users. For all data (data sets), in particular, the database tables of the main database server 110, which must be shared between Enterprise 1 and Enterprise 2 in the above decoupling table another data layer is put down (set, set), for example, a basic data layer (“Basic data layer”), in particular, the aforementioned attribute, in a particular case, an identifier, relations (belonging) to a data layer, for example, by the administrator described system, or by means of the described system, for example, at least one module (component) of the described system. So, for example, if under one of the roles (with one of the roles) described in the framework of the present invention, the user opens one of the directories, for example, a currency guide, then for writing in this directory the administrator of the described system (for example, programmer, developer, administrator client application, application server administrator, etc.) can explicitly indicate to which data layer this record (data, data set, for example, data stored in at least one cell of the database table) belongs. In the particular case, for such a mentioned role, the RLS mechanism may not apply (may not work) (the RLS policy is not applied), so using this role the data layer for any entry in any directory can be changed. It is worth noting that the values in the directories can be added automatically, and added to the corresponding data layer. So, for example, exchange rate values can be downloaded automatically by software modules, for example, scripts, software "robots" from one of the sites, for example, from the site of the Central Bank of the Russian Federation. Such a software module determines in which data layer the currency is (stored) and adds values for such a currency to the corresponding (in a particular case, in the same) data layer. It should be noted that such data may include, for example, data from the directory of currencies and exchange rates, and the directory of currencies and exchange rates. It is also worth noting that for enterprises several basic data layers can be affixed.

Table 3 shows an example variant of the decoupling table of data layer users (“Data layer users”) with the “Users” type of the enterprise (client) with a link by the data layer identifier (DataLayerID), and the data layer identifier (DataLayerID), in a particular case, is a link to the data layer directory (DataLayer) shown in the table above, and the user identifier (UserID) identifies the user and, in a particular case, is a link to the system directory of users containing a user password, in which, in a private case tea, contains the login (from the English login - the name (identifier) of the user account), the hash (hash sum) of the user's password (the result of applying the hash function to the user's password), the user's role. It should be noted that the decoupling (linking) table of users (users of data layers) contains a link between users and data layers, in particular, in the form of stored associated user identifiers and data layer identifiers. It is worth noting that, in the particular case, the identifier of the data layer is the identifier of the enterprise, i.e., in the particular case, it determines whether the data (data sets) belongs to one or another enterprise.

Table 3.

System-
first name
(title) Title
(Caption) Type of field in terms of the system
(Property type..NET Type) Type of field in terms of database
(Property type.Database type) Obligatory to
opinion Identity-
data layer cator (DataLayerID) Data layer, code (identical
data layer fixture) System.Int64 NUMBER (NUMBER (18)) TRUE Identity-
the user
body (UserID) User code (identifier
user lock) System.Int64 NUMBER (NUMBER (18)) TRUE

In the particular case, in all non-systemic (non-nuclear) directories, documents, connecting tables, tabular parts and totals, a link (Data LayerID) is added to the data layer directory (DataLayer). Also, in the particular case, in all transactional scripts (TransactionScripts) in transactions, filling in the data layer identifier (DataLayerID) is performed. It is worth noting that documents using transactionScripts post (make) postings to the Results, since the document belongs to (or contains) some specific, at least one data layer, then the posting must belong to the same data layer, thus, in the script registers for posting such a data layer from the mentioned document.

It is worth noting that to limit the access of users (and user groups) to data, the present invention uses the RLS policy (from the English row-level security, RLS, line-level security, line-level protection, line-level security policy) DBMS It is worth noting that the RLS policy allows you to differentiate access to data (information) stored in the database 145, in particular, based on certain knowledge about the user (s) and user groups. Such knowledge may be the role of the user in the enterprise (in particular, in the company of the enterprise), the position of the user, the department (unit) in which the user works, etc. The RLS policy (in the particular case, implemented by RLS technology) provides the ability to create security policies that restrict users access to certain data stored in the database 145, in particular, to the rows (and the data contained in the rows), at least , in one database table 145, even if such a user has standard rights (the ability to receive data from the table) to read (and change, including deleting and editing) data from the said table (s). It should be noted that when communicating at least one database object with a security policy, access control is carried out using logic, for example, the logic of a PL / SQL function (from the English Procedural Language / Structured Query Language, PL / SQL - language programming, a procedural extension of the SQL language developed by Oracle). At the same time, in the database 145 (for example, in the Oracle database), a (special) package is added (from the English package - packages that allow you to group sets of named blocks of code, and in which the state of the database session is stored for the lifetime available for functions and procedures included in the package) containing (separate elementary) functions and procedures and providing work with data layers. It is worth noting that the package is a PL / SQL package. The business logic space contains a software tool (which is created, for example, by a programmer, developer), in particular, implemented by a script that accesses (in particular, queries) database data, which checks the metadata object (for example, document, reference book, total etc.) the presence of an RLS policy in the DBMS for the tables corresponding to the metadata object (in particular, storing data of such a metadata object) of the database (in particular, database 145) and, in the absence of an RLS policy, creates Maintenance RLS-policy. In the particular case, the execution (launch) of the mentioned software tool, in particular, the module, is carried out after adding (in particular, the administrator of the described system) a new metadata object, in particular, the tool is launched by the programmer, user, or automatically (in particular, the application server 120 ) So, for example, for each (or at least one) directory, decoupling table, document, tabular section, total, etc. created in the business logic space, an RLS policy is created in database 145, restricting user access to data by values (identifiers) of the data layer, in particular, which data layer the user is associated with (to which data layer the user (or group of users) is attached), the user receives the data with this value (identifier) of the data layer.

It should be noted that to provide (and / or limit) access to users to the data of directories, documents, tabular parts of documents, totals and linking tables of directories for reading (viewing) or modifying (adding, changing or deleting) RLS policy (implemented by program code , in particular, in a scripting language, for example, a script) contains at least one expression, in particular, returns a logical result that determines which rows of the tables in which the mentioned data is stored will be provided for reading and modification to users, and which will not be provided for reading and modification to users. In the particular case, the mentioned expressions are calculated for each row of the tables before other conditions or functions (before other conditions and functions are fulfilled) coming from the user’s request from the client application. Thus, when applying (using) the RLS policy, table rows (therefore, data (in particular, data sets) contained in such rows) for which the mentioned RLS policy expression returns a logical FALSE will not be processed and will not be returned to request from the user (client application of the user).

In FIG. Figure 3 shows an example of restricting user (and user group) access to data using the RLS policy. To display the data requested by the user of the enterprise (for example, enterprise 1), the client application 130 installed on the computing device of the user 132 sends a data request (requests data) (315) from a directory, for example, a directory of goods (directory “Goods” (“Articles ")), To the application server 120, in particular, depending on what forms the user opens. The following is an example of the above data request:

"SELECT

    c.ID,

    c.NAME as Name

FROM

    ARTICLES c. ”.

In a client application (for example, 130, 136, etc.), the user opens, for example, the “Product Catalog” form. In order to display data on goods, said client application requests from the application server 120 the data of the goods directory. Further, the application server 120 determines in which table of the database 145 the data directory of the goods is stored and implements (sends) a request (325) to the database 145 for the corresponding data, to obtain such data from the table (query the table data), for example, from the table, in which the data of the "Goods" directory is stored, on the main database server 110. The table mentioned in which the data of the "Goods" directory is stored is given as an example and, in the particular case, is the database table in which the data (non-nuclear) is stored right nick in particular directory products, and such products table (in particular reference products) may contain fields such as product code, product name, product number, dimensions of goods, etc. Next, the main database server 110 means the DBMS searches for the RLS-policy for the table, for example, for the table "Products". Next, the main database server 110 applies the found RLS policy to the query for the table data, for example, the table “Products”. So, in the particular case, for each table of the database 145, where there should be (which should contain) the data layers in the database 145, an RLS policy must be created. Next, the main database server 110 returns to the application server 120 table data (335), for example, the “Products” table, for which the value (identifier) of the data layer matches the value (identifier) of the data layer of the user requesting the data. Next, the application server 120 transmits data (in particular, the same data (345) that the main database server 110 returned to the application server 120 tables, in particular, the table “Goods”) to the client application 130, and the client application 130 provides (display ) received data to the enterprise user.

It should be noted that the user, users, user groups or user groups of enterprise 1 is granted (by means of the RLS policy) access, in particular, for reading and displaying, only data with the value (identifier) of the first data layer for the first enterprise (Data Layer 1 for Enterprise 1) and the base data layer (Base Data Layer), i.e. access to the data of Layer 1 of Enterprise 1 and the Base data layer.

Also, the user, users, user group or user groups of Enterprise 2 is granted (by means of the RLS policy) access, in particular, for reading and displaying, only data with the value (identifier) of the second data layer for the second enterprise (Data Layer 2 for the Enterprise 2) and the Basic data layer, i.e. access to data from the Data Layer 2 Enterprise 2 and the Basic data layer.

It is worth noting that the user, users, user group or user groups of enterprise 1 (first enterprise) and enterprise 2 (second enterprise) cannot change or add data contained (stored) in the Base data layer and data in the Base data layer (t .e., in the particular case, contained in other data layers) that are not specified in the interchange table of users of data layers, which is implemented by the RLS policy.

In a particular case, when a user (in particular, belonging to at least one user group) adds a new record to the directory (for example, adding a new product to the catalog (directory) of goods) information (data) on belonging to a certain, at least , one data layer is automatically determined by the current user (where at least one user or group of users corresponds to at least one data layer), i.e. is determined depending on which data layer the user is associated with (to which data layer the user is “attached”), and the record is saved in the database 145 with the desired (corresponding) value (identifier) of the data layer.

In the particular case, when a new record is added (in particular, by a script algorithm that is launched when a new record is saved), information (data) on the layer membership is automatically determined by the current data layer (in particular, the data layer from the directory, to to which the stored row of the decoupling table belongs) and the record is saved in the database 145 with the desired (defined) value (identifier) of the data layer. It is worth noting that the record of the directory itself can be a record (data set) of a child data layer (it can refer to a child data layer), in particular, have a sign of relation to the data layer, in particular, to the child data layer, and not be accessible to users for editing (in the particular case, read access to the record (in particular, data) is granted only for reading).

It is worth noting that when adding a new record to the document, information (data) about belonging to the data layer is automatically determined by the current user and the record is stored in database 145 with the desired value (identifier) of the data layer.

It is also worth noting that when adding a new record to the tabular part of the document, information about belonging to the data layer is automatically subtracted (obtained) from the document and the record is stored in the database 145 with the desired value (identifier) of the data layer (in the particular case, the data that adds the user is added only to those virtual databases to which the user is “attached” (has access, is connected).

Also, for all “postings” based on the results (“postings” are made (only) by documents), data about the data layer from the document is used. "Posting" (in particular, account assignment) is a record in the database 145 about the change in the status of the goods being recorded.

It is worth noting that when adding (for example, a programmer, developer) a new metadata object (directory, decoupling table, document, tabular part of the document, total, etc.), the attribute of the data layer (identifier of the data layer, in particular, a link to the data layer ) is added to the object automatically, in particular, to the metadata object (document, directory, total (s)). It is worth noting that a new object is added (included) to the access control system (RLS, RLS policy) to data layer data (virtual database data), in particular, the RLS policy is created by the DBMS (automatically) for the corresponding table.

In FIG. 4 is a flowchart of an exemplary embodiment of a process for creating a new record (in particular, a directory entry, for example, a set of fields written to a database table 145) in a directory or a reference table of a directory. It is worth noting that when you save a record in the decoupling table, it is also saved in the directory entry to which the record of the decoupling table belongs. The process of creating a new entry in the directory and / or the decoupling (linking) table of the database directory 145 begins at step 410. As part of the process presented in FIG. 4, a new record is created and saved in at least one decoupling table of the (non-nuclear) directory and / or in the directory, in particular, in the non-nuclear directory (manually) when adding records (in particular, data sets) by the user (for example, in a client application 130 by a company user) or programmatically (for example, by periodically executing a task (from the English task), in particular, by an application server 120 in which a data layer, in particular, a basic data layer, is registered, and such a task (task ), for example, m Jette be loading and saving foreign exchange rates, etc.). In a particular case, the mentioned task is a special service user of the system, in particular, a script that sees the data of all data layers at once, and under such a user, periodic tasks are launched (executed, executed), for example, daily import (loading) of exchange rates and etc. It is worth noting that the task also has a user identifier, in particular in the same format as the user identifier described in the framework of the present invention.

In step 425, the application server 120 checks whether the (non-nuclear) directory is contained in the directory exception directory (the list of directory exceptions), which does not contain (identifiers) of data layers (which should not contain data layers, in particular, data layer identifiers ) If it is determined in step 425 that said directory is not contained in the directory of exclusions of directories, then the process proceeds to step 430, otherwise the process proceeds to step 460.

At step 430, it is carried out, in particular, by a script (application server 120), to check whether the value (identifier) of the data layer is specified in the record explicitly. In the particular case, an explicit addition of the value (identifier) of the data layer is the addition of such a value (identifier) of the data layer by the corresponding algorithm, script, program code, etc.

If it is determined in step 430 that the value (identifier) of the data layer is not specified explicitly, then the process proceeds to step 435. If in step 430 it is determined that the value (identifier) of the data layer is specified explicitly, the process proceeds to step 445.

In step 435, the value (identifier) of the data layer for the current user (adding records) is obtained by calling the script function, in particular, the scalar function, "GET_CURR_USER_DATA_LAYER" from the package "PACK_DATA_LAYERS" (an approximate version of the program code of which is given below) of the database 145 The function (method) "GET_CURR_USER_DATA_LAYER" sends a request to the table "DATA_LAYER_TO_USERS" (a decoupling table for users of data layers, an example of which is shown in Table 3) with a filter (in particular, a database request 145 with the WHERE clause) for the current user, returns the code (identifier, value, attribute) of the data layer, in particular, added earlier, in particular, to the table of users of data layers, for example, by the administrator of the described system or at least one module described system.

Next, in step 440, the value (identifier, attribute, code) of the data layer is stored, in particular, the relation to a specific virtual database, in the directory entry into which the user adds the entry.

Next, in step 445, (in particular, the aforementioned script on the application server 120) check is made whether the current user is a task (i.e., the storage is performed by the above-mentioned task (task) or (manually) by the user). If it was determined in step 445 that the user is a task (that is, saving is performed by the task mentioned above, and not (manually) by the user), then the process proceeds to step 450. If it was determined in step 445 that the user is not a task ( that is, the saving is performed not by the task mentioned above, but (manually) by the user), the process proceeds to step 455.

In step 450, to all unsaved records of all decoupling tables that (directory entries) relate to the entry being added, the value (identifier) of the data layer from the added directory entry is added and the process proceeds to step 460.

In step 455, the value (identifier) of the data layer from the current user (saving the record) is added to all unsaved records of all decoupling tables related to the record being added, and the value (identifier) of the data layer for the current user is performed using a call to the above (execution, execution ) functions, in particular, the scalar function, "GET_CURR_USER_DATA_LAYER" from the package "PACK_DATA_LAYERS" of the database 145 to query the table "CLIENT_TO_USERS" with a filter by the current user, determines the enterprise ( lienta) and returns associated with (attached to) the enterprise (customer) value (identifier feature code) of the data layer, in particular, the identifier of the user layer, and the process proceeds to step 460.

At step 460, the data sets are saved. In particular, the data sets are saved with the corresponding data layer identifiers or without the corresponding data layer identifiers, depending on the fact whether the directory is contained in the exception directory defined in step 425.

In FIG. 5 is a flowchart of an exemplary embodiment of a process for saving a new document. The process of saving a new document, in particular, creating and saving a new record (new document) in the document journal containing documents of this type (manually) by the user or programmatically, starts at step 513. Next, at step 515, a check is made that the document type is not it is contained in the directory of type exceptions (a list of type exceptions), in which (in particular, in documents with a certain type (of a certain type)) there should not be data layers (identifiers of data layers should not be contained), in the particular case if An exception is necessary that in some type of documents there is no division of data into data layers, i.e. users from different data layers (users who are assigned different data layer identifiers) save such documents with access to them by all users.

If it is determined in step 515 that the directory is not contained in the type exclusion directory (type exclusion list), then the process proceeds to step 517, otherwise the process proceeds to step 529.

In step 517, the script (performed, in particular, when saving the document or directory) checks whether the value (identifier) of the data layer is specified explicitly.

If it is determined in step 517 that the value (identifier) of the data layer is not specified explicitly, then the process proceeds to step 519. If in step 517 it is determined that the value (identifier) of the data layer is specified explicitly, the process proceeds to step 523.

In step 519, the value (identifier) of the data layer for the current user is obtained by calling the function, in particular, the scalar function, GET_CURR_USER_DATA_LAYER from the package PACK_DATA_LAYERS of the database 145. The function (method) GET_CURR_USER_DATA_LAYER sends the request to the table CLIENT_TO_USERS with the filter defines the user with the filter enterprise (client) and returns the data layer code (in particular, identifier, value (identifier) of the data layer associated with (attached to) the enterprise (client).

Next, in step 521, the value (code, identifier, value) of the data layer in the (current) document is added (assigned).

Next, in step 523, a check is made to see if the current user is a task. If it was determined in step 523 that the user is a task (task), then the process proceeds to step 525. If it was determined in step 523 that the user is not a task (task), the process proceeds to step 527.

In step 525, the (all) unsaved records of (all) table parts related to the document record being added are assigned the value (identifier) of the data layer from the document being added (and, in the particular case, the document is also a record in the database) and the process goes to step 529.

In step 527, the (all) unsaved records of (all) table parts related to the document record to be added are assigned the value (identifier) of the data layer added (assigned) in step 521.

At step 529, the document is saved. In particular, the document datasets are saved with the corresponding data layer identifiers or without the corresponding data layer identifiers, depending on the fact whether the type of the added document is contained in the document type exceptions directory defined in step 515.

FIG. 6 illustrates an exemplary embodiment of virtual database generation according to the present invention. In FIG. 6 shows a physical database 145, from the data of which virtual databases are formed, for example, virtual database 1, virtual database 2, virtual database 3 ... virtual database N (respectively 615, 620, 625, 630), in a particular case , which are part of the database 145. Virtual databases are formed depending on the data layers for the respective users (and / or user groups, including super-user groups), in particular, by means of RLS-policies, each such virtual database containsdata (for example, directories, decoupling tables, documents, totals) available to specific users (or groups of users, including super-user groups) of a particular enterprise (client), and virtual databases can also be generated in which data is accessible to everyone to users. as described in the framework of the present invention. It is worth noting that super-users have access (for reading and changing) to all data in all virtual databases. It should be noted that data for at least one virtual database is available to super-users (for example, data from two, three, etc. virtual databases). So, for example, all data of virtual database 1 (615) and all data of virtual database 2 (620) are available to super-user group 1 (642), all data of virtual database 1 (615) is available to super-user group 2 (644) ) and all the data of the virtual database 3 (625), the group of super-users N (646) is available all the data of the virtual database 3 (625) and all the data of the virtual database N (630). Also, in the particular case, only one virtual database data is available to one user group, for example, only virtual database 1 (615) data is available to user group 1 (651), only virtual database 2 data is available to user group 2 (652) ( 620), user group 3 (653) can only access virtual database data 3 (625), user group N (654) can only access data from virtual database 3 (630), etc.

It is worth noting that in a particular case, virtual databases contain the same metadata (for example, metadata (226, FIG. 2)) as the database from which data virtual databases are generated. So, for example, virtual database 1 (615) contains all or part of database metadata 145, virtual database 2 (620) contains all or part of database metadata 145, virtual database 3 (625) contains all or part of database metadata 145, virtual database N (630) contains all or part of the metadata of database 145, etc. It is also worth noting that each virtual database may contain all database data 145 or part of the metadata related to the data of a particular virtual database.

It is worth noting that virtual database 1 (615) contains data (data sets) related to data layer 1 (i.e., data sets have identifiers of data layer 1); virtual database 2 (620) contains data (data sets) related to data layer 2; virtual database 3 (625) contains data (data sets) related to data layer 3, etc .; the virtual database N (630) contains data (data sets) related to the data layer N.

FIG. 7 illustrates an exemplary embodiment of a virtual database hierarchy according to the present invention. In FIG. 7, database 145 may contain at least one virtual database, for example, may contain virtual database 1 (615), virtual database 2 (620), virtual database 3 (625), and other virtual databases, children for which can be other virtual databases, for example, virtual database 0 (710). So, for example, virtual database 0 (710) can be a child of virtual database 1 (615) and / or for virtual database 2 (620) and / or for virtual database 3 (625). So, for example, virtual database 1 (615) can be formed for a user or group of users (in particular, user group 1 (651) of enterprise 1), virtual database 2 (620) can be formed for a user or group of users (in in particular, user groups 2 (652) enterprises 2), a virtual database 3 (625) can be formed for a user or group of users (in particular, user groups 3 (652) enterprises 3 and / or enterprises 2), and a virtual database 0 (710), in which data is stored, about s for user group 1 and user group 2.

Because shown in FIG. 7 virtual database 0 (710) is a child of virtual database 1 (615), for virtual database 2 (620) and for virtual database 3 (625), then:

- user group 1 (651) has access to virtual database 1 (615), in particular, to data from virtual database 1 (615), and has access to virtual database 0 (715), in particular, to virtual database data data 0 (710);

- user group 2 (652) has access to virtual database 2 (620), in particular, to data from virtual database 2 (620), and has access to virtual database 0 (710), in particular, to virtual database data data 0 (710);

- the user group 3 (653) has access to the virtual database 3 (625), in particular, to the data of the virtual database 3 (625), and has access to the virtual database 0 (710), in particular, to the data of the virtual database data 0 (710);

- a group of super-users 1 (642) has access to virtual database 1 (615), in particular, to data from virtual database 1 (615), and to virtual database 2 (620), in particular, to data from a virtual database data 2 (620), and has access to virtual database 0 (710), in particular, to data from virtual database 0 (710);

- the group of super-users 2 (644) has access to virtual database 1 (615), in particular, to the data of virtual database 1 (615), and to virtual database 3 (625), in particular, to data of the virtual database data 3 (625), and has access to virtual database 0 (710), in particular, to data from virtual database 0 (710).

It is worth noting that virtual database 1 (615) contains data (data sets) related to data layer 1; virtual database 2 (620) contains data (data sets) related to data layer 2; virtual database 3 (625) contains data (data sets) related to data layer 3; virtual database 0 (710) contains data (data sets) related to layer 0. It is also worth noting that data layer 0 is a child of data layer 2.

FIG. 8 illustrates an embodiment of the present invention. In step 808, a directory of data layers stored in at least one table is created in the database 145 of the (primary) server database 110 of the remote server (server of the ERP system) 112. It should be noted that the directory of data layers contains the names of all data layers, and also contains identifiers (values) of data layers for data sets stored in the metadata objects of the business logic of the application server of the enterprise resource planning system server (ERP system). As mentioned above, metadata objects are directories, documents, tabular parts of documents, totals and linking tables of directories stored in the database tables 145 of the main database server 110 associated with the server of the ERP system 112. It should be noted that the identifier of the data layer defines the data set belongs to the corresponding data layer.

In step 818, a data layer user linking table is created in the database 145 of the (primary) database server 110 of the ERP system server 112. It should be noted that the user data linking user table stores the relationships between users and data layers in the form of stored associated user identifiers and their corresponding data layer identifiers from the available data layer identifiers of the data layer directory, and the relationships between users and data layers determine the availability of data sets s for reading and modification of users associated data layers.

In step 828, a linking table of the child data layers is created in the database 145 of the (primary) database server 110 of the ERP system server 112. It should be noted that the hierarchy between the data layers is stored in the link table of the child data layers in the form of stored associated identifiers for the child layers data and identifiers for parent data layers, and the relationship between parent layers and child layers determines the availability of data sets of child data layers for users to read parent layers of data s.

In step 838, a row-level security policy (RLS policy) is created for each of the database server database tables that store directories, documents, tabular parts of documents, totals and linking reference tables, and RLS policies are stored in the database database server data, and the RLS policy contains at least one expression that returns a logical result that determines the availability of data sets from the database server database tables to the user, depending on the identity data layer cathors for such data sets.

In step 848, the application server 120, when adding the data set to the directory, establishes (defines, identifies) the identifier of the data layer for the added data set and saves the added data set to the database of the database server with saving the set layer identifier for such a data set.

In step 858, the application server 120, when adding the data set to the linking table, sets the data layer identifier for the data set to be added and saves the data set to be added to the database server database while preserving the set layer identifier for such a data set.

It is worth noting that when adding at least one data set, at least one decoupling table of the directory and / or at least one other directory other than the directory of directory exclusions, checks for the presence of the directory in which Adding a dataset to the directory exceptions directories. Also, the presence of a data layer identifier in the added data set is checked for the added data set when the absence of the directory is found in the directory of directory exclusions. When the absence of a data layer identifier for the data being added is added to the data set, the data layer identifier is obtained from the data layer user linking table and the obtained data layer identifier is added to the data set to be added in the directory. Next, a check is made to add a data set to the database by a user or a task executed by program code. Moreover, when adding a dataset by a user, assignment, in a particular case, is added, of the identifier of the data layer obtained from the user data linking table to the unsaved data sets of the linking tables associated with the added data set. Also, in the case of adding a data set by a task executed by the program code, the identifier of the data layer obtained from the added data set is assigned to the unsaved data sets of the linking tables associated with the added data set.

In step 868, the application server 120, when adding the document, sets the identifier of the data layer for at least one added data set contained in the added document, and stores the added document and at least one document data set in the database of the database server data with saving for the saved data set of the document of the set layer identifier.

It is worth noting that when adding a document to the database of the database server, it checks the presence of the type of the added document in the directory of exceptions for document types. Also, when establishing the absence of the type of the added document in the directory of exceptions of document types, the presence of the identifier of the data layer for the data of the added document is checked in the added document. When the absence of a data layer identifier in the added document is established, the data layer identifier is obtained from the data layer user linking table and the obtained identifier is added to the added document. Next, the implementation of adding a document to the database by a user or a task executed by program code is checked. Moreover, when a document is added to the database by the user, the identifier of the data layer obtained from the added document is assigned to the unsaved data sets of tabular parts associated with the added document. If a document is added to the database by a task executed by program code, the identifier of the data layer, added to the document from the data layers obtained from the user linking table, is assigned to the unsaved data sets of the table parts associated with the added document.

In step 878, the application server 120, when adding the data set to the tabular part of the document, sets the data layer identifier for the data set to be added and stores the data set to be added to the database server database, while preserving the set layer identifier for the data set.

In step 898, the remote server (ERP system server) 112, by applying the RLS policy executed by the database server to queries made to data sets from the database server database, provides the user with access to data sets and data sets for reading and modifications, the identifiers of the data layers of which are associated with the user identifier in the linking table of users of the data layers when querying data from the database of the database server by the user using ientskogo application installed on the user computing device associated with the server system 112 ERP-server applications.

In step 899, the remote server (server of the ERP system) 112 provides access to the user of the parent layer to the data sets and data sets of the child data layer for reading, without the possibility of their modification, by applying the RLS policy executed by the database server to requests made to datasets from the database server database using data layer identifiers for child data layers and data layer identifiers for parent data layers from a child table linking table s data for establishing communications between a parent data layer and at least one subsidiary data layer in the implementation of the data request from the database of the database server data user using a client application installed on a user's computing device associated with the server application server ERP-system.

In FIG. 9 shows an example of a general-purpose computer system that includes a multi-purpose computing device in the form of a computer 20 or a server, or a module of the system described in the present invention, including a processor 21, system memory 22, and system bus 23 that couples various system components, including system memory with 21 processor.

The system bus 23 may be any of various types of bus structures, including a memory bus or memory controller, a peripheral bus, and a local bus using any of a variety of bus architectures. System memory includes read-only memory (ROM) 24 and random access memory (RAM) 25. The ROM 24 stores the basic input / output system 26 (BIOS), consisting of basic routines that help exchange information between elements within the computer 20, for example, at the time of launch.

Computer 20 may also include a hard disk drive 27 for reading from and writing to a hard disk, not shown, a magnetic disk drive 28 for reading from or writing to a removable magnetic disk 29, and an optical disk drive 30 for reading from or recording to a removable optical disc 31 such as a compact disc, a digital video disc, and other optical means. The hard disk drive 27, the magnetic disk drive 28, and the optical disk drive 30 are connected to the system bus 23 by means of the hard disk drive interface 32, the magnetic disk drive interface 33, and the optical drive interface 34, respectively. Storage devices and their respective computer-readable means provide non-volatile storage of computer-readable instructions, data structures, program modules and other data for computer 20.

Although the typical configuration described here uses a hard disk, a removable magnetic disk 29, and a removable optical disk 31, one skilled in the art will appreciate that other types of computer-readable media that can store data that are accessible by a computer may also be used in a typical operating environment. such as magnetic cassettes, flash memory cards, digital video disks, Bernoulli cartridges, random access memory (RAM), read-only memory (ROM), etc.

Various software modules, including operating system 35, may be stored on a hard disk, magnetic disk 29, optical disk 31, ROM 24, or RAM 25. Computer 20 includes a file system 36 associated with or included with the operating system 35 or more software application 37, other program modules 38, and program data 39. A user may enter commands and information into computer 20 using input devices such as a keyboard 40 and pointing device 42. Other input devices (not shown) may include be a microphone, joystick, gamepad, satellite dish, scanner, or any other.

These and other input devices are connected to the processor 21 often through a serial port interface 46 that is connected to the system bus, but can be connected via other interfaces, such as a parallel port, game port, or universal serial bus (USB). A monitor (display) 47 or other type of visual display device is also connected to the system bus 23 via an interface, for example, a video adapter 48. In addition to the monitor 47, personal computers typically include other peripheral output devices (not shown), such as speakers and printers.

Computer 20 may operate in a networked environment through logical connections to one or more remote computers 49. The remote computer (or computers) 49 may be another computer, server, router, network PC, peer to peer device, or other node on a single network, and typically also includes includes most or all of the elements described above with respect to computer 20, although only an information storage device 50 is shown. Logical connections include a local area network (LAN) 51 and a global computer network (GCS) ) 52. Such network environments are usually common in institutions, corporate computer networks, and the Internet.

The computer 20 used in the LAN network environment is connected to the local area network 51 via a network interface or adapter 53. The computer 20 used in the GC network environment typically uses a modem 54 or other means to establish communication with the global computer network 52, such as the Internet.

The modem 54, which may be internal or external, is connected to the system bus 23 via the serial port interface 46. In a networked environment, program modules or parts thereof described with reference to computer 20 may be stored on a remote information storage device. It should be noted that the network connections shown are typical, and other means may be used to establish communication communication between computers.

Below is an example code of the mentioned package “PACK_DATA_LAYERS”:

"

CREATE OR REPLACE PACKAGE ULTIMA.PACK_DATA_LAYERS AS

    TYPE DATA_LAYER_ID IS RECORD (DATA_LAYER_ID DATA_LAYERS.ID% type);

    TYPE DATA_LAYER_LIST IS TABLE OF DATA_LAYER_ID;

    - The function returns the data layer code for the specified user

FUNCTION GET_USER_DATA_LAYER (vUserID KERNEL.USERS.ID% type) RETURN DATA_LAYERS.ID% type;

    - The function returns the data layer code for the current user

    FUNCTION GET_CURR_USER_DATA_LAYER RETURN DATA_LAYERS.ID% type;

    - The function returns a set of all data layers (including dependent data layers) for the specified user

FUNCTION GET_USER_DATA_LAYERS (vUserID KERNEL.USERS.ID% type) RETURN DATA_LAYER_LIST pipelined;

    - The function returns a set of all data layers (including dependent data layers) for the current user

FUNCTION GET_CURR_USER_DATA_LAYERS RETURN DATA_LAYER_LIST pipelined;

    - Procedure for recalculating user data layers when a user changes in a client (enterprise)

    PROCEDURE RECALC_CLIENT_USER_BY_ROW (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vCLIENT_ID CLIENT_TO_USERS.CLIENT_ID% type, vUSER_ID CLIENT_TO_USERS.USER_ID% type);

    - Procedure for recalculating user data layers when changing a client (enterprise) in the data layer

    PROCEDURE RECALC_CLIENT_DL_BY_ROW (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vDATA_LAYER_ID DATA_LAYERS.ID% type, vCLIENT_ID DATA_LAYERS.CLIENT_ID% type);

    - Procedure for recalculating a set of users when inserting a node into a tree of data layers

    PROCEDURE RECALC_DL_USER_EXT_NODE_INS (vPARENT_ID DATA_LAYERS.ID% type, vCHILD_ID DATA_LAYERS.ID% type, vRECURSION_COUNTER NUMBER);

    - Procedure for recounting a set of users when deleting a node from the data layer tree

    PROCEDURE RECALC_DL_USER_EXT_NODE_DEL (vCHILD_ID DATA_LAYERS.ID% type, vRECURSION_COUNTER NUMBER);

        FUNCTION GET_DL_RLS_PREDICATE (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vALL_USER_LAYERS NUMBER) RETURN VARCHAR2;

END PACK_DATA_LAYERS;

CREATE OR REPLACE PACKAGE BODY ULTIMA.PACK_DATA_LAYERS AS

    CTX_HAS_KEYMAKER_PERM NUMBER: = -1;

    CTX_HAS_KEYMAKER_EDIT_PERM NUMBER: = -1;

    FUNCTION GET_USER_DATA_LAYER (vUserID KERNEL.USERS.ID% type) RETURN DATA_LAYERS.ID% type IS

        vDataLayerID DATA_LAYERS.ID% type: = 0;

    BEGIN

        select DL.ID into vDataLayerID

        from CLIENT_TO_USERS CU

            join DATA_LAYERS DL on DL.CLIENT_ID = CU.CLIENT_ID

        where CU.USER_ID = vUserID;

        return nvl (vDataLayerID, 0);

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

        IF vUserID IS NOT NULL THEN

            RAISE_APPLICATION_ERROR (-20000, 'Data layer for user #' || vUserID || 'not found.');

        ELSE

            RAISE_APPLICATION_ERROR (-20000, 'User not specified.');

        END IF;

    END GET_USER_DATA_LAYER;

    FUNCTION GET_CURR_USER_DATA_LAYER RETURN DATA_LAYERS.ID% type IS

    BEGIN

        RETURN GET_USER_DATA_LAYER (KERNEL.GET_UID ());

    END GET_CURR_USER_DATA_LAYER;

    FUNCTION GET_USER_DATA_LAYERS (vUserID KERNEL.USERS.ID% type) RETURN DATA_LAYER_LIST pipelined IS

    BEGIN

        for curr in

        (

            SELECT UDL.DATA_LAYER_ID

            FROM DATA_LAYER_USERS UDL

            WHERE UDL.USER_ID = vUserID

        )

        loop

            pipe row (curr);

        end loop;

    END GET_USER_DATA_LAYERS;

    FUNCTION GET_CURR_USER_DATA_LAYERS RETURN DATA_LAYER_LIST pipelined IS

    BEGIN

        for curr in

        (

            SELECT UDL.DATA_LAYER_ID

            FROM DATA_LAYER_USERS UDL

            WHERE UDL.USER_ID = KERNEL.GET_UID ()

        )

        loop

            pipe row (curr);

        end loop;

    END GET_CURR_USER_DATA_LAYERS;

    - Procedure for recalculating a set of users when a user associates with a data layer (assigning a data layer identifier to a user, linking user identifiers and data layer identifier)

    PROCEDURE RECALC_DL_USER_INS (vDATA_LAYER_ID DATA_LAYER_USERS.DATA_LAYER_ID% type, vUSER_ID DATA_LAYER_USERS.USER_ID% type, vRECURSION_COUNTER NUMBER) IS

        vTMP_NUMBER NUMBER (18): = 0;

        CURSOR CHILD_DATA_LAYERS (vPARENT_ID DATA_LAYERS.ID% type) IS

            SELECT DLT.CHILD_ID

            FROM DATA_LAYER_TREE_NODES DLT

            WHERE DLT.PARENT_ID = vPARENT_ID;

    BEGIN

        IF vRECURSION_COUNTER> 200 THEN

            RAISE_APPLICATION_ERROR (-20021, 'Too long recursion');

        END IF;

        SELECT COUNT (*) INTO vTMP_NUMBER

        FROM DATA_LAYER_USERS RPE

        WHERE RPE.DATA_LAYER_ID = vDATA_LAYER_ID AND RPE.USER_ID = vUSER_ID;

        IF vTMP_NUMBER = 0 THEN

            - insert a line for the current role if there is none

            INSERT INTO DATA_LAYER_USERS (DATA_LAYER_ID, USER_ID)

            VALUES (vDATA_LAYER_ID, vUSER_ID);

        END IF;

        - passage through subsidiaries

        FOR CHILD_REC IN CHILD_DATA_LAYERS (vDATA_LAYER_ID) LOOP

            SELECT COUNT (*) INTO vTMP_NUMBER

            FROM DATA_LAYER_USERS UDL

            WHERE UDL.DATA_LAYER_ID = CHILD_REC.CHILD_ID AND UDL.USER_ID = vUSER_ID;

            --check for revocation of the right in the role itself

            IF vTMP_NUMBER = 0 THEN

                RECALC_DL_USER_INS (CHILD_REC.CHILD_ID, vUSER_ID, vRECURSION_COUNTER + 1);

            END IF;

        END LOOP;

    END

    - Procedure for recounting user recruitment at deletion

    PROCEDURE RECALC_DL_USER_DEL (vDATA_LAYER_ID DATA_LAYER_TO_USERS.DATA_LAYER_ID% type, vUSER_ID DATA_LAYER_TO_USERS.USER_ID% type, vRECURSION_COUNTER NUMBER) IS

    BEGIN

        IF vRECURSION_COUNTER> 200 THEN

            RAISE_APPLICATION_ERROR (-20021, 'Too long recursion');

        END IF;

        DELETE FROM DATA_LAYER_USERS RPE

        WHERE RPE.USER_ID = vUSER_ID;

    END

    - A procedure for recalculating a set of users when changing specified dimensions.

    PROCEDURE RECALC_DL_USER_EXT_BY_ROW (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vDATA_LAYER_ID DATA_LAYER_USERS.DATA_LAYER_ID% type, vUSER_ID DATA_LAYER_USERS.USER_ID% type)

    BEGIN

        IF vROW_OPERATION_ID = 2 THEN

            RECALC_DL_USER_INS (vDATA_LAYER_ID, vUSER_ID, 0);

        ELSIF vROW_OPERATION_ID = 4 THEN

            - when updating the operation, deletion is performed

            RECALC_DL_USER_EXT_BY_ROW (8, vDATA_LAYER_ID, vUSER_ID);

            - and add

            RECALC_DL_USER_EXT_BY_ROW (2, vDATA_LAYER_ID, vUSER_ID);

        ELSIF vROW_OPERATION_ID = 8 THEN

            RECALC_DL_USER_DEL (vDATA_LAYER_ID, vUSER_ID, 0);

        END IF;

    END

    - Procedure for recalculating a set of users when adding (inserting) a node to the tree of data layers

    PROCEDURE RECALC_CLIENT_DL_BY_ROW (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vDATA_LAYER_ID DATA_LAYERS.ID% type, vCLIENT_ID DATA_LAYERS.CLIENT_ID% type) IS

        CURSOR CLIENT_USERS IS

        SELECT CU.USER_ID

        FROM CLIENT_TO_USERS CU

        WHERE CU.CLIENT_ID = vCLIENT_ID;

    BEGIN

        FOR CLIENT_USER IN CLIENT_USERS LOOP

            RECALC_DL_USER_EXT_BY_ROW (vROW_OPERATION_ID, vDATA_LAYER_ID, CLIENT_USER.USER_ID);

        END LOOP;

    END

    - Procedure for recalculating user data layers when a user changes in a client (enterprise)

    PROCEDURE RECALC_CLIENT_USER_BY_ROW (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vCLIENT_ID CLIENT_TO_USERS.CLIENT_ID% type, vUSER_ID CLIENT_TO_USERS.USER_ID% type) IS

        vTMP_NUMBER_ID NUMBER (18): = 0;

        vDATA_LAYER_ID NUMBER (18): = 0;

    BEGIN

        SELECT COUNT (*) INTO vTMP_NUMBER_ID

        FROM DATA_LAYERS DL WHERE DL.CLIENT_ID = vCLIENT_ID;

        IF vTMP_NUMBER_ID = 1 THEN

            SELECT DL.ID INTO vDATA_LAYER_ID

            FROM DATA_LAYERS DL WHERE DL.CLIENT_ID = vCLIENT_ID;

            RECALC_DL_USER_EXT_BY_ROW (vROW_OPERATION_ID, vDATA_LAYER_ID, vUSER_ID);

        END IF;

    END

    - Procedure for recalculating a set of users when adding (inserting) a node to the tree of data layers

    PROCEDURE RECALC_DL_USER_EXT_NODE_INS (vPARENT_ID DATA_LAYERS.ID% type, vCHILD_ID DATA_LAYERS.ID% type, vRECURSION_COUNTER NUMBER) IS

        CURSOR CHILD_DATA_LAYERS (vPARENT_ID DATA_LAYERS.ID% type) IS

            SELECT CHILD_ID

            FROM DATA_LAYER_TREE_NODES RT

            WHERE RT.PARENT_ID = vPARENT_ID;

    BEGIN

        IF vRECURSION_COUNTER> 200 THEN

            RAISE_APPLICATION_ERROR (-20021, 'Too long recursion');

        END IF;

        INSERT INTO DATA_LAYER_USERS (DATA_LAYER_ID, USER_ID)

        SELECT vCHILD_ID, RPE.USER_ID

        FROM DATA_LAYER_USERS RPE

        WHERE RPE.DATA_LAYER_ID = vPARENT_ID AND RPE.USER_ID NOT IN (

            - what the child data layer already has

            SELECT RPE.USER_ID

            FROM DATA_LAYER_USERS RPE

            WHERE RPE.DATA_LAYER_ID = vCHILD_ID

        );

        FOR CHILD_REC IN CHILD_DATA_LAYERS (vCHILD_ID) LOOP

            RECALC_DL_USER_EXT_NODE_INS (vPARENT_ID, CHILD_REC.CHILD_ID, vRECURSION_COUNTER + 1);

        END LOOP;

    END

    - Procedure for recounting a set of users when deleting a node from the data layer tree

    PROCEDURE RECALC_DL_USER_EXT_NODE_DEL (vCHILD_ID DATA_LAYERS.ID% type, vRECURSION_COUNTER NUMBER) IS

        CURSOR CHILD_DATA_LAYERS (vPARENT_ID DATA_LAYERS.ID% type) IS

            SELECT CHILD_ID

            FROM DATA_LAYER_TREE_NODES RT

            WHERE RT.PARENT_ID = vPARENT_ID;

    BEGIN

        IF vRECURSION_COUNTER> 200 THEN

            RAISE_APPLICATION_ERROR (-20021, 'Too long recursion');

        END IF;

        DELETE FROM DATA_LAYER_USERS RPE

        WHERE RPE.DATA_LAYER_ID = vCHILD_ID AND RPE.USER_ID NOT IN (

            SELECT DISTINCT USER_ID FROM

            (

                SELECT CHILD_P.USER_ID FROM DATA_LAYER_USERS CHILD_P

                WHERE CHILD_P.DATA_LAYER_ID IN (

                    SELECT PARENT_ID

                    FROM DATA_LAYER_TREE_NODES RT

                    WHERE RT.CHILD_ID = vCHILD_ID

                )

                UNION ALL

                SELECT CU.USER_ID

                FROM DATA_LAYERS DL

                    join CLIENT_TO_USERS CU on DL.CLIENT_ID = CU.CLIENT_ID

                WHERE DL.ID = vCHILD_ID

            )

        );

        FOR CHILD_REC IN CHILD_DATA_LAYERS (vCHILD_ID) LOOP

            RECALC_DL_USER_EXT_NODE_DEL (CHILD_REC.CHILD_ID, vRECURSION_COUNTER + 1);

        END LOOP;

    END

    FUNCTION HAS_KEYMAKER_PERMISSION RETURN NUMBER IS

    BEGIN

        IF CTX_HAS_KEYMAKER_PERM = -1 THEN

            SELECT COUNT (*) INTO CTX_HAS_KEYMAKER_PERM

            FROM KERNEL.ROLE_PERMISSIONS_EXT P

                JOIN KERNEL.USERS U ON U.ROLE_ID = P.ROLE_ID

            WHERE U.ID = KERNEL.GET_UID AND P.PERMISSION_ID = 77;

        END IF;

        RETURN CTX_HAS_KEYMAKER_PERM;

    END

    FUNCTION HAS_KEYMAKER_EDIT_PERMISSION (vUSER_ID NUMBER) RETURN NUMBER IS

    BEGIN

        IF CTX_HAS_KEYMAKER_EDIT_PERM = -1 OR vUSER_ID = 110 THEN

            SELECT COUNT (*) INTO CTX_HAS_KEYMAKER_EDIT_PERM

            FROM KERNEL.ROLE_PERMISSIONS_EXT P

                JOIN KERNEL.USERS U ON U.ROLE_ID = P.ROLE_ID

            WHERE U.ID = KERNEL.GET_UID AND P.PERMISSION_ID = 87;

        END IF;

        RETURN CTX_HAS_KEYMAKER_EDIT_PERM;

    END

    FUNCTION GET_DL_RLS_PREDICATE (vROW_OPERATION_ID KERNEL.ACC_OPERATIONS.ID% type, vALL_USER_LAYERS NUMBER) RETURN VARCHAR2 IS

        vUSER_ID NUMBER (18): = 0;

    BEGIN

        vUSER_ID: = KERNEL.GET_UID;

        IF HAS_KEYMAKER_EDIT_PERMISSION (vUSER_ID) = 1 OR vUSER_ID = 13 OR vUSER_ID = 14 OR vUSER_ID = 15 / * OR vUSER_ID = 110 * / THEN

            RETURN NULL;

        END IF;

        IF vROW_OPERATION_ID = 1 AND HAS_KEYMAKER_PERMISSION = 1 THEN

            RETURN NULL;

        END IF;

        / * IF vALL_USER_LAYERS = 1 THEN

            RETURN 'DATA_LAYER_ID in (select DATA_LAYER_ID from TABLE (PACK_DATA_LAYERS.GET_CURR_USER_DATA_LAYERS))';

        ELSE

            RETURN 'DATA_LAYER_ID = PACK_DATA_LAYERS.GET_CURR_USER_DATA_LAYER';

        END IF; * /

        IF vALL_USER_LAYERS = 1 THEN

            RETURN 'DATA_LAYER_ID in (SELECT UDL.DATA_LAYER_ID FROM DATA_LAYER_USERS UDL WHERE UDL.USER_ID =' || to_char (vUSER_ID) || ')';

        ELSE

            RETURN 'DATA_LAYER_ID in (select DL.ID

                from CLIENT_TO_USERS CU

                    join DATA_LAYERS DL on DL.CLIENT_ID = CU.CLIENT_ID

                where CU.USER_ID = '|| to_char (vUSER_ID) || ')';

        END IF;

    END GET_DL_RLS_PREDICATE;

END PACK_DATA_LAYERS;

"

In conclusion, it should be noted that the information provided in the description are examples that do not limit the scope of the present invention defined by the claims. One skilled in the art will recognize that there may be other embodiments of the present invention consistent with the spirit and scope of the present invention.

Claims (25)

1. A method for restricting access to data in a database, in which: - creating a directory of data layers stored in at least one table in the database of the database server, and the directory of data layers contains at least the names of all data layers and contains the identifiers of the data layers for the data sets stored in objects metadata of the business logic of the application server of the enterprise resource planning system (ERP-system) server, and the metadata objects are directories, documents, tabular parts of documents, totals and linking tables of directories, saved data in the database tables of the database server associated with the server of the ERP system, where the identifier of the data layer determines whether the data set belongs to the corresponding data layer; - creating a linking table of users of data layers in the database of the database server, and in the linking table of users of data layers, relations between users and data layers are stored in the form of stored associated user identifiers and corresponding data layer identifiers from available data layer identifiers of the data layer directory, moreover, the relations between users and data layers determine the availability of data sets for reading and modification to users related to and data layers; - the linking table of the child data layers is created in the database of the database server, and the hierarchy between the data layers is stored in the link table of the child data layers in the form of stored linked identifiers for the child data layers and identifiers for the parent data layers, and the relationship between the parent layers and the child layers determines the availability of data sets of child data layers for users to read parent data layers; - security policy is created at the row level (RLS policies) for each of the database server database tables, which store directories, documents, tabular parts of documents, totals and linking reference tables, and RLS policies are saved in the server database database, and the RLS-policy contains at least one expression that returns a logical result that determines the availability of data sets from the database tables of the database server to the user, depending on the identifiers data layers for such data sets; - when adding the data set to the directory, the application server establishes the identifier of the data layer for the added data set and saves the added data set to the database of the database server with saving the set layer identifier for such a data set; - when adding the data set to the linking table, the application server establishes the identifier of the data layer for the data set to be added and saves the added data set to the database of the database server with saving the set layer identifier for such a data set; - when adding a document, the application server sets the identifier of the data layer for at least one added data set contained in the added document, and the added document and at least one document data set are saved to the database server database for the saved document dataset of the set layer identifier; - when adding the data set to the tabular part of the document, the application server sets the identifier of the data layer for the data set to be added and saves the data set to be added to the database of the database server with saving the set layer identifier for such a data set; - the server of the ERP system, by applying the RLS policy executed by the database server to requests made to data sets from the database of the database server, provides the user with access to data sets and data sets for reading and modification, the identifiers of the data layers of which are connected with the user identifier in the data layers user linking table when querying data from the database server database by the user using the client application, install data on the user's computing device connected to the application server of the ERP system server; - the server of the ERP system provides access to the user of the parent layer to the data sets and data sets of the child data layer for reading, without the possibility of modification, by applying the RLS policy executed by the database server to requests made to data sets from the server database database using data layer identifiers for child data layers and data layer identifiers for parent data layers from the linking table of child data layers to establish communication data between the parent layer and at least one subsidiary data layer in the implementation of the data request from the database server database data user using a client application installed on a user's computing device associated with the server application server ERP-system. 2. The method according to claim 1, characterized in that it creates a directory of directory exclusions containing directories whose data sets do not contain data layer identifiers, and when adding at least one data set, at least one decoupling table of the directory and / or, at least in one other directory, different from the directory of directory exclusions, is carried out: - checking the availability of the directory to which the data set is being added in the directory of directory exclusions; - verification of the presence in the added data set of the identifier of the data layer for the added data set when establishing the absence of the directory in the directory of exceptions of directories; - obtaining the data layer identifier from the linking table of data layer users and adding the obtained data layer identifier to the added data set in the directory when it is found that the data layer has no identifier for the data layer for the added data; - verification of the addition of a data set to the database by a user or a task executed by program code; - assignment of the identifier of the data layer obtained from the data linking user table to the unsaved data sets of the linking tables associated with the data set to be added when the user data set is added; - Assigning the identifier of the data layer obtained from the added data set to unsaved data sets of the linking tables associated with the added data set when adding the data set to a task executed by program code. 3. The method according to claim 1, characterized in that a directory of exceptions of document types is created containing documents whose data sets do not contain identifiers of data layers, and when a document is added to the database of the database server, it is carried out: - checking the presence of the type of the added document in the directory of exceptions for document types; - checking the presence in the added document of a data layer identifier for the data of the added document when establishing the absence of the type of the added document in the directory of exceptions of document types; - obtaining the identifier of the data layer from the linking table of users of the data layers and adding the obtained identifier to the added document when establishing the absence of the identifier of the data layer in the added document; - verification of the implementation of adding a document to the database by a user or a task executed by program code; - Assigning the identifier of the data layer obtained from the added document to unsaved datasets of the table parts associated with the added document when the user adds the document to the database; - assignment of the identifier of the data layer added to the document from the data layers obtained from the user linking table to the unsaved datasets of the table parts associated with the added document when adding the document to the database by a task executed by program code.

Images