Re: Problem: only sensible queries allowed

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Mon, 19 Oct 2009 16:01:03 +0300
Message-ID: <OFF229CD85.B6B8ACE7-ONC2257654.00470601-C2257654.00479808_at_seb.lt>

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

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

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

Received on Mon Oct 19 2009 - 08:01:03 CDT