Common PL/SQL package to truncate tablesOf the top of my head, there is a way where you can run a procedure under the invoker's rights rather than the procedure owner's rights. if u create it that way, you need not bother checking if the user who is calling the procedure has the rights to truncate the table - oracle will do it for you


Original Message -----
From: Thomas Jeff
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, December 11, 2002 4:23 PM
Subject: Common PL/SQL package to truncate tables

We have a DSS database containing numerous datamarts, each stored in it's own schema.
Each datamart schema has a corresponding OPS$ batch account, which does the ETL work.
DML privs on all tables within a schema are granted to a {schema}_LOAD_ROLE, which
in turn is granted to the pertinent batch account.

Previously, each schema has it's own copy of a common utilty package, which provided
among other things, a routine to truncate a specified table. The batch account would
call this routine to perform all truncates. As the number of datamarts grew it started
becoming a pain to maintain and compile the same package in multiple schemas.

So, the idea is to use a database-wide common utility package which would be compiled
under a DBA ID, with execute granted to the OPS$ batch accounts. This package's truncate
routine would verify a truncate request by checking the calling USER against DBA_ROLE_PRIVS
to ensure it had the requisite {schema}_LOAD_ROLE for the {schema}.table_name passed as
a parameter.

Any security holes or caveats with this idea? Or maybe a more elegant way to accomplish this?


Jeffery D Thomas

Thomson Information Services
Thomson, Inc.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at:
Select 'Indy DBA' then 'DBA Web Pages'

Please see the official ORACLE-L FAQ: http://www.orafaq.com
Author: Babu Nagarajan
INET: orclbabu_at_hotmail.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).

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 5 | next ›
Discussion Overview
grouporacle-l @
postedDec 11, '02 at 9:23p
activeDec 12, '02 at 2:59a



site design / logo © 2022 Grokbase