Assess Exact Online User Base and Software Compliance


Exact Online use has grown over the years. However, limited audit features are available to assess the risks in terms of privileges and software compliance. An Invantive SQL query is presented to assess these risks.

Exact Online Flavours

Exact Online was originally developed as an accounting package for small companies and has grown considerably over time to also cover mid-size companies and very large numbers of companies.

Exact Online is offered in solutions packaged for two different types of audiences:

  • Entrepreneurs, which are independent companies or groups of related companies sharing a common holding.
  • Accountants, which are commercial organizations whose primary business is to manage the accounting process of their customers.

Entrepreneurs are facilitated by simple accounting subscriptions and by industry subscriptions which add industry-specific functionality like for project-based organizations, manufacturing organizations and trade.

Accountants are allowed to handle a large number of companies as “internal companies” to which the company for which the accounting is done has no access to. Additionally, Exact has a special feature in their subscription model which allows accountants to be granted access to the data of a subscription of entrepreneurs. The collaboration through the cloud enables a modern and especially efficient way for accountant and entrepreneur to run their business.

User Base and Software Compliance

With the advent, the number of Exact Online users has grown considerably, both per subscription as well as overall. However, audit features on Exact Online are limited. This introduces risks in at least two areas:

  • data security risks in that users have been granted access and/or privileges to data by accident or to generic functionality that enables them to access or manipulate data they should not have access to.
  • software compliance risk that the terms of the subscriptions are violated by non-compliant use of the subscription.

In the following steps I will illustrate how you can use Invantive SQL to assess the user base, their essential privileges and your software compliance. It is meant as an example and the code might not apply to your situation and/or subscription and/or legislation.

The code is also available in our Gitlab repository.

Invantive SQL Query

The SQL code first creates a number of temporary tables. I’ve chosen temporary tables, but you could also have used inline SQL or views. Temporary tables make it easier to explain the logic in a text that is typically consumed from top to bottom.

The first table uses an XML API to retrieve an overview of users and the companies they have access to. In this case, the “OwnerUserAdministrations” are queried, which returns only the companies associated with the subscription of the user. For an entrepreneur, this is identical to “AllUserAdministrations”, but for an accountant this would restrict the query to only display the internal companies.

Please note that the XML API is extremely slow; it is not uncommon for the query to run for multiple minutes in large environments.

local Make a condensed overview of users and their privileges.

-- Users and the companies they have access to.
create or replace table OwnerUserAdministrations@inmemorystorage
-- Convert the text '{GUID}' to a GUID by removing the accolades
-- and casting to a GUID.
select to_guid(translate(oun.user_id_attr, 'x{}', 'x')) user_id_attr
,      oun.user_fullname
,      oun.administration_code_attr
,      oun.administration_number_attr
,      oun.administration_name
from   OwnerUserAdministrations oun

The following query first collects the currently assigned roles (privileges) of individual users. Each role has a number of metadata properties listed in UserRoleProperties like whether the roles gives the user access to user accounts or company management, enabling an attack vectors such as the user first granting himself more privileges and then using them.

create or replace table ActiveUserRoles@inmemorystorage
select ure.UserID
,      ure.Role
,      ure.Description
,      ury.*
from   userroles ure
join   UserRoleProperties ury
on     ury.roleid = ure.role
where  ( ure.EndDate is null or ure.EndDate >= sysdate )

In the final query, the previously collected data is combined with the user list. When executed, the following signals might indicate an issue:

  • Privileges: The column ‘SuperUser’ is checked. This signals a user that has significant access to one or all companies and can easily corrupt data or leak data. It is recommended that super users are subjected to special conditions in their labor agreement, similar to a system administrator.

  • Software Compliance: the column ‘Classification’ signals the type of user. Whereas a normal business user typically has access to at most 10 companies, an accountant typically has access to 10 or many more. The Dutch number of average number of companies managed per accountant is 22 with wide variations, such as a small holding or highly automated environment requiring little work and a large operational companies requiring a dedicated accountant.

  • Software Compliance: the column ‘#Postal Areas of Companies’ signals the geographical distribution of companies the user has access to. For accountants only: when a user has access to few companies in a limited geographical region please make sure that not by accident an end user has been given access to your internal companies.

  • Privileges and Compliance: the column ‘#Roles with Write Access’ signals the number of roles a user has to modify the contents of companies. Users that are intended to be read-only should not have write access granted by accident.

Please note the use of left outer join and pre-aggregating results in an inline view after the left outer join. This ensures that even when a user has for instance no roles, the user still appears in the list. Also, the aggregates calculated by grouping functions are made independent by pre-aggregating results.

-- Overview of users and their roles.
select usr.fullname
,      usr.username
,      substr(, instr(, '@') + 1) 
       label 'Email Domain'
,      usr.startdate
,      usr.lastlogin
,      rlesu.role_superuser_cnt > 0
       label 'Superuser'
,      case
       when oun.company_cnt is null
       then 'No access'
       when oun.company_cnt <= 10
       then 'Non-accountant'
       when oun.company_cnt >= 100
       then 'Poweruser'
       else 'Accountant'
       label 'Classification'
,      oun.company_cnt
       label '#Companies Access'
,      ounpc4.company_in_postcode4_cnt
       label '#Postal Areas of Companies'
,      rle.role_all_cnt
       label '#Roles'
,      rlewrite.role_write_cnt
       label '#Roles with Write Access'
,      rlesu.role_superuser_cnt
       label '#Roles with Superuser Access'
from   users usr
join   ( select user_id_attr
         ,      count(*) company_cnt
         from   OwnerUserAdministrations@inmemorystorage
         by     user_id_attr
       ) oun
on     oun.user_id_attr = usr.userid
join   ( select oun.user_id_attr
         ,      count(distinct postcode4) company_in_postcode4_cnt
         from   OwnerUserAdministrations@inmemorystorage oun
         join   ( select code, substr(postcode, 1, 4) postcode4 from systemdivisions sdn ) sdn
         on     sdn.code = oun.administration_code_attr
         by     oun.user_id_attr
       ) ounpc4
on     ounpc4.user_id_attr = usr.userid
join   ( select aue.userid
         ,      count(*) role_all_cnt
         from   activeuserroles@InMemoryStorage aue
         by     aue.userid
       ) rle
on     rle.userid = usr.userid
join   ( select aue.userid
         ,      count(*) role_write_cnt
         from   activeuserroles@InMemoryStorage aue
         where  changesdata = true
         by     aue.userid
       ) rlewrite
on     rlewrite.userid = usr.userid
join   ( select aue.userid
         ,      count(*) role_superuser_cnt
         from   activeuserroles@InMemoryStorage aue
         where  ( isusermanagement = true
                  iscompanymanagement = true
         and    changesdata = true
         by     aue.userid
       ) rlesu
on     rlesu.userid = usr.userid
where  usr.lastlogin is not null
by     usr.fullname