| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
-----Original Message-----
Sent: Wednesday, December 11, 2002 4:59 PM
To: Multiple recipients of list ORACLE-L
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-----
Sent: Wednesday, December 11, 2002 5:24 PM
To: Multiple recipients of list ORACLE-L
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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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). Received on Wed Dec 11 2002 - 20:59:18 CST
 |
 |