Re: "Write once-Read many" table ?

From: DA Morgan <>
Date: Sun, 02 Mar 2008 15:43:17 -0800
Message-ID: <>

Niall Litchfield wrote:
> On Mar 2, 6:25 pm, DA Morgan <> wrote:

>> Niall Litchfield wrote:
>>> On Feb 28, 8:26 am, DA Morgan <> wrote:
>>>> wrote:
>>>>> Hello,
>>>>> For legal reasons, we would like to create tables where one can
>>>>> insert, select but never never delete nor update.
>>>>> I know we can prevent delete and update with revoke grants or with
>>>>> triggers but this is not satisfactory because grants can be re-granted
>>>>> and triggers can be dropped.
>>>>> We want to make sure that a line inserted will never be changed - even
>>>>> by the owner of the schema or by SYS or by any powerful dba.
>>>>> Is there a way to achieve this ?
>>>>> Is there a module to ensure this (database vault...) ?
>>>>> Thank you in advance
>>>>> Jean-Michel
>>>> You are incorrect in your assumptions.
>>>> You can absolutely lock down a database with triggers so that grants
>>>> can not be made: Even by SYS AS SYSDBA. Audit Vault and Database
>>>> Vault are perfect examples of how to do this.
>>> If you can lock down a database as completely as you suggest Audit
>>> Vault (secure storage for audit logs) would be a waste of money
>>> surely. What happens to a database vault protected database if someone
>>> starts it up with a different password file? I think you'll find that
>>> sysdba wll work just fine - check out the official advice on what to
>>> do if you forget the Database Vault Owners password for example.
>>> Niall Litchfield
>> It appears, from your last paragraph, that you don't understand how
>> security in DataVault and AuditVault are implemented. It has nothing
>> to do with the password file it has to do with wallets and event
>> triggers that make short work of anyone that thinks those triggers
>> can be end-run with a simple DISABLE or DROP.
>> Here's a simple example for you:
>> $ orapwd file=orapwavomega password=oracle1 entries=5 force=y
>>    nosysdba=n
>> $ sqlplus /nolog
>> SQL> conn / as sysdba  -- this will fail
>> After you figure out how to log in as SYS with SYSDBA try to
>> SELECT from any table owned by DVSYS. Try to enable, disable,
>> grant, or revoke any privilege related to DVSYS.

> Why then the following note in the docs
> "Users who have access to a system as the Oracle software owner have
> control over the Oracle software, including the following activities:
> *
> Disabling Oracle Database Vault in the given system
> *
> Reading unencrypted database files
> *
> Moving and deleting database files
> *
> Starting or stopping Oracle programs in the system
> Oracle Database Vault does not provide protection against the
> operating system access of the Oracle software owner. "
> I'll certainly do the test you describe, but the fact remains that
> anyone with access to run the command you describe (and that'll be
> commonly the dbas and the system administrators) can disable database
> vault entirely.
> If you can run the command you list then you can also run
> mv orapwavomega orapwavomega.old
> orapwd file=orapwavomega password=oracle1 entries=5
> what happens then?
>> Let me know how you do. <g>
>> Again, please, off-line.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> (replace x with u to respond)
>> Puget Sound Oracle Users

The docs may be correct in one sense but they sure don't work the way it implies.

You can do any thing you want with the password file, I've tried, you'll not get into DVSYS ... Because even after you log in with SYS AS SYSDBA ... you can not select from those tables and you can not grant permissions to anyone else to do so.

The lockdown is not perfect. But it is vastly superior to what you assume. I would expect that the average Developer or DBA, given all userids and passwords would never get in. I would expect that an expert DBA or developer might but they should expect it to be a very challenging project.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Mar 02 2008 - 17:43:17 CST

Original text of this message