Differences

This shows you the differences between two versions of the page.

--- configuration:lisanew:recordaccess [2018-12-02 22:09]
127.0.0.1 external edit
+++ — (current)
@@ Line 1: / Line 1: @@
-===== Record 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.
-==== Query ====
-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:
-<code tsql>company.office.region = activeuser.office.region</code>
-The expression ''activeuser'' is a keyword that will be replaced with the active user's coworker record.
-<WRAP round important>
-=== 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!
-</WRAP>
-<WRAP round tip>
-=== Tip! ===
-Always use full and complete paths when **Record access** is set to Query. Example for the ''history'' table:
-<code tsql>activeuser.idcoworker = history.company.coworker.idcoworker</code>
-</WRAP>
-==== Custom function ====
-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.
-<code tsql>
-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
-</code>