Who's been looking at my data? Recording access to Company and Person screens

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Who's been looking at my data? Recording access to Company and Person screens

  • Comments 1
  • Likes

I have discussed creating logs and audit trails of changes to data in previous articles.


But many customers have the much more straight forward business need to record when their users have accessed data.

Imagine a organization called "XYZ". XYZ is a charitable or grant awarding body, and has a duty of confidentiality towards its clients. Information about clients are stored in the person table in Sage CRM. If sensitive information about a client, that was provided in confidence, is revealed to a third party then enormous damage could be done to XYZ's reputation and to their client. 

Another scenario is a small Financial Services company called ABC. If a member of staff is accessing details of ABC's customers and passing these on to a competitor then a huge breach of trust has taken place.

Sage CRM has sophisticated security mechanisms that restrict access to rows and fields based on a users team and territory membership.  

And the System Administrator can restrict who can use Groups, who can create and run reports including summary reports and export any data from Sage CRM. The system administrator can also restrict access to the Dashboard. So the system can be really "locked down" for some users.

But there is always going to be a minimal level of access that has to be granted to users to allow them to do their job and to answer customer queries.

So how can an organisation spot patterns of data access that might indicate a user is accessing data they should not? If there is suspicion that data has been passed to a third party what evidence can be gathered to establish the fact of the matter?  

For both the scenarios above, XYZ and ABC need to be able to run reports that allow them to identify atypical behaviour. To paraphrase the journalist's favourite question, "who accessed what, and when?"

Adding a Access Audit  Table

The following is a one simple way of adding monitoring into the system

1) Create an AccessAudit table using

Administration -> Advanced Customization -> Tables and Databases

2) Add fields to the AccessAudit table that will record how and what data was accessed

  • Screen
  • Table
  • RecordID
  • SystemAction

3) Add new 'Audit' fields to each Table to be monitored e.g.

  • Person - pers_auditfield
  • Company - comp_auditfield

4) Add the xxxx_auditfield to each main screen that will be monitored

  • CompanyBoxLong
  • PersonBoxLong

5) Add the following createscripts to the fields in the screens


For the auditfield in companyboxlong

var myRecord = CRM.CreateRecord("accessaudit");
myRecord.acau_screen = "CompanyBoxLong";
myRecord.acau_recordid = CRM.GetContextInfo("company","comp_companyid");
myRecord.acau_systemaction = Values("act");  
myRecord.acau_tablename = "Company";

For the auditfield in personboxlong

var myRecord = CRM.CreateRecord("accessaudit");
myRecord.acau_screen = "PersonBoxLong";
myRecord.acau_recordid = CRM.GetContextInfo("person","pers_personid");
myRecord.acau_systemaction = Values("act");  
myRecord.acau_tablename = "Person";

The code can be adapted as needed, with extra information be stored in other columns but the idea is that any access to the screens will then be recorded into the table.

And the data can queried easily, e.g.

select acau_CreatedDate,
from AccessAudit

Note: This technique requires a database insert.  You will need to consider whether this will have an impact on required system resources and wether you will need to add a clustered index to the primary key of the accessaudit table.

Note:  This article does not cover the data viewed in the result grids of search screens, however this can be monitored using a similar technique.

  • Could this be used in the OnChange area to record changes to CRM records?