×
Security

Materialized Views and Row Level Security

By Larry Grey • December 27, 2005

While consulting at the same customer in NYC, we were looking at performance issues related to implementing row level security.

As with many customers who implement row level security with PeopleSoft products, there is a relatively complex join between a set of tables that map users to the data values they have access to, and finally to the data table (i.e. Ledger table in this circumstance). Because the data table has a lot of values, and because the joins to it are complex, performance is often a significant issue.

Thinking back to PeopleTools 9

One of the features planned for PeopleTools 9 was definitional row level security. We planned to provide a centralized means of defining the rules that drove how sets of data values mapped to sets of users (including support for trees). Because we were trying to minimize the administrative effort required by organizations who were implementing this type of security, the logic required to evaluate a rule could be relatively time consuming if it were executed every time a user tried to access data.

Therefore, we decided that a good trade-off was to create a security staging table that was optimized for joining to the data table. This staging table would identify all the individual values a user or user group had access to, allowing an equality join to be done to the data table. The security staging table would be populated periodically by evaulating the rules that drove the mapping of the users to the data values.

Applying the design concept here
This customer had already created a set of tables and UI for administering those rules. However, in order to identify which rows of data a user should see in the ledger table, a relatively complex set of joins and unions needed to occur. As we looked at the potential performance implications of this, we recognized that evaluating the rule at the same time data is selected from the table would cause serious issues.

Because this customer was running on Oracle, we were able to use the database to create the staged table using a materialized view. Depending on the complexity of the SQL needed to get the appropriate data, the view can either be updated incrementally, or not. In this circumstance, the SQL required two unions (the first select got the data for the first chartfield, the secon select got data for an alternative chartfield, and the third got data for blank values). This meant that the refresh of the materialized view needed to occur on a pre-defined schedule or be triggered. In this circumstance, the customer created a trigger on each of the tables used in the view (OPRCLASS, the Custom mapping table, and each of the chartfields referenced).

One important note was that the materialized view did NOT include the join to the data table (i.e. LEDGER table). The Ledger reporting view was used instead. This is important, because of the following factors:

  • The LEDGER table changes frequently, which means that the refresh would have to be run each time data in the ledger table is modified.
  • Materializing the join between the ledger table and the security table would physically store the combination of each user and each row of data that user has access to (in other words, much of the ledger table would be replicated for each user… although disk is cheap, it’s not that cheap.)

The Grey Sparling approach to Data Security

Although this is the method used in PeopleSoft to support row level security out of the box, it does have significant drawbacks. This is why we developed our own reporting security product.

Keep in mind that the PeopleSoft approach to row level security is to hide the security rule from the application and to filter on OPRID, OPRCLASS, or ROWSECCLASS when it finds it in the record definition. This means the following:

  1. The criteria used to filter the data is hidden from the report. This is a big no-no from an auditing and compliance perspective, because you cannot easily figure out whether the data was missing in a report because security was applied, or whether the data simply didn’t exist.
  2. The filtering of the data is convoluted. In other words, instead of filtering the data directly against the data table, multiple joins are required to do the filtering.
  3. It is not trivial to apply the security rules to multiple data tables in the application. In each circumstance, either the record definition must be modified (for PS/Query), or a view joining the data table to the security rules must be created (for PS/nVision or PS/Query).

Our solution is to provide a place where the security rule can be defined in a manner optimal for the administrator, and then apply the rule directly to the report in the reporting tool. This means that the performance from row level security is better, it is easy to see how the data in the report had been filtered, and the security is applied consistently across different ledgers or tables without requiring app designer access or DBA access to create views. The secret sauce to this is how we apply the rule to the report without allowing the person running the it to be able to modify it. If you want to find out how we do that, feel free to contact me to get an NDA, I’ll add you to the Grey Sparling SPAM engine (just kidding), and we’ll talk…

Of course, I’m sure many of you are thinking “Well, if this is so great, why didn’t this customer buy the product instead of using this technique?” The answer comes down to timing. If our product existed before they did the analysis, design, development, and testing, they may have used it. Because we were looking primarily at the performance implications of using something that was about ready to be moved to production, it made more sense to look at performance as a stand-alone item (instead of revisiting the decisions already made). It’s entirely possible that there may be a future posting about how this customer purchased and implemented the Grey Sparling Report Compliance, Security and Distribution Extender.

Labels: ,

Stay Updated

Request a Demo