ORACLE restricts malicious operations on critical tables by certain IPs and users

1, problem description
          Oracle Default account is not restricted IP, the hidden danger is that if I know the Oracle account username password, I can only connect to the DB, it is possible to operate the DB, which is very dangerous for the DB on the line, Because some non-DBA staff, such as developers, testers accidentally deleted the data on the line, it is miserable. So find a way to put a trigger on some important tables to limit the user's operation of the table on the online db.
 
2, trigger writing
Span style= "font-size:13px" > If you open a global SQL audit, the consumption performance is too large, not very suitable, want to only on some important tables to do the limit, the initial solution to the problem.
1) Authentication IP: (Sys_context (' Userenv ', ' ip_address ') Not in (' 192.168.120.211 ')
2) Verify the user name: Selects. USERNAME into V_username from V$session s where s.audsid= (selectuserenv (' SESSIONID ') from dual) and rownum<2
3) sample stored procedure is as follows:

Create or Replace trigger pri_stu_test_limit before update or delete or insert on stu.zzz_testDECLA    RE PRAGMA autonomous_transaction; Autonomous transactions . For a procedure defined as an autonomous transaction, it is actually equivalent to a separate run of the program segment, which does not depend on the main program, and does not interfere with the main program V_username varchar2 ( $)default "'; BEGINSelectS.username into V_username fromV$session S Wheres.audsid= (SelectUserenv'SESSIONID') fromDual) and rownum<2; IF Deleting and (Sys_context ('Userenv','IP_Address') notinch('192.168.120.211') OR'Stuuser'Like v_username) Then raise_application_error ( -20001, ' Can not delete the table '); Used to customize incorrect messages in the Plsql usage program
The syntax is: Raise_application_error (Error_number,message[,[true|false]);
Error_number used to define an incorrect number, the incorrect number must be a negative integer between 20000 and 20999;
Message is used to specify an incorrect message, and the message cannot exceed 2048 bytes in length;
The third parameter, if true, is incorrectly placed in the previously incorrect stack, assuming false (the default) instead of all previously incorrect.
elsif Inserting and (Sys_context ('Userenv','IP_Address') notinch('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') notinch('192.168.120.211') OR'Stuuser'Like v_username) Then Raise_application_error (-20001,'can not update the table'); END IF; END;

4) Verify:
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
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
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
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.

ORACLE restricts malicious operations on critical tables by certain IPs and users