Summary
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
as
--
-- 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
as
select ure.UserID
, ure.Role
, ure.Description
, ury.*
from userroles ure
left
outer
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
, usr.email
, substr(usr.email, instr(usr.email, '@') + 1)
emaildomain
label 'Email Domain'
, usr.startdate
, usr.lastlogin
, rlesu.role_superuser_cnt > 0
usr_classification
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'
end
usr_classification
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
left
outer
join ( select user_id_attr
, count(*) company_cnt
from OwnerUserAdministrations@inmemorystorage
group
by user_id_attr
) oun
on oun.user_id_attr = usr.userid
left
outer
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
group
by oun.user_id_attr
) ounpc4
on ounpc4.user_id_attr = usr.userid
left
outer
join ( select aue.userid
, count(*) role_all_cnt
from activeuserroles@InMemoryStorage aue
group
by aue.userid
) rle
on rle.userid = usr.userid
left
outer
join ( select aue.userid
, count(*) role_write_cnt
from activeuserroles@InMemoryStorage aue
where changesdata = true
group
by aue.userid
) rlewrite
on rlewrite.userid = usr.userid
left
outer
join ( select aue.userid
, count(*) role_superuser_cnt
from activeuserroles@InMemoryStorage aue
where ( isusermanagement = true
or
iscompanymanagement = true
)
and changesdata = true
group
by aue.userid
) rlesu
on rlesu.userid = usr.userid
where usr.lastlogin is not null
order
by usr.fullname