Oracle-L: Common PL/SQL package to truncate tables

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Wed, 11 Dec 2002 13:23:43 -0800
Message-ID: <F001.0051800A.20021211132343@fatcity.com>

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?

Thanks.

Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba
Select 'Indy DBA' then 'DBA Web Pages'

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Thomas Jeff
INET: ThomasJe_at_tce.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 - 15:23:43 CST