20.09.2014

There is a directive "Allowed" in the query language. It is intended to be used by the framework to filter out records to which the user does not have rights when setting a database record-level limit.

It would seem that in queries it is better to always use this directive. I will argue that this is not the case. Also, I will argue that if possible, you should avoid using it, that's why.

Let's say we make a report on mutual settlements of individuals. The user has rights to one organization, and there is more than one organization in the database, and the record-level restriction is enabled in the database. Also, the database has a register "Mutual Settlements" with the dimensions "Organization" and "Individual". If there is a request in the system

"Choose

Organization,

Individual

and it will be executed on behalf of a user with permission to one organization, then the query will fail if there are records of other organizations in this register. An error will occur, and the description of the error will be "The user does not have sufficient rights to complete the request!" and this is true, the platform does not cheat, since it does not have rights to the records of other organizations in this register.

What to do in this case, use the "Allowed" directive? Not worth it in my opinion. You just need to set the selection by organization and the user will be able to generate a report. The query for a report with data composition will look like this

"Choose

Organization,

Individual

(Choose

Organization

individual)

From Accumulation Register. Mutual Settlements

(Where

Organization

individual)

If the user executes a query on the table without selection, then the report will not be generated, and the user will not recognize the data for other organizations, and if he sets the selection for his organization, he will generate the correct data.

You can ask again - "Why shouldn't you use the Allowed directive", this immediately imposes a selection, saves the user from messages he does not need!

The answer to this question will be the following - how in this case the user will know that all the necessary data has been included in the report. Suppose, earlier, this user worked under full rights and made a mistake and chose an individual from another organization in the document. There may also be a situation, the data was being loaded - and a subdivision of another organization was registered in the documents of the organization (in the ZUP, restrictions on the owner are also imposed on them). In this case, the "Allowed" directive will cut off the forbidden data without any messages to the user, and he will not know that not everything that should be included in the report was included.

Therefore, it is not necessary to enter this directive en masse into requests for typical configurations, considering this a mistake. It is highly discouraged to do this in regulated reporting requests. Also, do not do this in other reports and documents where accuracy of information is needed.

But how can you still avoid the error of "falling" the program with a lack of rights?

Yes, it's very simple, you need to use the "Try" command, here's an example:

Attempt

Request.Execute();

Exception

Report(ErrorDescription());

End of Attempt;

In reports using ACS, the program code for executing the report must be written manually, also through an attempt.

As a result, the user will not receive incorrect data and will receive a sane error message.

You can familiarize yourself with the nuances of setting RLS in separate divisions in our article.

). Using this keyword avoids an error when getting records for which the user does not have rights.

Problem: In some cases, the result of data access restrictions in 1C 8.3 may depend on the DBMS query plan. This article discusses possible situations and gives recommendations on how to avoid it.

The problem of the possible dependence of the result of data access restrictions on the DBMS query plan may arise when a database query is executed without the keyword ALLOWED if there are data access restrictions for the current user and the query contains one or more comparisons of the form:

• <Выражение над полями>(IN|NOT IN) (<Вложенный запрос>)
• (<Выражение над полями 1>, …, <Выражение над полями N>) (IN|NOT IN) (<Вложенный запрос>)

If in this case < > (query in a query) uses database tables that are subject to access restrictions, then it is possible that the query will be executed successfully on some DBMSs, and a message will be issued on others, provided that the data in the infobases is completely identical.

Get 267 1C video lessons for free:

Reason for differences

The possible difference in behavior is due to the implementation of data access restrictions without the keyword ALLOWED in 1C Enterprise 8.3.

Query without a keyword ALLOWED will be executed successfully only if during its execution there are no accesses to forbidden data. To do this, a special signal field is added to it, which takes the value True for those records in the formation of which only allowed data participated, and the value Lie for all other entries. If at least one record of the selection contains the value Lie in the signal field, then the query terminates abnormally.

The same signal field is added to the results of queries nested in the comparison. IN/NOT IN. Moreover, the check of the value of the signal column in this case is performed by means of the DBMS. Thus, if in the process of executing a nested query there was an access to forbidden data, then the execution of the query should end with an error The user has insufficient rights to perform an operation on the database.

However, when building a query plan, the DBMS may not receive the full selection <Вложенным запросом> , and get only those records that are actually needed to check the condition IN/NOT IN. In this case, the query may succeed even if <Вложенного запроса> access to prohibited data could occur as an independent request.

Let's consider a simple example. Let on the table Directory.Individuals data access restrictions. In this case the request is:

Table.Individual AS Individual

will be executed with an error due to an attempt to access prohibited data. If this query is involved in the comparison, for example:

Table.Individual AS Individual

Directory.Individuals AS Table)

then, depending on the query plan selected by the DBMS, the query can be executed either successfully or with an error. This behavior of the request is not erroneous, since access to forbidden data during the execution of this request may or may not occur. To obtain a more predictable result, it is necessary to build a query in such a way that the nested query is guaranteed not to perform accesses to obviously unnecessary data. In particular, if the previous query is rewritten like this:

Contract for the Performance of Work with an Individual.Employee.Individual

Document. Contract for the Performance of Work with an Individual AS an Agreement for the Performance of Work with an Individual

