We know that Oracle and SUN/Solaris go together quite well on high end
installs. To insure an audit trail for BASEL , HIPPA, Sarbanes Ox and
other federal laws, one can ship oracle logs to an offsite server.
Yet, how can this guarentee an audit trail, when Solaris does not
support immutable files? Immutable files are files where not even root
can change/delete/move a file set as immutable.

We know, that an administrator with root access can change the settings
in UNIX to have the logs shipped elsewhere and not to the intended
offsite server. Maybe /dev/null. If you have a config file set as
immutable the admin, even as root cannot change the setting where the
log files are sent.

The FreeBSD and OpenBSD have immutable files. When designing ZFS,
which is an awesome filesystem, why didn't SUN's engineers have a look
at the BSD's and take the concept of immutable files into ZFS? After
all, don't FreeBSD and Solaris share a very common lineage? If I were
a Solaris FileSystem developer, I'd at least take a look at what the
competition is doing when building a next - gen filesystem. I do
admit, that this is the only shortfall of ZFS that I can think of but
it is kind of significant.

For the Oracle DBAs, how can you guarentee an audit trail without
immutable files? Is there an Oracle solution to this vexing dilema of
a system administrator being able to change the file that contains the
setting which tells the operating system where to ship the log files?

Karen Hill wrote:
> We know that Oracle and SUN/Solaris go together quite well on high end
> installs. To insure an audit trail for BASEL , HIPPA, Sarbanes Ox and
> other federal laws, one can ship oracle logs to an offsite server.
> Yet, how can this guarentee an audit trail, when Solaris does not
> support immutable files? Immutable files are files where not even root
> can change/delete/move a file set as immutable.

The secret it to keep audit trails inside the database and create an
audit trail of any attempt to alter it.

How can I tell if the audit trail's been altered?
One way is to apply DBMS_CRYPTO to the data.
Data alteration becomes impossible.

Want additional methods? Apply some of Oracle's built-in capabilities
such as checksums. Here are a list of topics you can look up in Morgan's
Library at www.psoug.org that may help.

DBMS_CRYPTO
OWA_OPT_LOCK.CHECKSUM
OWA_OPT_LOCK.VERIFY_VALUES
--
Puget Sound Oracle Users Group

Karen Hill wrote:
> We know that Oracle and SUN/Solaris go together quite well on high end
> installs. To insure an audit trail for BASEL , HIPPA, Sarbanes Ox and
> other federal laws, one can ship oracle logs to an offsite server.
> Yet, how can this guarentee an audit trail, when Solaris does not
> support immutable files? Immutable files are files where not even root
> can change/delete/move a file set as immutable.
>

I don't believe immutable files will be sufficient for SEC 17a-4 rules.
You typically implement this at the storage level.

It would still be a good idea to implement this in ZFS/UFS though

Pete

On 1 Sep 2006 12:28:12 -0700
"Karen Hill" <karen_hill22@yahoo.com> wrote:

> Immutable files are files where not even root
> can change/delete/move a file set as immutable.

But root can unset the immutable flag. Thus it only serves as
protection against accidental deletions or modifications. This is
slightly useful. Roles are better for that purpose.

> For the Oracle DBAs, how can you guarentee an audit trail without
> immutable files?

You cannot guarantee it with immutable files.

Immutability is _not_ a security feature. It does _not_ solve the
problem that root can change any file. If you cannot trust your root
user, you've got major problems. Trust is a difficult concept for PHBs,
but there is no magic solution.

Learn to live with it.

--
Stefaan A Eeckels
--
Tener razón es una razón más para no tener ningún éxito.
--Nicolás Dávila

Stefaan A Eeckels wrote:
> On 1 Sep 2006 12:28:12 -0700
> "Karen Hill" <karen_hill22@yahoo.com> wrote:
>
> > Immutable files are files where not even root
> > can change/delete/move a file set as immutable.
>
> But root can unset the immutable flag. Thus it only serves as
> protection against accidental deletions or modifications. This is
> slightly useful. Roles are better for that purpose.

Not when they are at a networked run level according to the OpenBSD man
page on the subject. They would have to reboot, or bring it down to
single user mode to do that. Rebooting an OS running a production
database would be extremely difficult to cover by an admin.

> > For the Oracle DBAs, how can you guarentee an audit trail without
> > immutable files?
>
> You cannot guarantee it with immutable files.

