RE: Problem: only sensible queries allowed

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Mon, 19 Oct 2009 00:26:01 -0400
Message-ID: <000d01ca5074$458939d0$d09bad70$_at_com>

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.  

Ken  

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  

Hi All,

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.

E.g.

SELECT ......

  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,..)

select ....
from....
where
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?

Thoughts:

1. 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'
2. 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?

Cheers
Tony

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 18 2009 - 23:26:01 CDT