Contract for the Performance of Work with an Individual.Employee.Individual B (

Table.Individual AS Individual

Directory.Individuals AS Table

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. With the help of queries, you can quickly get any data stored in the database. Its syntax is very similar to SQL, but there are some differences.

The main advantages of the 1C 8.3 (8.2) query language over SQL:

• dereferencing reference fields (turning one or more dots to object attributes);
• work with the results is very convenient;
• the ability to create virtual tables;
• the request can be written both in English and in Russian;
• the ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

• unlike SQL, in 1C queries do not allow you to change data;
• lack of stored procedures;
• the impossibility of converting a string to a number.

Consider our mini tutorial on the basic constructions of the 1C query language.

Due to the fact that requests in 1C only allow you to receive data, any request must begin with the word "SELECT". After this command, the fields from which you want to get data are indicated. If you specify "*", then all available fields will be selected. The place from where the data will be selected (documents, registers, directories, etc.) is indicated after the word "FROM".

In the example below, the names of the entire nomenclature are selected from the "Nomenclature" reference book. After the word “HOW”, aliases (names) for tables and fields are indicated.

CHOOSE
Nomenclature.Name AS NameNomenclature
FROM
Directory. Nomenclature AS Nomenclature

Next to the "SELECT" command, you can specify keywords:

• VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
• FIRST n, Where n– the number of rows from the beginning of the result to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of the latest documents by date.
• ALLOWED. This design allows you to select from the database only those records that are available to the current user. If this keyword is used, the user will receive an error message if they try to query records they do not have access to.

These keywords can be used all together or separately.

FOR CHANGE

This clause locks data to avoid conflicts. Locked data will not be read from another connection until the end of the transaction. In this clause, you can specify specific tables that you want to lock. Otherwise, all will be blocked. The design is relevant only for the automatic blocking mode.

Most often, the "FOR CHANGE" clause is used when receiving balances. Indeed, when several users work in the program at the same time, while one receives the balances, the other can change them. In this case, the resulting balance will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will have to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount Mutual settlements Balance
FROM
Accumulation Register. Mutual Settlements WITH Employees. Balances AS Mutual Settlements
FOR CHANGE

WHERE

The construction is necessary for imposing any selection on the unloaded data. In some cases of obtaining data from registers, it is more reasonable to prescribe selection conditions in the parameters of virtual tables. When using "WHERE", all records are obtained first, and only then the selection is applied, which significantly slows down the query.

The following is an example of a request to get contact persons with a specific position. The selection parameter has the following format: &ParameterName (parameter name is arbitrary).

SELECTION (CASE)

The construct allows you to specify conditions directly in the request body.

In the example below, the "AdditionalField" will contain text depending on whether the document is posted or not:

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN
THEN "Document posted!"
ELSE "Document not posted..."
END AS AdditionalField
FROM
Document.Receipt of GoodsServices AS ReceiptT&C

JOIN

Joins link two tables by a certain link condition.

LEFT/RIGHT JOIN

The essence of the LEFT join is that the first specified table is taken completely and the second one is attached to it by the condition of the connection. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.

For example, you need to get item items from the documents “Receipt of goods and services” and prices from the information register “Item prices”. In this case, if the price of any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.

CHOOSE
Receipt of T&U. Nomenclature,
Prices.Price
FROM
Document.Receipt of GoodsServices.Goods AS ReceiptT&C
INNER JOIN
ON Receipt of Q&A.Nomenclature = Prices.Nomenclature

In RIGHT, everything is exactly the opposite.

FULL CONNECTION

This type of join differs from the previous ones in that all records of both the first table and the second will be returned as a result. If no records are found in the first or second table for the specified link condition, NULL will be returned instead.

When using the full join in the previous example, all item items from the Goods and Services Receipt document and all the latest prices from the Item Prices register will be selected. The values ​​of not found records, both in the first and in the second table, will be NULL.

INNER JOIN

The difference between an INNER join and a FULL join is that if a record is not found in at least one of the tables, then the query will not display it at all. As a result, only those item items from the Goods and Services Receipt document will be selected for which there are entries in the Item Prices information register, if in the previous example we replace FULL with INTERNAL.

GROUP BY

Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a certain common feature (grouping fields). Grouping fields can only be displayed using aggregate functions.

The result of the next query will be a list of item types with their maximum prices.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.TypeNomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are already added to them. Grouping displays only generalized records.

Results can be summarized for the entire table (using the keyword "GENERAL"), for several fields, for fields with a hierarchical structure (keywords "HIERARCHY", "ONLY HIERARCHY"). When summing up, it is not necessary to use aggregate functions.

Consider an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.

CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
RESULTS
MAXIMUM(Price)
BY
Type Nomenclature

HAVING

This operator is similar to the WHERE operator, but is only used for aggregate functions. Other fields than those used by this operator must be grouped. The "WHERE" operator is not applicable for aggregate functions.

In the example below, the maximum item prices are selected if they exceed 1000, grouped by item type.

CHOOSE

MAX(Price.Price) AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
GROUP BY
Prices.Nomenclature.TypeNomenclature
HAVING
MAX(Prices.Price) > 1000

SORT BY

The "ORDER BY" operator sorts the query result. To ensure that records are output in a consistent order, AUTO-ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.

An example of getting a list of employees sorted by name:

CHOOSE
Employees.Name AS Name
FROM
Directory. Employees AS Employees
SORT BY
Name
AUTO ORDER

Other constructions of the 1C query language

• UNITE- the results of two queries in one.
• UNITE ALL– similar to JOIN, but without grouping identical rows.
• EMPTY TABLE- sometimes used when joining queries to specify an empty nested table.
• PUT- creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.

Query language features

• SUBSTRING truncates a string from a specified position by the specified number of characters.
• YEAR…SECOND allow you to get the selected value of the numeric type. The input parameter is a date.
• BEGINNING OF THE PERIOD AND END OF THE PERIOD are used when working with dates. The period type (DAY, MONTH, YEAR, etc.) is specified as an additional parameter.
• ADDDATE allows you to add or subtract from the date the specified time of a certain type (SECOND, MINUTE, DAY, etc.).
• DATE DIFFERENCE determines the difference between two dates, specifying the type of output value (DAY, YEAR, MONTH, etc.).
• IS NULL replaces the missing value with the specified expression.
• PRESENTATION and PRESENTATIONLINKS get the string representation of the specified field. They are used for any values ​​and only reference values, respectively.
• TYPE, VALUE TYPE are used to determine the type of the input parameter.
• LINK is a logical comparison operator for the attribute value type.
• EXPRESS is used to convert the value to the desired type.
• DATE TIME gets a value of type "Date" from numeric values ​​(Year, Month, Day, Hour, Minute, Second).
• MEANING in a 1C request, it is used to specify predefined values ​​\u200b\u200b- directories, enumerations, plans for types of characteristics. Usage example: " Where LegalIndividual = Value(Enumeration.LegalIndividual.Individual)«.

Query Builder

To create queries with 1C, there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:

• "Tables and fields" - contains the fields to be selected and their sources.
• "Links" - describes the conditions for the CONNECTION construct.
• "Grouping" - contains a description of the constructions of groupings and summarized fields by them.
• "Conditions" - is responsible for the selection of data in the request.
• "Advanced" - additional query parameters, such as the keywords of the "SELECT" command, etc.
• “Joins / Aliases” - the possibilities of joining tables are indicated and aliases are set (the “HOW” construct).
• "Order" - is responsible for sorting the result of queries.
• "Totals" - similar to the "Grouping" tab, but is used for the "TOTALS" construction.

The text of the request itself can be viewed by clicking on the "Request" button in the lower left corner. In this form, it can be corrected manually or copied.

Query Console

To quickly view the result of a query in the "Enterprise" mode, or to debug complex queries, use . The query text is written in it, parameters are set, and its result is shown.

You can download the query console on the ITS disk, or by .

The "role" configuration object gives a set of rights to operations (actions) on configuration objects.

Role "Full rights".

This is just a role (not predefined) that has checkboxes for all kinds of rights on all configuration objects.

Its difference from other roles is the presence of the “Administration” right.

If at least one user is created, the system starts checking for the "Administration" right - at least one user must have it.

Restrict access at the record level

Row Level Security (RLS) - Restriction at the record level.

The mechanism of data access restrictions allows you to manage access rights not only at the level of metadata objects, but also at the level of database objects. The following objects can be used to restrict access to data:

• roles,
• session options,
• functional options,
• privileged common modules,
• keyword ALLOWED in the query language.

The mechanism is designed to restrict access to the records of the table of metadata objects according to arbitrary conditions imposed on the values ​​of the row fields of these tables. For example, to see records only for "your" counterparties, organizations, etc.

Technical implementation of access restrictions in 1C

1C generates a request to the DBMS. The server cluster adds a WHERE section to the request, which contains the text of the condition for restricting access by RLS, then this request is sent to the DBMS, the extracted data is returned to the 1C client.

This mechanism will work for any request from the client:

• in the reports
• in dynamic lists and regular list forms
• in random requests.

Such an implementation of the mechanism greatly affects performance.

Ways to bypass access restrictions.

In large resource-intensive operations (processing reposting documents, for example), part of the code can be moved to privileged modules.

A) privileged module is a shared module with the "Privileged" flag in the properties.

