RE: Need details of Tables being deleted from PL/SQL Package

Excellent answer (IMHO), and also get ahold of the collection of essays about using PL/SQL by Bryn Llewellyn and Charles Whetherell.  

IF your developers use PL/SQL the way the erstwhile product manager and product developer intended, then it is pretty doggone difficult to inject rogue SQL.  

It’s also not that hard, as long as you set things up the way they suggest. For my part, I would also set things up to minimize transaction concurrency and only submit data parameters and function or procedure names (even better if obscured as numbers) across any intervening communications link and then executing all the steps of the transaction together with the commit or rollback only starting the transaction when all the needed pieces have arrived at the database server.  

IF you do all that, you have an ideal point to log (or not log, probably depending on a configuration value in the database sometimes such things are for verification the application is doing what is expected and sometimes they are for permanent auditing) the details of the entire transaction. Ebiz has a good definition of “who” columns for identifying the actor.  

Good luck,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andy Sayer Sent: Monday, October 31, 2022 9:12 PM
To: Asad
Cc: oracle-l_at_freelists.org; oracle-l-admins_at_freelists.org Subject: Re: Need details of Tables being deleted from PL/SQL Package  

Logging to the dbms_output buffer isn’t going to be too helpful when the thing calling your package doesn’t check it. I would log the whole statement to a table, everyone has an opinion on what should go with it but I would include at least the date of when it was attempted and something related to your session (sid maybe). I would do the logging before the attempt, otherwise you won’t see anything that fails. Maybe check out Logger

https://github.com/OraOpenSource/Logger to get started.  

Then, you just query your logging table separately.  

Thanks,

Andy  

On Mon, Oct 31, 2022 at 6:07 PM, Asad <asad.hasan2004_at_gmail.com> wrote:

Thanks Andy for the answer , can I enable logging like this in the package itself :  

--execute immediate 'delete from '|| p_table_name ||' where '||p_pk_column_name||' = :1' using p_id_tab(i);

      execute immediate 'select from '|| l_detail_rec.table_name||' where '|| l_detail_rec.fk_column ||' in ('||l_con_condition_clause||')';
      dbms_output.put_line(' records (s) deleted form table '||l_detail_rec.table_name );       

 

Will this display all the tables ?  

On Tue, Nov 1, 2022 at 6:30 AM Andy Sayer <andysayer_at_gmail.com> wrote:

Hi,  

Follow the logic of the package to see what values end up in those variables. Ideally you would be logging the final statement somewhere with your existing instrumentation.  

I would do this sort of logging at the same point that I’m doing checks that the input values are not sneaky SQL injection (And if you’re not doing that then you need to).  

Of course, you can just check what’s in v$sql with a program_id of your package’s object_id and a program_line# of the execute immediate statement. This relies on all the relevant cursors still being in memory. If you do this, don’t forget about SQL injection.  

Thanks,

Andy  

On Mon, Oct 31, 2022 at 5:43 PM, Asad <asad.hasan2004_at_gmail.com> wrote:  

Hi All ,  

I have a task to identify the tables from which data is being purged while executing a apps package .  

I checked the package code it seems its doing the following :  

execute immediate 'delete from '|| l_detail_rec.table_name||' where '|| l_detail_rec.fk_column ||' in ('||l_con_condition_clause||')';  

How to get the list of tables being deleted ?      

-- 

Asad Hasan


+91 9582111698






 

-- 

Asad Hasan


+91 9582111698







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