Re: INDEX Use How?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/04
Message-ID: <34d8e1d6.29740098@www.sigov.si>#1/1

On Wed, 04 Feb 1998 11:37:18 -0600, byrd_at_mscf.med.upenn.edu wrote:

>We want to establish two tablespaces(one for tables and the other for
>indices). If we give users privileges to create an index via "create
>index" or create a table using a primary key, how can we guarantee that
>the indices reside in the established index tablespace by default? In
>other words how do we make sure all indices are where we want them to be.

The only solution I can see for this is to revoke CREATE TABLE/INDEX from users. Instead you'll have to create some generic PL/SQL stored procedures (or wrap them into package), which would take the "CREATE INDEX ....." or "CREATE TABLE ...." text as an IN parameter and would set the desired tablespace for indexes before creating the wanted object. You'll have to use DBMS_SQL inside the package/procedures.

Then grant execute privilege on this package/procedures to users. So instead of writing

   "CREATE INDEX blahblah ...."
users will need to call the appropriate procedure, like

   "ddl_package.cr_index('CREATE INDEX blahblah ....')

>Karen Byrd
>Univ of Pa, School of Med.
>Computing and Info Tech

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Feb 04 1998 - 00:00:00 CST