【方法】如何限定IP通路Oracle資料庫
1.1 BLOG文檔結構圖
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLi0zaHRGcWdUYuVzVa9GczoVdG1mWfVGc5RHLwkzX39GZhh2csATMflHLwEzX4xSZz91ZsADMx8FdsYkRGZkRG9lcvx2bjxSa2EWNhJTW1AlUxEFeVRUUfRHelRHL2EzXlpXazxyayFWbyVGdhd3LcV2Zh1Wa9M3clN2byBXLzN3btg3PnVGcq5SMwAzYlRmZ0kzY4QmNyIzMldDZ0cDOidDOmNmZhFTM38CX5EzLcVDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjL3M3Lc9CX6MHc0RHaiojIsJye.jpeg)
1.2 前言部分
1.2.1 導讀和注意事項
各位技術愛好者,看完本文後,你可以掌握如下的技能,也可以學到一些其它你所不知道的知識,~O(∩_∩)O~:
① 限定IP通路Oracle資料庫的3種方法(重點)
② 如何将資訊寫入到Oracle的告警日志中
③ RAISE_APPLICATION_ERROR不能抛出錯誤到用戶端環境
④ 系統觸發器
⑤ 隐含參數:_system_trig_enabled
Tips:
① 本文在itpub(http://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和微信公衆号(xiaomaimiaolhr)上有同步更新。
② 文章中用到的所有代碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載下傳,小麥苗的雲盤位址見:http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若網頁文章代碼格式有錯亂,請下載下傳pdf格式的文檔來閱讀。
④ 在本篇BLOG中,代碼輸出部分一般放在一行一列的表格中。
本文若有錯誤或不完善的地方請大家多多指正,您的批評指正是我寫作的最大動力。
1.3 本文簡介
本文詳細介紹了3種限制IP位址登入Oracle資料庫的辦法。
1.3.1 本文實驗環境介紹
項目 | source db |
db 類型 | RAC |
db version | 11.2.0.3.0 |
db 存儲 | ASM |
OS版本及kernel版本 | RHEL 6.5 |
資料庫伺服器IP位址 | 192.168.59.130 |
用戶端IP位址 | 192.168.59.1或192.168.59.129 |
1.4 限定IP通路Oracle資料庫的3種辦法
1.4.1 利用登入觸發器
1.4.1.1 簡單版
SYS@orclasm > CREATE OR REPLACE TRIGGER CHK_IP_LHR 2 AFTER LOGON ON DATABASE 3 DECLARE 4 V_IPADDR VARCHAR2(30); 5 V_LOGONUSER VARCHAR2(60); 6 BEGIN 7 SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 8 SYS_CONTEXT('USERENV', 'SESSION_USER') 9 INTO V_IPADDR, V_LOGONUSER 10 FROM DUAL; 11 IF V_IPADDR LIKE ('192.168.59.%') THEN 12 RAISE_APPLICATION_ERROR('-20001', 'User '||V_LOGONUSER||' is not allowed to connect from '||V_IPADDR); 13 END IF; 14 END; 15 / Trigger created. SYS@orclasm > create user lhr8 identified by lhr; User created. SYS@orclasm > grant resource,connect to lhr8; Grant succeeded. |
用戶端登入:
D:\Users\xiaomaimiao>ipconfig 以太網擴充卡 VMware Network Adapter VMnet8: 連接配接特定的 DNS 字尾 . . . . . . . : 本地連結 IPv6 位址. . . . . . . . : fe80::850a:3293:c7fb:75e1%24 IPv4 位址 . . . . . . . . . . . . : 192.168.59.1 子網路遮罩 . . . . . . . . . . . . : 255.255.255.0 D:\Users\xiaomaimiao>sqlplus lhr8/lhr@orclasm SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 18 17:29:27 2017 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: User LHR8 is not allowed to connect from 192.168.59.1 ORA-06512: at line 10 Enter user-name: |
告警日志無輸出。
1.4.1.2 複雜版
複雜版就是需要記錄登入日志,并把報錯資訊輸出到告警日志中。
CREATE TABLE XB_AUDIT_LOGON_LHR( ID NUMBER PRIMARY KEY, INST_ID NUMBER, OPER_DATE DATE, OS_USER VARCHAR2(255), CLIENT_IP VARCHAR2(20), CLIENT_HOSTNAME VARCHAR2(30), DB_SCHEMA VARCHAR2(30), SID NUMBER, SERIAL# NUMBER, SPID NUMBER, SESSION_TYPE VARCHAR2(1000), DATABASE_NAME VARCHAR2(255) ) NOLOGGING PARTITION BY RANGE(OPER_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH(INST_ID) SUBPARTITION TEMPLATE ( SUBPARTITION SP1 , SUBPARTITION SP2 ) (PARTITION P201610 VALUES LESS THAN(TO_DATE('201610','YYYYMM'))); CREATE SEQUENCE S_XB_AUDIT_DDL_LHR START WITH 1 INCREMENT BY 1 CACHE 2000; SELECT S_XB_AUDIT_DDL_LHR.NEXTVAL FROM DUAL; CREATE INDEX IND_AUDIT_DDL_OS_USER ON XB_AUDIT_LOGON_LHR(OS_USER) LOCAL NOLOGGING; CREATE INDEX IND_AUDIT_DDL_SID ON XB_AUDIT_LOGON_LHR(SID,SERIAL#) LOCAL NOLOGGING; GRANT SELECT ON XB_AUDIT_LOGON_LHR TO PUBLIC; CREATE OR REPLACE PROCEDURE PRO_TRI_DDL_INSET_LHR AUTHID CURRENT_USER AS SP_XB_AUDIT_DDL_LHR XB_AUDIT_LOGON_LHR%ROWTYPE; V_COUNT NUMBER; V_TMP VARCHAR2(255); V_MODULE VARCHAR2(4000); V_ACTION VARCHAR2(4000); V_MESSAGE VARCHAR2(4000); BEGIN BEGIN SELECT A.SID, A.SERIAL#, (SELECT B.SPID FROM GV$PROCESS B WHERE B.ADDR = A.PADDR AND B.INST_ID = USERENV('INSTANCE')) SPID, UPPER(A.OSUSER) OSUSER, A.MACHINE || '--' || A.PROGRAM || '--' || A.MODULE || '--' || A.ACTION SESSION_TYPE, A.USERNAME, A.INST_ID INTO SP_XB_AUDIT_DDL_LHR.SID, SP_XB_AUDIT_DDL_LHR.SERIAL#, SP_XB_AUDIT_DDL_LHR.SPID, SP_XB_AUDIT_DDL_LHR.OS_USER, SP_XB_AUDIT_DDL_LHR.SESSION_TYPE, SP_XB_AUDIT_DDL_LHR.DB_SCHEMA, SP_XB_AUDIT_DDL_LHR.INST_ID FROM GV$SESSION A WHERE A.AUDSID = USERENV('SESSIONID') AND A.INST_ID = USERENV('INSTANCE'); --job 資訊 不同的資料庫這裡的os_user需要修改 IF UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) = 'ORACLE' THEN SELECT COUNT(1) INTO V_COUNT FROM DBA_JOBS_RUNNING A, DBA_JOBS B WHERE A.JOB = B.JOB AND A.SID = SP_XB_AUDIT_DDL_LHR.SID AND A.INSTANCE = USERENV('INSTANCE'); IF V_COUNT > 0 THEN SELECT '【DBA_JOBS:' || B.JOB || '--' || B.WHAT || '】' INTO V_TMP FROM DBA_JOBS_RUNNING A, DBA_JOBS B WHERE A.JOB = B.JOB AND A.SID = SP_XB_AUDIT_DDL_LHR.SID AND A.INSTANCE = USERENV('INSTANCE'); ELSE SELECT '--' || B.JOB_TYPE || '--' || B.JOB_ACTION FROM DBA_SCHEDULER_RUNNING_JOBS A, DBA_SCHEDULER_JOBS B WHERE A.JOB_NAME = B.JOB_NAME AND A.SESSION_ID = SP_XB_AUDIT_DDL_LHR.SID AND A.RUNNING_INSTANCE = USERENV('INSTANCE'); END IF; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; --v_module is much useful, "plsqldev.exe" DBMS_APPLICATION_INFO.READ_MODULE(V_MODULE, V_ACTION); V_MESSAGE := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' (User ' || SYS.LOGIN_USER || ' logon denied from [IP:' || ORA_CLIENT_IP_ADDRESS || ', ' || UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) || '] with ' || V_MODULE || ' ' || V_ACTION || ')'; --write alert.log SYS.DBMS_SYSTEM.KSDWRT(2, V_MESSAGE); INSERT INTO XB_AUDIT_LOGON_LHR (ID, INST_ID, OPER_DATE, OS_USER, CLIENT_IP, CLIENT_HOSTNAME, DB_SCHEMA, SID, SERIAL#, SPID, SESSION_TYPE, DATABASE_NAME) VALUES (S_XB_AUDIT_DDL_LHR.NEXTVAL, USERENV('INSTANCE'), -- sp_xb_audit_ddl_lhr.INST_ID ora_instance_num SYSDATE, UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), -- sp_xb_audit_ddl_lhr.os_user SYS_CONTEXT('userenv', 'ip_address'), --ora_client_ip_address SYS_CONTEXT('userenv', 'terminal'), --sys_context('userenv', 'host') NVL2(ORA_LOGIN_USER, SYS_CONTEXT('USERENV', 'SESSION_USER'), SP_XB_AUDIT_DDL_LHR.DB_SCHEMA), -- SYS_CONTEXT('USERENV', 'SESSION_USER') sys.login_user SP_XB_AUDIT_DDL_LHR.SID, ---- SYS_CONTEXT('USERENV', 'SID'), SP_XB_AUDIT_DDL_LHR.SERIAL#, SP_XB_AUDIT_DDL_LHR.SPID, SP_XB_AUDIT_DDL_LHR.SESSION_TYPE || V_TMP, ORA_DATABASE_NAME --sys_context('USERENV', 'DB_NAME') ); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END PRO_TRI_DDL_INSET_LHR; / CREATE OR REPLACE TRIGGER CHK_IP_LHR AFTER LOGON ON DATABASE DECLARE V_IPADDR VARCHAR2(30); V_LOGONUSER VARCHAR2(60); V_MODULE VARCHAR2(4000); V_ACTION VARCHAR2(4000); V_MESSAGE VARCHAR2(4000); SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'SESSION_USER') INTO V_IPADDR, V_LOGONUSER FROM DUAL; V_MESSAGE := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' (User ' || SYS.LOGIN_USER || ' logon denied from [IP:' || ORA_CLIENT_IP_ADDRESS || ', ' || UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) || '] with ' || V_MODULE || ' ' || V_ACTION || ')'; IF V_IPADDR LIKE ('192.168.59.%') THEN PRO_TRI_DDL_INSET_LHR; RAISE_APPLICATION_ERROR('-20001', V_MESSAGE); END IF; END; |
告警日志:
查詢日志表:
SELECT * FROM XB_AUDIT_LOGON_LHR;
1.4.1.3 注意事項
需要注意的問題:
① 觸發的對象類型可以為DATABASE,也可以為“使用者名.SCHEMA”,如:
AFTER LOGON ON DATABASE
AFTER LOGON ON SCOTT.SCHEMA
② 當觸發的對象類型為DATABASE的時候,登入使用者不能擁有“ADMINISTER DATABASE TRIGGER”的系統權限;當觸發的對象類型為“使用者名.SCHEMA”的時候,登入使用者不能擁有“ALTER ANY TRIGGER”的系統權限。否則,這些使用者還是會正常登入到資料庫,隻是将相應的報錯資訊寫入到告警日志中。是以,擁有IMP_FULL_DATABASE和DBA角色的使用者以及SYS和EXFSYS使用者将不能通過這種方式限制登入。
③ 隐含參數“_SYSTEM_TRIG_ENABLED”的預設值是TRUE,即允許DDL和系統觸發器。當設定隐含參數“_SYSTEM_TRIG_ENABLED”為FALSE的時候,将禁用DDL和系統觸發器。是以,當該值設定為FALSE的時候将不能通過這種方式限制登入。
一、 測試第二點
第二點測試如下:
SYS@orclasm > grant ADMINISTER DATABASE TRIGGER to lhr8; |
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 18 18:33:13 2017 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options LHR8@orclasm> |
Sat Mar 18 18:33:13 2017 2017-03-18 18:33:13 (User LHR8 logon denied from [IP:192.168.59.1, XIAOMAIMIAO] with sqlplus.exe ) Errors in file /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_33505.trc: ORA-20001: 2017-03-18 18:33:13 (User LHR8 logon denied from [IP:192.168.59.1, XIAOMAIMIAO] with ) ORA-06512: at line 21 |
繼續測試:
SYS@orclasm > revoke ADMINISTER DATABASE TRIGGER from lhr8; Revoke succeeded. SYS@orclasm > GRANT ALTER ANY TRIGGER TO LHR8; SYS@orclasm > |
用戶端繼續登入,發現不能正常登入。将觸發器中的AFTER LOGON ON DATABASE修改為AFTER LOGON ON LHR8.SCHEMA,其他不變,繼續測試:
發現可以正常登入了,告警日志:
二、 測試第三點
将觸發器中的AFTER LOGON ON LHR8.SCHEMA修改為AFTER LOGON ON DATABASE,其他不變,繼續測試:
不能正常登入,下面禁用系統觸發器:
SYS@orclasm > set pagesize 9999 SYS@orclasm > set line 9999 SYS@orclasm > col NAME format a40 SYS@orclasm > col KSPPDESC format a50 SYS@orclasm > col KSPPSTVL format a20 SYS@orclasm > SELECT a.INDX, 2 a.KSPPINM NAME, 3 a.KSPPDESC, 4 b.KSPPSTVL 5 FROM x$ksppi a, 6 x$ksppcv b 7 WHERE a.INDX = b.INDX 8 and lower(a.KSPPINM) like lower('%?meter%'); Enter value for parameter: _system_trig_enabled old 8: and lower(a.KSPPINM) like lower('%?meter%') new 8: and lower(a.KSPPINM) like lower('%_system_trig_enabled%') INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- -------------------------------------------------- -------------------- 1750 _system_trig_enabled are system triggers enabled TRUE SYS@orclasm > alter system set "_system_trig_enabled"=false; System altered. |
進行登入:
發現可以正常登入了。将參數"_system_trig_enabled"修改回原值。
SYS@orclasm > alter system set "_system_trig_enabled"=true; SYS@orclasm > alter system reset "_system_trig_enabled" scope=spfile sid='*'; |
1.4.1.4 利用登入觸發器實作時間段登入
Use Event Triggers
------------------
If you allow the users to log in the database only from Monday to Friday included,
and from 8AM to 6PM, create an event trigger that checks after logon on
database for each user (except the DBA users) that the connection occurs only
within this timeframe.
Example 1
-------
1. No check set up yet: any ordinary user can log into the database:
SQL> connect test_trigger/test_trigger
Connected.
2. The DBA creates an event trigger that checks if the connection occurs
between Monday and Friday , and within working hours: 8AM to 6PM.
SQL> connect system/manager SQL> create or replace trigger logon_trg after logon on database begin if (to_char(sysdate,'D') not between '2' and '6') or (to_char(sysdate, 'HH24') not between '08' and '18') then RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to log into database now.'); end if; end; / |
Trigger created.
3. It is Friday 5PM : an ordinary user can log into the database:
It is Monday 7AM : an ordinary user cannot log into the database It is Saturday 9AM : an ordinary user cannot log into the database: ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: You are not allowed to log into database now. ORA-06512: at line 3 Warning: You are no longer connected to ORACLE. SQL> |
Example 2
Another example to restrict the logon periods for a users so that they can only
access the database betrween the periods to 17:00 - 24:00 daily.
If the user attempts to logon during a period outside of this range his logon
attempt will fail:
SQL> CREATE OR REPLACE TRIGGER ScottLoginTrigger after logon on scott.schema declare temp varchar2(50); v_time varchar2(50); begin temp := 'select to_char(sysdate,''HH24:MI'') from dual'; EXECUTE IMMEDIATE temp into v_time; if (to_date(v_time,'HH24:MI') < to_date('17:00','HH24:MI')) then raise_application_error (-20001,'SCOTT access is denied until 17:00. The current time is '||v_time,true); end if; if (to_date(v_time,'HH24:MI') > to_date('23:59','HH24:MI')) then raise_application_error (-20001,'SCOTT access is denied because the time is past 23:59. The current time is '||v_time,true); end; / |
However, users with ADMINISTER DATABASE TRIGGER system privilege can log into
the database any time.
1.4.2 利用sqlnet.ora
第二種是修改$ORACLE_HOME/network/admin/sqlnet.ora檔案,增加如下内容:
TCP.VALIDNODE_CHECKING=YES #開啟IP限制功能 TCP.INVITED_NODES=(127.0.0.1,IP1,IP2,……) #允許通路資料庫的IP位址清單,多個IP位址使用逗号分開 TCP.EXCLUDED_NODES=(IP1,IP2,……) #禁止通路資料庫的IP位址清單,多個IP位址使用逗号分開 |
之後重新啟動監聽器即可。這樣用戶端在登入的時候會報“ORA-12537: TNS:connection closed”的錯誤。
① 需要設定參數TCP.VALIDNODE_CHECKING為YES才能激活該特性。
② 一定要許可或不要禁止資料庫伺服器本機的IP位址,否則通過lsnrctl将不能啟動或停止監聽,因為該過程監聽程式會通過本機的IP通路監聽器,而該IP被禁止了,但是通過服務啟動或關閉則不影響。
③ 當參數TCP.INVITED_NODES和TCP.EXCLUDED_NODES設定的位址相同的時候以TCP.INVITED_NODES的配置為主。
④ 修改之後,一定要重起監聽才能生效,而不需要重新啟動資料庫。
⑤ 這個方式隻是适合TCP/IP協定。
⑥ 這個配置适用于Oracle 9i以上版本。在Oracle 9i之前的版本使用檔案protocol.ora。
⑦ 在伺服器上直接連接配接資料庫不受影響。
⑧ 這種限制方式是通過監聽器來限制的。
⑨ 這個限制隻是針對IP檢測,對于使用者名檢測是不支援的。
删除之前建立的觸發器,繼續測試。
[grid@rhel6lhr ~]$ more $ORACLE_HOME/network/admin/sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/grid/11.2.0/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /u01/app/grid TCP.VALIDNODE_CHECKING=YES TCP.INVITED_NODES=(127.0.0.1,192.168.59.130,192.168.59.1,192.168.59.2) TCP.EXCLUDED_NODES=(172.168.*) [grid@rhel6lhr ~]$ |
重新開機監聽:
[grid@rhel6lhr ~]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-MAR-2017 18:55:54 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))) The command completed successfully |
用戶端連接配接:
[oracle@orcltest ~]$ ip a | grep eth0 4: eth0: inet 192.168.59.129/24 brd 192.168.59.255 scope global eth0 [oracle@orcltest ~]$ sqlplus lhr8/[email protected]/orclasm.lhr.com SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 18 18:57:43 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. ORA-12537: TNS:connection closed |
監聽報錯:
Sat Mar 18 18:58:44 2017 18-MAR-2017 18:58:44 * 12546 TNS-12546: TNS:permission denied TNS-12560: TNS:protocol adapter error TNS-00516: Permission denied |
使用192.168.59.1用戶端進行登入:
D:\Users\xiaomaimiao>sqlplus lhr8/[email protected]/orclasm.lhr.com SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 18 19:00:15 2017 [email protected]/orclasm.lhr.com> |
發現可以正常登入。将TCP.INVITED_NODES的IP裡加入192.168網段,則可以正常登入:
[grid@rhel6lhr ~]$ more $ORACLE_HOME/network/admin/sqlnet.ora TCP.INVITED_NODES=(127.0.0.1,192.168.59.130,192.168.59.1,192.168.59.2,192.168.*) |
SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 18 19:03:27 2017 |
1.4.3 利用防火牆
第3種是修改資料庫伺服器的IPTABLES(配置檔案:/etc/sysconfig/iptables)來限制某些IP登入資料庫伺服器。如下:
iptables -I INPUT -s 192.168.59.129 -j DROP service iptables save |
則,192.168.59.129這台主機将不能連接配接到資料庫伺服器了,會報“ORA-12170: TNS:Connect timeout occurred”的錯誤。
測試:
SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 18 19:19:23 2017 ORA-12170: TNS:Connect timeout occurred [oracle@orcltest ~]$ tnsping 192.168.59.130/orclasm.lhr.com TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 18-MAR-2017 19:18:16 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orclasm.lhr.com))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))) ^C [oracle@orcltest ~]$ ping 192.168.59.130 PING 192.168.59.130 (192.168.59.130) 56(84) bytes of data. --- 192.168.59.130 ping statistics --- 3 packets transmitted, 0 received, 100% packet loss, time 2136ms [oracle@orcltest ~]$ |
該部分可以參考網絡配置,小麥苗從網上找了很多。
我們可以通過以下的iptables的設定來限制使用者通路oracle所在linux作業系統的安全。
1、清楚作業系統預設的iptables政策
我本機安裝的是centos6.0,安裝之後系統會提供iptables預設的policy政策,我們首先要清楚預設的政策
iptables -F
2、開發22和1521端口對區域網路的某個IP,在本例中用戶端ip是192.168.1.125,oracle所在機器的IP是192.168.1.144,在這裡,設定僅有該用戶端可以通路22和1521端口,區域網路内的其他IP都不允許通路,
iptables -A INPUT -s 192.168.1.125/32 -i eth0 -p tcp --dport 22 -j ACCEPT
iptables -A INPUT -s 192.168.1.125/32 -i eth0 -p tcp --dport 1521 -j ACCEPT
iptables -A INPUT -s 192.168.1.0/24 -p tcp --dport 22 -j DROP
iptables -A INPUT -s 192.168.1.0/24 -p tcp --dport 1521 -j DROP
這樣同一網段内除192.168.1.125之外其他IP都不能通路資料庫伺服器,即使ping指令也不可以
3、開發22和1521的OUTPUT鍊給192.168.1.125,否則已經啟動的oracle instance的pmon程序無法動态注冊到1521端口中
iptables -A OUTPUT -d 192.168.1.125/32 -p tcp --sport 22 -j ACCEPT
iptables -A OUTPUT -d 192.168.1.125/32 -p tcp --sport 1521 -j ACCEPT
4、儲存目前設定的iptables規則
service iptables save
這時系統會将已經設定的規則儲存到/etc/sysconfig/iptables檔案中
否則重新開機之後之前設定的規則都會失效
先關閉所有的80端口
開啟ip段192.168.1.0/24端的80口
開啟ip段211.123.16.123/24端ip段的80口
# iptables -I INPUT -p tcp --dport 80 -j DROP
# iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 80 -j ACCEPT
# iptables -I INPUT -s 211.123.16.123/24 -p tcp --dport 80 -j ACCEPT
以上是臨時設定。
1.先備份iptables
# cp /etc/sysconfig/iptables /var/tmp
2.然後儲存iptables
# service iptables save
3.重新開機防火牆
#service iptables restart
以下是端口,先全部封再開某些的IP
iptables -I INPUT -p tcp --dport 9889 -j DROP
iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 9889 -j ACCEPT
如果用了NAT轉發記得配合以下才能生效
iptables -I FORWARD -p tcp --dport 80 -j DROP
iptables -I FORWARD -s 192.168.1.0/24 -p tcp --dport 80 -j ACCEPT
常用的IPTABLES規則如下:
隻能收發郵件,别的都關閉
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -j DROP
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p udp --dport 53 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p tcp --dport 25 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p tcp --dport 110 -j ACCEPT
IPSEC NAT 政策
iptables -I PFWanPriv -d 192.168.100.2 -j ACCEPT
iptables -t nat -A PREROUTING -p tcp --dport 80 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:80
iptables -t nat -A PREROUTING -p tcp --dport 1723 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:1723
iptables -t nat -A PREROUTING -p udp --dport 1723 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:1723
iptables -t nat -A PREROUTING -p udp --dport 500 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:500
iptables -t nat -A PREROUTING -p udp --dport 4500 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:4500
FTP伺服器的NAT
iptables -I PFWanPriv -p tcp --dport 21 -d 192.168.100.200 -j ACCEPT
iptables -t nat -A PREROUTING -p tcp --dport 21 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:21
隻允許通路指定網址
iptables -A Filter -p udp --dport 53 -j ACCEPT
iptables -A Filter -p tcp --dport 53 -j ACCEPT
iptables -A Filter -d www.3322.org -j ACCEPT
iptables -A Filter -d img.cn99.com -j ACCEPT
iptables -A Filter -j DROP
開放一個IP的一些端口,其它都封閉
iptables -A Filter -p tcp --dport 80 -s 192.168.100.200 -d www.pconline.com.cn -j ACCEPT
iptables -A Filter -p tcp --dport 25 -s 192.168.100.200 -j ACCEPT
iptables -A Filter -p tcp --dport 109 -s 192.168.100.200 -j ACCEPT
iptables -A Filter -p tcp --dport 110 -s 192.168.100.200 -j ACCEPT
多個端口
iptables -A Filter -p tcp -m multiport --destination-port 22,53,80,110 -s 192.168.20.3 -j REJECT
連續端口
iptables -A Filter -p tcp -m multiport --source-port 22,53,80,110 -s 192.168.20.3 -j REJECT iptables -A Filter -p tcp --source-port 2:80 -s 192.168.20.3 -j REJECT
指定時間上網
iptables -A Filter -s 10.10.10.253 -m time --timestart 6:00 --timestop 11:00 --days Mon,Tue,Wed,Thu,Fri,Sat,Sun -j DROP
iptables -A Filter -m time --timestart 12:00 --timestop 13:00 --days Mon,Tue,Wed,Thu,Fri,Sat,Sun -j ACCEPT
iptables -A Filter -m time --timestart 17:30 --timestop 8:30 --days Mon,Tue,Wed,Thu,Fri,Sat,Sun -j ACCEPT
禁止多個端口服務
iptables -A Filter -m multiport -p tcp --dport 21,23,80 -j ACCEPT
将WAN 口NAT到PC
iptables -t nat -A PREROUTING -i $INTERNET_IF -d $INTERNET_ADDR -j DNAT --to-destination 192.168.0.1
将WAN口8000端口NAT到192。168。100。200的80端口
iptables -t nat -A PREROUTING -p tcp --dport 8000 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:80
MAIL伺服器要轉的端口
iptables -t nat -A PREROUTING -p tcp --dport 110 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:110
iptables -t nat -A PREROUTING -p tcp --dport 25 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:25
隻允許PING 202。96。134。133,别的服務都禁止
iptables -A Filter -p icmp -s 192.168.100.200 -d 202.96.134.133 -j ACCEPT
禁用BT配置
iptables –A Filter –p tcp –dport 6000:20000 –j DROP
禁用QQ防火牆配置
iptables -A Filter -p udp --dport ! 53 -j DROP
iptables -A Filter -d 218.17.209.0/24 -j DROP
iptables -A Filter -d 218.18.95.0/24 -j DROP
iptables -A Filter -d 219.133.40.177 -j DROP
基于MAC,隻能收發郵件,其它都拒絕
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -j DROP
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -p tcp --dport 25 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -p tcp --dport 110 -j ACCEPT
禁用MSN配置
iptables -A Filter -p udp --dport 9 -j DROP
iptables -A Filter -p tcp --dport 1863 -j DROP
iptables -A Filter -p tcp --dport 80 -d 207.68.178.238 -j DROP
iptables -A Filter -p tcp --dport 80 -d 207.46.110.0/24 -j DROP
隻允許PING 202。96。134。133 其它公網IP都不許PING
iptables -A Filter -p icmp -j DROP
禁止某個MAC位址通路internet:
iptables -I Filter -m mac --mac-source 00:20:18:8F:72:F8 -j DROP
禁止某個IP位址的PING:
iptables –A Filter –p icmp –s 192.168.0.1 –j DROP
禁止某個IP位址服務:
iptables –A Filter -p tcp -s 192.168.0.1 --dport 80 -j DROP
iptables –A Filter -p udp -s 192.168.0.1 --dport 53 -j DROP
隻允許某些服務,其他都拒絕(2條規則)
iptables -A Filter -p tcp -s 192.168.0.1 --dport 1000 -j ACCEPT
禁止某個IP位址的某個端口服務
iptables -A Filter -p tcp -s 10.10.10.253 --dport 80 -j ACCEPT
iptables -A Filter -p tcp -s 10.10.10.253 --dport 80 -j DROP
禁止某個MAC位址的某個端口服務
iptables -I Filter -p tcp -m mac --mac-source 00:20:18:8F:72:F8 --dport 80 -j DROP
iptables -I Filter -m mac --mac-source 00:11:22:33:44:55 -j DROP
1.5 本文總結
在Oracle中,有3種辦法可以限定特定IP通路資料庫。第一種是利用登入觸發器,如下:
RAISE_APPLICATION_ERROR('-20001', 'User '||V_LOGONUSER||' is not allowed to connect from '||V_IPADDR); |
② 當觸發的對象類型為DATABASE的時候,登入使用者不能擁有“ADMINISTER DATABASE TRIGGER”的系統權限;當觸發的對象類型為“使用者名.SCHEMA”的時候,登入使用者不能擁有“ALTER ANY TIGGER”的系統權限。否則,這些使用者還是會正常登入到資料庫,隻是将相應的報錯資訊寫入到告警日志中。是以,擁有IMP_FULL_DATABASE和DBA角色的使用者以及SYS和EXFSYS使用者将不能通過這種方式限制登入。
iptables -A INPUT -s 192.168.59.1/32 -i eth0 -p tcp --dport 1521 -j DROP |
則,192.168.59.1這台主機将不能通過1521端口連接配接到資料庫伺服器了,會報“ORA-12170: TNS:Connect timeout occurred”的錯誤。
1.6 參考
1.6.1 MOS
1.6.1.1 Connecting as DBA Does not Fire RAISE_APPLICATION_ERROR in a AFTER LOGON ON DATABASE TRIGGER (文檔 ID 226058.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 03-Aug-2016***
SYMPTOMS
TRIGGER AFTER LOGON ON DATABASE does not fire
Login as a user with DBA privileges
ORA-00604: error occurred at recursive SQL level %s
ORA-06512: at %sline %s
CAUSE
Documented and expected behavior.
SOLUTION
Oracel Database 11.2:
Oracle? Database PL/SQL Language Reference 11g Release 2 (11.2)
Chapter 9 PL/SQL Triggers
Exception Handling in Triggers
In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.
In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):
The triggering event is either AFTER STARTUP ON DATABASE or BEFORE SHUTDOWN ON DATABASE.
The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.
The triggering event is AFTER LOGON ON SCHEMA and the user either owns the schema or has the ALTER ANY TRIGGER privilege.
Oracle Server 11.1:
Oracle Database PL/SQL Language Reference 11g Release 1
Chapter: Using Triggers
Section: Error Conditions and Exceptions in the Trigger Body
If a predefined or user-defined error condition or exception is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back (unless the error is trapped by an exception handler). Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or integrity constraints.
The only exception to this is when the event under consideration is database STARTUP, SHUTDOWN, or LOGIN when the user logging in is SYSTEM. In these scenarios, only the trigger action is rolled back.
REFERENCES
BUG:1415194 - RAISE_APPLICATION_ERROR DOES NOT RAISE AN EXCEPTION INSIDE A AFTER LOGON TRIGGER
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#CIHGJCFI
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#autoId33
1.6.1.2 How to Prevent Users From Log Into a Database Within Defined Periods (文檔 ID 220491.1)
PURPOSE
-------
This document explains how to set up the database security so that users cannot
log into the database outside defined time-window.
SCOPE & APPLICATION
-------------------
For DBAs who need to prevent users from log into the database outside defined
time periods.
Use Event Triggers
------------------
If you allow the users to log in the database only from Monday to Friday included,
and from 8AM to 6PM, create an event trigger that checks after logon on
database for each user (except the DBA users) that the connection occurs only
within this timeframe.
Example 1
-------
1. No check set up yet: any ordinary user can log into the database:
SQL> connect test_trigger/test_trigger
Connected.
2. The DBA creates an event trigger that checks if the connection occurs
between Monday and Friday , and within working hours: 8AM to 6PM.
SQL> connect system/manager
Connected.
SQL> create or replace trigger logon_trg after logon on database
begin
if (to_char(sysdate,'D') not between '2' and '6')
or (to_char(sysdate, 'HH24') not between '08' and '18') then
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to log into
database now.');
end if;
end;
/
Trigger created.
3. It is Friday 5PM : an ordinary user can log into the database:
SQL> connect test_trigger/test_trigger
Connected.
It is Monday 7AM : an ordinary user cannot log into the database
It is Saturday 9AM : an ordinary user cannot log into the database:
SQL> connect test_trigger/test_trigger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to log into database now.
ORA-06512: at line 3
Warning: You are no longer connected to ORACLE.
SQL>
Example 2
-------
Another example to restrict the logon periods for a users so that they can only
access the database betrween the periods to 17:00 - 24:00 daily.
If the user attempts to logon during a period outside of this range his logon
attempt will fail:
SQL> CREATE OR REPLACE TRIGGER ScottLoginTrigger after logon on scott.schema
declare
temp varchar2(50);
v_time varchar2(50);
begin
temp := 'select to_char(sysdate,''HH24:MI'') from dual';
EXECUTE IMMEDIATE temp into v_time;
if (to_date(v_time,'HH24:MI') < to_date('17:00','HH24:MI')) then
raise_application_error (-20001,'SCOTT access is denied until 17:00. The current time is '||v_time,true);
end if;
if (to_date(v_time,'HH24:MI') > to_date('23:59','HH24:MI')) then
raise_application_error (-20001,'SCOTT access is denied because the time is past 23:59. The current time is '||v_time,true);
end if;
end;
/
However, users with ADMINISTER DATABASE TRIGGER system privilege can log into
the database any time.
RELATED DOCUMENTS
----------------- Note:70679.1 How to Audit Logon/Logoff Events with Triggers
1.6.1.3 ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (文檔 ID 265012.1)
***Checked for relevance on 02-Oct-2012***
PURPOSE
-------
This bulletin explains which effect the system privilege ADMINISTER DATABASE
TRIGGER has on database triggers when errors are raised.
Similarly, ALTER ANY TRIGGER system privilege causes schema logon triggers to be
skipped on errors.
SCOPE & APPLICATION
-------------------
For DBAs who set up database access control using logon triggers.
ADMINISTER DATABASE TRIGGER Privilege Behavior with Database Logon Trigger
--------------------------------------------------------------------------
Logon triggers can be used to mediate database access: when the restrictive
conditions are not met, an application error with a message is raised that
causes the logon to be denied.
create or replace trigger on_logon
after logon on database
begin
if USER='TEST' then
raise_application_error(-20002,'LOGON ERROR',true);
end if;
end;
/
If user TEST connects, he is rejected:
SQL> connect test/test
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: LOGON ERROR
ORA-06512: at line 21
Warning: You are no longer connected to ORACLE.
However, we need to keep at least one user who can still connect when there is
a problem : a fallback mechanism must exist where an administrative user is
exempt from such errors of a prohibited connection.
Any user granted the ADMINISTER DATABASE TRIGGER system privilege can still
connect : instead of getting the error causing the session to be terminated,
the error is recorded in the alert.log and a trace file in user_dump_dest.
SQL> connect / as sysdba
Connected.
SQL> grant ADMINISTER DATABASE TRIGGER to TEST;
Grant succeeded.
SQL> connect test/test
Connected.
In alert.log :
Fri Mar 5 12:17:08 2004
Errors in file /ots2/app/oracle/admin/v920/udump/v920_ora_7682.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: LOGON ERROR
ORA-06512: at line 21
In trace file :
*** SESSION ID:(15.76) 2004-03-05 12:17:08.750
Skipped error 604 during the execution of SYS.ON_LOGON
*** 2004-03-05 12:17:08.768
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: LOGON ERROR
ORA-06512: at line 21
The ADMINISTER DATABASE TRIGGER is by default granted to the following users and
roles (are not listed the options' schemas) :
SQL> select grantee from dba_sys_privs
2 where privilege='ADMINISTER DATABASE TRIGGER';
GRANTEE
------------------------------
DBA --> role
SYS --> user
IMP_FULL_DATABASE --> role
EXFSYS --> user
ALTER ANY TIGGER Privilege Behavior with Schema Logon Trigger
-------------------------------------------------------------
Similarly, if the logon trigger is on SCHEMA and the current user is not the
owner, ALTER ANY TRIGGER privilege is required to be able to connect.
connect system/manager
create or replace trigger on_logon
after logon on TEST.schema
begin
raise_application_error(-20002,'LOGON ERROR',true);
end;
/
SQL> connect test/test
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: LOGON ERROR
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
SQL> conn system/manager
Connected.
SQL> grant alter any trigger to test;
Grant succeeded.
SQL> conn test/test
Connected.
Triggers owned by SYS and O7_DICTIONARY_ACCESSIBILITY
-----------------------------------------------------
Note that in the above example, the schema trigger is actually owned by user SYSTEM,
this means when user TEST has the ALTER ANY TRIGGER privilege, the trigger can be altered.
As of version 9i, it is O7_DICTIONARY_ACCESSIBILITY parameter that governs/protects the
access to SYS objects, this means that if a schema trigger is owned by user SYS, even if the
user has the ALTER ANY TRIGGER privilege, the logon would still fail as this privilege
is not sufficient to alter SYS objects unless O7_DICTIONARY_ACCESSIBILITY = true.
For security reasons, Oracle recommends that you use this setting only with great caution.
RELATED DOCUMENTS
----------------- Note:120712.1 Database or Logon Event Trigger becomes Invalid: Who can Connect? Note:220491.1 How to Prevent Users From Log Into a Database Within Defined Periods Note:116636.1 ORA-4098 or ORA-4045 logging on to database having AFTER LOGON
event trigger
第二章 實驗中用到的SQL總結
grant ADMINISTER DATABASE TRIGGER to lhr8; GRANT ALTER ANY TRIGGER TO LHR8; CREATE OR REPLACE TRIGGER CHK_IP_LHR AFTER LOGON ON DATABASE DECLARE V_IPADDR VARCHAR2(30); V_LOGONUSER VARCHAR2(60); BEGIN SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'SESSION_USER') INTO V_IPADDR, V_LOGONUSER FROM DUAL; IF V_IPADDR LIKE ('192.168.59.%') THEN RAISE_APPLICATION_ERROR('-20001', 'User '||V_LOGONUSER||' is not allowed to connect from '||V_IPADDR); END IF; END; / set pagesize 9999 set line 9999 col NAME format a40 col KSPPDESC format a50 col KSPPSTVL format a20 SELECT a.INDX, a.KSPPINM NAME, a.KSPPDESC, b.KSPPSTVL FROM x$ksppi a, x$ksppcv b WHERE a.INDX = b.INDX and lower(a.KSPPINM) like lower('%?meter%'); alter system set "_system_trig_enabled"=true; alter system reset "_system_trig_enabled" scope=spfile sid='*'; iptables -I INPUT -s 192.168.59.129 -j DROP service iptables save |
-------------------------------------------------------------------------