Summaries, Hierarchies and Row-Level Security
July 13th, 2005 by Mark Rittman
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.
July 13th, 2005 at 4:49 pm
In Discoverer, I would define a summary table but make it not available for queries.
Then create a “details” view off of the summary table with row-level security. This view would have the columns that you want security applied.
Next, create a “summary” view off of the summary table without row-level security. This view would only have the columns that do not need security.
Finally, set up both views as externally managed summary tables that ARE available for queries.
This way, any report that does not have the “secure columns” show all of the data. Otherwise, the user only sees data they have security for.
This worked in Discoverer 3i, but I think its not possible in later versions
July 14th, 2005 at 2:11 pm
Hi Ken,
I’m not sure this would address the problem actually.
> 1) Define a security view with row-level security. This view would have all columns that need security applied.
> 2) Define an open view without row-level security. This view would only have information everyone could see.
In the base level view, all columns would need to have security applied - the measure columns would by definition need RLS, and the keys don’t matter anyway (people wouldn’t report on these directly). So there wouldn’t be an open view, the only view (at the detail level anyway) would be the security view.
> This way, queries that have columns that need security, will use view 1) through query substitution. Queries that do not need security will use view 2) through query substitution.
There isn’t really a distinction between columns, not if you mean that col A requires security, col B doesn’t. There’s only a col A, and it needs security when viewed at a detail level.
> If you want these views to show summarized data, you can also define a summary table in Discoverer but make it NOT available for query substitution. Then use this table in the above two views.”
Not sure what you really mean by this, sorry. Could you explain this a bit further?
So, is this idea based on having some columns that need security, some that don’t? In my case, there effectively is only one column (transaction value) and, at a detail level, it always needs RLS applied. It’s only when this single column is summarised that we can take RLS off.
Thanks for the thoughts though, If I’ve misread this please let me know as otherwise it sounds quite promising.
regards
Mark
July 18th, 2005 at 10:58 pm
Mark,
Thank you for again sharing your thoughts and experiences with us.
Concerning the use of summaries and row-level security I’d like to suggest a different approach which might be useful in certain circumstances. It boils down to the assumption that row-level security is too restrictive: in your example a user is totally excluded from other customer data, when in fact the user should only be excluded from other customer data on customer level i.e. cust_id en cust_name. In the solution you describe this requires an extra MV.
An alternative would be to put the fact-table “locked down” behind a view. This view must then suppress (in effect censor) the data on customer level were necessary. Because cust_id is used to join fact_table with customers_dim the join with customers_dim must also placed behind this view. This is a drawback of this solution. You will however be able to drill to different levels using only the one view. A separate vpd_authorization table is used to define which customer data a user is allowed to access. This also opens the possibility to authorize a user on cust_category level. Users are not allowed to directly access fact_table nor the vpd_authorization table. No row_level security is needed. MV’s might be needed solely for performance reasons, I have not yet checked if transparent query rewrite works in that case.
In the “Kyteian” tradition here’s an basic example of what I mean (enhancements are possible using CASE instead of DECODE and using different levels of authorization)
Regards,
Melle.
CREATE TABLE VPD_AUTHORIZATION
( USER_NAME VARCHAR2(30),
CUST_ID NUMBER,
CUST_CATEGORY_ID NUMBER, — not used
PROD_ID NUMBER — not used
);
insert into vpd_test.vpd_authorization (user_name, cust_id)
values (’VPD_USER_1′, 1);
insert into vpd_test.vpd_authorization (user_name, cust_id)
values (’VPD_USER_2′, 2);
create view fact_view as select c.cust_category_name
, decode(a.cust_id, c.cust_id, c.cust_name, N-U-L-L, c.cust_name,’XXX’) as cust_name
, c.cust_category_id
, decode(a.cust_id, c.cust_id, c.cust_id, N-U-L-L, c.cust_id, -1) as cust_id
, f.prod_id
, f.revenue
, f.cost
, f.units
from customers_dim c, fact_table f
, vpd_test.vpd_authorization a
where c.cust_id = f.cust_id
and a.user_name = sys_context(’USERENV’,'SESSION_USER’);
1 BEGIN
2 DBMS_RLS.DROP_POLICY (
3 ‘vpd_test’, ‘fact_table’, ‘cust_policy’);
4* END;
SQL> /
PL/SQL procedure successfully completed.
grant select on fact_view to vpd_user_1;
grant select on fact_view to vpd_user_2;
create public synonym fact_view for fact_view;
connect vpd_user_1/password
SQL> select cust_category_name, cust_name, sum(revenue)
2 from fact_view
3 group by cust_category_name, cust_name
4 order by 1;
CUST_CATEGORY_NAME CUST_NAME SUM(REVENUE)
——————– ——————– ————
Corporate XXX 23230
Private John Smith 3400
Private XXX 800
SQL> select cust_category_name, sum(revenue)
2 from fact_view
3 group by cust_category_name
4 order by 1;
CUST_CATEGORY_NAME SUM(REVENUE)
——————– ————
Corporate 23230
Private 4200
SQL> conn vpd_user_2/password
Connected.
SQL> /
CUST_CATEGORY_NAME SUM(REVENUE)
——————– ————
Corporate 23230
Private 4200
SQL> select cust_category_name, cust_name, sum(revenue)
2 from fact_view
3 group by cust_category_name, cust_name
4 order by 1;
CUST_CATEGORY_NAME CUST_NAME SUM(REVENUE)
——————– ——————– ————
Corporate Global Enterprises 400
Corporate XXX 22830
Private XXX 4200