Its peculiarity lies in the fact that the code in it is executed without any access control, including RLS.

B) also privileged mode can be enabled for document object modules. This is done in the document properties, flag

• Privileged mode when holding
• Privileged mode when unscheduling

C) Method SetPrivilegedMode()

A system command that allows you to make part of the code of any module privileged.

From the next line of code, the privileged mode of execution will be in effect.

It will act until the line for disabling this mode or until the end of the procedure / function

(True);

// any code here will be executed without rights control and RLS

SetPrivilegedMode(Lie ); // or end of procedure / function

The number of activations of privileged mode must match the number of deactivations. However, if privileged mode was enabled (once or more) inside a procedure or function, but it was not disabled, the system will automatically perform the shutdown as many times as there were pending activations in the procedure or function being abandoned.

If in a procedure or function method calls SetPrivilegedMode(False) more than method calls made SetPrivilegedMode(true) then an exception will be thrown

Function PrivilegedMode() returns True if privileged mode is still enabled, and False if privileged mode is completely disabled. It does not analyze the number of privileged mode settings in a particular function.

All called procedures and functions will also be executed in privileged mode.

It is also possible to start a privileged session. This is a session in which the privileged mode is set from the very beginning of the system. At the same time, during operation, the method PrivilegedMode() will always return True , and the ability to disable privileged mode is not supported. Only a user with administrative rights (the Administration right) can start a privileged session. The session can be started using the command line switch for launching the client application UsePrivilegedMode or the infobase connection string parameter prmod .

