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

From: Babu Nagarajan <orclbabu_at_hotmail.com>
Date: Wed, 11 Dec 2002 14:38:51 -0800
Message-ID: <F001.005180BF.20021211143851@fatcity.com>

Common PL/SQL package to truncate tablesOf the top of my head, there is a way where you can run a procedure under the invoker's rights rather than the procedure owner's rights. if u create it that way, you need not bother checking if the user who is calling the procedure has the rights to truncate the table - oracle will do it for you

babu

Original Message ----- From: Thomas Jeff To: Multiple recipients of list ORACLE-L 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: 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: Babu Nagarajan
INET: orclbabu_at_hotmail.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:38:51 CST