RE: Common PL/SQL package to truncate tables

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Wed, 11 Dec 2002 14:59:03 -0800
Message-ID: <F001.00518110.20021211145903@fatcity.com>

We thought about that initially, but it won't work as the batch accounts won't have the privs to truncate the tables, we'd have to grant DROP ANY TABLES to them.

-----Original Message-----
Sent: Wednesday, December 11, 2002 5:24 PM To: Multiple recipients of list ORACLE-L

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).

-- 
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 - 16:59:03 CST