[20210428]数据库连接加密.txt

来源：这里教程网时间：2026-03-03 16:38:37 作者：

[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