Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Oracle value-based audit", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Oracle value-based audit" this article.

Oracle can implement value-based auditing by using triggers.

Value-based audit lab:

1. User scott has an employee table emp, in which the field sal records the salary information of each employee.

2. Create an audit table to record the audit results

Create table scott.emp_sal_audit

Host varchar2 (50), ip_address varchar2 (20), emp_no varchar2 (20), old_value number, new_value number, update_time date)

3. Create a trigger for the table scott.emp, trigger the update of the value of the field sal, record the values before and after the update, and record the client information that performs the update.

Create or replace trigger sal_audit

After update of sal on scott.emp

For each row

Begin

If: old.sal! =: new.sal then

Insert into scott.emp_sal_audit values

(sys_context ('userenv',' host')

Sys_context ('userenv',' ip_address')

: new.empno

: old.sal

: new.sal

Sysdate)

End if

End

4. Query the current record and perform updates

Select * from emp where empno = 7369

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

Update emp set sal = 900where empno = 7369

Commit

5. View audit records

Select * from emp_sal_audit

HOST IP_ADDRESS EMP_NO OLD_VALUE NEW_VALUE UPDATE_TIME

--

WORKGROUP\ MYPC 192.168.133.1 7369 800900 16:34:27 on 2015-9-3

The table records the client machine performing the change, the IP address, the value before and after the update, the update time and so on. The oracle function sys_context is used in the trigger to obtain the system environment information.

The above is all the contents of the article "how to audit Oracle based on values". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.