This chapter contains:
See Also:
About Auditing
9 Verifying Security Access with Auditing. Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is.
Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2. Oracle 11g Release 1 turned on auditng by default for the first time. Oracle 11g Release 2 now allows better management of the audit trail using the DBMS_AUDIT_MGMT package. Moving the Database Audit Trail to a Different Tablespace; Controlling the Size and Age of the OS. Oracle 11g Security - Guidelines for Auditing By James Koopmann Before starting to audit your Oracle database, use some of these guidelines to help you plan your auditing initiative.
Auditing is the monitoring and recording of selected user database actions. In standard auditing, you use initialization parameters and the
AUDIT and NOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multitier activities.
There are also activities that Oracle Database always audits, regardless of whether auditing is enabled. These activities are administrative privilege connections, database startups, and database shutdowns. See Oracle Database Security Guide for more information.
Another type of auditing is fine-grained auditing. Fine-grained auditing enables you to audit at the most granular level, data access, and actions based on content, using Boolean measurement, such as
value > 1000 . You can use fine-grained auditing to audit activities based on access to or changes in a column. You can create security policies to trigger auditing when someone accesses or alters specified elements in an Oracle database, including the contents within a specified object. You can create policies that define specific conditions that must take place for the audit to occur. For example, you can audit a particular table column to find out when and who tried to access it during a specified period of time. Furthermore, you can create alerts that are triggered when the policy is violated, and write this data to a separate audit file. Oracle Database Security Guide explains how to perform fine-grained auditing.
Why Is Auditing Used?
You typically use auditing to perform the following activities:
Where Are Standard Audit Activities Recorded?
Oracle Database records audit activities in audit records. Audit records provide information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail. Oracle Database also provides a set of data dictionary views that you can use to track suspicious activities. See Oracle Database Security Guide for more information about these views.
When you use standard auditing, Oracle Database writes the audit records to either to
DBA_AUDIT_TRAIL (the SYS.AUD$ table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.
In addition, the actions performed by administrators are recorded in the
syslog audit trail when the AUDIT_SYSLOG_LEVEL initialization parameter is set.
Auditing General Activities Using Standard Auditing
This section explains how to use standard auditing to audit activities performed on SQL statements, privileges, schema objects, and network or multitier activities.
This section contains:
See Also:
Oracle Database Security Guide for detailed information about managing the standard audit trail
About Standard Auditing
In standard auditing, you enable auditing of SQL statements, privileges, schema objects, and network or multitier activities. You can audit a specific schema table if you want. To perform this type of audit, you use Database Control.
You can view the standard audit trail by querying the
DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL data dictionary views.
See Also:
Oracle Database Security Guide for a roadmap of how and why you can use the different types of audit options available
Enabling or Disabling the Standard Audit Trail
Before you perform the standard auditing procedures described in this section, you must enable standard auditing. When you enable standard auditing, you can create the audit trail in the database audit trail or write the audit activities to an operating system file. If you write to an operating system file, you can create the audit record in text or XML format.
To enable or disable the standard audit trail:
Note the following:
Using Default Auditing for Security-Relevant SQL Statements and Privileges
When you use Database Configuration Assistant (DBCA) to create a new database, Oracle Database configures the database to audit the most commonly used security-relevant SQL statements and privileges. It also sets the
AUDIT_TRAIL initialization parameter to DB . If you decide to use a different audit trail type (for example, OS if you want to write the audit trail records to operating system files), then you can do that: Oracle Database continues to audit the privileges that are audited by default. If you disable auditing by setting the AUDIT_TRAIL parameter to NONE , then no auditing takes place.
Oracle Database audits the following privileges by default:
Oracle Database audits the following SQL statement shortcuts by default:
To individually control the auditing of SQL statements and privileges, use the
AUDIT and NOAUDIT statements.
Oracle strongly recommends that you audit the database. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations and catch any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software. By enabling auditing by default, you can generate an audit record for audit and compliance personnel.
Note:
If your applications use the default audit settings from Oracle Database 10g Release 2 (10.2), then you can revert to these audit settings until you modify the applications to use the Release 11g audit settings. To do so, run the undoaud.sql script.
After you have modified your applications to conform to the Release 11g audit settings, then you can manually update your database to use the audit configuration that suits your business needs, or you can run the
secconf.sql script to apply the Release 11g default audit settings.
The
undoaud.sql and secconf.sql scripts are in the $ORACLE_HOME/rdbms/admin directory. The undoaud.sql script affects audit settings only, and the secconf.sql script affects both audit and password settings. They have no effect on other security settings.
See Also:
Individually Auditing SQL Statements
The SQL statements that you can audit are in the following categories:
Statement auditing can be broad or focused, for example, by auditing the activities of all database users or of only a select list of users.
See Also:
Oracle Database Security Guide for detailed information about auditing SQL statements
Individually Auditing Privileges
Privilege auditing is a way to audit statements that can use a system privilege. For example, you can audit the
SELECT ANY TABLE privilege if you want to audit all the SELECT statements that will use the SELECT ANY TABLE privilege. You can audit the use of any system privilege. Similar to statement auditing, privilege auditing can audit the activities of all database users or of only a specified list. As with SQL statement auditing, you use the AUDIT and NOAUDIT statements to enable and disable privilege auditing. In addition, you must have the AUDIT SYSTEM system privilege before you can enable auditing.
Privilege audit options match the corresponding system privileges. For example, the option to audit use of the
DELETE ANY TABLE privilege is DELETE ANY TABLE . For example:
To audit all successful and unsuccessful uses of the
DELETE ANY TABLE system privilege, enter the following statement:
To audit all unsuccessful
SELECT , INSERT , and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:
See Also:
Oracle Database Security Guide
![]() Using Proxies to Audit SQL Statements and Privileges in a Multitier Environment
You can audit the activities of a client in a multitier environment by specifying a proxy in the Add Audited Statements or Add Audited Privileges page in Database Control. You can use the SQL
AUDIT statement to audit the activities of a client in a multitier environment. To do so, use the BY user clause in the AUDIT statement.
For example, to audit
SELECT TABLE statements issued by the proxy application user jackson :
Afterward, user
jackson can connect using the appserve proxy user as follows:
The middle tier can also set the user client identity in a database session, enabling the auditing of user actions through the middle-tier application. The user client identity then shows up in the audit trail.
See Also:
Oracle Database Security Guide for detailed information about auditing in a multitier environment
Individually Auditing Schema Objects
Schema object auditing can audit all
SELECT and DML statements permitted by object privileges, such as SELECT or DELETE statements on a particular table. The GRANT and REVOKE statements that control those privileges are also audited.
See Also:
Oracle Database Security Guide
Oracle 11g Audit Trailfor detailed information about auditing schema objectsAuditing Network Activity
You can use the
AUDIT statement to audit unexpected errors in network protocol or internal errors in the network layer. The types of errors uncovered by network auditing are not connection failures, but can have several other possible causes. One possible cause is an internal event set by a database engineer for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption.
To enable network auditing:
Oracle 11g Audit Programs
See Also:
Oracle Database Security Guide for detailed information about auditing network activity
Tutorial: Creating a Standard Audit Trail
Suppose you wanted to audit
SELECT statements on the OE.CUSTOMERS table. In this tutorial, you enable standard auditing, enable auditing for the SELECT SQL statement, run the SELECT SQL statement on the OE.CUSTOMERS table, and then check its audit file.
In this tutorial:
Step 1: Log In and Enable Standard Auditing
First, log in, and, if necessary, enable standard auditing.
To enable standard auditing:
Step 2: Enable Auditing for SELECT Statements on the OE.CUSTOMERS Table
Next, enable auditing for
SELECT statements on the OE.CUSTOMERS table.
To enable auditing of SELECT statements for the OE.CUSTOMERS table:
Step 3: Test the Audit Settings
At this stage, auditing is enabled and any
SELECT statements performed on the OE.CUSTOMERS table are written to the to DBA_AUDIT_TRAIL data dictionary view. Now, you are ready to test the audit settings.
To test the audit settings:
Step 4: Optionally, Remove the Components for This Tutorial
Optionally, remove the audit settings that you created earlier.
To remove the audit settings in Database Control:
Step 5: Remove the SEC_ADMIN Security Administrator Account
This is the last example in this guide. If you no longer need the
sec_admin administrator account, then you should remove it.
To remove the sec_admin security administrator account:
Guidelines for Auditing
This section contains the following topics:
Guideline for Using Default Auditing of SQL Statements and Privileges
When you create a new database, you can enable the auditing of a select set of SQL statements and privileges. Oracle recommends that you enable default auditing. Auditing is an effective method of enforcing strong internal controls so that your site meets its regulatory compliance requirements. See 'Using Default Auditing for Security-Relevant SQL Statements and Privileges' for more information about default auditing.
Guidelines for Managing Audited Information
Although auditing has a minimal impact on database performance, limit the number of audited events as much as possible. This minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.
Follow these guidelines when devising an auditing strategy:
Oracle 11g Audit Sys OperationsGuidelines for Auditing Typical Database Activity
When your purpose for auditing is to gather historical information about particular database activities, follow these guidelines:
Guidelines for Auditing Suspicious Database Activity
When you audit to monitor suspicious database activity, follow these guidelines:
Initialization Parameters Used for Auditing
Table 7-1 lists initialization parameters that you can use to secure auditing.
Table 7-1 Initialization Parameters Used for Auditing
To modify an initialization parameter, see 'Modifying the Value of an Initialization Parameter'. For detailed information about initialization parameters, see Oracle Database Reference and Oracle Database Administrator's Guide.
Active6 years, 3 months ago
Is it possible with Oracle auditing to find out if the role can be revoked from particular user without loosing any privileges that he actually uses?
Something like
AUDIT webadmin_role BY webapp_user WHENEVER SUCCESSFUL ?
ml43ml43
1 Answer
TL;DR: You cannot audit the roles usage.
You can however use the following views to determine which system privileges, object privileges, and other roles are granted to the roles which are in turn granted to the current user:
The only complicated thing here is privileges and roles inherited from other roles. If it's not the case for you, and the roles granted to your user have one level of inheritance, i. e. these roles are not children of any other roles, then you can just use the following queries to determine the privileges inherited by a particular user from the roles.
Determine system privileges:
Determine object privileges:
In this example the user
SPONGEBOB is assigned two roles INDEX_MGR and HR_QUERY from which he inherits system and object privileges. But what if these roles inherit privileges from other roles? For such case let's implement some more complex model:
As you might guess,
APP_USER is a role granted to another role APP_UNLIM_USER , which in turn is granted to the role INDEX_MGR . The roles INDEX_MGR and HR_QUERY are granted directly to the user SPONGEBOB . In the diagram, all the privileges granted to the roles are put in front of the roles' names to which they are granted in the corresponding lines, e. g. APP_USER role is granted only CREATE SESSION privilege.
Let's query
ROLE_ROLE_PRIVS view described earlier on behalf of the SPONGEBOB user:
From the result, you can see that the role inheritance matches the one shown in the diagram. Now, we can use the role names returned by the query to determine what privileges these roles are granted.
First, let's present the roles' names in one column:
Then we'll determine which roles are assigned to the user
SPONGEBOB directly. As you can see on the diagram above, these are HR_QUERY and INDEX_MGR :
Now, we'll combine the two previous queries together to have all roles' names in one result:
Having obtained all the roles' names, we can now determine which privileges are granted to that roles. (In the examples below I replaced the previous query code with
MYROLES for brevity.)
System privileges:
Object privileges:
Since you now have all the privileges shown in a user-friendly format, you can automatically construct the auditing statements to determine which privileges are actually used. For example, the following query will return you the audit statements which you can use to check what system privileges are used:
The following query returns the object auditing statements:
In the same vein, you can construct Yasir ArsanukaevYasir Arsanukaev
NOAUDIT statements to stop auditing the privileges.
2,71533 gold badges1414 silver badges2929 bronze badges
Not the answer you're looking for? Browse other questions tagged oracleaudit or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |