ORACLE restricts malicious operations of certain IPs and users on important tables

1, problem description

Oracle default account is not limited IP, the hidden danger is that if I know the Oracle account username password, I can only connect to the DB, I can operate on the DB, which is very dangerous for the DB on the line, because some non-DBA personnel, such as developers, The tester accidentally deleted the data on the line, it was miserable, the pit too big dare not look. So we checked and found a way to put a trigger on some important tables to limit the user's operation of the table on the online db.

2, the trigger is written

If you open a global SQL audit, consumption performance is too large, not very suitable, want to only on some important tables to do the limit, the initial solution.

1) Verify IP: (Sys_context (' Userenv ', ' ip_address ') Not in (' 192.168.120.211 ')

2) Verify user name: Selects. USERNAME into V_username from V$session s where s.audsid= (selectuserenv (' SESSIONID ') from dual) and rownum<2

3) The sample stored procedure is as follows:

Create or replace Triggerpri_stu_test_limit

Before update or delete or insert on Stu.zzz_test

DECLARE

PRAGMA autonomous_transaction;

V_username varchar2 (+) default ';

BEGIN

Select S.username to 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;

Original Blog Address: http://blog.csdn.net/mchdba/article/details/48790259, did not pass the original Bo Master Douglas Fir (MCHDBA) agreed, not allowed reprint, thank you.

3, verify that:

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 additions and deletions can be limited, should temporarily solve the problem, followed by a lot of problems need to be solved together.

Reference article Oracle Limit ip:http://blog.chinaunix.net/uid-9518120-id-197704.html

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

ORACLE restricts malicious operations of certain IPs and users on important tables