Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security DB Auditing, Considerations - Part 4
Focusing Statement, Privilege, and Schema Object Auditing
The Oracle Database Server lets you focus statement,
privilege, and schema object auditing in three ways.
-> Auditing statement Execution,
-> Number of executions of a statement,
-> Audit by user.
Auditing Statement Executions: Successful, Unsuccessful, or
Both
For the auditing of statements, privileges and schema
object auditing, Oracle allows you to audit successful attempts, unsuccessful
attempts or both. This gives you to
monitor those activities that were not successful, so that you can investigate
to determine if there was a malicious attempt to inappropriately change your
data.
Statements that did not execute successfully, but were also
not a valid SQL statement will not be audited.
If the statement failed for example a CREATE TABLE statement that did
not have enough quota for a specific tablespace, then you will be in a position
to do something about it.
When you audit statement includes the WHENEVER SUCCESSFUL
clause, then you will enable all the successful executions of the audited
statement. When your audit statement
contains the WHENEVER NOT SUCCESSFUL clause, then you will be auditing only the
unsuccessful audited statements. When
your audit statement does not contain either of the above tow clauses, then you
will be auditing the audited statement whether it is successful or not.
Number of Audit Records from Multiple Executions of a
Statement
The important points in this concept are the SESSION, and
the number of times a statement is executed.
Let’s say that in a single user session, an audited statement is
executed a number of times. If the
auditing was defined BY ACCESS, then there will be an audit record for every
time the statement was executed. If the
auditing was defined BY SESSION, then there will only be one record for that
session, regardless of how many times the statement was executed during the
session.
Several audit options can only be set BY ACCESS:
-> All statement audit options that audit DDL
statements.
-> All privilege audit options that audit DDL
statements.
All other audit options, BY SESSION is used by default.
BY SESSION
For any type of audit on a schema object, statement, or
privilege, that is not a DDL statement.
BY SESSION inserts only one audit record in the audit trail, for each
schema or user object that includes an audited action, during the session. We define a session here as the time from
when a user connects to the database, and ends when the user disconnects from
the Database.
BY SESSION Example
-> The SELECT TABLE statement auditing option is set BY
SESSION.
-> User1 connects to the database and issues seven
SELECT statements against the table named DEPT, and then disconnects from the
database, or ends his session.
-> User2 creates a session and issues two select
statements against the table EMP, and then ends the session by disconnecting.
The audit trail now only contains two records, although
there were nine select statements issued.
There is one for each session that issued a SELECT statement.
BY SESSION Additional Example
-> The SELECT TABLE statement auditing option is set BY
SESSION.
-> User3 connects to the database and issues eleven
select statements against the table DEPT, and four select statements against
the table EMP, and then disconnects from the database, so ending his session.
In this case the audit trail contains two records and not fifteen
records. One record for each schema
object against which the user issued a SELECT statement in the session.
However if
you are directing your audit records to the operating system audit trail then
things work differently. Even if you
have defined the SELECT TABLE auditing option as BY SESSION, the Oracle
Database Server will still create an audit record for each time an access is
made. Therefore if you are writing your
audit records to the operating system audit trail, then BY SESSION is equivalent
to BY ACCESS.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment