ORACLE restricts some IP addresses and malicious operations on important tables

ORACLE restricts some IP addresses and malicious operations on important tables
1. Problem Description

The default oracle account has no restriction on ip addresses. The hidden danger is that if I know the username and password of the oracle account, I can operate on the database as long as I can connect to the database, this is very dangerous for online databases, because some non-dba staff, such as developers and testers, accidentally deleted online data, too big to look. So I checked and found a way to add a trigger to some important tables to restrict the user's operations on online db tables.

2. Trigger writing

If global SQL audit is enabled, the performance consumption is too high and it is not suitable. If you want to restrict some important tables, the problem will be solved initially.

1) Verify the ip address: (sys_context ('userenv', 'IP _ address') not in ('192. 168.120.211 ')

2) Verify the USERNAME: selects. USERNAME into v_username from v $ session s where s. audsid = (selectuserenv ('sessionid') from dual) and rownum <2

3) the storage procedure is as follows:

create or replace triggerpri_stu_test_limitbefore update or delete or insert on stu.zzz_testDECLAREPRAGMA AUTONOMOUS_TRANSACTION;v_username varchar2(200) default '';BEGINselect s.USERNAME into v_username from v$session s wheres.audsid=(select userenv('SESSIONID') from dual) and rownum<2;IFdeletingAND (sys_context('userenv','ip_address') not in('192.168.120.211') OR 'stuuser' like v_username)THENRAISE_APPLICATION_ERROR(-20001, 'can not delete the table ');ELSIF insertingAND (sys_context('userenv','ip_address') not in('192.168.120.211') OR 'stuuser' like v_username)THENRAISE_APPLICATION_ERROR(-20001, 'can not insert the table ');ELSIF updatingAND (sys_context('userenv','ip_address') not in('192.168.120.211') OR 'stuuser' like v_username)THENRAISE_APPLICATION_ERROR(-20001, 'can not update the table ');END IF;END;

3. Verification:

SQL>SQL> insert into stu.zzz_testvalues(3,'zhuren33');insert into stu.zzz_testvalues(3,'zhuren33')ORA-20001: can not insert the tableORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 18ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'SQL> commit;Commit completeSQL>SQL> update stu.zzz_test setremark='zhuren33_up' where id=3;update stu.zzz_test setremark='zhuren33_up' where id=3ORA-20001: can not update the tableORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 22ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'SQL> commit;Commit completeSQL>SQL> delete from stu.zzz_test where id=3;delete from stu.zzz_test where id=3ORA-20001: can not delete the tableORA-06512: at"stuuser.PRI_STU_ACCT_LIMIT", line 14ORA-04088: error during execution oftrigger 'stuuser.PRI_STU_ACCT_LIMIT'SQL> commit;Commit completeSQL>

OK can be added, deleted, and modified. The problem should be solved temporarily, and many problems need to be solved together in the future.