Oracle 在重要的表上限制某些IP、用户的恶意操作

Oracle默认账号是没有限制ip的,这样的隐患就在于,如果我知道了oracle账号用户名密码,我只要能连接到db,就可以对db进行操作,这样对于线上的db来说是很危险的,因为有些非dba人员,比如开发人员、测试人员一不小心误删除了线上的数据,就惨了,坑太大不敢看。所以查了查,找到一种办法,在一些重要的表上加触发器来限制用户对线上db的表的操作。

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增删改都可以被限制住了,应该暂时解决了问题所在,后续还是有很多问题需要一起解决的。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/5211d8159f63173176d80c40251a7e9e.html