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.
- Thomas Jeff <ThomasJe_at_tce.com> wrote:
> 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'
> --------------------------------------------
>
>
>
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Baumgartel
INET: treegarden_at_yahoo.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 - 16:23:51 CST