Are you sure? I'm read in the man pages that root cannot change or
delete an immutable file in BSD without rebooting the server. And
restarting a server is something that one could easily detect. I'm
adding the openbsd group to see if they have anything to add of
relevance to the immutable file discussion.

OpenBSD is a great system, unfortunately, scaling up to the processor
level required to run a medium sized corporate database server is
something only Solaris / AIX seem to be able to do.

> Immutability is _not_ a security feature. It does _not_ solve the
> problem that root can change any file. If you cannot trust your root
> user, you've got major problems. Trust is a difficult concept for PHBs,
> but there is no magic solution.
> Learn to live with it.
>

When an auditor has to sign off on it, "learn to live with it" is not a
very good solution when dealing with Sarb-Ox.

On Fri, 1 Sep 2006 23:33:06 +0200 Stefaan A Eeckels <hoendech@ecc.lu> wrote:
> On 1 Sep 2006 12:28:12 -0700
> "Karen Hill" <karen_hill22@yahoo.com> wrote:
>
>> Immutable files are files where not even root
>> can change/delete/move a file set as immutable.
>
> But root can unset the immutable flag. Thus it only serves as
> protection against accidental deletions or modifications. This is
> slightly useful. Roles are better for that purpose.
>
>> For the Oracle DBAs, how can you guarentee an audit trail without
>> immutable files?
>
> You cannot guarantee it with immutable files.
>
> Immutability is _not_ a security feature. It does _not_ solve the
> problem that root can change any file.

In *BSD, it can. You can disable unsetting the immutable flag.

> If you cannot trust your root user, you've got major problems. Trust
> is a difficult concept for PHBs, but there is no magic solution.

For some environments, root indeed has to be untrusted. e.g. kerberized
NFS can be setup in such a way that root on the local box does not get
you access to data you shouldn't have access to. (lots of ifs and buts
here, of course).

Anyway, if shipping a log file off the machine is enough to meet audit
requirements, then immutability shouldn't come into the picture. You
might have an immutable configuration that ships the logs (which would
protect against root changing this config), but root can change routing
to intercept the logs and alter them. Even if the system is so locked
down that this can't be done, root probably has physical access to the
machine and can insert his interceptor device. Even if that's not the
case, some network admin has access and can do this. So ultimately you
have to have trusted employees. Audit controls are about protecting
yourself from UNTRUSTED employees, not eliminating trust from the system.

No auditor will balk at not having immutable files as long as only trusted
employees are in the position to undetectably alter data.

-frank

DA Morgan wrote:
> Karen Hill wrote:
> > We know that Oracle and SUN/Solaris go together quite well on high end
> > installs. To insure an audit trail for BASEL , HIPPA, Sarbanes Ox and
> > other federal laws, one can ship oracle logs to an offsite server.
> > Yet, how can this guarentee an audit trail, when Solaris does not
> > support immutable files? Immutable files are files where not even root
> > can change/delete/move a file set as immutable.
>
> The secret it to keep audit trails inside the database and create an
> audit trail of any attempt to alter it.
>
> How can I tell if the audit trail's been altered?
> One way is to apply DBMS_CRYPTO to the data.
> Data alteration becomes impossible.

Where does Oracle keep the encryption keys? If someone has root could
they not just sniff out where oracle has the encryption keys and then
decrypt the data?

>
> Want additional methods? Apply some of Oracle's built-in capabilities
> such as checksums. Here are a list of topics you can look up in Morgan's
> Library at www.psoug.org that may help.

checksums are good, but how about the collisions in the sha1 and md5
that have been discovered?

>
> DBMS_CRYPTO
> OWA_OPT_LOCK.CHECKSUM
> OWA_OPT_LOCK.VERIFY_VALUES

Looks very informative, I will certainly look into this. I understand
that it is a layered defence which is good. I've read that oracle has
its own filesystem that one can apply to the raw disk. Maybe the
oracle filesystem has immutable settings?

On Fri, 01 Sep 2006 15:07:44 -0700
Frank Cusack <fcusack@fcusack.com> wrote:

