Oracle Audit Detail-Go

http://blog.chinaunix.net/u2/66903/showart_2082884.html
Oracle uses a number of different audit methods to monitor what permissions to use and which objects to access. Auditing does not prevent the use of these permissions, but it can provide useful information to uncover abuses and misuse of permissions. The following table summarizes the different types of audits in the Oracle database. Audit Type description Statement audits audit SQL statements by statement type, regardless of which particular schema object is accessed. You can also specify one or more users in the database to audit these user rights for specific statements audit audit system permissions, such as CREATE TABLE or alterINDEX. As with statement auditing, a permission audit can specify that one or more specific users as the target mode object of an audit audit a specific statement that runs on a particular pattern object (for example, an UPDATE statement on a departments table). Schema object auditing is always applied to all users in the database fine-grained audits audit table access and permissions based on the content of the Access object. Use package DBMS_FGA to establish policies on specific tables The following sections describe how DBAs manage the auditing of system and object permissions. When a certain granularity is required, a DBA can use fine-grained auditing to monitor access to certain rows or columns in the table, not just to access the table. and audit-related key parameters SQL>Show parameter auditaudit_file_destaudit_sys_operationsaudit_trailaudit_sys_operations: Default is False, when set to true, All SYS users (including those logged in as Sysdba,sysoper) will be logged, and audit trail will not be written in the aud$ table, which is a good understanding if the database has not been started aud$ not available, then like Conn/ asSysdba Such connection information, can only be recorded in other places. If it is a Windows platform, audti Trail is logged in the event management of Windows, if it is a Linux/The UNIX platform is recorded in the file specified by the Audit_file_dest parameter. Audit_trail:none: Is the default value, does not do the audit; DB: Audit trail is recorded in the Database audit related table, such as aud$, the result of the audit is only the connection information; DB, Extended: In this way, the audit results include the specific statements executed at that time except the connection information; OS: Audit trail is recorded in the operating system file and the file name is specified by the audit_file_dest parameter; xml:10g. 10gValues: NonedisablesDatabaseAuditing.osenablesDatabaseAuditing andDirects AllAudit Records toThe operating system'S audit trail.dbenables database auditing and directs all audit records to the database audit trail (the SYS. aud$ table). Db,extendedenables database auditing and directs all audit records to the database audit trail (the SYS. aud$ table). In addition, populates the SQLBIND and sqltext CLOB columns of the SYS. aud$ Table.xmlenables Database Auditing and writes all audit records to XML format OS files.xml,extendedenables database A Uditing and prints all columns of the audit trail, including SQLText and SqlBind values. Note: The parameter audit_trail is not dynamic, in order to make the Audit_trai The changes in the L parameter take effect, and the database must be shut down and restarted. When auditing the sys.aud$ table, it is important to monitor the size of the table to avoid affecting the space requirements of other objects in the SYS table space. It is recommended to periodically archive the rows in the sys.aud$ and intercept the table. Oracle provides role Delete_catalog_role, which is used with special accounts in batch jobs for archiving and intercepting audit tables. Audit_file_dest:audit_trail=os file location 1. Statement audit all types of audits use the Audit command to open an audit and use the Noaudit command to turn off auditing. For statement auditing, the format of the AUDIT command appears as follows: AUDIT sql_statement_clause by {SESSION | Access}whenever [NOT] Successful;sql_statement_clause contains a number of different information, such as the type of SQL statement you want to audit and who to audit. In addition, you want to audit each action as it occurs (by Access) or only once (by session). Impliedby the session. Sometimes you want to audit a successful action: There are no statements that generate an error message. For these statements, add whenever successful. And sometimes only the command that uses the audit statement fails, the reason for the failure is the violation of permissions, the use of space in the table space, or a syntax error. For these cases, use whenever not successful. For most categories of auditing methods, if you do want to audit all types of table access or any permissions for a user, you can specify all instead of a single statement type or object. Table 1 lists the types of statements that can be audited, and contains a brief description of the related statements in each category. If all is specified, any statements in the list are audited. However, the statement types in table 2 do not belong to the all category when auditing is enabled, and they must be explicitly specified in the audit command. Table 1 includes the auditable statement options in the All category SQL action alter system all ALTER system options, for example, dynamically changing instance parameters, switching to the next log file group, and terminating user session clustercreate, ALTER, Drop or Truncate cluster contextcreate context or drop contextdatabase linkcreate or drop database link dimensioncreate, Alter or drop dimension directorycreate or drop directory indexcreate, ALTER, or DROP INDEX materialized viewcreate, alter, or drop materialized view not exists failure of SQL statements due to non-existent reference objects procedurecreate or drop FUNCTION, LIBRARY, package, package body or procedureprofilecreate, The ALTER or Drop profile public database linkcreate or drop public databases link the synonymcreate or drop common synonym rolecreate, ALTER, Drop or set role rollback segmentcreate, ALTER, or drop rollback segment sequencecreate or drop sequence session login and exit synonymcreate or drop synonym system Audit system permissions Audit or Noauditsystem grantgrant or revoke system permissions and Roles Tablecreate, DROP or TRUNCATE TABLE tablespacecreate, alter or drop TABLE space triggercreate, ALTER (Enable/disable), drop trigger; have enable all triggers or disable all Triggers alter TABLETYPECREATE, ALTER and drop types as well as type Principal usercreate, alter or drop user viewcreate or Drop view chart 2 explicitly specified statement type statement option SQL Make alter sequence any ALTER SEQUENCE command ALTER TABLE any ALTER TABLE command Comment table to add comments to tables, views, materialized views, or any columns in them delete Table deletes rows in tables or views execute procedure a procedure, function, or any variable or cursor on a grant directorygrant or revoke directory object in the execution package grant Proceduregrant or revoke permissions on a procedure, function, or package on a grant sequencegrant or revoke sequence permission grant Tablegrant or revoke a permission on a table, view, or materialized view Typegrant or revoke type permissions on the Insert Tableinsert into table or view lock Table command on a table or view Select Sequence any command that references a sequence of Currval or Nextval select Tableselect from table, view, or materialized view Update table Some examples of performing an update on a table or view can help readers understand all of these options more clearly. In the sample database, user Kshelton has the permissions on all tables in HR mode and other modes. Allows Kshelton to create indexes on some of these tables, but if there are performance issues related to execution plan churn, you need to know when to create those indexes. The index created by Kshelton can be audited using the following command:sql> Audit index by Kshelton; Audit succeeded. One day later, Kshelton created an index on the Hr.jobs table:sql> CREATE index job_title_idx on hr.jobs (job_title); index Created. Check the audit trail in data dictionary view Dba_audit_trail, and you can see Kshelton actually 5:15 p.m. on August 12. Created index:sql> Select username, To_char ( Timestamp'Mm/Dd/YY Hh24:mi') timestamp,2 obj_name, Action_name, sql_text from dba_audit_trail3 where username ='Kshelton'; USERNAME TIMESTAMP obj_name action_name sql_text--------------------------------------------- ----------------------Kshelton 08/12/07 17:15 job_title_idx CREATE index CREATE INDEX HR.JOB_TITLE_IDX onhr.jo BS (job_title) 1 row selected. Note: Starting with Oracle Database 11g, columns in Dba_audit_trail are populated only when the initial parameter audit_trail is set to db_extended Sql_ Text and Sql_bind. By default, the value of Audit_trail is db. In order to close the Kshelton audit on the Hr.jobs table, you can use the Noaudit command, as shown below:sql> Noaudit index by Kshelton; Noaudit succeeded. You may also want to audit successful and unsuccessful logins in a regular manner, which requires two audit commands:sql> audit session whenever successful; Audit succeeded. Sql> Audit session whenever not successful; Audit Succeeded.2. Rights Audit Audit system permissions have the same basic syntax as statement auditing, but the audit system permissions are in Sql_statement_clause, not in statements, to specify system permissions. For example, you might want to grant ALTER TABLESPACE permission to all DBAs, but you want to generate audit records when this happens. The command to enable auditing of this permission looks similar to statement audit:sql> Audit alter tablespace by Access whenever successful; Audit succeeded. Each time you successfully use the ALTER TABLESPACE permission, a line of content is added to the sys.aud$. A system administrator who uses SYSDBA and Sysoper permissions or is connected to a database with a SYS user can take advantage of a special audit. In order to enable this additional level of auditing, you can set the initial parameters Audit_sys_Operations is true. This audit record is sent to the same location as the operating system audit record. Therefore, this location is associated with the operating system. All SQL statements executed when one of these permissions are used, and any SQL statements executed as user sys, are sent to the operating system audit location. Schema object audit audit access to various schema objects looks similar to statement auditing and permission auditing: AUDIT schema_object_clause by {SESSION | Access}whenever [NOT] successful;schema_object_clause specifies the type of object access and the object accessed. You can audit 14 different types of operations on a specific object, and these actions are listed in the following table. Object options description alter alter a table, sequence, or materialized view audit a command on any object comment add a comment to a table, view, or materialized view Delete Row execute execution from a table, view, or materialized view Function or package flashback perform a flashback operation on a table or view grant grants permissions on any type of object index creates a table or indexes on a materialized view insert inserts rows into a table, view, or materialized view lock table, View or materialized view read reads the contents of a directory object rename rename a table, view, or procedure select the row Update Update table from a table, view, sequence, or materialized view, View or materialized view if you want to audit all the insert and UPDATE commands on the Hr.jobs table, regardless of who is updating, you can use the Audit command shown below:sql> Audit Insert whenever the action occurs, update on Hr.jobs by Access whenever successful;  Audit successful. User Kshelton decide to add two new rows to the Hr.jobs table:sql> insert INTO Hr.jobs (job_id, Job_title, Min_salary, max_salary) 2 VALUES ('In_cfo','Internet Chief Fun Officer', 7500, 50000); 1 row created. sql> INSERT INTO Hr.jobs (job_id, Job_title, Min_salary, max_salary) 2 values ('Oe_vld','OrderEntry CC Validation', 5500, 20000); 1 row created. View the Dba_audit_trail view to see the two insert commands in the Kshelton session: USERNAME TIMESTAMP OWNER Obj_nam E action_namesql_text------------------------------------------------------------------------------------------ ------------------------------Kshelton 08/12/07 22:54 HR JOBS insertinsert into Hr.jobs (job_id, Job_title, Min_salary, Max_salary) VALUES ('In_cfo','Internet Chief Fun Officer', 7500, 50000); Kshelton 08/12/07 22:53 HR JOBS insertinsert into Hr.jobs (job_id, Job_title, min_salary, max_salary) values ('Oe_vld','OrderEntry CC Validation', 5500, 20000); Kshelton 08/12/07 22:51 LOGON3 rows selected. 4. Fine-grained audits start with oracle9i, and by introducing fine-grained object audits, or FGA, audits become more focused and more accurate. The FGA is implemented by the PL/SQL package called DBMS_FGA. With standard auditing, you can easily discover which objects are accessed and who accesses them, but you cannot know which rows or columns are accessed. Fine-grained auditing solves this problem by specifying a predicate (or a WHERE clause) for the row that needs to be accessed, as well as specifying the columns that are accessed in the table. By auditing access to a table only when certain rows and columns are accessed, you can significantly reduce the number of audit table entries. Package DBMS_FGA has 4 procedures: Add_policy Adds an audit policy that uses predicates and audit columns Drop_policy deletes an audit policy disable_policy disables the audit policy, but retains the policy associated with the table or view Enable_ Policy-enabled policies user Tamara typically accesses the Hr.employees table every day to find an employee's e-mail address. The system administrator suspects that Tamara is viewing the manager's salary information, so they set up a FGA policy to audit any manager's access to the salary column: begindbms_fga.add_policy (object_schema ='HR', object_name ='EMPLOYEES', Policy_name ='Sal_select_audit', audit_condition ='InStr (job_id,"'_man"')> 0', Audit_column ='SALARY'); end; You can use the data dictionary view Dba_fga_audit_trail to access audit records for fine-grained audits. If you generally need to view standard audit lines and fine-grained audit lines, the data dictionary view Dba_common_audit_trail combines the rows in both types of auditing. To continue looking at the example, user Tamara runs the following two SQL queries:sql> select employee_id, first_name, last_name, email from hr.employees2 where employee_ id = 114; employee_id first_name last_name EMAIL----------------------------------------------------- -----------raphaely Den DRAPHEAL1 Row selected. Sql> Select employee_id, first_name, last_name, salary from hr.employees2 where employee_id = 114; employee_id first_name last_name SALARY--------------------------------------------------- -----------raphaely Den 110001 Row selected. The first query accesses the manager information, but does not have access to the salary column. The second query is the same as the first query, but accesses the salary column, triggering the FGA policy, resulting in a row:sql> select To_char (timestamp, in the audit trail ).'Mm/Dd/YY Hh24:mi') timestamp,2 Object_schema, object_name, Policy_name, statement_type3 from Dba_fga_audit_trail4 where Db_user = 'TAMARA'; TIMESTAMP object_schema object_name policy_name statement_type-------------------------------------- --------------------------------08/12/07 18:07 HR EMPLOYEES sal_select_audit SELECT1 Row selected. Because fine-grained access control is established in the VPD example earlier in this chapter to prevent unauthorized access to the salary column, you need to double-check the policy functions to ensure that salary information is still properly restricted. Fine-grained audits and standard audits are a good way to ensure that the authorization strategy is first set up correctly. 5. Audit-related data dictionary views the following table contains an audit-related data dictionary view. Data dictionary View Description Audit_actions contains a description of the audit trail action type code, such as INSERT, DROP VIEW, DELETE, logon, and Lockdba_audit_object audit trail related to objects in the database Dba_ The fine-grained audit strategy in the Audit_policies database dba_audit_session all audit trail records related to connect and disconnect dba_audit_statement with Grant, REVOKE, The audit trail entries associated with the AUDIT, Noaudit, and alter system commands Dba_audit_trail contain standard audit trail entries.                                                                      User_audit_trailuser_trail_audit audit trail entries that contain only the audited lines of connected users dba_fga_audit_trail fine-grained audit policies (cont.) Data dictionary View Description Dba_common_audit_trail combines standard audit lines and fine-grained audit lines in one view dba_obj_audit_opts audit options for database objects dBA _priv_audit_opts Audit options for system permissions dba_stmt_audit_opts audit option for statements in effect 6. Protection Audit Trail Audit trail itself needs to be protected, especially if non-system users must access the table sYs. When aud$. Built-in role Delete_any_catalog is a way for non-SYS users to access audit trails (for example, archiving and intercepting audit trails to ensure that it does not affect the space requirements of other objects in the SYS table space). In order to establish an audit of the audit trail itself, connect to the database as SYSDBA, and run the following command:sql> audit all in sys.aud$ by Access; Audit succeeded. Now, all actions against the table sys.aud$, including SELECT, INSERT, update, and delete, are recorded in the sys.aud$ itself. However, you might ask, what happens if someone deletes an audit record that identifies sys.aud$ access to the table? The row in the table is deleted, but then another row is inserted, and the row is deleted. Therefore, there is always evidence of some (intentional or incidental) activity against the sys.aud$ table. In addition, if Audit_sys _operations is set to true, any sessions that use as SYSDBA, as Sysoper, or in SYS itself are logged to the operating system audit location, and even the Oracle DBA may not be able to access the location. Therefore, there are a number of appropriate security measures that are used to ensure that all permissions in the database are logged and any attempts to hide the activity. 7. Enable enhanced auditing starting with the Oracle database 11g, it is easy to enable the default (enhanced) audit by the DB Configuration Assistant (ASSISTANT,DBCA). While there is some overhead in documenting audit information, the compatibility requirements (for example, the compatibility requirements stipulated in the Sarbanes-oxley Act) require strict monitoring of all business operations, including security-related operations in the database. You can use DBCA to configure default auditing when you create a database or after the database has been created. If you have changed many audit settings and want to reset the audit options to baseline values, it is useful to configure a default audit using DBCA after the database has been created. In addition to setting the value of the initial parameter audit_trail to DB, the default audit setting also audits the audit role command itself. Also, on the Oracle Enterprise Manager Audit Settings page of the audited Privileges tab, you can view the default audit permissions. Supplementary instructions: 8, example 8.1, activate Audit audit related table installation sqlplus> connect/as SYSDBA sqlplus> SELECT * FROM sys.aud$;   --No records returned sqlplus> select * from Dba_audit_trail;   -No records returned if the query was made to find that the table does not exist, the audit-related tables are not installed and need to be installed. sqlplus> connect/as sysdba sqlplus> @ $ORACLE _home/rdbms/admin/cataudit.sql Audit table installed in the system table space.   So make sure that the system tablespace has enough space to hold the audit information. To restart the database after installation to move the audit related tables to another table space because audit-related tables such as the aud$ table are stored in the system table space, it is a good idea to move the aud$ to other tablespaces in order not to affect the performance of the system. You can use the following statement to move: sql>connect/as sysdba;sql>alter table aud$ move tablespace <new tablespace>;sql>alter in Dex i_aud1 Rebuild Online tablespace <new tablespace>; Sql> ALTER TABLE audit$ move tablespace <new tablespace>; sql> ALTER index I_audit rebuild online tablespace <new tablespace>; Sql> ALTER TABLE audit_actions move tablespace <new tablespace>; sql> ALTER index i_audit_actions rebuild online tablespace <new tablespace>; Sql> conn/as sysdbasql> Show parameter auditname TYPE VALUE----------------- ------------------- ----------- ------------------------------Audit_file_dest string/u01/app/oracle/admin/orcl/adumpaudit_sys_operations Boolean falseaudit_syslog_level Stringaudit_trail string NO    Nesql> alter system set AUDIT_SYS_OPERATIONS=TRUE Scope=spfile; --Audit management user (login with sysdba/sysoper role) sql> alter system set audit_trail=db,extended Scope=spfile; sql> startup force; Sql> Show parameter auditname TYPE VALUE--------------------------------------- --------------------------------------audit_file_dest String/u01/app/oracle/admin/orcl/adumpau                          Dit_sys_operations Boolean Trueaudit_syslog_level Stringaudit_trail String DB, EXTENDED 8.2, starting audit sql> Conn/as sysdbasql> audit all on t_test; Sql> Conn u_testsql> select * from T_test; sql> INSERT INTO U_test.t_test (C2,C5) VALUES ('Test1','2'); Sql> commit; Sql> Delete from U_test.t_test; Sql> commit; sql> conn/as sysdbasql> Col dest_name format a30col os_username format a15col USERNAME format a15col userhost forma T a15col TERMINAL format a15col obj_name format a30col sql_text format a60sql> Select Os_username,username,userhost,ter Minal,timestamp,owner,obj_name,action_name,sessionid,os_process,sql_text from dba_audit_trail;sql> Audit Select    Table by U_test by Access; If you add by user after the command, only the user's operation is audited, and if you omit by users, all users in the system are audited (without the SYS user). Example: AUDIT DELETE any TABLE;    --Audit Delete table operation Audit Delete any table whenever not successful;    --only audit deletion failure Audit Delete any TABLE whenever successful;    --only audit deletion success Audit Delete,update,insert on user.table by test; --Audit test user Delete,update,insert operation of table user.table 8.3, revocation audit sql> noaudit all on t_test;9, audit statements Multi-layered environment Audit: appserve-Application Server, Jackson-clientaudit SELECT TABLE by Appserve on BEHALF of Jackson; audit connection or disconnection: AUDIT SESSION;    AUDIT SESSION by Jeff, Lori; --Specify user audit rights (actions to be performed using this permission): AUDIT DELETE any TABLE by ACCESS whenever not successful; AUDIT DELETE any TABLE; AUDIT SELECT table, INSERT table, DELETE table, EXECUTE PROCEDURE by ACCESS whenever not successful; object audit: AUDIT DELETE on J Eff.emp; AUDIT SELECT, INSERT, DELETE on jward.dept by ACCESS whenever successful; Cancel Audit: Noaudit session; Noaudit session by Jeff, Lori; Noaudit DELETE any TABLE; Noaudit SELECT table, INSERT table, DELETE table,execute PROCEDURE;    Noaudit all;    --Cancel All statement audit Noaudit all privileges;    --Cancel All permissions audit Noaudit all on DEFAULT; --Cancels all object audits 10, clears the audit information delete from SYS. aud$;D elete from SYS. aud$ WHERE obj$name='Emp'; 11, audit view Stmt_audit_option_map--Audit option type code audit_actions--Action code all_def_audit_opts--The Default object audit option when object is created Dba_stm T_audit_opts--Current database system audit options dba_priv_audit_opts--rights audit options Dba_obj_audit_opts user_obj_audit_opts--Object audit Options Dba_au Dit_trail User_audit_trail--Audit record dba_audit_object User_audit_object--List of audit objects Dba_audit_session USER_AUDIT_ Session--session audit Dba_audit_statement User_audit_statement--statement audit dba_audit_exists--using by AUDIT not EXISTS Select Audit Dba_audit_policies--audit Policiesdba_common_audit_trail--standard audit + fine audit 12, moving the audit results table from the system table space to a different tablespace actually sys.aud$ The table contains two LOB fields, not a simple move table. The following is the specific process: ALTER TABLE sys.aud$ move tablespace users;alter table sys.aud$ move lob (SQLBind) store as (tablespace users); alt ER table sys.aud$ move lob (SQLTEXT) store as (Tablespace USERS); alter INDEX SYS. I_AUD1 rebuild tablespace users;

Oracle Audit Detail-Go