Introducing the New Data Warehouse Security Model

User Access

Analytics is the primary mission of our new data warehouse. To develop an analytics-focused data warehouse, we needed a security model that would not create security-based barriers between data domains for those engaged in analytics, while at the same time removing access to sensitive data for those that do not need to see it.

In the previous warehouse, which was optimized for operational reporting, warehouse data security was “tightly coupled” to the source system. This meant that for a report author to use human resources data, she would need to be given access to the HR application first, and then the security from the HR application would flow into the Human Resource datamart, allowing her access to that data in the warehouse. However, this model can prove limiting in an analytics-focused warehouse.

Another reason we needed a different security approach was that previously data security was accomplished using a hybrid approach, with Cognos Framework Models playing a key role alongside some security data stored in tables in the warehouse. While this did the job, it prevented us from opening up new, primarily self-serve reporting & analytics tools like Tableau or PowerBI (as well as from allowing users to query the data warehouse directly), since the primary security constructs were built into Cognos only.

Thus, we needed to try a different method of securing the new warehouse, and the approach chosen was Role Based Access Control, or RBAC.

What is Role Based Access Control?

RBAC is primarily an interplay between “Roles” and their “Entitlements” or specific fields in the warehouse. The methodology calls for the creation of Roles in the warehouse, and assigns read permissions on specific fields to those roles. Users are then assigned to these roles.

Benefits of RBAC Approach:

  • “Loose coupling”: A loose coupling with source system security allows us to be expansive and creative – guided by source security, but not controlled or limited by it.
  • By bringing the security closer to the data layer, we remove the dependency on the Cognos (or other) applications to implement security constructs.
  • Using this approach, a user connected to any application accessing a particular dataset from the warehouse gets the same base dataset across all applications, including straight SQL queries.

Alongside to the RBAC approach, we will be utilizing the RLS approach to secure data for a few specific requirements. RLS stands for Row Level Security, which as the name implies, restricts datasets based on what users are allowed to see at the row level. Both are implemented using security roles to make administration efficient.

In addition, data is encrypted natively at rest and in flight within the Azure ecosystem. Some sensitive data is also hashed explicitly. Further, specific “view” control mechanisms like masking and unmasking of specific datasets can be accomplished using Dynamic Data Masking, tied to security roles. On top of all this, all data access is monitored by login credentials and logged (with plans for automated and intelligent data security compliance reporting on this in the future).

See additional information about RBAC security in the Data Warehouse