The question naturally arises: Why, then, set up access restrictions at all, if it can be bypassed so easily?

Safe mode.

Yes, it is possible to write external processing with privileged execution mode and unload/corrupt data. To prevent this, the system has a global context method

Set SafeMode().

Safe mode, among other things, ignores privileged mode.

It must be set before programmatically calling external handlers or export procedures and functions from their modules.

Throws an exception when performing prohibited operations at runtime.

In addition, for users, you can turn off the ability to interactively launch external reports and processing at the role settings level.

Access restriction setting

RLS can only be configured for rights:

• reading (select)
• adding (insert)
• change (update)
• deletion (delete)

For read operations and deletion, the object in the database must comply with the data access restriction.

For the add operation the data access restriction must correspond to the object that is planned to be written to the database.

For the change operation The data access restriction must match the object both before the change (for the object to be read) and after the change (for the object to be written).

For all other rights, this option is not available.

Let's add a new restriction for the "read" right of the "Nomenclature" reference book. A list of fields for which you can configure the added restriction will open.

This means that if you try to access checkboxed fields, the restriction will work, and if you try to access unchecked fields, the restriction will not work.

If you select the flag Other fields”, the restriction will be set for all fields of the table, except for fields for which restrictions are set explicitly.

*Feature: for the rights to add, change, delete:

• The restriction can only be configured for all fields.
• There can only be one limit.

For the "Read" right, you can set several conditions, they will be combined with the logical operator "AND".

In restrictions on database objects of the following types, not all fields of the main data object of the restriction can be used:

• in accumulation registers, access restrictions can contain only measurements of the main object of restriction;
• in accounting registers in restrictions, you can use only the balance measurements of the main object of the restriction

If, under the conditions of limited access to the data of the turnover register of accumulation, measurements are used that are not included in the totals, then when accessing the virtual turnover table, the stored totals are not used and the query is executed completely according to the movement table.

The mechanism for imposing access restrictions.

Any operation on data stored in the database in 1C:Enterprise ultimately results in accessing the database with some request to read or modify the data. During the execution of queries to the database, internal mechanisms of 1C:Enterprise impose access restrictions. Wherein:

• The list of rights is formed(read, add, update, delete), a list of database tables, and a list of fields used by this query.
• From all roles of the current user select access restrictions to data for all rights, tables and fields involved in the request. Moreover, if any role does not contain access restrictions to the data of any table or field, then this means that the values ​​of the required fields from any record are available in this table. In other words, the absence of a data access restriction means that there is a WHERE True restriction.
• Get current values ​​of all session parameters and functional options participating in the selected constraints.

Getting the value of a session parameter or functional option does not require the current user to have the right to get that value. However, if the value of some session parameter has not been set, then an error will occur and the database query will not be executed.

Constraints derived from the same role are combined with an AND operation.

Constraints received from different roles are combined with the OR operation.

The constructed conditions are added to the SQL queries with which 1C:Enterprise accesses the DBMS. When accessing data from the side of access restriction conditions, no rights check is performed (neither to metadata objects, nor to database objects). Moreover, the mechanism for adding conditions depends on the chosen mode of operation of the restrictions “all” or “allowed”.

*Feature: If a user has access to several roles with configured restrictions at the level of records to one object, then in this case the conditions of restrictions are added by the logical operation "OR". In other words, the user's permissions are cumulative.

This leads to the following conclusion: do not allow the condition of restricting access to one object in different roles to be crossed, because in this case the query text will become much more complicated and this will affect performance.

All way.

When restrictions are imposed using the “all” method, conditions and fields are added to SQL queries so that 1C:Enterprise can obtain information about whether the data that is prohibited for the given user was used in the process of executing a database query or not. If forbidden data has been used, then the request is aborted due to an access violation.

The imposition of access restrictions by the “everyone” method is schematically shown in the figure:

"Allowed" method.

When restrictions are imposed using the “allowed” method, such conditions are added to SQL queries so that entries prohibited for the current user do not affect the result of the query. In other words, when restrictions are imposed in the “allowed” mode, records prohibited for this user are considered missing and do not affect the result of the operation, which is schematically shown in the figure:

Data access restrictions are imposed on database objects when 1C:Enterprise accesses the database.

In the client-server version of 1C:Enterprise, restrictions are applied on the 1C:Enterprise server.

However, this option (ALLOWED) will not work if we refer to a table in the query for which access restrictions are not configured, but in which there are links to table rows with configured restrictions. In this case, the result of the query will be "<Объект не найден>…...” instead of the reference field value.

If you are developing a report or processing using generic or custom configuration queries, always check the "Allowed" flag for the report to work under any user with any set of rights.

In the case of object reading data from the database, it is not possible to set the "Allowed" flag. Therefore, it is necessary configure selections for object reading, taking into account possible restrictions on access rights for the user. There are no means of obtaining only permitted data in object technology.

