FAQ
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:

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?

Cheers
Tony

Search Discussions

  • Kenneth Naim at Oct 19, 2009 at 4:26 am
    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
    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:

    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?

    Cheers
    Tony
  • Niall Litchfield at Oct 19, 2009 at 5:47 am
    Does resource manager not work for you? If not and you already have BI
    tools like the ones Ken mentions you can achieve 2 things.

    a view of the data that makes business sense so people are more likely to
    ask sensible questions of the data and
    most of them have built in resource governors that limit queries by
    duration etc.

    Niall
    On Mon, Oct 19, 2009 at 4:45 AM, 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
    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Tim Gorman at Oct 19, 2009 at 12:25 pm
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    "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
  • Laimutis.Nedzinskas_at_seb.lt at Oct 19, 2009 at 1:01 pm
    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:

    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?

    Cheers
    Tony

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 19, '09 at 3:45a
activeOct 19, '09 at 1:01p
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase