Skip to Main Content
  • Questions
  • Audit Trail : Disable my bash script audit

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, gonzalo.

Asked: January 12, 2018 - 5:35 pm UTC

Last updated: January 27, 2018 - 2:20 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

Hello Tom.

I set audit trail to "XML,EXTENDED" , because my $AUD table was growing to much.
I have a lot of 4kb files generated. I have several scripts in my crontab, and that is what is being audited.

The content of the files are like this:

Audit file /o2/audit_trail/BASE_ora_5110_2.aud
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
ORACLE_HOME = /o/app/oracle/product/11.2.0/db_seo
System name:    Linux
Node name:      oracleDB
Release:        3.8.13-118.2.5.el6uek.x86_64
Version:        #2 SMP Tue Jan 19 15:43:24 PST 2016
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: BASE
Redo thread mounted by this instance: 1
Oracle process number: 85
Unix process pid: 5110, image: oracle@oracleDB (TNS V1-V3)

Fri Jan 12 08:25:01 2018 -03:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '*********'


After that, i set audit_trail to none, but still files are generated.


SQL> show parameter audit_trail;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_trail        string  NONE


My questions are :

1) Why are those file generated if audit_trail is NONE

2) How can I filter, in order to not auditing my own scripts.

Best,
Thank you in advanced.

and Connor said...

Easy - stop connecting as SYSDBA :-)

From MOS note 103964.1


Explanation
-----------
Administrative user connections are not written to database tables as these
connections are needed in order to start and stop the database. The files where
the administrative user connections are written to are stored externally from
the database so they can be accessed when the database is down. For example,
the administrative user connect to startup the database cannot be audited
(written) to a database table so auditing must be done externally to the
database, as opposed to other audited activity (as example: successfull/
unsuccessfull executions of specified SQL statements, auditing privileges or
objects).

The administrative user connections are always audited regardless of the init.ora
parameter audit_trail.

This feature was created in order to comply with the NCSC C2 security
evaluation criteria. NCSC deemed that an "oper" user should not be able to
disable this level of auditing.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

gonzalo amadio, January 15, 2018 - 5:35 pm UTC

Thank you. That was good answer.

So, what is the "standard" way of doing scripts.

I have a lot of scripts running on cron. Backups, reporting, logging to files, etc.

For example, one of my sscripts is:

#!/bin/bash
sqlplus -S "/ as sysdba" @show-blocking-locks.sql


Then I execute that on cron every X minutes.

What should I put instead of "sqlplus / as sysdba" ?

Or should I create a specific user with admin privileges?

Best, and thank you.
Connor McDonald
January 16, 2018 - 3:55 am UTC

Or should I create a specific user with admin privileges?

Definitely. I'm a big fan of using a "least set of privileges" approach.

And you can still avoid the need for passwords in script by using a wallet, eg

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

Great

gonzalo amadio, January 25, 2018 - 4:03 pm UTC

Nice..

What privileges do you reccomend me to assign to the user.

Also a question on wallet?

What happens if I create a wallet , but also want to connect with SO authentication , i.e. sqlplus / as sysdba. ?

Connor McDonald
January 27, 2018 - 2:20 am UTC

"as sysdba" is special, and should almost never be needed for day to day use. Keep SYSDBA for startup, shutdown, patching and the occasional grant of a SYS level privilege (eg execute on DBMS_LOCK).


"What privileges do you reccomend me to assign to the user. "

Only just enough to get the job done. If I'm (say) doing a free space check, then maybe:

create session
select access on dba_free_space, dba_data_files, etc etc

It can be very comforting knowing that *even* if someone hacks your monitoring accounts, all they can do is check free space and not much else.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.