Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.