[20210428]資料庫連線加密.txt

[20210428]資料庫連線加密.txt

--//有人問資料庫連線加密的問題，實際上加密是一把雙刃劍，好處是無法透過網路抓包獲取sql語句。
--//以前寫的連結：http://blog.itpub.net/267265/viewspace-1715834/ => [20150629]簡單的加密連線.txt
--//我的當時給的建議修改客戶端檔案sqlnet.ora加入：
SQLNET.ENCRYPTION_CLIENT = REQUIRED

--//這樣在服務端就無法透過網路抓取到sql語句，順便解答指令碼的問題。

#  cat -vT /usr/local/bin/Tcpdumpsql
#! /bin/bash
/usr/sbin/tcpdump  -l -i eth0 -s 0 -A -nn src host $1 and dst port 1521 2>/dev/null |  tee -a /tmp/aa1 |sed -u -e  "s/^M/!/g;s/^E\.\..\{1,100\}//;s/\.*$//;s/^\.*//" | \
awk '{if (tolower($0) ~ "select" || tolower($0) ~ "begin" || tolower($0) ~ "update" ||  tolower($0) ~ "delete" ||tolower($0) ~ "alter" || tolower($0) ~ "insert" || $0 ~ "ORA-" ) {p=1;print} \
else if(p == 1 && $0 !~ "^[0-9][0-9]:") {print} else if ($0 ~ "^[0-9][0-9]:") {p=0}}'

--//指令碼里面的^M表示回車，在vim下要透過ctrl+v，ctrl+M輸入。還是介面我指令碼寫死了eth0，大家根據需要修改網路介面名。
--//實際上涉及4個引數：
SQLNET.ENCRYPTION_SERVER
SQLNET.ENCRYPTION_CLIENT

SQLNET.ENCRYPTION_TYPES_CLIENT
SQLNET.ENCRYPTION_TYPES_SERVER

--//SQLNET.ENCRYPTION_SERVER,SQLNET.ENCRYPTION_CLIENT 啟用network encryption，一個設定在客戶端，一個設定在服務端。
--//Possible values of parameters are:
accepted to enable the security service if required or requested by the other side.
rejected to disable the security service, even if required by the other side.
requested to enable the security service if the other side allows it.
required to enable the security service and disallow the connection if the other side is not enabled for the security service.
--//前面2個很好理解，後面2個，英文有點繞口。

--//SQLNET.ENCRYPTION_TYPES_CLIENT,SQLNET.ENCRYPTION_TYPES_SERVER 設定加密的型別也就是演算法，預設採用AES256.
--//oracle 支援如下加密演算法：
3des112 for triple DES with a two-key (112-bit) option
3des168 for triple DES with a three-key (168-bit) option
aes128 for AES (128-bit key size)
aes192 for AES (192-bit key size)
aes256 for AES (256-bit key size)
des for standard DES (56-bit key size)
des40 for DES (40-bit key size)
rc4_40 for RSA RC4 (40-bit key size)
rc4_56 for RSA RC4 (56-bit key size)
rc4_128 for RSA RC4 (128-bit key size)
rc4_256 for RSA RC4 (256-bit key size)

--//這些加密的東西完全不懂，僅僅做一個記錄。我在簡單測試看看：

1.環境：
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試1:
--//服務端不做任何更改.
--//在客戶端設定。
E:\>grep -i ENCRYPTION E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
SQLNET.ENCRYPTION_CLIENT = REQUIRED
#SQLNET.ENCRYPTION_TYPES_CLIENT =aes256

SCOTT@78> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
        44         19 9104:7988                DEDICATED 21325                     27          8 alter system kill session '44,19' immediate;

SCOTT@78> select network_service_banner c100 from v$session_connect_info where sid =44;
C100
----------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 11.2.0.4.0 - Product
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.4.0 - Production
--//加密演算法AES256.
        
3.測試2:
--//服務端設定：
$ grep -i ENCRYPTION sqlnet.ora
#SQLNET.ENCRYPTION_TYPES_SERVER=aes256
SQLNET.ENCRYPTION_SERVER=rejected

--//客戶端設定:
E:\>grep -i ENCRYPTION E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
SQLNET.ENCRYPTION_CLIENT = REQUIRED
#SQLNET.ENCRYPTION_TYPES_CLIENT =aes256

