All language subtitles for [SubtitleTools.com] Auditing - Learning Oracle 12c [Video]

In this lesson, we'll
be looking at auditing.

So let me begin this with
a little story of something

that happened to me
professionally as a DBA.

So I was working for a company
who had hired a security

company to attempt to penetrate
our databases and, really,

our entire network.

And the report came back.

It was very brief.

It didn't go into
a lot of detail

for a very particular purpose
because that was really

why they had assigned a
security company to attempt

to penetrate the databases--

that said that one
of the databases, one

that I was not working
with or responsible for,

had been penetrated.

And so the company was,
obviously, upset about this.

And so it showed that
the security in place

was not adequate to protect
it from outside actions.

And so they really had
no incident response

as to what to do.

And so they talked to
me and asked me, well,

what should we do?

We have almost no information.

We know which database it
was that they penetrated.

But we don't know what
information they had.

We don't know where they came
from, so on and so forth.

And I simply said, well,
is auditing turned on?

And because auditing
was turned on,

we were able to look
at the audit trail

and find out that the would-be
attackers had penetrated

the database at
such and such time,

that they'd used this
account, that they'd

come from this machine,
and these are the actions

that they did.

So that's the
importance of auditing.

And auditing is very, very
important in the security world

because you may not be able
to prevent every attempt

to penetrate your defenses or
even the operations that might

occur should you be penetrated.

But you can have some
sort of incident response.

You can have a log
of what occurred.

And then you can take
action after that point.

So auditing goes back to
the security principle

that every action should be
traceable to one and only one

user.

Auditing is a history
of recorded operations

in the database.

So there are two
things that must

occur in order for auditing
to be working as it should.

It must be enabled.

And then auditing
must be assigned.

So that is to say we
must assign auditing

to record certain operations.

So we enable auditing using the
database parameter AUDIT_TRAIL.

And the AUDIT_TRAIL can
have several values.

The most common are
here on the screen.

So the possible values
are NONE or FALSE.

Both of those values
do the same thing.

And they turn off auditing.

So even if we have
assigned various operations

to be audited, no
record will occur

because the
parameter AUDIT_TRAIL

is set to NONE or FALSE.

Another possible value is OS.

If that is set to OS,
AUDIT_TRAIL equal OS,

then any audit records
will be written to the file

system of the operating system.

In Windows, this will actually
go to the security log.

And in Unix and Linux,
they'll actually

be written out to files
in a particular directory.

This is not really
advisable, in my opinion,

because the amount
of information

that could be generated by
auditing is substantial.

And having it out in hundreds of
thousands or tens of thousands

of files in the operating system
makes it very, very difficult,

if you do have a break
or a breach in security,

to try to piece
together what occurred.

The advisable value
for AUDIT_TRAIL

is DB, which means that
it goes to the database.

And it's actually stored
in a database table.

This is highly
preferable, in my opinion,

because you can use just
regular SQL commands to find

information in the audit trail.

So if you want to
look at everything

that happened within a
particular 24-hour period,

you can write a query, a
simple SELECT statement,

against the audit trail that
will give you that information.

So once we've
enabled auditing, we

have to assign audit actions.

For that, we use
the AUDIT command.

We can audit a system privilege
or an object privilege.

And we simply tell Oracle
to audit the CREATE TABLE

privilege, for instance.

We have the option of auditing
kind of on a finer grained

level.

We can say audit a
particular privilege

or a particular object
privilege whenever successful

or whenever not successful.

So we could say, AUDIT CREATE
TABLE WHENEVER NOT SUCCESSFUL.

So if somebody attempts to
create a table unsuccessfully,

maybe because they
don't have the privilege

or because they don't have
privileges on the storage,

that gets written.

But anytime someone
successfully creates a table,

we could deem that as
not a problem situation

because they have the privilege.

And they used it.

And so we don't need to audit
whenever it is successful.

We audit whenever
not successful.

We can also audit by
access or by session.

When we audit by
access, we're saying

that we will record an
audit record every time

a particular privilege is used.

So if we say AUDIT INSERT
on the table scott.emp

and we say BY ACCESS, every
time an INSERT statement occurs,

there will be an audit
record written for that.

And that could generate way
more auditing than we really

want to.

So we might audit by session.

And if we audit it by
session, then we're

saying that anytime
within a session

that the INSERT into
scott.emp occurs,

we'll record one audit record.

And it makes the management
of the audit data

much more manageable.

When we want to know auditing
information about what's

occurred in our recorded
operations in the database,

we look at DBA_AUDIT_TRAIL.

And DBA_AUDIT_TRAIL
is simply a view

that overlays a system table.

So the system tables
are owned by SYS.

So we can call that
table SYS.AUD$.

And the AUD$ table is
the Oracle-based table

that actually holds
auditing information.

So to take a look at this, we
have our system connection.

And we're going to open a
connection for kara as well.

The first thing
we should look at

is to see if
auditing is enabled--

show parameter audit trail.

And it states that
audit_trail is set to DB.

And that's the default
when you create

a database using the Database
Configuration Assistant.

And that is what we want.

So let's select star
from dba_audit_trail.

So auditing is turned on.

But as of this moment, we have
no actions that we're auditing.

So let's set a few.

We will audit select
on the scott.emp table.

Now we'll audit select on
scott.dept whenever successful.

And we'll audit delete on
scott.bonus whenever not

successful by session--

misspell there.

So we need it in this order,
audit delete on scott.bonus

by session whenever
not successful.

Now we had a successful audit.

Again, looking from
the audit_trail,

none of these
actions has occurred.

So let's have our kara
user do a few of these.

And so kara is going to
select star from scott.emp

and select star from scott.dept
and delete from scott.bonus.

Let's take a look at it first.

So that should generate
some audit information.

So we look at select star
from dba_audit_trail.

And we get some information.

We see that the OS
username is listed here.

So that's the name
of the account

that logged in to the database.

And this is their OS username.

So this is their
login on the machine

that that client
connection came from.

The username they
connected with was KARA.

Their hostname time stamp
for when it occurred,

the objects involved,
and the actions--

so it was scott.emp and
scott.dept and select on them--

lots of other information.

This extended time stamp
gives more time information.

But a lot of what we're
really interested in here

is in the first few columns.

So notice that there
are two records.

Kara did the select
from emp and dept.

But she also did a delete.

And we were auditing delete.

But kara successfully
deleted from the bonus table.

And we audited whenever
not successful.

So no entry was written
into the log for that

because we had specifically
indicated that we did not

want to record that event,
the delete of bonus,

only when it was not successful.

So this is an
example of auditing

and how we can use it to track
occurrences in the database

and keep a full record of what's
occurred for security purposes.