Database Support: Security DB Auditing, Consider

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 1

Auditing

Monitoring and or recording selected user database actions is called auditing. You can base auditing on actions, like a specific type of SQL statement, or you can base auditing on a combination of factors, that could include the username, application, time, IP address etc. Security policies trigger auditing when the specified elements in an Oracle Database are accessed or altered. This may include the contents of a specified object.

Auditing can be used to:

-> Enable future accountability: Current actions in a schema or on a named table, or a table row, or even to do with a certain piece of data that is sensitive.

-> Deter users, from performing inappropriate actions on the data, because of the knowledge that the database is being audited, and there will be accountability.

-> Establish and investigate suspicious activity. If it is suspected that a user is maliciously deleting data from tables, then you can audit all the connections to the database, and all successful and unsuccessful deletions of rows from all the tables in the database.

-> You can notify an auditor, that a user is manipulating data directly in the database, and that the user has more privileges than they are expected to have. This could lead to reassessing user authorizations.

-> Monitor and gather data for specific database activity.

The DBA’s can gather statistics with regards to which tables are being updated, how many logical I/O’s are performed, or how many concurrent users connect at peak times.

-> Use Auditing to detect problems with your authorization and access control systems.

You can set up policies that are never expected to fire, because the data is protected in other ways and by other security mechanisms. If one of these policies does fire and generate audit records, then you know that, then you know that your authorization and access control systems are not working the way you expected them to.

There are a few different types of Auditing available in Oracle:

-> Auditing types and Records

-> Statement Auditing

-> Privilege auditing

-> Schema Object Auditing

-> Fine-Grained Auditing

-> Focussing Statement, Privilege, and Schema Object Auditing

-> Auditing in a Multitier Environment

Auditing Types and Records

You can set your auditing options to be broad, or you can focus them in on specifics.

-> Successful statement executions, unsuccessful statement executions or both.

-> Statement executions once in each user session or once every time the statement is executed.

-> Activities of a specific user, or the activities of all the users.

Auditing Types and Descriptions

Type of Auditing (link to discussion)	Meaning/Description
Statement Auditing	Enables you to audit SQL statements by type of statement, not by the specific schema objects on which they operate. Typically broad, statement auditing audits the use of several types of related actions for each option. For example, AUDIT TABLE tracks several DDL statements regardless of the table on which they are issued. You can also set statement auditing to audit selected users or every user in the database, ie focussed or broad.
Privilege Auditing	Enables you to audit the use of powerful system privileges that enable corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing, which audits only a particular type of action. You can set privilege auditing to audit a selected user or every user in the database.
Schema Object Auditing	Enables you to audit specific statements on a particular schema object, such as AUDIT SELECT ON employees. Schema object auditing is very focused, auditing only a single specified type of statement (such as SELECT) on a specified schema object. Schema object auditing always applies to all users of the database, so it will pick up anybody who queries the employees table for example.
Fine-Grained Auditing	Enables you to audit at the most granular level, data access and actions based on content, using any Boolean measure, such as value > 1,000,000. Enables auditing based on access to or changes in a column.