Approach to the separation of schemes (users) in the design of OLTP databases

Problems and purpose:

Schema separation is basically implemented for scalability and security:

• Scalability from the point of view of databases should be such that the scheme can be moved to another database without damage to the functionality.
• From a database point of view, security should be such that external users operate only with business logic to which grants are distributed and do not have access to primary data.

The problem is that if we give grants for data modification not by the owner of the tables (primary data), we get a layer of problems:

• The problem with scalability - there is no possibility to spread the schemes in different databases.
• The problem with security is that when accessing one scheme, we get access to the primary data of another scheme.
• The problem with the transparency of the behavior of the system - It is not possible to understand where the data of the scheme is modified, which leads to unpredictable system behavior.

Problem solving method:

Solving problems with scalability / security / transparency of system behavior will help us:

')

• DML encapsulation inside the schema - all transaction management, as well as DML operations, is carried out within the framework of the table owner scheme.

Description of the proposed method architecture:

Figure 1 - The interaction of the schemes between each other



From the figure it is seen that the schemes interact between each other through GATE_PACKAGE or gate packets.



There are 2 types of gating packages:

• gate_package_in - for incoming requests to the scheme.
• gate_package_out- for outgoing requests from a scheme to another scheme.

All data changes are made within the schema; direct DML (insert, update, delete) does not occur from another schema (not the owner of the table) to the table.

Calling from another's scheme to any methods / constants / types and other objects is also made via gate packets.



This interaction allows you to have one exit and entry point from / into the scheme, and in the case of exploding the schemes into different databases, we get the following interaction scheme:







Figure 2 - The interaction of schemes when they are posted in different databases.



It can be seen from the figure that when dividing the schemes into different databases, we support the principle of scaling, i.e. 2 schemes were distributed to different databases without damage to the main functionality.

Imagine what problems could arise when direct DML from one scheme to another.

Below is a drawing of the distribution of grants in the interaction of 2 schemes: support for the current structure of scaling, security, and the elimination of uncertainty in terms of system behavior:







Figure 3 - The scheme of distribution of grants in the interaction of 2 schemes.



From the figure it is clear that we have banned DML operations insert, update, delete, as well as execute on objects of one schema to another, except in_gate_package.



We allow launching from gates packages, as well as requests from plates of another scheme are possible, only in case of problems with performance and the need to use plates within large queries.



Ideally, in the future, to prohibit the select grant with respect to the tables of another scheme and use only the methods of their gating package.



With such approaches (Figure 1, 2, 3), we get:

• Solving the problem of scalability - There are no problems when the circuits are distributed to different databases.
• The solution to the security problem is the prohibition of data modification and direct access to methods / objects for modifying the primary data not the owner of this data.
• The solution to the problem with the transparency of the behavior of the system - any modification of the data occurs through a single input / output point within one scheme.