David Mann | 18 Nov 15:41 2011
Picon

Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

I have been diving into auditing over the past few weeks and have
worked out almost all the scenarios that we are interested in
auditing. Most of the actions are related to user activity. We have
one database where the customer wants all SYS activity audited as
well. These are 10gR2 or later databases on Solaris and Linux.

So I checked multiple blog posts, articles, and metalink docs and
finally saw one that mentioned my concern... I was trying to figure
out what can keep a SYS user from invoking say UTL_FILE and messing
with a file that lives in AUDIT_FILE_DEST directory or just logging in
as the oracle OS user and rm * in the AUDIT_FILE_DEST directory.

From [ID 174340.1] "Audit SYS User Operations". : "The SYS audit
records must go to OS files since the user SYS can delete his actions
from AUD$, whereas if the files are written to the OS, they can be
secured from the Oracle DBA by root (root must have some means to
transfer the files to a secure location). It is not possible to
configure that these records go into the AUD$ table."

I can only think of one right now but it doesn't seem nearly secure
enough. I guess I could have a sysadmin write a cron script to run as
root and copy contents of the directory to a destination not
acccessible by the oracle OS user. But what is the resolution of CRON?
1 minute? Of course would have to make sure we only copied the file
once so if the source file was changed at a later date it could be
detected.

Can anyone suggest any other configurations or mechanisms can be set
up to protect these files?

(Continue reading)

Tim Gorman | 18 Nov 17:49 2011

Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

David,
In the past, I've decided that it isn't necessary to move the ".aud" files, but just to change their
ownership. Much later on, they can be moved as needed to an archive location.

So, I created a UNIX shell script to be executed by "cron" as "root" to execute the UNIX "find" command to
execute "chown root:sys" on any ".aud" files in the AUDIT_FILE_DEST directory which are presently owned
by the Oracle software owner (i.e. "oracle").

So, here is a simplified sample script named "chown_adump.sh"...

#!/bin/ksh
#
sleep $1 # first, sleep specified number of seconds
#
_ADumpDir=${ORACLE_BASE}/admin/${ORACLE_SID}/adump # AUDIT_FILE_DEST directory pathname
_OracleOwner=oracle # name of Oracle software owner
#
find ${_ADumpDir} -name "*.aud" -owner ${_OracleOwner} -exec chown root:sys {} \;

Then, in the "crontab" for "root", specify entries such as this, in this example implementing a 15-second frequency...

* * * * * /scripts/root/chown_adump.sh 1
* * * * * /scripts/root/chown_adump.sh 16
* * * * * /scripts/root/chown_adump.sh 31
* * * * * /scripts/root/chown_adump.sh 46

All four entries will start every minute, but they will sleep for the specified period of time and then
execute. Of course, this could be done within a single script with 4 sleeps, etc...

Hope this helps...
(Continue reading)

Pete Finnigan | 21 Nov 14:57 2011

Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

Hi Tim,

Maybe i am missing something and i am not able to test for myself where
i am today but if you change the owner of any file to root:sys what
happens when the same file is needed to be appended to? - oracle uses
the PID on the file name and appends when the file exists?

cheers

Pete

Tim Gorman wrote:
> David,
> In the past, I've decided that it isn't necessary to move the ".aud" files, but just to change their
ownership. Much later on, they can be moved as needed to an archive location.
> 
> So, I created a UNIX shell script to be executed by "cron" as "root" to execute the UNIX "find" command to
execute "chown root:sys" on any ".aud" files in the AUDIT_FILE_DEST directory which are presently owned
by the Oracle software owner (i.e. "oracle").
> 
> So, here is a simplified sample script named "chown_adump.sh"...
> 
> #!/bin/ksh
> #
> sleep $1 # first, sleep specified number of seconds
> #
> _ADumpDir=${ORACLE_BASE}/admin/${ORACLE_SID}/adump # AUDIT_FILE_DEST directory pathname
> _OracleOwner=oracle # name of Oracle software owner
> #
> find ${_ADumpDir} -name "*.aud" -owner ${_OracleOwner} -exec chown root:sys {} \;
(Continue reading)

Don Granaman | 18 Nov 18:23 2011

RE: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

You *can* make audit_dump_dest a directory owned by the "oracle" user, but with group "auditors" (or
something other than dba), and then use the "oracle" OS account for routine DBA stuff.  With this, members
of the dba group cannot tinker with audit_dump_dest.

The basic idea is to prohibit OS logins as oracle unless an exception has been granted - and use "sudo -u
oracle ..." for other purposes, which logs the activity in the sudoers log.

Another potential option would be to set syslog_level and append the audit records to syslog.  This one has a
bunch of thorns though...

Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant .
Intelligent . Security

-----Original Message-----
From: oracle-l-bounce@...
[mailto:oracle-l-bounce@...] On Behalf Of David Mann
Sent: Friday, November 18, 2011 8:41 AM
To: oracle-l@...
Subject: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

I have been diving into auditing over the past few weeks and have
worked out almost all the scenarios that we are interested in
auditing. Most of the actions are related to user activity. We have
one database where the customer wants all SYS activity audited as
well. These are 10gR2 or later databases on Solaris and Linux.

So I checked multiple blog posts, articles, and metalink docs and
finally saw one that mentioned my concern... I was trying to figure
out what can keep a SYS user from invoking say UTL_FILE and messing
with a file that lives in AUDIT_FILE_DEST directory or just logging in
(Continue reading)

