[Ubuntu] iptables 規則

將 iptables 限制連線做 log
當我們運用 iptables 限制同一IP單位時間連線數時,無法得知有那些IP被DROP掉,這時可利用iptables增加一個chain來做log,並將它獨立到其他的LOG檔,而非/var/log/messages。
在/etc/sysconfig/iptables中修改設定,新建立一個chain,再將log及DROP的規則放到這個chain裡,只要事件觸發套用這個chain即可:

建立新的chain:
-N LOG_AND_DROP
-A LOG_AND_DROP -j LOG –log-prefix “iptables DROP: " –log-level 7
-A LOG_AND_DROP -j DROP

再將相關的rule套用這個chain:
-A INPUT -p tcp –dport 22 -m recent –rcheck –seconds 600 –hitcount 5 –name SSH_LOG –rsource -j LOG_AND_DROP
-A INPUT -p tcp –dport 22 -m recent –set –name SSH_LOG –rsource
-A INPUT -p tcp –dport 22 -j ACCEPT

在CentOS 6.3中可在 /etc/rsyslog.d/ 中建立一個新的 conf 檔/etc/rsyslog.d/iptables.conf:
:msg,contains,"iptables DROP: " /var/log/iptables.log
& ~
它即會將LOG_AND_DROP的事件記錄到 /var/log/iptables.log 中。

CentOS 6.3及較早的版本,可直接在 /etc/rsyslog.conf 或 /etc/syslog.conf 中加入log的設定檔:
kern.=7 /var/log/iptables.log

重新啟動syslogd(或rsyslogd)及iptables後,即會開始將log記錄到/var/log/iptables.log中:
#service syslogd restart
#service iptables restart

在iptables.log就可以看到相關的記錄:
Jan 30 04:51:30 hostname kernel: iptables DROP: IN=eth0 OUT= MAC=00:15:5d:43:b9:02:ff:34:96:20:c4:a4:28:00 SRC=118.174.1.133 DST=xxx.xxx.xxx.xxx LEN=60 TOS=0x00 PREC=0x00 TTL=50 ID=4358 DF PROTO=TCP SPT=51329 DPT=22 WINDOW=5840 RES=0x00 SYN URGP=0

如果要加入 logrotate,則可在 /etc/logrotate.d/syslog 中,加入 /var/log/iptables.log ,讓它一併做logrotate:
/var/log/cron
/var/log/maillog
/var/log/messages
/var/log/secure
/var/log/spooler
/var/log/iptables.log
{
sharedscripts
postrotate
/bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
endscript
}

設定好後,即可定時將記錄檔一併做rotate,再加上compress的選項更可節省空間。

reference: http://ishm.idv.tw/?p=192

[SQL] ORACLE 建立 sequence

建立 Sequence 語法
create sequence .
_s
increment by 1
start with 1
maxvalue 99999999999999999999
minvalue 1
nocycle;

其中 :
incerment: 每次增加的數值
start : Sequence 開始數值
maxvalue : Sequence 最大值
nocycle : 當 Sequence 達最大值時,不重頭開始

取得下一筆 Sequence 值
select .nextval[@db_link]
from dual;

查詢目前 Sequence 值
語法 1:
select .currval[@db_link]
from dual;

Mark 提供: 若 “目前所在的 DB Session" 尚未執行 nextval, 就先執行 currval, 則會得到
ORA-08002: sequence DB_SESSION_ID.CURRVAL is not yet defined in this session 錯誤.

語法 2:
select last_number – 1
from all_sequences
where sequence_name = ‘‘;

修改 Sequence
如: Alter Sequence MaxValue 999999999999999;

Alter Sequence 其他線上參考: http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_2011.htm

注意事項
1) Sequence 不能直接用在 “等式 :=" 之後.
如: vID := tomSeq.nextval;

2) Sequence 可以直接用在 “SQL" 之中.
如: insert into tomTable values( tomSeq.nextval, … );

reference: http://tomkuo139.blogspot.tw/2010/02/oracle-database-sequence.html

[SQL] 產生 TABLESPACE 、DROP用法

–新增 TABLESPACE
CREATE TABLESPACE A17A_LND_IDX datafile ‘D:/A17A/A17A_LND_IDX’ size 50m AUTOEXTEND ON NEXT 204800000 MAXSIZE 8192M;

–刪除 TABLESPACE
DROP TABLESPACE A17A_LND_IDX INCLUDING CONTENTS CASCADE CONSTRAINTS;

— 修改 TABLESPACE 大小 (未試過)
ALTER TABLESPACE bigtbs RESIZE 80G;

— TABLESPACE 剩餘空間
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name
) a,
(
select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name
) b
where a.tablespace_name(+)=b.tablespace_name;

— 將建出的 TABLESPACE 授權給 USER
ALTER USER “username" QUOTA UNLIMITED ON “tablespace name"
GRANT UNLIMITED TABLESPACE TO “username"

–範例如下
ALTER USER FDCA QUOTA UNLIMITED ON FDCA_LND_DAT
GRANT UNLIMITED TABLESPACE TO FDCA;

— TABLESPACE 放在哪個資料夾下
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;

— TABLESPACE 的大小
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, NVL(FLOOR(MAX_EXTENTS/(1024*1024)), ‘0’) || ‘G’ MAX_SIZE, PCT_INCREASE
FROM DBA_TABLESPACES;

— TABLE 在哪個 TABLESPACE 內
select owner, table_name,tablespace_name
from dba_tables
WHERE TABLE_NAME LIKE ‘LND%';

CREATE TABLE A17A.YCMC099(
CODEKEY NVARCHAR2(20),
TITLE NVARCHAR2(100) ,
CODE NVARCHAR2(20)
) Tablespace A17A_LND_IDX Pctfree 5 Storage (Initial 1M Next 16M);

— 刪 TABLE 要加 PURGE 否則會到 垃圾桶裡出現
DROP TABLE A17A.YCMC099 PURGE;
create index A17A.I_LNDU302_IDX2 on A17A.LNDU302 (MANAGE_CD) Tablespace A17A_LND_IDX Pctfree 5 Storage (Initial 1M Next 16M);

— 暫存
CREATE TABLESPACE A17A_LND_IDX datafile ‘d:/oracle’ size 50m
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

[Apache] server.xml 寫法

<Context docBase="xxx" path="/xxx" reloadable="true" source="org.eclipse.jst.jee.server:xxx">
<Resource
name="jdbc/xxx"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
maxActive="200″
maxIdle="30″
maxWait="10000″
username="xxxxxxx"
password="xxxxxxxxx"
type="javax.sql.DataSource"
url="jdbc:sqlserver://localhost:1433;database=xxx;" />
</Context>

[SQL] 寫檔/巨集

–mysql
SELECT * from myguests
INTO OUTFILE ‘d:/tmp/querydump.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"‘
LINES TERMINATED BY ‘\n’

–oracle
spool myoutputfile.txt
select * from users;
spool off;

[SQL] 建使用者/改密碼

CONNECT SYS AS SYSDBA –要先登入 才可做以下的修改

CREATE USER PUBA IDENTIFIED BY puba; –建使用者
alter user FDCA identified by fdca; –修改密碼
GRANT CREATE SESSION TO PUBA; –增加權限

建立使用者
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password';