It is important that if the ALLOWED keyword is not specified in a query, then all filters specified in that query must not conflict with any of the restrictions on reading the database objects used in the query. Moreover, if virtual tables are used in the query, then the corresponding filters must be imposed on the virtual tables themselves.

Practice 1. Query builder in RLS settings.

Let's compose the text of the "WHERE" section in the query to the directory. You can use the query builder.
The constructor is truncated.

Tab "Tables"

The main table will be the table of the object for which the constraint is being configured.

You can also select other tables and set up various relationships between them on the "Relationships" tab.

Conditions tab

Here you can configure the actual conditions for restricting access.

Let's add conditions for the "Price" attribute of the stock list directory for the right to "read" to all fields of the table.

"Nomenclature WHERE Nomenclature. Price > 500"

Let's see how this simple rule works. The reference table contains the following elements:

After setting the access restriction, the table will show only the elements that satisfy the condition:

Groups have also disappeared. Change the constraint text

"Nomenclature WHERE Nomenclature. Price > 500

OR Nomenclature.This is a Group"

Well, now here's what you need.

If you remove the display of the “code” field in the list settings, all elements of the directory will be displayed, i.e. the restriction didn't work. If you set the display of the "Code" field, the restriction will work.

At the same time, despite the fact that the lookup element is visible in the list field, its form cannot be opened, because a restriction on the attribute is set. The same in an arbitrary request: when trying to get a "restricted" attribute, there will be an access error.

If you try to get the "restricted" props programmatically, an access error will also be raised.

Moreover, it will be impossible to access any fields of the object through a link, because when a link is received, the system reads the entire object, and if it has “limited” details, the object will not be read.

Therefore, when working with database objects programmatically, you need to keep in mind possible restrictions at the record level and get all the necessary object data with a query and then place them in a structure or execute part of the code in a privileged module.

After setting up the access restriction, the display of the line in the list of rights changed - it became gray and an icon appeared.

Access Configuration Restrictions (RLS).

• No Summary section;
• You cannot access virtual register tables;
• You cannot explicitly use parameters;
• Subqueries can use any>/span> query language facilities, except for:
  • operator IN HIERARCHY;
  • offers RESULTS;
  • nested query results must not contain tabular parts>/span>;
  • virtual tables, in particular Balances and Turnovers

Practice 2. Nomenclature with the current price.

Make an access restriction if you need to display an item with the current price greater than a certain value, for example, 100.

Solution:

We add a new access restriction rule for the "Nomenclature" reference book for the "read" right.
Select "other fields".
In the constructor, add a nested query. In it, select the information register table "Item prices".
There is no “order” tab - this is a feature of the query builder for building an access restriction query.
On the “Advanced” tab, set “first 999999999”, the “order” tab has appeared.
Set up ordering by the "Period" field in descending order.
Then we set up the connection of the main table with the subquery by reference.

Access Restriction Templates.

Practice 3. Restriction on "contractors" by value in a constant.

Set up access restriction for the Counterparties directory by the value stored in the Constant.

In addition, you need to set up a restriction for all objects that use the "Contractors" directory in the details.

Solution

For the “Accounts” reference book, for the “read” right, we will set up a restriction by adding a nested query to the constant to the “Conditions” section. Don't Forget ThisGroup.

We see the problem, the Counterparties directory is filtered correctly, and all documents with the “Counterparty” attribute are displayed, some with “broken” links in the “Counterparty” attribute.

Now you need to configure access restriction for all objects using the link to "Accounts". Let's find them with the service "search for links to an object".

Let's copy and slightly modify the text of the RLS condition from the "Counterparties" directory. This must be done as many times as there are objects found.

Or use the Access Restriction pattern to avoid code duplication issues.

Access restriction templates are configured at the role level and can be used for any object within the edited role.

You can put any piece of access restriction text into the template. The template is called through the "#" symbol. For example, #TemplateContractor.

Through # in 1C, instructions are written to the preprocessor. In the context of executing access restriction settings, the platform replaces the template call text with the template text.

Let's move the text after the word WHERE to the "TemplateContractor" template, except for the text about ThisGroup.

Parameters in access restriction templates.

Let's continue solving problem 2.

The problem now is that the main table in the directory is called "counterparty", in the document "Invoice". The checked field in the directory is called "link", in the document - "Counterparty".

Change the name of the main table in the template text to "#CurrentTable"

"#CurrentTable" is a predefined parameter.

And through the dot we indicate the number of the input parameter - “.#Parameter(1)

"#Parameter" is also a predefined value. May contain an arbitrary number of input parameters. They are referred to by serial number.

In the text of the access restriction for the directory, we indicate the following:

For the document the following:

“Sale of Goods WHERE #TemplateContractor(“Contractor”)”

When calling the access restriction template, parameters should be passed to it only as a String, i.e. in quotes.

Main Table - Nomenclature

The template text is:

#CurrentTable WHERE #CurrentTable.#Parameter(1) = #Parameter(2)

The template text contains a part of the text in the data access restriction language and may contain parameters that are highlighted with the "#" symbol.

The "#" character may be followed by:

