1.Preamble.
There was a need to organize accounting for two organizations in one information security system. The situation is not unique, but it so happened that our very non-standard 250 gigabyte USB drive worked quite slowly, so instead of RLS we decided to try data separation. What it is is described, for example, or. In short, if RLS supplements with conditions SQL queries, then the data separator is an additional column in tables at the DBMS level, due to which the separation mechanism should work faster than RLS.
So, to the database where records were kept for LLC No. 1, it is necessary to transfer information from the separate database of LLC No. 2 and organize joint work. Just like in the picture:
Mere mortals work only with their own LLC, and the chief accountant sometimes looks at data for two legal entities. In the access mode to both LLCs, you can only read the data, so the chief accountant should be able to interactively switch between the “read all”/“write only for one organization” modes and select the LLC (i.e., set the value of the common details) to carry out, for example, cost calculation.
2. Implementation
Platform 8.2.19.90, without compatibility mode. DBMS - MSSQL Server 2008 R2 Standard.
We created the general attribute OrganizationSeparator of type "number", agreed with the proposal to create session parameters, filled out the composition of the details (included several directories, all documents, accumulation, accounting and calculation registers). Data separation - "Independent and joint". The session parameter value is set from standard settings user in the SettingSessionParameters procedure in the session module:
Organization = UserManagement.GetDefaultValue(chCurrentUser,"PrimaryOrganization");
SessionParameters.OrganizationSeparatorValue = Organization.SeparatorValue;
In the chief accountant’s interface, they made a form with the ability to switch between organizations and turn on/off the separation mode:
When separation is disabled, when SessionParameters.OrganizationSeparatorUsage = False, the platform refuses to write documents, crashing with errors like “SDBL error: expression expected (pos=12)”, so you cannot allow the user to write documents in this option. For reliability, we created subscriptions to the “Before recording” event for objects that are part of the general attribute:
IfSessionParameters.OrganizationSeparatorUsage = False Then
#If Client Then
Warning("Cannot write because data sharing is disabled!");
#EndIf
Refuse = True;
endIf;
Our action plan was as follows: prepare the receiver configuration of IS No. 1, set the values of the general attribute = 1, load data from IS No. 2, after loading, for all objects with an empty (equal to 0) separator value, set OrganizationSeparator = 2.
The configuration has been prepared, the question arose: how to set the value of the general details for documents and their movements in closed periods, quickly and without the risk that the numbers in the balance sheet will fly? It is impossible to write a separator separately from the object through the 1C object model, so I had to break license agreement get out and write a query for MS SQL. Since there are many objects in the general attribute, and there are even more tables in the table for these objects, we wrote a processing that generates a query for SQL (for each metadata object included in the separator, we wrote "update" + DB_Name + ".dbo._" + TableName + "set _" + FieldGeneralAttributes + "= 1";)
We entered the value, transferred some of the data from IS No. 2, and began testing.
The result was disappointing. Firstly, problems with the accounting register. When separation is enabled, the analyst is not visible:
This is due to the fact that the accounting register at the DBMS level is stored as several tables, and not all tables had the value of the general attribute entered (processing was used to view the structure).
Okay, let’s enter the separator value using MS SQL, and we’ll see the analytics. Reports no longer work. It turns out that there are problems with queries to the virtual tables of the accounting register “Turnover” and “TurnoverDtKt”:
(Fld27033 is just a general attribute in the accounting register table)
The separator is installed in all tables, this is visible at the DBMS level, it is not clear what the error may be. We deploy a standard empty SCP, make the configuration changes described above, enter a couple of documents (in this option, the platform itself enters the separator value in all accounting register tables), but the errors are reproduced. It’s bad, but we exclude accounting registers from the general details and continue testing.
Further, it turns out that the displacement mechanism of the calculation registers has stopped working. We did not separate the plans for calculation types; we are trying to look for the problem in the tables of the calculation register and in recalculations. We check, enter the value of the main details, do T&I - to no avail.
Along the way, we diagnose the problem when writing information from the list form to independent registers. In this case, the data is recorded and can be seen after a restart. The problem is also reproduced on the test base:
It was not possible to “fix” the information registers by manipulating with SQL (the separator value in all tables is set), so we simply excluded them from the general attributes. After several days of experimentation, attempts to restore the functionality of preemption also turn out to be unsuccessful.
At this point, we decide to turn off data separation and use RLS. When setting the partition to “do not use,” we encounter the errors “Microsoft OLE DB Provider forSQL Server: CREATE UNIQUE INDEX terminated because a duplicate keywas found for index...”. That is, it is not so easy to return to the state before the division. Problem with indexes of conversion tables, settings for storing totals and others. The fact is that the tables store identical rows, differing only in the value of the general attribute. When you delete a common attribute, non-unique records appear. You will have to delete unnecessary records directly in MS SQL, something like this (for the conversion table):
Use base;
ALTER TABLE_CRgRecalc1399
ADD id INT IDENTITY(1,1);
GO
DELETE FROM_CRgRecalc1399
WHERE id< (SELECT MAX(id)
FROM _CRgRecalc1399 AS T1
WHERE _CRgRecalc1399._RecorderTRef = T1._RecorderTRef and
_CRgRecalc1399.[_RecorderRRef] = T1.[_RecorderRRef] and
_CRgRecalc1399.[_CalcKindRRef] = T1.[_CalcKindRRef] and
_CRgRecalc1399.[_Fld1400RRef] = T1.[_Fld1400RRef] and
_CRgRecalc1399.[_Fld1401RRef] = T1.[_Fld1401RRef] and
_CRgRecalc1399.[_Fld1402RRef] = T1.[_Fld1402RRef]
);
GO
ALTER TABLE_CRgRecalc1399
DROP COLUMN id;
And only after cleaning several dozen tables is it possible to turn off data separation. After turning off the separation there are no problems.
3. Conclusions.
There was a glimmer of hope that the problems were solved in 8.3. We weren’t too lazy, we checked it on 8.3.4.482 (with compatibility mode disabled). We looked at an almost standard control unit, with changes in the configuration only for general details. On this test base, separation was enabled before information was entered, i.e. the platform had to correctly write the separator value into all tables; they did not write anything directly into MS SQL themselves.
Result:
The problem with queries to the virtual tables "Turnover" and "TurnoverDtKt" is reproduced.
The problem with repression is reproduced.
The problem with writing to independent information registers is reproduced.
The problem with turning off the separation is that you can’t get rid of it with one click of a button!
Thus, we were unable to replace RLS with a new mechanism. This mechanism was apparently conceived for cloud services, and in the option of using shared data “independently”, maybe the division will work, but we need a common master data. It remains to be seen that 1C will correct the errors, or even better, implement a standard mechanism for separating by organization in standard configurations.
Once we discussed mechanisms for restricting user access in 1C and in particular.
It allows the user to work not with all documents, but only with those that indicate a specific organization or warehouse. The selections are made dynamically, and therefore impose a certain load on the database.
The property of the general separator attribute – 1C user separation – allows you to set the availability of the list of users depending on the use of separators.
If the separator is enabled for a user, then it will be visible in the list of users in 1C Enterprise mode - otherwise it will not be visible.
This way you can organize different lists of users for different parts of the database.
The property of the general separator attribute – 1C authentication separation – allows you to create users with the same user names for different parts of the database.
Conditional division 1C
Conditional separation 1C allows you to enable or disable the separator based on database data. In this way, you can create chains of separators that are dependent on each other and act dynamically in a particular case.
To enable conditional division 1C - you need to specify in the property of the general separator attribute - Conditional division 1C - which will be responsible for determining the fact of enabling division 1C.
It is possible to use a constant with a Boolean type or a directory attribute with a Boolean type.
Important - you need to disable the use of this constant/this reference book (select Do not use) as part of delimiters, only then can it be selected.
General details in 1C 8.3 is a platform metadata object that allows you to use one attribute for many configuration objects (directories, documents, charts of accounts, etc.). The object was created mainly to make the developer's work easier and to separate data.
General details were initially implemented in version 1C 7.7, but the developers did not immediately include it in the platform version 8. The mechanism of general details was introduced by 1C developers only in release 8.2.14.
It is very convenient to add general details so as not to change standard objects in the configuration; I often use them along with .
After adding a general attribute, it can be used in queries and displayed on the object form - Outwardly, it is no different from ordinary props.
The only limitation of general details is the inability to use them in .
Let's look at the basic settings and properties of general details that differ from other configuration objects:
Compound— a list of objects to which the general details will be used; the setting is reminiscent of setting up an exchange plan.
Get 267 video lessons on 1C for free:
Auto use— the setting determines whether general props will be used for those objects that have the “Automatic” usage mode specified in their composition.
Data separation— we will consider this setting separately.
Separation of data in 1C using common details
Data separation- a mechanism similar to the mechanism. However, the performance of this mechanism is more efficient and it is easier to configure.
The mechanism allows you to configure the display of only elements that the user can see. For example, you can distinguish between all objects (documents, directories, etc.) where a certain organization is installed.
Setting up data separation using general 1C details
To configure the general details, you need to specify the data separation - Divide. Immediately after clicking, the system will offer to create default accounting parameters:
In this case, it will be necessary to specify the session parameters when starting the system; how to do this was described with an example in the article.
This completes the setup - the user will only have access to the information that is specified in the selected session parameters.
Example of using common props
Let's look at setting up general props in 1C 8.3 using the example of a frame configuration and props Organization:
The system has 3 documents where it is necessary to indicate the details of the Organization: these are the Receipt Invoice, the Expenditure Invoice, and the Payroll.
The setup is simple:
- We create a new General attribute, specify the type - DirectoryLink.Organization.
- In the composition we arrange for our documents - Use.
That's it, the setup is complete!
Let's see the result:
The system displays general details “as if they were your own”: in requests, in form details, and in other places. This is such magic! 🙂
General requisites 1C 8.3 are not added
A common attribute is an attribute that is added to several configuration objects and can also be used as component special data separation mechanism:
- Common props for several objects. An attribute that is present in several configuration objects in which this attribute retains its meaning and type. An example of such use: The “Organization” attribute in regulated accounting documents in an application solution
- General attributes as an integral part of a special data separation mechanism. This mechanism allows you to divide the work into separate parts application solution and all stored data. In this case, data separation is enabled for the general attribute.
Example of such use: In one physical information base Different “owners” of data work independently, and each user of such an application solution will only have access to his data
Property "Data separation" of the general attribute
If this property is set to "Do not use", then the created configuration object will be used only as an attribute included in several configuration objects.If the property is set to "Separate", the common attribute will be used as the data separator
Composition of objects
The "Composition" property of a general attribute determines the list of configuration objects that include this general attribute.If the "Auto-Use" property is set to "Do not use", the automatic addition of props will not occur, and to select objects in which you want to include common props, you should use the "Composition" property.
Also, the “Composition” property should be used if, when using the common attribute automatically, there are objects in which the common attribute should not be present.
Using common props
For each configuration object, the Usage column can take one of three values:- Automatically – means that the assignment of a configuration object to a general attribute depends on the value of the "Auto-Use" property
- Use - means that the configuration object is part of the general attribute
- Not used - means that the configuration object is not part of the general attribute
Configuration Objects
The general attribute (not in data sharing mode) may include the following configuration objects:- Directories
- Documents
- Document logs
- Characteristic type plans
- Calculation type plans
- Business processes
- Tasks
- Information registers
- Accumulation registers
- Accounting registers
- Exchange plans
- External data sources
Peculiarities
When recording a document, the general journal attribute is assigned the value of the general document attribute or the value NULL if the document is not part of the general attributeGeneral attributes can be used in data access restrictions. It makes sense to include external data sources as part of a common attribute when the common attribute is a separator.
ADVICE! You should not use general attributes to describe data that is part of the business logic of specific objects.
