"SfR Fresh" - the SfR Freeware/Shareware Archive 
As a special service "SfR Fresh" has tried to format the requested source page into HTML format using source code syntax highlighting with prefixed line numbers.
Alternatively you can here view or download the uninterpreted source code file.
That can be also achieved for any archive member file by clicking within an archive contents listing on the first character of the file(path) respectively on the according byte size field.
1 Quick notes:
2 --------------------------------------------
3 [tonu@x153 mysql-4.0]$ cat /etc/my.cnf
4 [mysqld]
5 ssl-ca=SSL/cacert.pem
6 ssl-cert=SSL/server-cert.pem
7 ssl-key=SSL/server-key.pem
8
9 [mysql]
10 ssl-ca=SSL/cacert.pem
11 ssl-cert=SSL/client-cert.pem
12 ssl-key=SSL/client-key.pem
13
14 [mysqldump]
15 ssl-ca=SSL/cacert.pem
16 ssl-cert=SSL/client-cert.pem
17 ssl-key=SSL/client-key.pem
18
19 [tonu@x153 mysql-4.0]$
20 --------------------------------------------
21 To remove passwords from keyfiles:
22 [tonu@x153 SSL]$ openssl rsa -inform pem < server-req.pem > server-key.pem
23 read RSA key
24 Enter PEM pass phrase:
25 writing RSA key
26 [tonu@x153 SSL]$
27 --------------------------------------------
28 To run server:
29
30 sql/mysqld --ssl-ca=SSL/cacert.pem --ssl-cert=SSL/server-cert.pem --ssl-key=SSL/server-key.pem --skip-grant --debug='d:t:O,-' > /tmp/mysqld.trace
31 --------------------------------------------
32 To run client:
33
34 client/mysql --ssl-ca=SSL/cacert.pem --ssl-cert=SSL/server-cert.pem --ssl-key=SSL/server-key.pem --debug='d:t:O,/tmp/client.trace' -h 127.0.0.1
35 --------------------------------------------
36 openssl s_client -host 127.0.0.1 -port 1111 -debug -verify 1 -cert ../SSL/client-cert.pem -key ../SSL/client-key.pem -CAfile ../SSL/cacert.pem -pause -showcerts -state
37
38 --------------------------------------------
39 openssl s_server -port 1111 -cert ../SSL/server-cert.pem -key ../SSL/server-key.pem
40
41
42
43
44 --------------------------------------------
45
46 CA stuff:
47
48 [tonu@x153 bin]$ pwd
49 /usr/local/ssl/bin
50 [tonu@x153 bin]$
51 [tonu@x153 bin]$ ./CA.sh
52 [tonu@x153 bin]$ ./CA.sh -h
53 usage: CA -newcert|-newreq|-newca|-sign|-verify
54 [tonu@x153 bin]$
55 [root@x153 bin]# ./CA.sh -newca
56 CA certificate filename (or enter to create)
57
58 Making CA certificate ...
59 Using configuration from /usr/lib/ssl/openssl.cnf
60 Generating a 1024 bit RSA private key
61 .++++++
62 ................++++++
63 writing new private key to './demoCA/private/./cakey.pem'
64 Enter PEM pass phrase:
65 Verifying password - Enter PEM pass phrase:
66 phrase is too short, needs to be at least 4 chars
67 Enter PEM pass phrase:
68 Verifying password - Enter PEM pass phrase:
69 -----
70 You are about to be asked to enter information that will be incorporated
71 into your certificate request.
72 What you are about to enter is what is called a Distinguished Name or a DN.
73 There are quite a few fields but you can leave some blank
74 For some fields there will be a default value,
75 If you enter '.', the field will be left blank.
76 -----
77 ountry Name (2 letter code) [AU]:FI
78 State or Province Name (full name) [Some-State]:
79 Locality Name (eg, city) []:Helsinki
80 Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL Finland AB
81 Organizational Unit Name (eg, section) []:
82 Common Name (eg, YOUR name) []:Tonu Samuel
83 Email Address []:tonu@mysql.com
84 [root@x153 bin]#
85 [root@x153 bin]# ls -la demoCA/
86 total 13
87 drwxr-xr-x 6 root root 232 Jun 24 18:50 ./
88 drwxr-xr-x 3 root root 2136 Jun 24 18:41 ../
89 -rw-r--r-- 1 root root 1241 Jun 24 18:50 cacert.pem
90 drwxr-xr-x 2 root root 48 Jun 24 18:41 certs/
91 drwxr-xr-x 2 root root 48 Jun 24 18:41 crl/
92 -rw-r--r-- 1 root root 0 Jun 24 18:44 index.txt
93 drwxr-xr-x 2 root root 48 Jun 24 18:41 newcerts/
94 drwxr-xr-x 2 root root 80 Jun 24 18:44 private/
95 -rw-r--r-- 1 root root 3 Jun 24 18:44 serial
96 [root@x153 bin]#
97 [root@x153 bin]# ls -la demoCA/private/
98 total 5
99 drwxr-xr-x 2 root root 80 Jun 24 18:44 ./
100 drwxr-xr-x 6 root root 232 Jun 24 18:50 ../
101 -rw-r--r-- 1 root root 963 Jun 24 18:50 cakey.pem
102 [root@x153 bin]#
103 [root@x153 bin]# ./CA.sh -newreq
104 Using configuration from /usr/lib/ssl/openssl.cnf
105 Generating a 1024 bit RSA private key
106 ..................++++++
107 ........................++++++
108 writing new private key to 'newreq.pem'
109 Enter PEM pass phrase: <- new key password, not CA
110 Verifying password - Enter PEM pass phrase:
111 -----
112 You are about to be asked to enter information that will be incorporated
113 into your certificate request.
114 What you are about to enter is what is called a Distinguished Name or a DN.
115 There are quite a few fields but you can leave some blank
116 For some fields there will be a default value,
117 If you enter '.', the field will be left blank.
118 -----
119 Country Name (2 letter code) [AU]:EE
120 State or Province Name (full name) [Some-State]:
121 Locality Name (eg, city) []:Tallinn
122 Organization Name (eg, company) [Internet Widgits Pty Ltd]:Noname
123 Organizational Unit Name (eg, section) []:
124 Common Name (eg, YOUR name) []:Mr Noname
125 Email Address []:a@b.c
126
127 Please enter the following 'extra' attributes
128 to be sent with your certificate request
129 A challenge password []:
130 An optional company name []:
131 Request (and private key) is in newreq.pem
132 [root@x153 bin]#
133 [root@x153 bin]# ls -la newreq.pem
134 -rw-r--r-- 1 root root 1623 Jun 24 18:54 newreq.pem
135 [root@x153 bin]#
136 [root@x153 bin]# ./CA.sh -sign
137 Using configuration from /usr/lib/ssl/openssl.cnf
138 Enter PEM pass phrase: <- CA's one!
139 Check that the request matches the signature
140 Signature ok
141 The Subjects Distinguished Name is as follows
142 countryName :PRINTABLE:'EE'
143 stateOrProvinceName :PRINTABLE:'Some-State'
144 localityName :PRINTABLE:'Tallinn'
145 organizationName :PRINTABLE:'Noname'
146 commonName :PRINTABLE:'Mr Noname'
147 emailAddress :IA5STRING:'a@b.c'
148 Certificate is to be certified until Jun 24 15:50:23 2002 GMT (365 days)
149 Sign the certificate? [y/n]:y
150
151
152 1 out of 1 certificate requests certified, commit? [y/n]y
153 Write out database with 1 new entries
154 Data Base Updated
155 Certificate:
156 Data:
157 Version: 3 (0x2)
158 Serial Number: 1 (0x1)
159 Signature Algorithm: md5WithRSAEncryption
160 Issuer: C=FI, ST=Some-State, L=Helsinki, O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com
161 Validity
162 Not Before: Jun 24 15:50:23 2001 GMT
163 Not After : Jun 24 15:50:23 2002 GMT
164 Subject: C=EE, ST=Some-State, L=Tallinn, O=Noname, CN=Mr Noname/Email=a@b.c
165 Subject Public Key Info:
166 Public Key Algorithm: rsaEncryption
167 RSA Public Key: (1024 bit)
168 Modulus (1024 bit):
169 00:ab:3b:7d:5b:6c:93:f6:46:1a:2c:46:73:6f:89:
170 8a:99:bb:e9:6b:94:0d:74:aa:aa:c4:5c:a2:61:cf:
171 56:bb:a1:a9:5a:37:c4:4e:b2:ec:5c:18:3a:a4:8d:
172 af:3d:23:66:7c:85:7f:d1:f2:e3:fc:16:a7:4c:a2:
173 d6:45:06:92:75:d8:a2:3b:f9:aa:77:da:26:b9:87:
174 e0:df:50:54:e4:36:9f:35:87:39:8e:a6:7c:3e:a8:
175 e4:49:1a:76:c2:6f:73:0b:22:93:2a:04:67:0d:7d:
176 ae:34:5c:fe:7c:29:b8:a2:fe:1e:ef:d1:0c:4d:dd:
177 5b:7a:67:b0:0a:22:88:a0:af
178 Exponent: 65537 (0x10001)
179 X509v3 extensions:
180 X509v3 Basic Constraints:
181 CA:FALSE
182 Netscape Comment:
183 OpenSSL Generated Certificate
184 X509v3 Subject Key Identifier:
185 83:D1:0D:52:0F:DE:61:2D:A6:10:20:B8:46:0C:77:D5:D2:D0:BE:20
186 X509v3 Authority Key Identifier:
187 keyid:A5:0A:D6:72:B5:DF:E4:C2:2B:7B:07:5E:D3:4D:52:07:E1:83:6B:7F
188 DirName:/C=FI/ST=Some-State/L=Helsinki/O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@mysql.com
189 serial:00
190
191 Signature Algorithm: md5WithRSAEncryption
192 60:85:f7:d0:54:2a:67:88:0e:37:a6:a8:8e:fd:a0:c9:a1:d7:
193 c6:fc:4c:2e:59:8d:88:6d:69:0a:b8:b2:67:5f:81:94:39:0e:
194 ab:67:fc:8b:62:de:85:f6:b3:8c:2d:1a:e3:dc:28:fc:f5:99:
195 39:f0:3d:50:ca:88:c0:8e:f8:c2:02:5d:34:19:63:9f:c4:a2:
196 f6:a8:81:c9:8d:6d:bd:c4:42:4a:0c:49:5a:cc:24:ea:65:80:
197 dd:79:20:89:9e:ea:6b:80:7a:86:f9:bb:6d:24:3c:80:13:5b:
198 e6:16:fc:3d:8d:f6:16:ea:33:25:c6:90:20:81:a4:b0:15:2e:
199 9c:1c
200 -----BEGIN CERTIFICATE-----
201 MIIDfjCCAuegAwIBAgIBATANBgkqhkiG9w0BAQQFADCBhTELMAkGA1UEBhMCRkkx
202 EzARBgNVBAgTClNvbWUtU3RhdGUxETAPBgNVBAcTCEhlbHNpbmtpMRkwFwYDVQQK
203 ExBNeVNRTCBGaW5sYW5kIEFCMRQwEgYDVQQDEwtUb251IFNhbXVlbDEdMBsGCSqG
204 SIb3DQEJARYOdG9udUBteXNxbC5jb20wHhcNMDEwNjI0MTU1MDIzWhcNMDIwNjI0
205 MTU1MDIzWjBvMQswCQYDVQQGEwJFRTETMBEGA1UECBMKU29tZS1TdGF0ZTEQMA4G
206 A1UEBxMHVGFsbGlubjEPMA0GA1UEChMGTm9uYW1lMRIwEAYDVQQDEwlNciBOb25h
207 bWUxFDASBgkqhkiG9w0BCQEWBWFAYi5jMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCB
208 iQKBgQCrO31bbJP2RhosRnNviYqZu+lrlA10qqrEXKJhz1a7oalaN8ROsuxcGDqk
209 ja89I2Z8hX/R8uP8FqdMotZFBpJ12KI7+ap32ia5h+DfUFTkNp81hzmOpnw+qORJ
210 GnbCb3MLIpMqBGcNfa40XP58Kbii/h7v0QxN3Vt6Z7AKIoigrwIDAQABo4IBETCC
211 AQ0wCQYDVR0TBAIwADAsBglghkgBhvhCAQ0EHxYdT3BlblNTTCBHZW5lcmF0ZWQg
212 Q2VydGlmaWNhdGUwHQYDVR0OBBYEFIPRDVIP3mEtphAguEYMd9XS0L4gMIGyBgNV
213 HSMEgaowgaeAFKUK1nK13+TCK3sHXtNNUgfhg2t/oYGLpIGIMIGFMQswCQYDVQQG
214 EwJGSTETMBEGA1UECBMKU29tZS1TdGF0ZTERMA8GA1UEBxMISGVsc2lua2kxGTAX
215 BgNVBAoTEE15U1FMIEZpbmxhbmQgQUIxFDASBgNVBAMTC1RvbnUgU2FtdWVsMR0w
216 GwYJKoZIhvcNAQkBFg50b251QG15c3FsLmNvbYIBADANBgkqhkiG9w0BAQQFAAOB
217 gQBghffQVCpniA43pqiO/aDJodfG/EwuWY2IbWkKuLJnX4GUOQ6rZ/yLYt6F9rOM
218 LRrj3Cj89Zk58D1QyojAjvjCAl00GWOfxKL2qIHJjW29xEJKDElazCTqZYDdeSCJ
219 nuprgHqG+bttJDyAE1vmFvw9jfYW6jMlxpAggaSwFS6cHA==
220 -----END CERTIFICATE-----
221 Signed certificate is in newcert.pem
222 [root@x153 bin]# ls -la demoCA/newcerts/
223 total 5
224 drwxr-xr-x 2 root root 72 Jun 24 18:58 ./
225 drwxr-xr-x 6 root root 296 Jun 24 18:58 ../
226 -rw-r--r-- 1 root root 3533 Jun 24 18:58 01.pem
227 [root@x153 bin]#
228 [root@x153 mysql-4.0]# ./sql/mysqld --ssl-cert=SSL/server-cert.pem --ssl-ca=SSL/cacert.pem --ssl-ke
229 y=SSL/server-req.pem -L /home/tonu/mysql-4.0/sql/share/english/ -u root
230 Enter PEM pass phrase:
231 ./sql/mysqld: ready for connections
232 [tonu@x153 mysql-4.0]$ client/mysql --ssl-key=SSL/client-req.pem --ssl-ca=SSL/cacert.pem --ssl-cert
233 =SSL/client-cert.pem
234 Enter PEM pass phrase:
235 ERROR:
236
237 [tonu@x153 mysql-4.0]$
238
239
240
241
242 -8<------------------------
243 SSL encrypts data between MySQL server and client.
244
245 You need openssl (formerly SSLeay) for MySQL SSL support. Development
246 and testing was done on openssl version 0.9.3a
247
248 To compile MySQL one must do:
249 ./configure --with-openssl=/usr
250
251 or
252
253 ./configure --with-openssl=yes
254
255 There are sample keys and certificates included with MySQL tarball in
256 directory ./SSL. They are meant to be for quick start and
257 testing only. Using them in production environment means same as not
258 using encryption. This is because private keys are publicly
259 accessible for everyone. You must use openssl distribution for new key
260 and certificate generation for both client and server.
261
262 ----------- for manual: ---------------------
263 *New API calls:*
264
265 mysql_ssl_set() - Set SSL properties (key, certificate,
266 certificates authority certificate). Must be called before
267 mysql_real_connect();
268 mysql_ssl_clear() - Clear and free resources occupied by
269 mysql_ssl_set() API call.
270 char *mysql_ssl_cipher(MYSQL *) - returns cipher in use. For example
271 "DES-CDC3-SHA" means that you have combined triple DES symmetric
272 algorithm and SHA
273 hashing algorithm.
274
275
276 *New command line switches:*
277 --ssl Use SSL for connection (automatically set with
278 other flags. This means one can use encrypted connection without strong
279 cryptological authentication. Normally one must use all switches
280 together including ssl-key, ssl-cert and ssl-ca and never mind about
281 --ssl because this is assumed by defult if any of them (--ssl-...)
282 included.
283 --ssl-key X509 key in PEM format (implies --ssl)
284 --ssl-cert X509 cert in PEM format (implies --ssl)
285 --ssl-ca CA file in PEM format (check OpenSSL docs,
286 implies --ssl)
287 --ssl-capath CA directory (check OpenSSL docs, implies --ssl
288 ----------------
289 This is about using SSL in MySQL privilege system. My idea is to make
290 possible use of x509 certificates and keys instead of MySQL native
291 passwords
292 Some basic theory about crypt, SSL and x509:
293 x509 is standard for certificates. SSL is standard for secure
294 communication. Certificates are issued by someone anyone can trust. This
295 trusted party is called "Certificate Authority" or "CA". This is
296 someone, we MUST trust. Everyone must have some "fingerprint" of CA (so
297 called "CA certificate" or "CA cert") using which one can verify
298 authenticity of other
299 certificates issued by this CA. CA uses his power to give certificates
300 to persons (they can be physical (like "monty") or logical (like some
301 process). Person is identified by "subject" like
302 "/C=EE/ST=Harjumaa/L=Tallinn/O=MySQL client bogus certificate/CN=Tonu
303 Samuel/Email=<EMAIL: PROTECTED>". and signed cryptologically. This sign can be
304 verified using CA-cert. So, if we trust CA, then we can trust identity
305 of user.
306 There can be many CA-s (usually not but who knows). Also there can be
307 some users we don`t trust or have different privileges. This means we
308 must have one table to hold CA-certs and other table to hold so called
309 "subjects" (users). I think it`s a good idea to use existing structure
310 of host/user/db/field and add some x509 relationship. Then we can
311 use usual simple user/host pair or x509 subject/CA pair.
312 So I think user must grant rights using old method GRANT blabla ON
313 blabla TO blabla IDENTIFIED BY blabla
314 or new way:
315 -----------8<---------------------------
316 GRANT blabla ON blabla TO blabla
317 IDENTIFIED BY X509 SUBJECT "/C=EE/ST=Harjumaa/L=Tallinn/O=MySQL client
318 bogus certificate/CN=Tonu Samuel/Email=<EMAIL: PROTECTED>" AND ISSUER
319 "/C=EE/ST=Harjumaa/L=Tallinn/O=TCX AB/CN=Tonu
320 Samuel/Email=<EMAIL: PROTECTED>";
321 -----------8<---------------------------
322 Please note the difference in Subject and Issuer. This command requests
323 user to authenticate itself with exact subject and exact certificate
324 issuer. Next possibility is just have any certificate of some good CA:
325 -----------8<---------------------------
326 GRANT blabla ON blabla TO blabla IDENTIFIED BY X509 ISSUER
327 "/C=EE/ST=Harjumaa/L=Tallinn/O=TCX
328 AB/CN=Tonu Samuel/Email=<EMAIL: PROTECTED>";
329 -----------8<---------------------------
330 or if any registered CA is good enough (usual case when only one CA is
331 registered)
332 but we care about exact user, then something like:
333 -----------8<---------------------------
334 GRANT blabla ON blabla TO blabla IDENTIFIED BY X509 SUBJECT
335 "/C=EE/ST=Harjumaa/L=Tallinn/O=MySQL client
336 bogus certificate/CN=Tonu Samuel/Email=<EMAIL: PROTECTED>";
337 -----------8<---------------------------
338 And case if user must authenticate itself but we don`t care about exact
339 person until he have some certificate issued by CA registered in our
340 system:
341 -----------8<---------------------------
342 GRANT blabla ON blabla TO blabla IDENTIFIED BY X509;
343 -----------8<---------------------------
344 Then additionally we need one exception. Let`s assume we need SSL
345 encryption
346 for preventing eavesdropping but we don`t care who it is at all. We need
347 privilege to exclude all non-SSL users but we accept anyone using SSL.
348 How
349 this must be done in GRANT syntax? Maybe:
350 -----------8<---------------------------
351 GRANT blabla ON blabla TO blabla
352 IDENTIFIED BY blabla AND USING SSL
353 -----------8<---------------------------
354 But maybe we want to add in future possibility to check different
355 algorithms and key lengths? Something like:
356 -----------8<---------------------------
357 GRANT blabla ON blabla TO blabla IDENTIFIED BY blabla AND USING SSL WITH
358 CIPHER "DES-CBC3-SHA" OR "DES-CBC3-MD5"
359 -----------8<---------------------------
360 Also we need some command to include/exclude CA certificates. This must
361 be some commands like INSERT/DELETE/UPDATE/REPLACE to do it.
362 All examples is given for clarify my problem. I asking for help because
363 I don`t know
364 any similar command in other SQL-s.
365 ------------8<------------------------
366
367 So, at moment SSL communications is ready and working. I don`t have this
368 command iterface at moment yet and this can be changed a lot if someone
369 can suggest good idea or reason to change them. We are ready to listen
370 every opinion.
371 About Kerberos: I just don`t know much about it. I have to read this
372 again before I can comment. I never used it itself and forgot most of
373 theory. Sorry. Anyway now the problem/need is known and I will put
374 thinking about this in personal TODO.
375
376