> On Fri, 1 Sep 2006 23:33:06 +0200 Stefaan A Eeckels <hoendech@ecc.lu>
> wrote:
> > On 1 Sep 2006 12:28:12 -0700
> > "Karen Hill" <karen_hill22@yahoo.com> wrote:
> >
> >> Immutable files are files where not even root
> >> can change/delete/move a file set as immutable.
> >
> > But root can unset the immutable flag. Thus it only serves as
> > protection against accidental deletions or modifications. This is
> > slightly useful. Roles are better for that purpose.
> >
> >> For the Oracle DBAs, how can you guarentee an audit trail without
> >> immutable files?
> >
> > You cannot guarantee it with immutable files.
> >
> > Immutability is _not_ a security feature. It does _not_ solve the
> > problem that root can change any file.
>
> In *BSD, it can. You can disable unsetting the immutable flag.

You have to get into single user mode, which makes doing evil things a
bit more difficult. But only a bit, because scheduled downtime happens.

> > If you cannot trust your root user, you've got major problems. Trust
> > is a difficult concept for PHBs, but there is no magic solution.
>
> For some environments, root indeed has to be untrusted. e.g.
> kerberized NFS can be setup in such a way that root on the local box
> does not get you access to data you shouldn't have access to. (lots
> of ifs and buts here, of course).

OK, root on a workstation != the sysadmins. What I meant is that if
the sysadmins of the "corporate servers" cannot be trusted, you have
major problems.

<...>
> Audit controls are about protecting yourself from UNTRUSTED
> employees, not eliminating trust from the system.

Indeed - but the OP suggested that immutable files enabled a DBA to
protect her database from interference by the sysadmin.

> No auditor will balk at not having immutable files as long as only
> trusted employees are in the position to undetectably alter data.

In the 1980ies, I had to deal with an auditor who wanted to ensure that
two people were needed -together- to gain root access (each having
half the password). He also wanted a transcript of the root sessions to
be printed to a printer in a locked cabinet in his office. Major PITA,
this fellow, but he caught the GM at financial irregularities, so maybe
he had a point.

--
Stefaan A Eeckels
--
You rarely have time for everything you want in this life, so you
have to make choices. And hopefully your choices can come from a
deep sense of who you are. -- Fred Rogers

Comments in-line

Karen Hill wrote:
> DA Morgan wrote:
>> Karen Hill wrote:
>>> We know that Oracle and SUN/Solaris go together quite well on high end
>>> installs. To insure an audit trail for BASEL , HIPPA, Sarbanes Ox and
>>> other federal laws, one can ship oracle logs to an offsite server.
>>> Yet, how can this guarentee an audit trail, when Solaris does not
>>> support immutable files? Immutable files are files where not even root
>>> can change/delete/move a file set as immutable.
>> The secret it to keep audit trails inside the database and create an
>> audit trail of any attempt to alter it.
>>
>> How can I tell if the audit trail's been altered?
>> One way is to apply DBMS_CRYPTO to the data.
>> Data alteration becomes impossible.
>
> Where does Oracle keep the encryption keys? If someone has root could
> they not just sniff out where oracle has the encryption keys and then
> decrypt the data?

They can not sniff them for different reasons depending on how you go
about creating the system. Here's one way.

Create they key using the DBMS_CRYPTO package with RANDOMBYTES
inside of a function created using DBMS_DDL.CREATE_WRAPPED.
No human ever sees it and no human ever can.

Just be very sure you back up the system with great care.

>> Want additional methods? Apply some of Oracle's built-in capabilities
>> such as checksums. Here are a list of topics you can look up in Morgan's
>> Library at www.psoug.org that may help.
>
> checksums are good, but how about the collisions in the sha1 and md5
> that have been discovered?
>
>> DBMS_CRYPTO
>> OWA_OPT_LOCK.CHECKSUM
>> OWA_OPT_LOCK.VERIFY_VALUES
>
> Looks very informative, I will certainly look into this. I understand
> that it is a layered defence which is good. I've read that oracle has
> its own filesystem that one can apply to the raw disk. Maybe the
> oracle filesystem has immutable settings?

Stay out of the file system. The file system is for system admins and
there is nothing they can do that they can not undo. And these days,
with Oracle 10g there is really little you can do outside of the
database you can't do better inside.
--
Puget Sound Oracle Users Group

Stefaan A Eeckels wrote:

>>> Immutability is _not_ a security feature. It does _not_ solve the
>>> problem that root can change any file.
>> In *BSD, it can. You can disable unsetting the immutable flag.
>
> You have to get into single user mode, which makes doing evil things a
> bit more difficult. But only a bit, because scheduled downtime happens.

A bigger problem might be installing Oracle and getting support.
--
Daniel Morgan
Puget Sound Oracle Users Group