Re: "Write once-Read many" table ?

Niall Litchfield wrote:
> On Mar 2, 6:25 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org