Efficient operation of data access restrictions on the record level

The article offers a tentative division of access rights restrictions at the record level into simple and complex ones; it also demonstrates how these restrictions can influence queries to the database management system and provides tips for writing efficient restrictions at the record level.

1. Modes for applying restrictions

There are two modes for applying restrictions at the record level. The first one has the ALLOWED keyword in the query and is used to exclude records from selection results if they are forbidden for a user (e.g., display of a document list). The second one does not involve the ALLOWED keyword and does not filter forbidden records out, but it does set the behavior of database operation in a way that when a user attempts to access forbidden data, an exception occurs.

2. The difference between simple and complex restrictions

Access restrictions at the record level are defined by a query that specifies which records are accessible for the user. This query aways defines the main table that corresponds to the protected table. If a table is not specified (a query may begin with WHERE), it is identical to including a single table in the FROM section.

Access restrictions at the level of metadata objects can be classified into two types: simple and complex. Simple restrictions include those that only have one table in the FROM section (and this table is protected). In this case, an access restriction is only defined with the attributes of this table without using any connections to any other tables. Implicit connections, i.e. addressing a reference attribute using . (dot), which are automatically added to a query make such a restriction a complex one. Complex restrictions include queries that have multiple tables in the FROM section.

Sometimes a restriction that appears to be simple is actually executed as a complex one. It can happen when a restriction is defined for a balances register or an accounting register because in this situation the database interaction mechanism protects not only the table of register records but the balances table as well. So the query to the balances table gets the restrictions for the register. In order to include these restrictions in the database management system query (to get access to register attributes), the database operation mechanism adds the register records table into the access restrictions condition.

3. Principles of translation of access restrictions at the record level to a database management system query

Let us consider the most elementary case when a query selects data from a single table.

If the query contains the ALLOWED keyword, a simple restriction will be added to the WHERE section of the original query. A database management system query will get additional instructions to select only the records that the user is allowed to view, and therefore only the data the user is allowed to access will be received.

Query

Restriction

Database management system query

SELECT ALLOWED

    Code

FROM

    Catalog.Restricted

WHERE AccessCode = "1"

SELECT

   T1._Code

FROM

   _Reference8 T1

WHERE

      T1._Fld11 = N'1'

If a query does not contain the ALLOWED keyword, a database management system query is generated in a different manner. This query will have an additional indicator column that shows whether the record of this row is accessible. If a record is not allowed, the database operation mechanism aborts the query execution and creates an exception. However, the data accessible at this moment can be output to the event log if detailed logging of access is set up (the AccessDenied event).

Query and restriction

Database management system query

See above

SELECT

   CASE

         WHEN T1._Fld11 = N'1' THEN 0x01

         WHEN NOT (T1._Fld11 = N'1') THEN 0x00

   END,

   T1._Code

FROM

      _Reference8 T1

Complex restrictions that include other tables (in addition to the protected one) participate in the translation in a totally different manner. And while the principles above are still valid, queries with complex restrictions at the record level make the resulting database management system query (as well as its execution plan and time) much more complex.

Complex access restrictions at the record level are included in a database management system query as subqueries. This means that the database management system executes a subquery with complex restrictions for every potential record. If a query has multiple complex restrictionsand some of them can be added implicitly by addressing a reference attribute using . (dot)for each record multiple subqueries are executed rather than a single one: their number equals the number of complex restrictions included in the database management system query. Note that advanced database management systems are capable of optimizing the received queries, and in many cases optimization helps reduce the query execution time. But sometimes the database management system optimizer selects a plan that is not the most efficient, and it, therefore, takes extra time to execute the query.

4. Writing efficient access restrictions on the record level

As we have shown above, complex conditions make queries much more complex. If you use simple restrictions at the record level, database management system queries using such restrictions will be simpler and more predictable in terms of execution time. We, therefore, recommend including the attributes that affect record availability in the configuration object itself rather than accessing them using . (dot).

In addition, it is advisable to index the attributes used in access restrictions. This allows the database management system to use the index for selecting the records matching the restriction at the record level. However, note that such indexes are only efficient when an attribute is compared to determine whether it equals something; if the comparison is for more or less or inclusion in a range is checked, every such case should be considered individually, and it is hard to offer a common recommendation here.

Next page: Formatting spreadsheet document rows

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.