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'
--------------------------------------------
