Implementing row level security on any database

Row-level or ‘Member-level’ security is a fundamental requirement in most analytic projects controlling which data items different users can view and analyze, from the highest level of detail down to the lowest.

For example, in a company where salespeople are assigned to different regions, one might be able to view customers in the North, another can only view customers in the South, and a third, the manager, can view both regions. As security requirements often have more complex rules governing access policies, a flexible, high-performant approach is required to resolve the different scenarios. Complicating things, few database engines offer a comprehensive solution for enacting row-level security. This goes from bad to worse in organizations that use multiple technologies—where BI managers find themselves enacting multiple security regimes.

Pyramid’s comprehensive security framework provides solutions for these types of issues—including security controls for access to functionality, content, reports, databases, data columns, and ultimately data rows. The entire framework pivots off a user/role-based model that can be designed centrally and applied to any supported database using point-and-click tools with a common security model.

For row- or member-level security specifically, this can be amped up significantly with a more scalable approach, where row-security rules can be materialized using tables and values in the database itself to deliver a fast, efficient mechanism for implementing row-level security.

In this blog, I am going to focus on the most complicated type of security implementation: row-level security.

The problem

Without applying access security, users can view all content and data in the database. In a multi-user or multi-tenant database, this is not acceptable since most projects require users only to see authorized data and content.

While many tools often offer functions suitable only to specific levels of expertise (professional, intermediate, or beginner), access to reports, databases, and even specific data columns should be controlled by security depending on users or roles. Row- or member-level security enables restrictions on data row access, and it’s usually the most essential layer of security in realistic deployments.

Some BI tools deploy a basic security model that only caters to simplistic requirements. In contrast, other BI tools do not apply the security filtering on the query but rather query all the data and only apply the security filtering on the results—an inefficient mechanism. Some tools offer the mechanics but are very unscalable when applied to thousands of different users, each user with their own security profile. And then many other tools can only offer row-level security on their own proprietary database technologies. Last, applying security at this level requires an enterprise approach that cannot be achieved in a desktop environment—which then makes many desktop-based technologies difficult to secure.

Pyramid’s solution

Pyramid’s security framework is designed so that administrators can deploy security operations centrally and then apply it to one or more data technologies to restrict access to functions, content, databases, data tables, columns, and, most critically: data rows. For row-level security, Pyramid provides both basic and data-driven options.

The access restriction logic is always incorporated into the final query, ensuring complete conformity between all aggregated and detailed reports. The same report can then display different results depending on a user’s profile.

In a basic row-security scenario, using simple point-and-click commands, administrators can assign hierarchy members explicitly to users or roles. This can be done for one or more hierarchies and to set security on entire hierarchies and measures. However, this is inconvenient if there are hundreds or thousands of security permutations for users and roles. In this case, a data-driven framework becomes a necessity.

A typical example of this is where a user must be granted viewer permission to all transactional data in one or multiple regions. The regions exist as a column in the customer hierarchy, which in turn is related to the transactional sales data. By mapping the user with one or more regions in a separate security table, access can be controlled through the relationships between the security, customer, and sales transaction tables.

So, by identifying the user using a standard scripting function, all subsequent relationships in the data model are injected into the query, ensuring all security rules are applied within the query. By designating the mapped user table as a security table, non-administrative users cannot access the table and can’t use it to build reports and queries.

Best of all, the security mechanics in the data-driven approach are highly scalable, using the database’s inherent capabilities to resolve who should see what without any tremendous memory or querying overhead.

Business case

Galina manages ILT’s enterprise security for three thousand users. They use SAP HANA for their data warehouse, a Snowflake database for their web logging, and Pyramid for their analytics. Galina adds a new data model for customer sales, where Galina applies security to ensure that customer sector users can only view customer data from their own sector. Several users belong to multiple sectors. A mapping security table has been created that defines which sectors each user is able to view. James, a manager, has access to retail, corporate, and commercial sector customers. Donette is a sales representative for both corporate and commercial sectors, and Kris works only in the commercial sector.

In this example, the Security table is linked to the Customers table via the Customer Segment, and the Customers table is linked to the Sales Transactions table via the CustomerKey This chain of linked tables enables a user’s view to be automatically filtered by the system-controlled Userid of the logged in user.

This will ensure that each user, when logged in, will only be able to query data pertinent to their defined customer segments. For example, James will be able to query records 1 to 3, Donette will only be able to query records 1 and 2, and Kris will only be able to query record 3. No users will be able to query record 4 as none of them have been assigned to the Government sector.

Galina maps the Security Table in the data model and designates it as a security table. Galina also links the Customer Segment in the Security Table to the sector in the Customers table.

Galina must now apply security functionality to the Security Table, identifying the logged-in user and linking the user to the Userid member in the Security Table. By doing so, the security parameters will be injected into the query at run time, ultimately linking the fact table to the logged-in user. Galina adds a user role from the data source manager in the Admin console that all these users will use. She then assigns PQL logic to the Userid hierarchy from the security table. The one-line PQL code utilizes the string to member function to convert the username (derived from the logged-in user), returning a data “member” with the current Userid from the Security Table. The corresponding Customer Segment(s) links to the associated segment in the Customers table, further connecting to all related transactional data to ensure robust member-level security.

In effect, the PQL function dynamically changes the resolved data member used in the query joins based on the user that is logged in. So, when James logs in, it resolves to “[Security].[UserId].[James]” and when Kris logs in it becomes “[Security].[UserId].[Kris]”. These are then inherently used in any queries to filter the underlying tables and secure the data and results.

When James views the Sales report, he views totals for all records belonging to the retail, corporate, and commercial segments. When Donette views the same report using the same data model and the same role, the report only displays data from corporate and commercial segments. Kris only retrieves totals for the commercial segment when viewing the same report.

James’ view

Donette’s view

Kris’ view

Summary

Pyramid’s comprehensive security framework provides solutions for controlling access to functionality, content/reports, databases, data columns, and, ultimately, data rows. The entire framework pivots off a user/role-based model that can be designed centrally and applied to any supported database using point-and-click tools with a common security model.

Row-level or Member-level security is a critical requirement in analytics, ensuring the right data is accessible to the right people from the highest level of detail to the lowest. A flexible solution is required to cater for the complex security rules governing access policies, often difficult to implement in row-level scenarios. Many BI tools only cater to basic requirements and are inefficient when deploying member-level security. Other tools either apply query algorithms, use unscalable solutions, or only offer row-level security on their own proprietary databases.

Pyramid’s row-level basic security framework allows easy point-and-click security assignments. The more advanced, scalable approach for larger security scenarios uses tables and values in the database in conjunction with Pyramid’s Query Language (PQL). Apart from being scalable and efficient, the entire framework pivots off a user/role-based model that can be applied to one or more supported databases or technologies using the same administrative tools and system.  Schedule a demo today!