1，问题描述
2，触发器编写
如果开全局的sql审计，消耗性能太大，不太合适，想来只有在某些重要的表上做限制，初步解决问题了。
1）? 验证ip：(sys_context('userenv','ip_address')not in('192.168.120.211')
2）? 验证用户名：selects.USERNAME into v_username from v$session s where s.audsid=(selectuserenv('SESSIONID') from dual) and rownum<2
3）? 样例存储过程如下：
create or replace triggerpri_stu_test_limit
?before update or delete or insert on stu.zzz_test
DECLARE
? PRAGMA AUTONOMOUS_TRANSACTION;
? v_username varchar2(200) default '';
BEGIN
?
?select s.USERNAME into v_username from v$session s wheres.audsid=(select userenv('SESSIONID') from dual) and rownum<2;
?
? IFdeleting
? AND (sys_context('userenv','ip_address') not in('192.168.120.211')? ? OR 'stuuser' like v_username)
? ? ? THEN
? RAISE_APPLICATION_ERROR(-20001, 'can not delete the table ');
?ELSIF inserting
? AND (sys_context('userenv','ip_address') not in('192.168.120.211')? ? OR 'stuuser' like v_username)
? THEN
? RAISE_APPLICATION_ERROR(-20001, 'can not insert the table ');
?ELSIF updating
? AND (sys_context('userenv','ip_address') not in('192.168.120.211')? ? OR 'stuuser' like v_username)
? THEN
? RAISE_APPLICATION_ERROR(-20001, 'can not update the table ');
? END IF;
END;
3，验证：
SQL>
SQL> insert into stu.zzz_testvalues(3,'zhuren33');
insert into stu.zzz_testvalues(3,'zhuren33')
ORA-20001: can not insert the table
ORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 18
ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'
SQL> commit;
Commit complete
?
SQL>
SQL> update stu.zzz_test setremark='zhuren33_up' where id=3;
update stu.zzz_test setremark='zhuren33_up' where id=3
ORA-20001: can not update the table
ORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 22
ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'
SQL> commit;
Commit complete
?
SQL>
SQL> delete from? stu.zzz_test where id=3;
delete from stu.zzz_test where id=3
ORA-20001: can not delete the table
ORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 14
ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'
SQL> commit;
Commit complete
SQL>
OK增删改都可以被限制住了，应该暂时解决了问题所在，后续还是有很多问题需要一起解决的。