Record access

Warning!

Record Access is deprecated in favor of Object Access

With the record access functionality it is possible to filter records the users have access to in a specific table. For example a salesman in Denmark will only have access to Danish companies while a salesman in Sweden will only have access to Swedish companies.

There are two different ways of setting record access. The first one involves building a query and the second one writing an SQL function. Building a query is the easiest way of setting record access, but when you need additional functionality or when you want to optimize performance a writing an SQL function is usually a better choice. To activate record access on a table the property Record Access must be set to Function or Query. If it is set to None record access will be deactivated even if a query is built and/or a function is created.

In the table properties there is also a setting for Record Access Mode. In Lime CRM 10.9 and later the record access functionality is significantly faster (usually 2-3 times) than earlier versions. This performance enhancement is achieved by using SQL from the function definitions directly instead of using the function. Therefore this setting should rarely be changed from its default Use SQL. The backward compability mode Use function should only be used when there are problems with the record access functionality.

The expression for Query for read access is similar to the WHERE-clause in an SQL statement.

Suppose we have a table called company with a relation to a table called office. In table office we have a field called region that specifies the location of the office. There is also a table called coworker with a relation to office. If a user only is supposed to have access to companies in the same region as the user itself, the record access expression will look like this:

company.office.region = activeuser.office.region

The expression activeuser is a keyword that will be replaced with the active user's coworker record.

Warning!

Be aware that complicated queries will affect performance. The query will be executed every time a user tries to access data in the table!

Tip!

Always use full and complete paths when Record access is set to Query. Example for the history table:

activeuser.idcoworker = history.company.coworker.idcoworker

It is also possible to write a custom function to perform the record access check. The structure of the function have to look like the example below. If Record Access Mode is set to Use SQL only the SQL inside the parenthesis will be used, but the rest of code is necessary for the Lime CRM Server to be able to extract the SQL.

CREATE FUNCTION dbo.cfn_getcompany_sys__access(@@idrecord int, @@iduser int)
  RETURNS int    
AS  
BEGIN
  DECLARE @access int
  IF @@idrecord IS NULL
    SELECT @access = 15
  ELSE
    SELECT @access =  
    (
      -- Insert your SQL query here
      -- It is supposed to return 15 if the user has access to the record
      -- and 0 if the user hasn't got access to the record
    )
 
  RETURN @access
END
  • Last modified: 5 years ago
  • (external edit)