76 / 96
W H I T E P A P E R
© 2017 Persistent Systems Ltd. All rights reserved. 76
www.persistent.com
—
Mask sensitive data elements like ID numbers, name, compensation, revenue or any other personally
identifiable information (PII) during the ETL process. Most database engines can encrypt the data at column
level.
—
Configure BI tools with a single administrative ID for access to the DW and limit the user access to that tool.
This way all read access is controlled via one ID. This is also sometimes referred as reporting service account.
—
Configure BI tool to implement project and folder-level security to limit access to reports and ad-hoc templates.
—
Unrestricted data – accessible to all users who are authorized to access BI or DW database, under reporting
service account, no special permissions to be given
—
Restricted data – restricted reports don’t require a security layer within the underlying database and can be
handled at BI system layer.
—
Filtered data – the data presented is different to each user based on the row-level and column-level access
control and cannot be cached as the report is dynamic in nature. Connect user’s IDwith data elements such as
department, business unit and columns thereof. Recommended way to do is to have the source query written
as a stored procedure or function accepting user’s identification claim as a input parameter or use row-level
security in database. This is expensive to develop and maintain (in cases like change in user privileges or
business unit mergers), so use this data security pattern with utmost care.
—
Ad Hoc access – here users leave the boundaries of predefined reports and authorization is recommended to
be controlled at database layer. This is done by providing access to views, tables and creating security related
metadata tables (user ID and list of dimension row keys where user have access) which can be joined with fact
table to filter the data. Implement an analytic server or OLAP cube for more rich yet secured ad hoc access.
—
External user access – it is recommended not to provide access to external users to DW/BI system and follow
a subscription-based approach. Provide data access via a RESTAPI layer or e-mail the reports to subscribed
users.
7.2.9 Security at Deployment and support stage
—
A solid backup and recovery system is a key to any security plan. In case of disk crashes, ETL should be
designed in such a way that it can be run in historical mode to source the data again or restore the data from
backup stage or DW tables. Most database engines provide good backup and restore mechanisms. However,
restoration may happen to a point in time where backup was taken and it is imperative to ensure there is no
data loss or inflation, after re-run of ETL for the duration of data loss and few simple data integrity checks would
go a long way.
—
There are various hardware, database, network and application components in a DW/BI system distributed
across the enterprise and even beyond its boundaries. This means security is a distributed problem. We
recommend identify someone in the BI team as a security manager who can co-ordinate with architecture and
security team to secure all possible components to implement a robust security strategy and address
vulnerabilities, on a continuous basis.
—
Secure physical assets – these are servers, build machines, source control systems, workstations, virtual
machines and laptops involved in the implementation of BI system. Most modern servers/systems provide
some level of access control.