? Describe the DBA's responsibility for security and audit work? Enable standard database auditing? Specify audit options? Review audit information? Maintain audit trail
? A user with DBA authority must be trustworthy. – Abuse of trust – protect trusted locations with audit trails? Must share the responsibility of the DBA.? Never share an account.? The DBA and system administrator must be a different person. Separate the responsibilities of the operator and the DBA. The following are the main requirements to meet the separation of responsibilities.The DBA must be trustworthy:It's hard to limit what a DBA does. To get the job done, the DBA needs a high level of authority. The DBA is a trusted position and must therefore undergo a thorough inspection. Even a trusted DBA must be held accountable. Consider the following factors:? Abuse of trust:The DBA may abuse the encrypted password in the Dba_users view.To protect a trusted location with an audit trail:After careful implementation of the audit and adherence to the guidelines, the audit trail indicates that a particular person is not violating the procedures or damaging behavior. A well-designed audit trail captures this behavior if a malicious user tries to make people suspect a trusted user.Oracle Database Vault:In the following cases, you can use the Oracle database Vault option: Databases must enforce segregation of duties or do not allow DBAs to view data in some or all of the database scenarios.
A secure system ensures the confidentiality of the data contained. Security features in the following areas:? Restrict access to data and services? Authenticate users? Monitoring suspicious active Database security Oracle Databases 11g provides the industry's best security system framework. However, to make this framework work, the database administrator must follow best practices and continuously monitor database activity.restricting access to data and servicesnot all users should have access to all data. Depending on what is stored in the database, limited access can be enforced by business needs, customer expectations, and increasing legal restrictions. Credit card information, healthcare data, identification information, etc. must be protected from unauthorised access. Oracle DB provides finegranular authorization control to restrict access to the database. Restricting access must include the apply least privilege principle. Verifying usersIn order to enforce access control over sensitive data, the system must first know who is trying to access the data. If the authentication mechanism is vulnerable, all other security precautions become useless. The most basic user authentication method is to require the user to provide known authentication information, such as a password. If you can guarantee that passwords follow simple rules, you can greatly enhance the security of your system. A more rigorous validation approach involves requiring the user to provide certain validation information, such as a token or public key Infrastructure (PKI) certificate. A more rigorous approach is to identify users through unique biological features such as fingerprints, iris, and bone tissue patterns. Oracle DB supports advanced authentication techniques, such as token, biology, and certificate-based identity technology, by using a premium security option. To prevent a drill-through validation vulnerability, you must lock out the currently unused user accounts.Monitor suspicious activityEven authorized and authenticated users sometimes drill down to system vulnerabilities. To find out why the information was stolen, the first step was to identify unusual database activity, such as an employee who suddenly started querying large amounts of credit card information, research results, or other sensitive information. To track user activity and identify trends in suspicious activity, Oracle DB provides a number of audit tools.
Monitoring or auditing is an integral part of the security process. Please review the following:? Mandatory audits? Standard Database Audits? value-based auditingFine-grained audit (FGA)? SYSDBA (and Sysoper) auditsMonitoring compliance audits means capturing and storing information about what is happening on the system, which increases the amount of work that the system must perform. The audit must be focused so that only meaningful events are captured. If the audit focus is set appropriately, the impact on system performance is minimized. If the audit focus is not properly set, it will have a significant impact on system performance.? Mandatory audits:All Oracle DB audits specific operations regardless of how other audit options or parameters are set. A mandatory audit log exists because the database needs to record database activity such as authorizing user connections.? Standard Database audits:By using
Audit_trailInitialization parameters are enabled at the system level. After auditing is enabled, select the objects and permissions to audit and set the audit properties using the Audit command.
? Value-based auditing:Extends the capabilities of standard database auditing to capture not only the audit events that occur, but also the actual values of insertions, updates, or deletions.
value-based auditing is implemented through database triggers. Fine-grained audit (FGA):Extends the capabilities of standard database audits to capture the emitted
Actual SQL statements, not just the case of events. SYSDBA (and Sysoper) audits: The audit responsibilities of DBAs and auditors or security administrators are separated, and auditors or security administrators are responsible for monitoring DBA activity in the operating system audit trail.
To use a database audit, you must first place a static
Audit_trailparameter is set to the location where the audit record is stored. Doing this enables database auditing. After you enable database auditing and specify audit options (logon events, exercised system and object permissions, or SQL statements used), the database begins to collect audit information.Audit_trail = {none | os | db [, extended] | xml [, extended]}If theAudit_trail set to OS, audit records are stored in the audit system of the operating system. In a Windows environment, audit records are stored in the event log. In UNIX or Linux environments, audit records are stored in
audit_file_destparameter in the specified file. If thethe Audit_trail parameter is set to DB or DB, EXTENDED, you can view audit records in the Dba_audit_trail view (part of the SYS scheme). IfAudit_trail set to XML or xml,extended, the audit record is written to the XML file in the directory that the audit_file_dest parameter points to. Use the V$xml_audit_trail view to view all the XML files in this directory. Maintaining an audit trail is an important administrative task. The audit trail may grow rapidly, depending on the audit focus of the audit option. If it is not maintained properly, the audit trail creates too many records to affect the performance of the system. The audit overhead is directly related to the generated record number.
You can enable database auditing using Audit_trail. You can use the initialization Parameters (initialize parameters) page in Enterprise Manager or use the Alter SYSTEM SET command in Sql*plus to set the Audit_trail parameter. ALTER SYSTEM SET audit_trail= ' XML ' scope=spfile;because this is a static parameter, you must restart the database for the changes to take effect. If the database was created using Databaseconfiguration Assistant (DBCA),by default, the Audit_trail parameter is set to DB。 [Email protected]> show parameter audit_trail NAME TYPE VALUE----------------------------------------------------- ------------------------audit_trail String db when Audit_trail is set to DB, the default behavior is to log the audit trail to the aud$ table in the database. For most sites, this type of audit does not affect database performance too much. Oracle recommends using an operating system audit trail file. If the database was created manually (using the CREATE DATABASE command), the Audit_trail is set to none by default.
Oracle db tracks the same fields for standard audits and fine-grained audits, which makes it easy to analyze database activity. To achieve this, standard audit trails and fine-grained audit trails contain mutually complementary attributes. Passadditional information collected by standard audits includes：? System change Number (SCN), which records every change to the system. The exact SQL text that the user executes and the binding variables that are used with the SQL text. These columns appear only if you have specified audit_trail=db, extended. Pass
additional information collected by fine-grained auditsIncluding:? Serial number of each audit record.? A statement number that links multiple audit entries from one statement to another.Common properties include:? Global timestamp, expressed in global standard Time (UTC). This field is especially useful for monitoring databases in different geographic locations and time zones. Unique instance number for each real application Cluster (RAC) instance.? The transaction identifier used to make the audit record of a transaction a group. Standard audit logging and fine-grained audit logging are combined in the Dba_common_audit_trail view.
SQL Statement Audit:AUDIT table; The statements shown above can be audited for impactany data definition language (DDL) statements for the table, including create TABLE, DROP table, and TRUNCATE table. You can also set the SQL statement audit focus by user name or by success or failure:sql> AUDIT TABLE by HR whenever not successful;System Authority Audit:can be used toany system permissions exercised by the audit (such as drop any TABLE). The focus of the audit can be set by user name or success or failure. By default,auditing is set to by ACCESS。 An audit record is generated each time the audited system permissions are exercised. You can choose to use the by session clause to make these records a group so that only one record is generated per session. (This way, if one user issues multiple UPDATE statements for another user's table, only one audit record is collected.) Use the by session clause to limit performance and storage impact due to auditing system permissions. System Authority Audit (non-focus and focus): AUDIT Select any table, create any trigger; AUDIT Select any table by HR by SESSION;Object Rights auditing: can be used to audit operations on tables, views, procedures, sequences, directories, and user-defined data types. This type of audit can focus on success or failure, and can be grouped by session or access rights. Unlike system privilege audits,by default, object rights auditing is grouped by session。if you want to generate an audit trail record for each operation separately, you must explicitly specify by ACCESS。 Object Rights Audit (non-focus and focus): AUDIT all on hr.employees; AUDIT update,delete on hr.employees by ACCESS;
When auditing is enabled in Oracle Database 11g, the specific permissions and statements that are critical to security are audited by default. These permissions and statements listed above are audited by success, failure, and access for all users.
- Enterprise Manager Audit Page
On the Database Control home page, you can access the Audit (audit) pages by clicking the Server tab, and then clicking the Audit Settings (Audit settings) link in the security area. The following areas are included on the Audit (audit) page:? Configuration: Displays the current configuration parameter values, including links for editing parameter values? Audit Trails (audit trail): Easy access to collected audit information you can use the following tab pages to set and deselect audit options:? Audited privileges (Audit permissions): Show auditing permissions? Audited Objects (object of Audit): Show audited objects? Audited statements (Statement of Audit): Displays the statement of the audit
- Use and maintain audit information
Best practices suggest that auditing can cause performance degradation, proportional to the number of audit trail writes. To customize audit options that meet your site's needs, you can enable only those options that are required to meet your security policy. Set the audit focus to reduce the number of audit trail entries.
When you perform a database audit, the INSERT, update, and delete operations that occur in the Audit object are logged, but the actual values of the changes are not captured. To extend database auditing, you can use a value-based audit to capture changed values using Database triggers (event-driven PL/SQL constructs). The user is connected to the corresponding
TriggerTable, the trigger copies the audit information to the table that contains the audit information in the background when data is inserted, updated, or deleted. Because the audit trigger code must be executed every time an INSERT, update, or delete operation occurs, the performance degradation is greater when using a value-based audit compared to a standard database audit. The performance degradation depends on the efficiency of the trigger code. Value-based auditing is used only if the standard database audit captures insufficient information. Value-based audits are implemented by users or by third-party code. Oracle DB provides a PL/SQL construct that can be used to build a value-based audit system. A key part of a value-based audit is
Audit triggers, which is a PL/SQL trigger that is constructed purely to capture audit information. The following is a typical example of an audit trigger: CREATE OR REPLACE TRIGGER system.hrsalary_auditafter UPDATE of Salaryon hr.employeesreferencing NEW as NE W old as oldfor each ROWBEGINIF:old.salary! =: new.salary theninsert into system.audit_employeesvalues (' user Env ', ' os_user '), Sysdate,sys_context (' Userenv ', ' ip_address '),: new.employee_id | | ' salary changed from ' | |:o ld.salary| | ' to ' | |:new.salary); END IF; end;/This trigger sets the focus of the audit to capturing changes to the Hr.employees table's payroll column. When a row is updated, the trigger checks the Payroll column. If the old and new salaries are not equal, the trigger inserts an audit record in the Audit_employees table (created by performing a separate operation in the system schema). The audit record includes the user name, the IP address where the change was made, the primary key that identifies the record that was changed, and the actual salary value that was changed. If the standard database audit collects insufficient data, you can also use database triggers to capture information about user connections. By using
logon triggers, an administrator can capture data that identifies the user who is connected to the database. Include the following:? The IP address of the logged in person? The first 48 characters of the program name used to connect to the instance? The terminal name used to connect to the instance reference "Sys_context" in many cases, fine-grained auditing (FGA) functionality is used instead of value-based triggers.
? Monitor data access based on content? Audit Select, INSERT, UPDATE, delete, and merge? Can I link to one or more columns in a table or view? Can you perform a procedure? Manage policies using the DBMS_FGA package: audit_emps_salary SELECT name,salaryfrom employeeswheredepartment_id = 10; Fine-grained audit database auditing records the fact that an action has occurred, but does not capture information about the statement that caused the action. Fine-grained auditing (FGA) expands audit capabilities to capture
actual SQL statement that queries or processes data。 Compared to standard database audits or value-based database audits, FGA sets the scope of the focus audit to narrower. The FGA option can be set toAudit by individual columns in a table or view, you can also set it as a conditional option to capture an audit only when a specific specification that is defined by the administrator is met. The FGA policy supports multiple related columns. By default, if any of these columns appears in the SQL statement, the statement is audited. By using DBMS_FGA. All_columns and DBMS_FGA. Any_columns, you can audit whether any or all of the related columns are used in the statement. You can use the DBMS_FGA PL/SQL package to create an audit policy on the target table or view. If any rows returned in the query block match the audit column and the specified audit criteria, the audit event causes the audit trail to be created and stored in the audit lead. In addition, audit events can perform a process. FGA automatically focuses the audit on the statement level. A SELECT statement returns thousands of rows, but only one audit record is generated.
The example in shows the use of DBMS_FGA. The Add_policy process creates a fine-grained audit policy process that accepts the following parameters.Policy NameEach FGA policy is created with its name specified. Name the Audit_emps_salary policy by using the following parameters: Policy_name = ' audit_emps_salary 'Audit ConditionsAn audit condition is a SQL predicate that is used todefine when an audit event must be triggered。 , all lines in department 10 are audited by using the following condition parameters: audit_condition = ' department_id = 10 ' Note: Fine-grained auditing views The result set of a query, so when using the displayed FGA policy, Returning a query that matches a row in the policy specification causesCreate an Audit record。 For example, querying "SELECT * FROM Employees" returns all rows, including those with "10" in the department_id, so an audit line will be created.Audit ColumnsThe audit column defines the data to be audited. An audit event occurs if this column is included in the SELECT statement or the selection that is allowed by the audit condition. The example audits two columns by using the following parameters: Audit_column = ' salary,commision_pct ' This parameter is an optional parameter. If this parameter is not specified, only the audit_condition parameter determines whether the audit event must occur.ObjectThe object is the table or view that you want to audit. You can pass an object by using the following two parameters:? Scenarios that contain objects? The name of the object is audited by using the following parameters hr.employees table: Object_schema = ' hr ' object_name = ' employees 'Processing programAn optional event handler is a PL/SQL procedure that defines the additional actions that must be performed during an audit. For example, an event handler can send an alert page to an administrator. If an audit event handler is not defined, an audit event entry is inserted in the audit trail. If an audit event handler is defined, an audit entry is inserted in the audit trail and an audit event handler is executed. Audit event entries includethe FGA policy that causes the event, the user who executes the SQL statement, the SQL statement, and its bound variables。 Event handlers can be passed through the following two parameters:? scenarios that contain PL/SQL program units? The name of the PL/SQL program Unit performs the secure.log_emps_salary process by using the following parameters: Handler_schema = ' secure ' handler_module = ' log_emps_ Salary ' By default, the audit trail always writes SQL text and SQL binding information to the LOB. You can change this default setting (for example, when the system experiences performance degradation).StatusStatus indicationwhether the FGA policy is enabled。 example, this policy is enabled with the following parameters: Enable = TRUE
- Audited DML Statements: Considerations
? If the FGA predicate is satisfied and the related column is referenced, the record is audited.The DELETE statement is audited regardless of the specified column. ? The merge statement is audited and the underlying insert, UPDATE, and DELETE statements are generated. Audited DML statements: Considerations when the FGA policy is defined for a DML statement, the DML statement is audited if the data rows to be processed, including new and old rows, conform to the policy predicate standard. However, if a related column is specified in the policy definition at the same time, thewhen the data conforms to the FGA policy predicate and the statement references the defined related columns, the statement is not audited. For DELETE statements, it is useless to specify related columns during the definition of a policy because the DELETE statement accesses all the columns in the table. So, whatever the relevant column is,always audit DELETE statements. The FGA supports the merge statement. These statements are audited if the underlying insert, UPDATE, and DELETE statements meet the defined INSERT, update, or DELETEFGA policies. When using the FGA policy defined earlier,The first statement is audited and the second statement is not audited。 No employee in department 10 received a commission, but employee_id=200 designated one of the employees in department 10. UPDATE hr.employeesset salary = 1000WHERE commission_pct =. 2; UPDATE hr.employeesset salary = 1000WHERE employee_id = 200;
? To audit all rows, use the null audit condition.? To audit all columns, use the null audit column. The policy name must be unique.? When you create a policy, the audited table or view must already exist. If the audit condition syntax is invalid, an ORA-28112 error occurs when accessing the audited object. If there are no audited columns in the table, no rows are audited. If no event handlers exist, no errors are returned, and audit records are still created. For SELECT statements,FGA will capture the statement itself rather than the actual row。 However, when FGA is combined with a flashback query, the rows are reconstructed in the form that the actual rows exist at that point in time.
A user with SYSDBA or Sysoper permissions can connect while the database is in a closed state. Audit trails must be stored outside the database. Connections performed as SYSDBA or Sysoper are always audited. You can use Audit_sys_operations to enable additional audits of SYSDBA or sysoper operations. You can use Audit_file_dest to control audit trails. SYSDBA and Sysoper userspermission to start and close the database。 Because these users can make changes when the database is turned off, the audit trail for these permissions must be stored outside the database.Oracle DB automatically captures logon events for SYSDBA and Sysoper users. This provides a valuable way to track authorized or unauthorized SYSDBA and sysoper operations, but only when viewing the operating system audit trail. Oracle DB always captures logon events for authorized users. If DBA auditing is specifically enabled, other actions are also captured. You can enable auditing for SYSDBA and Sysoper users by setting the following initialization parameters: Audit_sys_operations=true (the default is set to False.) If the SYS operation is audited, theaudit_file_dest initialization parameters control where audit records are stored。 On the Windows platform, audit trails are stored in the Windows event log by default. On UNIX and Linux platforms, audit records are stored in the audit_file_dest location.
Audit trails should be maintained according to best practice guidelines:? Review and store old records. Avoid storage problems. Avoid record loss. Maintaining an audit trail must maintain each type of audit trail. Basic maintenance must include reviewing audit records and deleting older records in the database or operating system. The audit trail continues to grow, filling up the available storage space. If the file system is full, the system may crash or simply cause performance problems. If the database audit trail fills the tablespace, the audited operation cannot be completed. If the audit trail fills the system tablespace, the performance of other operations is affected before the audit operation is stopped. Audit trails for standard audits are stored in the aud$ table. The audit trail for FGA is stored in the fga_log$ table. By default, both tables are created in the system table space. These two tables can be moved to another table space by using the data pump export and import utility. Note: Moving an audit table outside the system table space is not supported. Audit records may be lost in the process of deleting records from an audit table.
? Consolidate and protect audit data –oracle 9i Release 2 and later versions –sql Server 2000, 2005–IBM DB2 UDB 8.5 and 9.2–sybase ASE 12.5-15.0– secure and scalable – clean source Oracle audit Data? Centralized reporting – The reporting interface has been updated to use the popular Oracle Application express– compliance standard report – Add custom reports? Security threat Alerts – Detect and warn about security-related events The main benefits of Oracle Audit vaultoracle Audit Vault are as follows:? Starting with Oracle9idatabase Release 2, Oracle Audit Vault can be from Oracle DB, Microsoft SQL Server 2000 and 2005, IBM DB2 Unix, Linux, Windows 8.2, and 9.5 and the audit data is transparently collected and merged in Sybase ASE 12.5-15.0. Oracle Audit Vault provides built-in reports and customized reports to help companies streamline their work on making compliance reports. In addition, Oracle Audit Vault offers an open audit warehouse scenario that can be accessed from Oracle BI Publisher, Oracle Application Express, or any third-party reporting tools. Oracle Audit Vault alerts against suspicious activity, helping detect and protect against insider threats. At the core of Oracle Audit Vault is a secure and scalable audit warehouse built on Oracle data warehousing technology and secured by Oracle's database security offerings, including Oracle DB Vault and Oracle Advanced Security options. Oracle Audit Vault includes Oracle partitioning capabilities to improve manageability and performance. Oracle Audit Vault centrally manages database audit settings (policies) to help companies reduce it costs while making it easier for IT security principals and internal auditors to perform their work.
Source: http://blog.csdn.net/rlhua/article/details/12292365
