Re: "Write once-Read many" table ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 02 Mar 2008 10:25:19 -0800
Message-ID: <1204482313.696320@bubbleator.drizzle.com>


Niall Litchfield wrote:

> On Feb 28, 8:26 am, DA Morgan <damor..._at_psoug.org> wrote:

>> jm.scheiwi..._at_gmail.com 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
> http://www.orawin.info

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.

Let me know how you do. <g>

Again, please, off-line.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Mar 02 2008 - 12:25:19 CST

Original text of this message