--//這樣客戶端無法登入：
R:\>sqlplus -s -l scott/book@78
ERROR:
ORA-12660: Encryption or crypto-checksumming parameters incompatible
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

4.測試3：
--//服務端設定：
$ grep -i ENCRYPTION sqlnet.ora
SQLNET.ENCRYPTION_TYPES_SERVER=3des168
#SQLNET.ENCRYPTION_SERVER=rejected

--//客戶端設定:
E:\>grep -i ENCRYPTION E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
SQLNET.ENCRYPTION_CLIENT = REQUIRED
#SQLNET.ENCRYPTION_TYPES_CLIENT =aes256

SCOTT@78> select network_service_banner c100 from v$session_connect_info where sid =44;
C100
----------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: 3DES168 encryption service adapter for Linux: Version 11.2.0.4.0 - Produc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.4.0 - Production
--//加密演算法 3DES168.

5.測試4：
--//服務端設定：
$ grep -i ENCRYPTION sqlnet.ora
SQLNET.ENCRYPTION_TYPES_SERVER=3des168
#SQLNET.ENCRYPTION_SERVER=rejected

--//客戶端設定:
E:\>grep -i ENCRYPTION E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT =aes256
--//兩邊的加密演算法不同。

--//這樣客戶端無法登入：
R:\>sqlplus -s -l scott/book@78
ERROR:
ORA-12650: No common encryption or data integrity algorithm
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//如果修改一致，可以登入，不再貼出測試結果。其它組合不測試了

7.收尾：
--//註解這些相關設定。

SCOTT@78> select network_service_banner c100 from v$session_connect_info where sid =44;
C100
----------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.4.0 - Production
Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.4.0 - Production

--//Tcpdumpsql的跟蹤就能看到如下資訊：
# Tcpdumpsql 192.168.98.6
!....J...........^.a...........................!................................................................................................................................................select sysdate from dual
]./...k.........i..................^.a...............j...........!...............................................................................................................................................jselect dbms_utility.port_string port_string, version,v$version.* from v$instance,v$version where rownum<=1

B.Z...........s..............................................scott.....AUTH_SESSKEY`...`A8E931E6BB761167ADFD785D24893A1F393CF1F2E7C297CAC3148F55FCEB866865C0E6DADCEECCEFBBFD85DB0DC49397....!...!AUTH_PASSWORD@...@2A05F70CAEF31F55205873C39D9A4859561A8C892684EE2C817FEC95C77F4126.........AUTH_RTT.....8422....!...!AUTH_CLNT_MEM.....4096....!...!AUTH_TERMINAL.....IKD84BCP.........AUTH_PROGRAM_NM.....sqlplus.exe.........AUTH_MACHINE.....WORKGROUP\IKD84BCP.........AUTH_PID   ...     7992:5084.........AUTH_SID!...!Administrator.........AUTH_CONNECT_STRING.....(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))(SDU=32768)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)(CID=(PROGRAM=E:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus.exe)(HOST=IKD84BCP)(USER=Administrator)))).........SESSION_CLIENT_CHARSET.....852.........SESSION_CLIENT_LIB_TYPE.....1.........SESSION_CLIENT_DRIVER_NAME.....SQL*PLUS.........SESSION_CLIENT_VERSION ...     203424000.........SESSION_CLIENT_LOBATTR.....1.........AUTH_ACL.....8800.........AUTH_ALTER_SESSION......ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINA.RY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'...........AUTH_LOGICAL_SESSION_ID ... 9390D233D34E4CBB92FD84C696E2EDDB.........AUTH_FAILOVER_ID
B.e.K.........^.a...............]...........!...............................................................................................................................................]SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
B.h. .........i..................^      !...........................!................................................................................................................................................BEGIN DBMS_OUTPUT.DISABLE; END;
^.a...........................!................................................................................................................................................SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)
B.m...........i..................^!a...........................!................................................................................................................................................SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND   ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND   (UPPER(ATTRIBUTE) = 'ROLES')
B.n.^.........i..................^.)...............6...........!...............................................................................................................................................6BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;...........................................................................T........SQL*Plus
B.p.J.........i..................^.q...............,...........!...............................................................................................................................................,SELECT DECODE('A','A','1','2') FROM SYS.DUAL
*.B.s.:.........^.a...............L...........!...............................................................................................................................................Lselect network_service_banner c100 from v$session_connect_info where sid =44