----- Original Message -----
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?
Thanks.
--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information
Services
Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available
at:
http://gkmqp.tce.com/tis_dba
Select 'Indy DBA' then 'DBA Web Pages'
--------------------------------------------
