RE: Common PL/SQL package to truncate tables

[Deshpande, Kirti]

Title: RE: Common PL/SQL package to truncate tables

Here is how we did it:

 A special id with 'drop any table' privs, it owns a table, 'truncate_clearance'. The table has calling_user, table_owner, table_name. The calling user has exec privs on a special package (dbms_custom) with a 'truncate_table' procedure. The procedure accepts table owner and table name as parameters, and checks the truncate_clearance table for the existence of a row with calling user, supplied table owner and supplied table name. If found, table is truncated else, an error is returned. We are now thinking of adding some sort of auditing to this procedure to track how often a table gets truncated and by who.

This process was developed back when the db was running on 7.3.

 

- Kirti  

 

-----Original Message-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 4:59 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Common PL/SQL package to truncate tables

We thought about that initially, but it won't work as the batch accounts
won't have the privs to truncate the tables, we'd have to grant
DROP ANY TABLES to them.

-----Original Message-----
From: Paul Baumgartel [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 5:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Common PL/SQL package to truncate tables

What about creating the package with invoker rights?  I haven't checked
to see whether there are restrictions that might apply here, but it
seems like a natural.  The success of the procedure's execution would
depend on the invoker having the required privileges.