Sage CRM and Security Territories

6 minute read time.

Sage CRM has a powerful Security mechanism implemented as an automatic alteration of the SQL used to fetch either the application data to the screen or the meta data that controls the availability of features. A user will be able to access a particular set of data records to which another user may not have access. The user may be able to use features such as Delete and Change buttons when looking at a record but the second user may only have view rights with no access to the buttons in the interface.

The setup and creation of Territories is very well discussed in the System Administration guide.



The details of the security territories added are held in the Territories table in the CRM database.


select terr_caption as "Name",terr_territoryid as "Territory ID", terr_parentid as "Parent Territory", terr_rangeend, terr_nextrangestart from territories order by terr_territoryid






Each entity in CRM that is covered by the Security system has a column in the table that has the name _secterr. In the company table it is "comp_secterr", in the case table "case_secterr" and so on.

The security policy can involve not just reference to the security territory information but also may reference which user created the record, the team that it is associated with and the user that has been assigned to the record.

These columns differ in name from table to table.



Note:

For historic reasons the column that hold the Team information is called always called xxxx_Channelid.

The logs provide a very useful place to view the way in which the SQL is changed dynamically by CRM. For example if we log on as the user Susan Maye (Mayes) into the demo data and navigate to the reports section and run the report "My Open Opportunities" that can be found in the Sales section the following SQL in generated in the logs.


SELECT oppo_description,Oppo_OpportunityId,comp_name,Comp_CompanyId,pers_fullname,
Pers_PersonId,oppo_stage,oppo_opened,oppo_forecast_CID,oppo_forecast,
oppo_targetclose FROM vReportOpportunity WHERE ((comp_secterr is null OR (comp_ChannelId=1) OR (Comp_PrimaryUserId=4) OR (comp_CreatedBy=4) OR (comp_secterr>=-2147483639 AND comp_secterr=-1610612729 AND comp_secterr=-1610612729 AND oppo_secterr=-2147483639 AND oppo_secterr=-2147483639 AND pers_secterr=-1610612729 AND pers_secterr



Note:

A very important thing to point out at this stage is that for every table mentioned in the view will have the different security associated columns referenced. In the case of the above SQL, the view vReportOpportunity pulls its data from the opportunity, company and person tables. Each of these entities is covered by security and so will have the SQL automatically expanded by CRM to include checks on the following columns:

comp_secterr
comp_channelid
comp_primaryuserid
comp_createdby
pers_secterr
pers_channelid
pers_primaryuserid
pers_createdby
oppo_secterr
oppo_channelid
oppo_assigneduserid
comp_createdby

Because we know that these columns are going to be referenced automatically by CRM we must ensure that any views that we create include these columns in the select statement.

Note:

This topic is related to field level security. This will be discussed at another time.

Expansion of Territories

The Sage CRM security territory hierarchy can be expanded either horizontally or vertically. Horizontal expansion is where we choose to add more territories under the same parent at the same level. For example within the default demo data territories if we add 3 new territories: France, Germany and Italy to the Europe territory we will have expanded horizontally. Vertical expansion is the addition of territories under one another. It is the depth of the territory structure rather than its width.

After an update process known as rebalancing, administrators can add an greatly enhanced number of additional territories to a tree structure, which can be deeper than five levels.

When you have reached capacity, the attempt to add a new territory will trigger a prompt stating that the territory structure needs to be rebalanced.

"The territory structure needs to be rebalanced. Please perform a territory rebalance as soon as possible."

Rebalancing is a pretty SQL intensive process as the ranges that make up the territories would have to be recalculated.

The aim of rebalancing is to proportionally allocate the available range to the structure already in place, so where a child has many children below it, it should be allocated a larger share of the available range.

An outline of how this is done is found below:

First build a table of all the territories in memory.
Now calculate the total number of nodes by counting all the children and sub children recursively passing down the table.
Pass down the table again and for all the nodes with no children allow them to have 16 children to allow for expansion on each lowest node, incrementing the children count all the way up the parents. At the end of this, the top level territory will have the total number of all nodes.
Make another pass for the nodes which only have children, and increase the number of children already allocated to each parent by an internally decided fraction e.g. 30%. Starting at the top level, iterate down the tree until hitting a node with no children, this means we have reached the bottom of that path. For the parent of this node, go along each of its children recursing down till there are no more children for that particular node, then add another fraction to this node. Drop out of the recursion and try the next node. This ensures that the fractional increase is only added once to each parent node.
At this point we have the new increased total in the top level.
We know the maximum range value is the range of the root territory so divide this by the total number of children in the tree giving us a value per 1 child.
Now make a pass from the top level recursing down all the children and allocate this range value to each node multiplied by the number of children that node has.
This will all be done in memory and once the tree has been rebalanced in this way, we will have a value for each territory record. At this point the new field Terr_TerritorySaveID is updated with the value of Terr_TerritoryID. The new values of Terr_TerritoryID and ranges etc are then updated from the rebalanced values in memory. Now that the Territory table is correct, using the Terr_TerritorySaveID to link to the xxxx_secterr field, we can update the xxxx_secterr values with the rebalanced Terr_TerritoryID.


The database should be backed up before running the rebalance.

Note:

Sage CRM does not allow an infinite number of new territories to be added to the security structure but it does vastly increase the overall capacity. The new physical limit depends to the shape of the security hierarchy, the 'width' and the 'depth' being fed into algorithm that allocates the territory ranges.

Parents Comment Children
No Data