DB2 V8.02 and Oracle security audit information

Dear all,

from MS SQL, we can see if the  system will log failure login in the application log by right click on the DB server and then select security, then the failure login should be tick !

how can we make sure that DB2 V8.02 has the same thing and oracle 10g R2 and 11g R2 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi marrowyung,

It's a bit different with both Oracle and DB2.

Logging must be enabled on Oracle, and the login information is written to the listener log.

    ALTER SYSTEM set audit_trail=DB scope=spfile;

Stop and Start Oracle, then enter:

    AUDIT CONNECT;


DB2 normally uses O/S level authentication.  Login failures should be written to the system log.


Good Luck,
Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"...on Oracle ... the login information is written to the listener log".

That is not exactly correct.  The audit information in Oracle, if you set "AUDIT_TRAIL=DB", is written to the table: SYS.AUD$, which is usually viewed through the view: DBA_AUDIT_TRAIL.  

The "listener.log" file is an ASCII file on disk (outside of the database) that captures some information related to Oracle login attempts (and some other information).  But, that information is totally independent of auditing information in Oracle.
marrowyungSenior Technical architecture (Data)Author Commented:
KDO,

"DB2 normally uses O/S level authentication.  Login failures should be written to the system log."

so it is OS related, I have to read the solaris 9's system log anyway ?

markgeer,

"That is not exactly correct.  The audit information in Oracle, if you set "AUDIT_TRAIL=DB", is written to the table: SYS.AUD$, which is usually viewed through the view: DBA_AUDIT_TRAIL.  "

so once enable the audit of failure login only on instance level, I can then do select * from DBA_AUDIT_TRAIL  ?what should be the where condition ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
"what should be the where condition?"

That depends on how often you run this, and on what information you want to retrieve from there.  I don't know the answers to those questions.  Look at the data there and decide how to write the query based on what your organization requires.
marrowyungSenior Technical architecture (Data)Author Commented:
markgeer,

I find that there are no record by select * from SYS.AUD$ and DBA_AUDIT_TRAIL;

how can I make sure that only failure login audit trail is on ?

it seems that once i setup in Toad it doesnt' work as the query above shows nothing ! I tried to create failed login incident and it doesn't log down.
marrowyungSenior Technical architecture (Data)Author Commented:
I am reading this:

http://www.dba-oracle.com/t_tracking_counting_failed_logon_signon_attempts.htm

as we are undergoing ISO audit, how can I show if we have enable audit trial on failure logon attempt?

I run this :
audit create session whenever not successful;
on instance level ,

but not this
audit_trail=true

audit_file_dest='/u01/app/oracle/mysid/mydir/'

the running of this:

select 
   os_username,
   username,
   terminal,
   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')
from
   dba_audit_trail;

Open in new window


return nothing.

I use SQL developer 4.1  and Toad for oracle at office.
marrowyungSenior Technical architecture (Data)Author Commented:
Kdo,

you mean we can't even setup the DB2 failure login attempt log form DB level and then see the log from system level ? the system log you told ?
marrowyungSenior Technical architecture (Data)Author Commented:
right now on oracle, no matter how many time I do:

audit create session whenever not successful;

and follow by restart.

show parameter audit_trail;

still shows me:
NAME        TYPE   VALUE 
----------- ------ ----- 
audit_trail string NONE 

Open in new window

Kent OlsenDBACommented:
Hi marrowyung,

To stay within the DB2 tools, you'll need to turn on DB2 auditing.

  db2audit start

Then from DB2, define that you're auditing failed logins.

  db2 "create audit policy fl categories validate status both error type normal"
  db2 "audit database using policy fl"
 
That will set you up.  Audit policy fl (failed logins) is the control.
The next thing to do is create some data, just so we can see the results.

  db2 “connect to mydb"
  db2 "connect to mydb user db2admin"
  db2 "connect to mydb user notagooduserid"
  db2 "connect to mydb user {me} using {badpassword}"
  db2 "connect to mydb user {me} using {mypassword}"

Now we can audit the logins.

  db2audit flush
  db2audit database mydb to /tmp
  db2audit extract delasc to /tmp from files /tmp/db2audit.db.mydb.log


That should just about do it.

Kent
marrowyungSenior Technical architecture (Data)Author Commented:
"To stay within the DB2 tools, you'll need to turn on DB2 auditing.

   db2audit start

 Then from DB2, define that you're auditing failed logins.

   db2 "create audit policy fl categories validate status both error type normal"
   db2 "audit database using policy fl""

all these can be run from UI tools like Toad for DB2 ? or I must run under Solaris 9 environment ?

"  db2audit flush
   db2audit database mydb to /tmp
   db2audit extract delasc to /tmp from files /tmp/db2audit.db.mydb.log

"

what is that ?  can you explain command one by one ?

first thing is, how can I verify my DB2 has this ALREADY ? that one is a DB2 V8.02 on solaris 9 box.

and I just tried db2audit start on Toad UI, it said:

ERROR [42601] [IBM][DB2/SUN] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "db2audit start".  Expected tokens may include:  "JOIN <joined_table>".

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
also what will be the diff of the security log display by enabling
    db2audit start + db2 "create audit policy fl categories validate status both error type normal"
    db2 "audit database using policy fl""

from system.log as you said:

" Login failures should be written to the system log.
"
if both the same then I let system team to handle this


it seems that I have to execute this "   db2audit start" from within solaris system but not from UI tools for DB2, like Toad, after that, do I need to restart the dB2 for the DB2 to take effect ?

how about the other 2x command? no restart of DB2 is needed?

and if I want stop it, I have to type "db2audit stop", and then restart ?
marrowyungSenior Technical architecture (Data)Author Commented:
tks all for your help !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.