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>
>> 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.
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.orgReceived on Sun Mar 02 2008 - 12:25:19 CST