• One of the keywords:
  • A parameter followed by the number of the parameter in the template in parentheses;
  • CurrentTable - means inserting into the text the full name of the table for which the restriction is being built;
  • CurrentTableName– denotes insertion into the text of the full name of the table (as a string value, in quotation marks) to which the instruction is applied, in the current version of the built-in language;
  • NameCurrentPermission– contains the name of the right for which the current restriction is performed: READ/READ, ADD/INSERT, MODIFY/UPDATE, DELETE/DELETE;
• template parameter name – means inserting the restriction of the corresponding template parameter into the text;
• the symbol "#" - indicates the insertion of a single symbol "#" into the text.

An access restriction expression can contain:

• The access restriction pattern, which is specified in the format #TemplateName("Template parameter value 1", "Template parameter value 2",...). Each template parameter is enclosed in double quotes. If you need to specify a double quote character in the parameter text, use two double quotes.
• Function StrContains(WhereWe Are Looking For, What We Are Looking For). The function is designed to search for an occurrence of the WhatLooking for in the WhereLooking for string. Returns True if the match is found, False otherwise.
• The + operator for string concatenation.

For the convenience of editing the template text, on the Restriction templates tab in the role form, click the Set template text button. In the dialog that opens, enter the template text and click OK.

They cannot be installed using setParameter() or something similar.

In this case, the parameters are:

• Session Options
• Functional Options

Reading session parameters in an access restriction request occurs in a privileged mode, i.e. without control of rights to operate with them.

Practice 4. Access to “your” counterparties

It is necessary to set up restriction of the access of the current user to "their" counterparties.

There is a directory "Users", a directory "Counterparties", documents with the requisite "Counterparty".

The current user should see data only for those counterparties for which a connection has been established with him.

Communication also needs to be configured.

Possible options:

Establishing links user + counterparty

• Details in the directory counterparties
• Information register

Possible solutions to the problem:

• Storing the user in a constant is a bad option, the constant is available to all users.
• Keeping a fixed array of the current user's counterparties in the session parameters is not a good option, there can be many counterparties
• Store in the session parameters of the current user, then request to get a list of "his" counterparties - an acceptable option.
• Other options.

Solution.

Let's create a new session parameter "CurrentUser" and write its filling in the session module.

Let's create a register of information "Correspondence of managers and counterparties"

Let's create a new role and in it a new access restriction for the document "Receipt Invoice".

In the query text, we will connect the main table with the information register by Contractor = Contractor and Manager = &CurrentUser. Type of connection Internal.

If possible, it is better to avoid nested queries in access restriction texts, because it will be executed every time the data from this object is read from the database.

We check - the restrictions work

* Feature : If you change the list of user's counterparties in the register, access restrictions will take effect immediately without restarting the user's session.

Practice 5. No change date.

It is necessary to implement the restriction on data editing earlier than the date set for the prohibition of changes.
Users need to be limited.

Let's create a "ChangeBarDateDate" information register with the dimension User, RestrictedDate resource.

Let's build the logic of the solution in this way:

• if the user is not specified, then the ban applies to all users
• if there is a restriction for all users and a restriction for a specific user, then there is a restriction for a specific user, and for the rest according to the general principle.

Obviously, such a limit can be configured for database objects that have a certain position on the time axis. It can be

• Documentation
• Periodic information registers

Let's create a new role "RestrictionsBy ChangeProhibitionDate".

In it, for the document "Receipt Invoice" for the right "change" we will add a new access restriction.

The setting is specified for all fields.

The restriction text is:

Receipt Invoice FROM Document. Receipt Invoice AS Invoice Invoice

ChangeProhibitionDates.ProhibitionDate AS ProhibitionDate
FROM

INNER JOIN (SELECT
MAXIMUM(ChangeProhibitionDate.User) AS User
FROM
Register of Information. Dates of the Prohibition of Changes AS the Date of the Prohibition of Changes
WHERE
(ChangeProhibitionDates.User = &CurrentUser
ORChangeProhibitionDate.User = VALUE(Reference.users.NullReference))) AS OT_User
BYChangeProhibitedDate.User = OT_User.User) AS Subquery
Invoice Invoice.Date > NestedRequest.BanDate

We check - the restriction works.

Using preprocessor instructions

#If Condition1 #Then

Request Fragment 1

#ElseIf Condition2 #Then

Request Fragment 2

#Otherwise

Request Fragment 3

#EndIf

In conditions, you can use logical operations (and. or, not, etc.) and access to session parameters.

This approach in the context of building access restrictions is convenient because, depending on the conditions, a shorter query text will be compiled. A simpler request loads the system less.

The downside is that the query constructor will not work with such text.

*Peculiarity :

Unlike instructions to the 1C:Enterprise preprocessor in access restriction texts, precede the Then operator with a hash mark — #Then

Practice 6. Switch "Use RLS"

Let's supplement our restriction system with a switch that enables/disables the use of the restriction at the record level.

To do this, let's add a Constant and a session parameter named "UseRLS".

Let's write in the Session Module setting the value of the session parameter from the value of the constant.

Add the following code to all access restriction texts:

"#If &UseRLS #Then….. #EndIf"

We check - everything works.

However, now after turning on the “use radar” flag, the changes will not take effect immediately. Why?

Because the session parameter is set when the session starts.

