RE: Problem: only sensible queries allowed
Date: Mon, 19 Oct 2009 00:26:01 -0400
Preventing bad queries is a complicated task as it would involve writing a parsing engine. I recommend developing a mini data mart and using a tool like Business Objects, Cognos etc. with a prebuilt list of reports that they can then filter and drag and drop as need.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Tony Adolph
Sent: Sunday, October 18, 2009 11:45 PM
To: Oracle Discussion List
Subject: Problem: only sensible queries allowed
I've been asked to provide our marketing people select access on some large tables, but want to stop them using "stupid" where clauses that stop partition pruning / index lookup.
FROM PM_RATED_CDRS WHERE to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') > '20090914 00:00:00' AND to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') <= '20090917 13:00:31'
or (this one's a cracker,..)
and (trunc(a.CALL_DATE) >=
trunc(to_date('20070101000000', 'yyyyMMddHH24MISS'))) and (trunc(a.CALL_DATE) <
trunc(to_date('20090922235959', 'yyyyMMddHH24MISS') + 1))
BTW: The above 2 examples are real and I've feedback some polite suggestions on the use of dates, how trunc works etc. to our application developers. Note: I don't want to create a load of unnecessary functional indexes when its the code that needs fixing.
So I can try to fix the application developer's dodgy stuff.
But is there any way that I can block a query if it breaks some simple rules? Or allow only a short list of columns that can be used in the query?
- I was thinking about creating some views on the tables in question and giving these to marketing. But how can I stop select * from CDR_V1 where to_char(RATED_ON, 'YYYYMMDD HH24:MI:SS') > '20090914 00:00:00'
- Initially I was thinking about a load of pipelined functions that could be used something like select * from table(get_cdrs_for_date_range(to_date('20070101000000', 'yyyyMMddHH24MISS'), to_date('20090922235959', 'yyyyMMddHH24MISS') + 1));
but I may asking for a lot of work here,... "can we please have another one that......"
Any ideas folks?