David Robillard | 19 Nov 17:48 2011
Picon

Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

Hello David,

Why don't you send the audit logs over to syslog? Once configured to
work with syslog, you can keep a local copy or have then sent over to
your central syslog server. Easy, clean and secure.

<ShamelessPlug>
Maybe that could help?
http://itdavid.blogspot.com/2011/02/manage-oracle-11gr2-asm-and-rdbms-audit.html
</ShamelessPlug>

HTH,

David
--
David Robillard
http://www.linkedin.com/in/davidrobillard
http://itdavid.blogspot.com/

> I have been diving into auditing over the past few weeks and have
> worked out almost all the scenarios that we are interested in
> auditing. Most of the actions are related to user activity. We have
> one database where the customer wants all SYS activity audited as
> well. These are 10gR2 or later databases on Solaris and Linux.
>
> So I checked multiple blog posts, articles, and metalink docs and
> finally saw one that mentioned my concern... I was trying to figure
> out what can keep a SYS user from invoking say UTL_FILE and messing
> with a file that lives in AUDIT_FILE_DEST directory or just logging in
> as the oracle OS user and rm * in the AUDIT_FILE_DEST directory.
(Continue reading)

David Mann | 21 Nov 17:51 2011
Picon

Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

On Sat, Nov 19, 2011 at 11:48 AM, David Robillard
<david.robillard@...> wrote:
> Hello David,
>
> Why don't you send the audit logs over to syslog? Once configured to
> work with syslog, you can keep a local copy or have then sent over to
> your central syslog server. Easy, clean and secure.
>
> <ShamelessPlug>
> Maybe that could help?
> http://itdavid.blogspot.com/2011/02/manage-oracle-11gr2-asm-and-rdbms-audit.html
> </ShamelessPlug>

I think this is the way to go. I have probably skimmed that section of
the docs a half dozen times but obviously it never 'stuck;. Also
thanks to Paul D. who replied to me directly about the same method.
Now on to talk to the sysadmins and get a thumbs up from them :)

Don we are on our way to locking oracle user and using sudo 100% of
the time but not quite there yet.

Tim I like your method for getting granularity better than 1
time/minute with cron... but I think still there is some exposure
there ... if a malicious DBA is determined he could brute force rm* in
that directory and possibly remove some files.

-Dave

--

-- 
Dave Mann
(Continue reading)

Don Granaman | 23 Nov 18:19 2011

RE: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

Be aware that there are some potential "issues" with syslog.  Here are a few:

If AUDIT_SYS_OPERATIONS=TRUE, then the audit records generated by this will be sent to syslog - unless
AUDIT_TRAIL=XML.  Then they are in XML files and not appended to syslog.

If AUDIT_SYS_OPERATIONS=TRUE (and AUDIT_TRAIL=OS or DB), then the audit records in syslog generated by
AUDIT_SYS_OPERATIONS will break long chunks of SQL up into multiple pieces - and you will need to piece
them back together. In OS or XML files, long SQL will be in one long section (as of 10.2.0.4 at least).

For standard audit trail records to be sent to syslog requires AUDIT_TRAIL=OS.  EXTENDED is not available
for OS, so you cannot get SQLTEXT or SQLBIND.

You *can* set AUDIT_SYSLOG_LEVEL=<something.useful> and [AUDIT_TRAIL=DB,EXTENDED or
AUDIT_TRAIL=XML,EXTENDED] to send stuff subject to AUDIT_SYS_OPERATIONS to syslog and "standard
audit trail" records to DB or XML.  This would "protect" only the former from the DBA though.

Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | Solutionary | Relevant .
Intelligent . Security

-----Original Message-----
From: oracle-l-bounce@...
[mailto:oracle-l-bounce@...] On Behalf Of David Mann
Sent: Monday, November 21, 2011 10:51 AM
To: oracle-l@...
Subject: Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *NIX...

On Sat, Nov 19, 2011 at 11:48 AM, David Robillard
<david.robillard@...> wrote:
> Hello David,
>
(Continue reading)

David Robillard | 22 Nov 16:53 2011
Picon

Re: Protecting contents of AUDIT_FILE_DEST from 'oracle' OS user on *N

Hello David,

>> Why don't you send the audit logs over to syslog? Once configured to
>> work with syslog, you can keep a local copy or have then sent over to
>> your central syslog server. Easy, clean and secure.
>>
>> <ShamelessPlug>
>> Maybe that could help?
>> http://itdavid.blogspot.com/2011/02/manage-oracle-11gr2-asm-and-rdbms-audit.html
>> </ShamelessPlug>
>
> I think this is the way to go. I have probably skimmed that section of
> the docs a half dozen times but obviously it never 'stuck;.

Good idea :) I just noticed that in my blog post, logrotate is
configured to create the log file with owner "oracle" and group
"oinstall". This is obviously not what you want to do in your case. So
just change the /etc/logrotate.d/oracle.audit file by removing the
"create 0640 oracle oinstall" line from the file and you should be
good. You can test your logrotate configuration by running

sudo logrotate -d /etc/logrotate.conf

No changes will be made to your system, but if you have configuration
errors, they will be printed out along with all what logrotate would
do if it would normally execute.

> Also thanks to Paul D. who replied to me directly about the same method.
> Now on to talk to the sysadmins and get a thumbs up from them :)

(Continue reading)


Gmane