It is possible to have the session parameter reset when a new constant value is written, but this will only work for the current user session. Other users need to be prompted to restart the system.

End of the first part.

   

17 rules for compiling an optimal REQUEST to 1C database data

To form and execute queries to database tables in the 1C platform, a special programming language object is used. Request. This object is created by calling the construct New request. It is convenient to use a query when you need to get a complex selection of data, grouped and sorted as necessary. A classic example of using a query is getting a summary of the state of an accumulation register at a specific point in time. Also, the query mechanism makes it easy to obtain information in various time sections.

The request text is the instruction according to which the request should be executed. The body of the request describes:

• infobase tables used as query data sources;
• table fields that need to be processed in the query;
• grouping rules;
• sorting results;
• etc.

The instruction is compiled in a special language - the query language and consists of separate parts - sections, sentences, keywords, functions, arithmetic and logical operators, comments, constants and parameters.

The query language of the 1C platform is very similar to the syntax of other SQL languages, but there are differences. The main advantages of the built-in query language are: field dereferencing, virtual tables, convenient work with totals, untyped fields in queries.

Recommendations for writing database queries in the 1C platform query language:

1) The request body can contain predefined configuration data such as:

• enum values;
• predefined data:
• directories;
• plans of types of characteristics;
• charts of accounts;
• plans for types of calculations;
• empty links;
• values ​​of waypoints of business processes.

Also, the text of the request may contain system enumeration values ​​that can be assigned to the fields in the database tables: AccumulationMotionType, AccountType, and AccountingMovementType. Requests refer to predefined configuration data and system enumeration values ​​using a literal of the VALUE function type. This literal improves the readability of the query and reduces the number of query parameters.

An example of using a literal MEANING:

• WHERE City = VALUE(Directory.Cities.Moscow)
• WHERE City = VALUE(Reference.Cities.EmptyReference)
• WHEREItemType = VALUE(Enumeration.ProductTypes.Service)
• WHEREMovementType = VALUE(MovementTypeAccumulation.Income)
• WHERE RoutePoint = VALUE(BusinessProcess.BusinessProcess1.RoutePoint.Action1

2) Using instructions AUTO ORDER in a query, the query execution time can be very high, so if sorting is not required, then it is better not to use it at all. In most cases, the best way to apply sorting is with the statement SORT BY.

Auto-arranging works according to the following principles:

• If the ORDER BY clause was specified in the query, then each reference to the table in this clause will be replaced by the fields by which the table is sorted by default (for directories, this is the code or name, for documents, the date of the document). If the ordering field refers to a hierarchical directory, then hierarchical sorting by this directory will be applied.
• If there is no ORDER BY clause in the query, but there is a TOTALS clause, then the query result will be sorted by the fields present in the RESULTS clause after the keyword BY, in the same sequence and, if the totals were calculated by the fields - links, then by the sorting fields by default of the tables that were referenced.
• If there are no ORDER BY and TOTAL clauses in the query, but there is a GROUP BY clause, then the query result will be sorted by the fields present in the sentence in the same sequence and, if the grouping was carried out by fields - links, then by default sorting fields tables that were referenced.
• If the query does not contain the clauses and ORDER BY, TOTAL, and GROUP BY, the result will be ordered by the default sort fields for the tables from which the data is selected, in the order they appear in the query.
• If the query contains the TOTAL clause, each level of totals is ordered separately.

3) To avoid re-querying the database when displaying the query result to the user (for example, building a query or displaying the query result using a spreadsheet document), it is useful to use the instruction PRESENTATIONLINKS A that allows you to get a representation of a reference value. Example:

It is also possible to use the instruction PERFORMANCE- designed to get a string representation of a value of an arbitrary type. The difference between these instructions is that in the first case, if the instructions pass a reference, the result will be a string. In other cases, the result will be the value of the passed parameter. In the second case, the result of the instruction will always be a string!

4) If the query contains a field with a composite type, then for such fields it becomes necessary to cast the field values ​​to a specific type using the instruction EXPRESS, which will allow you to remove unnecessary tables from the left connection with a field of a composite data type and speed up the query. Example:

There is a register for the accumulation of Remains of Goods, in which the Registrar field has a composite type. In the request, the Date and Number of the Goods Receipt documents are selected, while accessing the details of the document through the Registrar field does not result in many left connections of the accumulation register table with the tables of register documents.

Code 1C v 8.x SELECT
EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Number AS Receipt Number,
EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Date AS Date of Receipt
FROM
Accumulation Register.Remains of Goods AS Remains of Goods

If the cast is considered not feasible, then the result of the cast is the value NULL.

5) Do not forget about the instructions ALLOWED, which means that the query will only select records for which the current user has permissions. If this word is not specified, then in the case when the query selects records for which the user does not have rights, the query will work with an error.

6) If the query uses a union, and in some parts of the union there are nested tables (a document with a tabular part), and in some there is no need to supplement the selection list with fields - empty nested tables. This is done using the keyword EMPTYTABLE, after which the aliases of the fields that the nested table will consist of are indicated in brackets. Example:

Code 1C v 8.x // Select fields Number and Composition
// from virtual table Document.Invoice
CHOOSE Reference.Number, EMPTYTABLE.(Nom, Tov, Qty) AS COMPOSITION
FROM Document.Invoice
UNITE ALL
SELECT Link.Number, Composition.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

