Re: Problem: only sensible queries allowed
- From: Laimutis.Nedzinskas@xxxxxx
- To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Oct 2009 16:01:03 +0300
You can use DBMS_RLS (row level security, later named VPD - virtual private
database ) to add some where clauses to all selects applied to a table
based on some conditions.
Just use it carefully.
But honestly you must cure the application first...
------------------------------
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
