"iGuard" from Teleran applies rules like this, plus just about anything
else that can be contrived, at the network layer. Instead of letting
the query even reach the database engine, it can turn it around with an
error message similar to what Oracle provides.
Tony Adolph wrote:
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
--
//www.freelists.org/webpage/oracle-l
|