7) In order to avoid duplicate lines in the query result, you should use the instruction VARIOUS, because it is clearer and clearer, and the instruction GROUP BY used for grouping using aggregate functions. By the way, when using aggregate functions, the sentence GROUP BY may not be specified at all, while all query results will be grouped into one single line. Example:

Code 1C v 8.x // It is necessary to find out which counterparties
// the goods were shipped for the period.
Select Various
Document.Invoice.Contractor

8) Instruction GROUP BY allows you to access top-level fields, without grouping results by these fields, if aggregate functions are applied to the fields of a nested table. Although it is written in the 1C help, when grouping query results, aggregate functions must be indicated in the list of selection fields, and in addition to aggregate functions, only the fields by which grouping is carried out can be indicated in the list of selection fields. Example:

Code 1C v 8.x SELECT
Receipt of Goods and Services. Goods. (SUM (Quantity), Nomenclature),
Receipt of Goods and Services. Link,
Receipt of Goods and Services. Counterparty
FROM
Document. Receipt of Goods and Services AS Receipt of Goods and Services
GROUP BY
Receipt of Goods and Services. Goods. (Nomenclature)

9) Instruction IS NULL intended to replace the value NULL to another value, but do not forget that the second parameter will be converted to the type of the first if the type of the first parameter is a string or a number.

10) When referring to the main table, you can refer to the data of the subordinate table in the condition. This feature is called dereferencing the fields of a sub-table.

Example (search for documents containing a certain product in the tabular section):

The advantage of this query over the query on the Incoming.Goods subtable is that if there are duplicates in documents, the query result will return only unique documents without using the DISTINCT keyword.

11) An interesting variant of the operator B is a check of the occurrence of an ordered set in the set of such sets (Field1, Field2, ... , FieldN) B (Field1, Field2, ... , FieldN).

Code 1C v 8.x SELECT
Contractors.Link
WHERE
(Contractors.Link, Goods.Link)
(SELECT Sales.Customer, Sales.Product
FROM Accumulation Register. Sales AS Sales)
FROM
Directory. Counterparties,
Directory.Products

12) Use virtual query tables whenever possible. When creating a query, the system provides a number of virtual tables as data sources - these are tables that are also the result of a query that the system generates at the time of execution of the corresponding code section.

The developer can independently obtain the same data that the system provides to him as virtual tables, however, the algorithm for obtaining this data will not be optimized, because:

All virtual tables are parameterized, i.e. the developer is given the opportunity to set some parameters that the system will use when generating a request to create a virtual table. Depending on what parameters of the virtual table are specified by the developer, the system can generate VARIOUS queries to get the same virtual table, and they will be optimized in terms of the parameters passed.

It is not always possible for a developer to gain access to the data that the system has access to.

13) In the client-server mode of operation, the function SUBSTRING() implemented using the SUBSTRING() function of the corresponding SQL statement passed to the SQL Server database server, which calculates the result type of the SUBSTRING() function according to complex rules depending on the type and values ​​of its parameters, as well as depending on the context in which it is used . In most cases, these rules do not affect the execution of a query, but there are cases where the maximum length of the result string calculated by SQL Server is essential to query execution. It is important to keep in mind that in some contexts when using the SUBSTRING() function, the maximum length of its result can be equal to the maximum length of a string of limited length, which is 4000 characters in SQL Server. This can lead to an unexpected crash in the query execution:

Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

HRESULT=80040E14, SQLSTATE=42000, native=8618

14) Use with care OR in construction WHERE, since using a condition with OR can significantly "weight" the query. The problem can be solved by design UNITE ALL. Example:

Code 1C v 8.x SELECT

FROM

WHERE
_DemoContractors.Link =Link1
UNITE ALL
CHOOSE
_Demo Counterparties.NameFull
FROM
Directory._DemoContractors HOW _DemoContractors
WHERE
_DemoContractors.Link =Link2

15) Condition NOT IN in construction WHERE increases the execution time of the request, as it is a kind of NOT (OR1 OR2 ... ORn), so for large tables try to use LEFT JOIN with IS NULL condition. Example:

Code 1C v 8.x SELECT
_DemoContractors.Link
FROM
Directory._DemoContractors HOW _DemoContractors
LEFT JOIN Document._DemoBuyerOrder AS _DemoBuyerOrder
Software _DemoContractors.Link = _BuyerDemoOrder.Contractor
WHERE
_Buyer's DemoOrder.Counterparty IS NULL

16) When using Temporary tables you need to index the condition and join fields in these tables, BUT, when using indexes, the query can run even slower. Therefore, it is necessary to analyze each query with and without an index, measure the speed of query execution and make a final decision.

If you place data in a temporary table that is initially indexed on some fields, then there will no longer be an index on these fields in the temporary table.

17) If you don't use Temp table manager, then there is no need to explicitly delete the temporary table, it will be deleted after the execution of the batch query is completed, otherwise the temporary table should be deleted in one of the following ways: by command DESTROY in the request, call the method TemporaryTable Manager.Close().

And in addition to the video from Evgeny Gilev: Typical mistakes when writing requests for 1C: