Hi,

I'm trying to figure out what is the best approach to balance security
and ease of maintenance for my application's databases. It seems that
those two goals are somewhat conflicting.

I should point out that security isn't the most accurate term, as we
are more concerned about safety. That is, we don't need to control
access to the data, rather we need to make sure that users don't
modify the structure of DBs.


Some background on the application:

The application is for financial data enrty and analysis. It will be
marketed to CPAs and financial service providers. As such it must
handle data for multiple companies.

Each company's data is stored in a seperate DB. This is for several
reasons: performance and simpler queries (no need to filter by company
id), maintenance (backup/restore individual companies), portability
(users may need to move data of a certain company to a laptop).

There is also a central DB to manage the list of companies that are
registered by the application, and some global data.


The questions:

1. In order to backup a DB the user must be the owner or SYSDBA.
However in that case the user may also have full access to modify
tables, triggers, SPs, etc. Is there any way to allow backup/restore
without providing access to metadata?

2. Most of the data is imported from other programs, mainly accounting
software. An import usually inserts between a few thousands to about a
million rows into a DB. We want to disable indices during the import,
but this requires that the user is either the creator of the indices,
or the owner of the DB, or SYSDBA. Is there any way to allow index
de-activation without providing full DDL access?

3. A similar problem to the above is with re-computing index
selectivity. Whether or not we disable indices, there are some indices
that can't be de-activates - those that are generated by constraints.
After import we want to re-compute their selectivity, but once again
this requires the user to be the creator of the index (what about DB
owner and SYSDBA?).

Any advice is welcome, including a completely different approach to
achieve the same goals.

Thanks,

Eyal.