I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
Introduction: – We are living in the world of username and password life. We all have countless password due to technology’s development. Password plays an important role in our life. Oracle Database leads the industry in security. To maximize the security features offered by Oracle Database in any business environment, it is imperative that the database itself be well protected.
how to change the password in Oracle Database in a secure way? Studying recently for a certification exam I came across a very interesting question and that very few DBAs are aware, I would have guessed that some 95% do not know this.
What I want to show here is that if you do not use encryption in SQL Net everything that goes from the client until the server is there visible on the network, just run a sniffer on the network or just do as I did. So when you change the password of a database user using the best-known command is actually leaving your database even more vulnerable.
Let’s go to what I want to show …
Changing password via SQL * Plus in an insecure way
I will run the tcpdump command to monitor my network card:
[root@oracle-srv ~]# tcpdump -XX -i eth0 > tcpdump.log tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes ^C99 packets captured 100 packets received by filter 0 packets dropped by kernel
And then change my user’s password in the database through the SQL * Plus client:
Frankys-MBP:~ frankyweber$ sqlplus system/oracle@192.168.15.21:1521/cdb3.localdomain SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 24 21:07:37 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Jun 24 2017 20:26:25 -03:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter user c##franky identified by oracle; User altered.
Let’s see what was written to the tcpdump.log file. I’ll show only the part that matters, because tcpdump captures all the traffic that passed through this network card. Let’s see:
[root@oracle-srv ~]# vi tcpdump.log
21:07:42.337686 IP Frankys-MBP.57728 > oracle-srv.localdomain.ncube-lm: Flags [P.], seq 6724:7023, ack 7042, win 4096, options [nop,nop,TS val 1454618478 ecr 31220076], length 299
0x0000: 0800 270f d012 80e6 5009 af7a 0800 4500 ..'.....P..z..E.
0x0010: 015f 1de8 4000 4006 7c3d c0a8 0f0e c0a8 ._..@.@.|=......
0x0020: 0f15 e180 05f1 aa83 ce5f 6566 9019 8018 ........._ef....
0x0030: 1000 60dc 0000 0101 080a 56b3 b76e 01dc ..`.......V..n..
0x0040: 616c 0000 012b 0600 0000 0000 035e 1121 al...+.......^.!
0x0050: 8000 0000 0000 00fe ffff ffff ffff ff7b ...............{
0x0060: 0000 00fe ffff ffff ffff ff0d 0000 00fe ................
0x0070: ffff ffff ffff fffe ffff ffff ffff ff00 ................
0x0080: 0000 0001 0000 0000 0000 0000 0000 0000 ................
0x0090: 0000 0000 0000 0000 0000 0000 0000 00fe ................
0x00a0: ffff ffff ffff ff00 0000 0000 0000 00fe ................
0x00b0: ffff ffff ffff fffe ffff ffff ffff fffe ................
0x00c0: ffff ffff ffff ff00 0000 0000 0000 00fe ................
0x00d0: ffff ffff ffff fffe ffff ffff ffff ff00 ................
0x00e0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00f0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0100: 0000 0000 0000 0000 0000 0000 0000 0029 ...............)
0x0110: 616c 7465 7220 7573 6572 2063 2323 6672 alter.user.c##fr
0x0120: 616e 6b79 2069 6465 6e74 6966 6965 6420 anky.identified.
0x0130: 6279 206f 7261 636c 6501 0000 0001 0000 by.oracle.......
0x0140: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0150: 0000 0000 0007 0000 0000 0000 0000 8000 ................
0x0160: 0000 0000 0000 0000 0000 0000 00 .............
Watch the danger … the command I used to change the password, including the password itself, is available to anyone who knows how to intercept it.
So I would like to introduce you to a command that few people know about and it is of great importance because it sends only the hash of the password to the database, even if you do not have Oracle Net encrypted.
Changing password via SQL * Plus securely
I will generate a new tcpdump and store the output in another log:
[root@oracle-srv ~]# tcpdump -XX -i eth0 > tcpdump_2.log tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes ^C112 packets captured 113 packets received by filter 0 packets dropped by kernel
I raise the password via SQL * Plus again, but this time using the password command, which sends the encrypted password to the database by increasing the security of the environment a bit:
Frankys-MBP:~ frankyweber$ sqlplus system/oracle@192.168.15.21:1521/cdb3.localdomain SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 24 21:14:51 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Jun 24 2017 21:07:35 -03:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> password c##franky Changing password for c##franky New password: Retype new password: Password Changed
Let’s look at the generated tcpdump log file. Again I will only show the contents of the file that interests us:
[root@oracle-srv ~]# vi tcpdump_2.log 21:14:59.420581 IP Frankys-MBP.57844 > oracle-srv.localdomain.ncube-lm: Flags [P.], seq 6724:7185, ack 7043, win 4096, options [nop,nop,TS val 1455054066 ecr 31653560], length 461 0x0000: 0800 270f d012 80e6 5009 af7a 0800 4500 ..'.....P..z..E. 0x0010: 0201 78cc 4000 4006 20b7 c0a8 0f0e c0a8 ..x.@.@......... 0x0020: 0f15 e1f4 05f1 b912 0a49 6970 cc99 8018 .........Iip.... 0x0030: 1000 28ab 0000 0101 080a 56ba 5cf2 01e2 ..(.......V.\... 0x0040: feb8 0000 01cd 0600 0000 0000 116b 118e .............k.. 0x0050: 0000 0082 b100 0001 0000 0003 7312 feff ............s... 0x0060: ffff ffff ffff 1b00 0000 0201 0000 feff ................ 0x0070: ffff ffff ffff 0800 0000 feff ffff ffff ................ 0x0080: ffff feff ffff ffff ffff 0963 2323 6672 ...........c##fr 0x0090: 616e 6b79 2400 0000 0c41 5554 485f 5345 anky$....AUTH_SE 0x00a0: 5353 4b45 5900 0000 0001 0000 0030 0000 SSKEY........0.. 0x00b0: 0010 4155 5448 5f4e 4557 5041 5353 574f ..AUTH_NEWPASSWO 0x00c0: 5244 c000 0000 4045 3435 3835 3731 4632 RD....@E458571F2 0x00d0: 3538 3846 3246 3534 3241 3643 3834 3732 588F2F542A6C8472 0x00e0: 3041 3137 3531 4244 3231 3232 4239 3046 0A1751BD2122B90F 0x00f0: 4534 3837 3933 3943 4132 3032 3733 4643 E487939CA20273FC 0x0100: 3432 3042 3933 4100 0000 0027 0000 000d 420B93A....'.... 0x0110: 4155 5448 5f54 4552 4d49 4e41 4c15 0000 AUTH_TERMINAL... 0x0120: 0007 7474 7973 3030 3500 0000 002d 0000 ..ttys005....-.. 0x0130: 000f 4155 5448 5f50 524f 4752 414d 5f4e ..AUTH_PROGRAM_N 0x0140: 4d5d 0000 001f 7371 6c70 6c75 7340 4672 M]....sqlplus@Fr 0x0150: 616e 6b79 732d 4d42 5020 2854 4e53 2056 ankys-MBP.(TNS.V 0x0160: 312d 5633 2900 0000 0024 0000 000c 4155 1-V3)....$....AU 0x0170: 5448 5f4d 4143 4849 4e45 2100 0000 0b46 TH_MACHINE!....F 0x0180: 7261 6e6b 7973 2d4d 4250 0000 0000 1800 rankys-MBP...... 0x0190: 0000 0841 5554 485f 5049 440f 0000 0005 ...AUTH_PID..... 0x01a0: 3333 3733 3500 0000 0018 0000 0008 4155 33735.........AU 0x01b0: 5448 5f53 4944 2100 0000 0b66 7261 6e6b TH_SID!....frank 0x01c0: 7977 6562 6572 0000 0000 3600 0000 1241 yweber....6....A 0x01d0: 5554 485f 414c 5445 525f 5345 5353 494f UTH_ALTER_SESSIO 0x01e0: 4e6f 0000 0025 414c 5445 5220 5345 5353 No...%ALTER.SESS 0x01f0: 494f 4e20 5345 5420 5449 4d45 5f5a 4f4e ION.SET.TIME_ZON 0x0200: 453d 272d 3033 3a30 3027 0001 0000 00 E='-03:00'.....
Note that this time the password is not displayed.
Changing the password via SQL Developer in an insecure way (1)
“Ah Franky, but we usually change the password through SQL Developer.”
OK, let’s test the same scenario to see what happens …
Gero again tcpdump:
[root@oracle-srv ~]# tcpdump -XX -i eth0 > tcpdump_3.log tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes ^C57 packets captured 58 packets received by filter 0 packets dropped by kernel
And I change the password:
Let’s see the tcpdump log again:
[root@oracle-srv ~]# vi tcpdump_3.log 21:21:44.160053 IP Frankys-MBP.55169 > oracle-srv.localdomain.ncube-lm: Flags [P.], seq 65:181, ack 76, win 4096, options [nop,nop,TS val 1455457433 ecr 32068599], length 116 0x0000: 0800 270f d012 80e6 5009 af7a 0800 4500 ..'.....P..z..E. 0x0010: 00a8 04b6 0000 4006 d626 c0a8 0f0e c0a8 ......@..&...... 0x0020: 0f15 d781 05f1 e264 3434 2dc7 5144 8018 .......d44-.QD.. 0x0030: 1000 1f4b 0000 0101 080a 56c0 8499 01e9 ...K......V..... 0x0040: 53f7 0000 0074 0600 0000 0000 035e 3b02 S....t.......^;. 0x0050: 8021 0001 012a 0101 0d00 0000 0004 7fff .!...*.......... 0x0060: ffff 0000 0000 0000 0000 0000 0001 0000 ................ 0x0070: 0000 0000 0000 0000 0000 0061 6c74 6572 ...........alter 0x0080: 2075 7365 7220 6323 2366 7261 6e6b 7920 .user.c##franky. 0x0090: 6964 656e 7469 6669 6564 2062 7920 6f72 identified.by.or 0x00a0: 3463 756c 3001 0101 0100 0000 0000 0000 4cul0........... 0x00b0: 0280 0000 0000 ......
For now it’s the same as in SQL * Plus.
Changing the password via SQL Developer in an insecure way (2)
Again generate a tcpdump:
[root@oracle-srv ~]# tcpdump -XX -i eth0 > tcpdump_4.log tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes ^C121 packets captured 122 packets received by filter 0 packets dropped by kernel
