- This chapter presents two approaches of auditing Data Manipulation Language (DML) statements. The first approach is to set up an audit trail for DML activities that occur on tables. The second approach is to register all column values either before or after the DML statement is applied to the table.
- Recording of the before value of the modified column(s) is referred to as column-level auditing.
- In Oracle, column-level auditing is known as fine-grained auditing (FGA).
- Auditing of DML changes is widely used wherever highly sensitive data exists
- A trigger is a stored PL/QL procedure that is executed automatically whenever a DML operation occurs or a specific database event occurs.
- Oracle has six Data Manipulation Language (DML) events, also known as trigger timings for INSERT, UPDATE, and DELETE.
- The FOR Each ROW clause indicates that a trigger fires for each row affected by a DML statement.
- Omitting the FOR EACH ROW clause indicates that the trigger fires only once, regardless of the number of rows affected by the statement. This is known as the STATEMENT LEVEL trigger.
- You can view all trigger created on a table by using the USER_TRIGGERS data dictionary view.
- The body of the a triggers is contained in the TRIGGER_BODY column of USER_TRIGGERS view.
- Fine-grained auditing (FGA) allows administrators to generate audit trail of DML activities to operating system files or database tables.
- FGA is capable of auditing columns or tables using the Oracle PL/SQL-supplied package called DBMS_FGA.
- Invoker rights indicates that a procedure is executed using the security credentials of the caller, not the credentials of the procedure owner.
- Definer rights indicate that a procedure is executed using the security credentials of the user that owns the procedure.
|
