| |
1. Description of the problem
Oracle is no limit ip default account of such risks is that, if I know the oracle user account name and password, as long as I can connect to the db, db you can operate, so for db line is very dangerous because some of the non-dba staff, such as developers, testers accidentally removed a data line, miserable, pit too afraid to look. Therefore, investigation of the investigation, to find a way to add some important trigger on the table to limit the user to operate the line db table.
2, the trigger write
If the open global sql audit, consume too much performance, is not appropriate, and only want to come in on the table to do some important limitations, has initially solved the problem.
1) Verify 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 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. Verification:
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 locked, it should be a temporary solution to the problem, there are still many issues that need follow-up together to solve. |
|
|
|