Re: Changing SQL statements inside the database

The Virtual Private Database (VPD) facility (a.k.a. "fine-grain" or "row-level" security) can be used to alter SQL on the server, regardless of origin. I implemented an access control list (ACL) feature for a system two years ago, complete with recursion inside PL/SQL for navigating the (implied) double-dimensioned linked-lists that typical ACL functionality requires.

Navigating linked-lists is naturally something that you wouldn't want to code in a SQL statement, but VPDs did it quite nicely by allowing me to run a custom PL/SQL packaged function during the PARSE phase of a SQL statement. The packaged function is supposed to return a string which is tacked onto the SQL statement, so you cannot "modify" existing text in the SQL, but you can add additional SQL text to the WHERE clause, including subqueries. There are other little useful twists and tricks and gotchas involved, but that's the general idea...

But, be *very* careful! You can just imagine how badly you can harm performance and accessibility through casual use of this feature... :-)

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 02, 2002 7:18 AM

> Hello list
>
> This time I need your help on how to implement a weird idea.
> We have a third party application that fire wasteful SQL's to the
database.
> (Remember the discussion on using x$dual Vs dual some days ago?)
>
> I am wondering: Is there a way to change the SQL that the user send?
>
> In ADABAS on the mainframe I can activate a user exit that gets the
command
> that
> the user send to the database and change it (Not only can, but we are
doing
> it!).
>
> Is there a way to do the same with SQL in Oracle?
>
> In the server (preferably) or in the client.
>
> Thanks
>
> Yechiel Adar
> Mehish
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Yechiel Adar
> INET: adaryechiel_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).