Home » SQL & PL/SQL » SQL & PL/SQL » Query rewrite problem related to FGAC/RLS/VPD (Oracle 10g, any OS)
Query rewrite problem related to FGAC/RLS/VPD [message #310787] Wed, 02 April 2008 07:27 Go to next message
dragos_craciun
Messages: 2
Registered: April 2008
Location: Bucharest
Junior Member

My problem in related to Fine Grained Access Control / DBMS_RLS.
Let's start with a simple example and elaborate further.
Basic problem:

Let's say we have two tables:

create table LEVEL1( L1NR NUMBER not null, TAG NUMBER);
alter table LEVEL1 add constraint LEVEL1_PK primary key (L1NR);

create table LEVEL2( L1NR NUMBER, L2NR NUMBER not null);
alter table LEVEL2 add constraint LEVEL2_PK primary key (L2NR);
alter table LEVEL2 add constraint LEVEL2_FK foreign key (L1NR) references LEVEL1 (L1NR);

I want to convince the database to rewrite a query that looks like this:
A)
SELECT L1.TAG, L2.L2NR
FROM
LEVEL2 L2
INNER JOIN LEVEL1 L1
ON L2.L1NR = L1.L1NR
WHERE
EXISTS( SELECT 1 FROM LEVEL1 L1B WHERE L1B.L1NR = L2.L1NR)

As L2 will be joined to L1 for my human eye and mind
it is obvious that the where clause is redundant,
and the query is equivalent to:
B)
SELECT L1.TAG, L2.L2NR
FROM
LEVEL2 L2
INNER JOIN LEVEL1 L1
ON L2.L1NR = L1.L1NR

Is there any way to convince the parser/optimizer to remove the redundant join?
Why do I have a query like A) and why I cannot rewrite it myself I will explain below.


For those to ask themselves why do I need such a thing let me elaborate.
We develop an application and we want to enforce security on the data at the row level.
The problem is more complicate than my following example, but the example is good enough.
Let's say that we have an application that makes invoices.
We want to give a user the privilege to make reports on the invoices issued for a category of customers.
I see three main ways to enforce row level security on data:
1) At the application level
2) Using views
3) Using Fine Grained Access Control / DBMS_RLS

Choice 1) is more flexible, but it has a major drawback:
You cannot make the database available for reporting with BI/ad-hoc reporting tools.
And also you have to be careful with every query you write. Or create something that takes care of the security, which can be really tricky.
And it also makes report creation for the application difficult since you need to embed the security system into the reporting module.
If you use Crystal Reports or some other reporting tool to build your application report you've got a problem.

Choices 2) and 3) are somewhat similar, but 3) is more flexible

For those who don't know how FGAC/DBMS_RLS works and don't want to dig deeper here is a short explanation:
DBMS_RLS allows the database developer to attach dynamic where clauses to queries.
It does this by rewriting queries like:
"select blabla from employees"
to "select blabla from (select * from employees where <some expression that filters the data according to the user's policy>)"

The filters are attached on a table by table basis, so queries like:

SELECT dept.NAME, empl.NAME
FROM
DEPARTMENT dept
INNER JOIN employees empl

are rewritten to

SELECT dept.NAME, empl.NAME
FROM
(SELECT * FROM DEPARTMENT WHERE <FILTER_DEPT>) dept
INNER JOIN (SELECT * FROM employees WHERE <FILTER_EMP>) empl

So far so good, nothing looks bad, but the devil is in the detail
Back to our reporting problem.
Let's say we have a scenario simpler than life, and I associate the user directly to a customer category by adding a CUST_CAT_ID column to the APP_USER table.
I want restrict the user to see only the data associated to its category (category, customers, invoices, invoice item)
So when he writes "select NAME, ADDRESS from customer" the query is rewritten to
"select NAME, ADDRESS from (SELECT * FROM CUSTOMER WHERE (EXISTS SELECT 1 FROM category join app_user on ... where customer.CUST_CAT_ID = app_user.CUST_CAT_ID and app_user.login = get_current_user()))"
where get_current_user is some framework function that gives me the current user.

A little complicated, but nothing too scary.


But when I write something like:

SELECT <relevant columns> FROM category cat JOIN customer cust JOIN invoice inv JOIN invoice_item item WHERE <some filters>

...(query totally legitimate for a sales report), this gets expanded to a scary query that looks like this:

SELECT <relevant columns>
FROM
(SELECT * FROM category WHERE (EXISTS SELECT 1 FROM app_user u WHERE u.CUST_CAT_ID = CAT_ID AND u.login = get_current_user())) cat
JOIN (SELECT * FROM customer WHERE(EXITS SELECT 1 FROM category JOIN app_user WHERE ...)) cust
JOIN (SELECT * FROM invoice WHERE(EXITS SELECT 1 FROM customer JOIN category JOIN app_user WHERE ...)) inv
JOIN (SELECT * FROM invoice_item WHERE(EXITS SELECT 1 FROM invoice JOIN customer JOIN category JOIN app_user WHERE ...)) item
WHERE
<some filters>

Oops! A query with 4 tables is expanded to a 14 tables query, when all I really need is:

SELECT <relevant columns>
FROM category cat JOIN customer cust JOIN invoice inv JOIN invoice_item item
WHERE <some filters> AND (EXISTS SELECT 1 FROM app_user u WHERE u.CUST_CAT_ID = CAT_ID AND u.login = get_current_user())

Let me tell you that we don't use here roles and privileges tables that we must use in a real life scenario.
In a real life scenario we will easily transform the original query in a 20-30 table join (grrrrrrr).
Well, I cannot change the way DBMS_RLS/FGAC works, and also if I choose to use views I cannot write a filtered view for every possible join that a user might create.

All I want is to find out if there is any way to instruct the parser/optimizer, using primary and foreign keys, optimizer parameters hints and other methods, to rewrite the query and eliminate redundant joins.

Of course I can create some materialized views or bitmap join indexes to help me in the process and speed up the query, but using this method in a database that has hundreds of tables can be a little problem in terms of management and performance.
Re: Query rewrite problem related to FGAC/RLS/VPD [message #310838 is a reply to message #310787] Wed, 02 April 2008 10:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Based on your explanation you have a written a policy function against all the tables you have mentioned. It will ideal if you could format your post and also provide the table structures and how the table hangs together and which table holds the user information. Also some test data. I can see where you are coming from but without this information it will be difficult atleast for me to help you.

Regards

Raj

[Updated on: Wed, 02 April 2008 10:12]

Report message to a moderator

Re: Query rewrite problem related to FGAC/RLS/VPD [message #310859 is a reply to message #310838] Wed, 02 April 2008 11:42 Go to previous messageGo to next message
dragos_craciun
Messages: 2
Registered: April 2008
Location: Bucharest
Junior Member

Ok. Here is a small example with tables and queries.
Re: Query rewrite problem related to FGAC/RLS/VPD [message #310863 is a reply to message #310859] Wed, 02 April 2008 12:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I have faced exactly the same issue at my previous work place but I was little bit fortunate in the sense I had one key which is the driving factor and it is denormalised atleast against majority of the tables.

In your case try removing the join condition from your policy functions and try to do like what I have suggested. I am not saying you will get only one where conditions appended to your outer select but atleast you won't be joining the same table again and again.
I have not written it to perfection but just to give you an idea.

-- for APP_USER: UPPER(LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN')

-- for CUST_CATEG: EXISTS(SELECT 1 FROM APP_USER u WHERE 
u.USER_CAT_ID = CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
-- for CUSTOMER: EXISTS(SELECT 1 FROM APP_USER u WHERE 
u.USER_CAT_ID = CUST_CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
-- for INVOICE: EXISTS(SELECT 1 FROM CUSTOMER c JOIN APP_USER u 
ON C.CUST_CAT_ID = u.USER_CAT_ID WHERE c.CUST_ID = INV_CUST_ID 
AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))

it could be re-written as

-- for APP_USER: UPPER(LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN')
-- for CUST_CATEG: user_cat_id in (SELECT user_cat_id FROM APP_USER u)
-- for CUSTOMER: cust_cat_id in (SELECT user_cat_id FROM 
APP_USER u -- for INVOICE: cust_id in (SELECT cust_id FROM CUSTOMER c 

Quote:

SELECT * FROM INVOICE

Your above query will be re-written to what you want. Try it out.

One slight correction. If you implement the way I suggested your above query will work as you want. But with the existing algorithm you will be getting all the records from Invoice table because you are using exists operator. But with the my approach you are joining it internally. Hope it makes sense now.

Hope this helps

Regards

Raj

P.S : Just a thought. I am not sure it is a generated code. Try to use mixed cases in your code.

[Updated on: Wed, 02 April 2008 12:44]

Report message to a moderator

Re: Query rewrite problem related to FGAC/RLS/VPD [message #311227 is a reply to message #310787] Thu, 03 April 2008 15:28 Go to previous message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Hi Dragos,

As a human I realise, without actually making the computations, that the result of
2 + (1 - 1) is 2, but the database optimiser is actually capable of doing this

2 + (1 – 1) = (1 - 1) + 2 = 2

Regarding your example, further simplification can probably be achieved by also putting the category (user_cat_id) in the context.

Maybe reading this link can also help you in improving your solution.

Regards
Marius NITU
Previous Topic: SUBSTR with just numeric characters
Next Topic: ORA-00933 when trying out returning clause
Goto Forum:
  


Current Time: Mon Dec 05 13:03:27 CST 2016

Total time taken to generate the page: 0.04960 seconds