Summaries, Hierarchies and Row-Level Security

One of our customers recently had an interesting database design problem.

The customer has a fact table that contains transactions at a customer level. Each customer should only be able to see their own transactions, but also needs to see totals at a customer category and all customers level that includes all customers, not just this particular customer.

For this exercise, we are assuming that the customer hierarchy has three levels - customer, customer category and all customers. All customers is implied through not applying a customer ID predicate.

The usual solution to this sort of problem is to put in place "row-level security", so that a user can only see the rows in the fact table that relate to them. This can be done either with a view, or using technology such as Virtual Private Database or Oracle Label Security. The issue with this though is that whilst the user would be able to sum up their own transactions with no problem, if they required a customer category total for the entire company, the total they would end up producing from the "locked-down" fact table would only include their own transactions. The same goes for a company-wide total.

So, how to you (a) put row-level security in place, but (b) still provide access to summarised data for the entire company?

I had a think about this, and also posted the question on the Dizwell Forum. Tom Kyte came back with a good suggestion.

The approach Tom suggested was to create a view over the fact table, at the same level of detail as the fact table (i.e. a direct copy), and put row-level security on that view. Then, create another view, off of the base fact table, but this time summarised to to the level of aggregation we need. This summary view wouldn't have row-level security set up on it. The original fact table would then be "locked down" so that no-one could directly use it.

When the user requires transactions at detail level, he queries the detail level view, which has row-level security applied. When he wants his own transactions summarised, he applies as GROUP BY on this view. When he wants summarised data for customer category or all customers, he directly queries the other view.

One thought I did have at the time was to create the summary view as a materialized view, and then get the query rewrite mechanism to redirect summary queries that the user runs against the detail-level view to the summary view, but you'll see later on that this isn't possible. What this means then is that if the user wants company summarised data, they have to go direct to the summary view, they can't just roll up their own figures from the detail-level view.

In practice, using a tool like Discoverer, users would have a transaction detail worksheet that directly queries the detail level view. If they want to work on summarised data and work down, they would have a workbook with two worksheets, with the first one querying the "all customers" view, then drilling down to customer category, and then if the user wanted to see inpidual transactions, we would drill out to the second worksheet that lists out inpidual transactions, working against the detail-level view with the VPD policy.

If you're interested, here's how I worked it all through.