Reset mysql root password

If you have forgotten the root password to a mysql database, it is easy to reset as long as you have permission to kill the existing running mysql instance and restart it directly.

So the first thing is to find the running mysql.  There are multiple ways the instance could be started (e.g. launched automatically from /etc/init.d, or launched manually by someone keying in mysqld_safe –user=mysql), and it may or may not have a .pid file — and even if it had a .pid file, it may be named differently or placed in different locations.  Looking for it from the processes list is the simplest way.

ps -ef |grep -F "mysql"

Use the command above to find the PID of the running mysql, and kill it with -9

kill -9 PID

Launch mysql with the –skip-grant-tables option.  It is recommended to also use –skip-networking so while you are resetting the password, it won’t let anyone else remotely connect to it.

cd /MYSQL/INSTALLATION/DIR
bin/mysqld_safe --skip-grant-tables --skip-networking

log into mysql as root, and this time, it will not ask you for password (how nice!)

mysql -u root

After you are in, reset the root password and exit as follows:

MYSQL> USE mysql;
MYSQL> UPDATE user SET Password=PASSWORD("newAndMemorablePassword") WHERE User="root";
MYSQL> FLUSH PRIVILEGES;
MYSQL> EXIT;

Follow the instructions above to locate the mysql process, and kill it again.

Then finally, start mysql the normal way. If you are using the mysql that was installed using the package management tool that came with your linux distro, you may do any of the following:

cd /etc/init.d
sudo mysql start

OR

sudo /sbin/service mysql start

OR sometimes, service is placed in a different directory

sudo /usr/sbin/service mysql start

However, if you installed mysql without using any package management tools, you probably have to start it manually:

cd /MYSQL/INSTALLATION/DIR
sudo bin/mysqld_safe --user=mysql &

Note: according to mysql’s own instructions, there is supposed to be a safer and preferred way to reset the password, which is to create an init file and launch mysqld_safe by using the –init-file option, and the content of the init file is the “UPDATE…” and “FLUSH…” statements listed above. But this method didn’t seem to be working for me.

Step by step instructions on self-signed certificate and Tomcat over SSL

Creating a self-signed certificate to test Tomcat https is easy, and this article gives you the step-by-step instructions on the following parts:

1. Create a self-signed host certificate using openSSL

2. Create a PKCS12 keystore and convert it to java keystore

3. Configure Tomcat 6 to use https, and redirect http to https

4. Create a Java client to talk to the Tomcat over SSL with the self-signed certificates

Part 1.  Create a self-signed host certificate using openSSL

There are different ways of creating a self-signed certificate, such as using Java keytool.  But I prefer openSSL because the keys and certificates generated this way are more standardized and can be used for other purposes.  The openSSL HOWTO page gives you a lot of details and other information.

1.1 Create a pair of PKI keys

PKI stands for Public Key Infrastructure, which is also known as Asymmetric key pair, where you have a private key and a public key.  The private key is a secret you guard with your honor and life, and the public key is something you give out freely.  Messages encrypted with one can be decrypted with the other.  While generally speaking, given one key, it should be infeasible to derive the other.  However, openSSL makes it so that given a private key, you can easily derive the public key (but not vice versa, otherwise the security is broken).  For this reason, when you generate a key using openSSL, it only gives you a private key.

As a side note, the word asymmetric is really a poor choice.  Once, a security expert was giving a presentation to a roomful of students on PKI, and one of his slides was supposed to have the title “Asymmetric key scheme”, but perhaps it was the fonts he used, or perhaps he made a last-minute typo,  it looked like there was a space between the letter “A” and the rest of the letter.  After that presentation, quite a few naive students began to think that  PKI is a symmetric (WRONG!) key scheme where it should be exactly the opposite — this is probably a less forgivable mistake than blowing up the chemistry lab because someone thinks inflammable means not flammable.

1.1.1 Create a host private key using openSSL


openssl genrsa -out HOSTNAME-private.pem 2048

This private key is 2048 bits long, generated using RSA algorithm, and we choose not to protect it with an additional passphrase because the key will be used with a server certificate.  The name of the private key is HOSTNAME-private.pem where HOSTNAME should be replaced by the name of the machine you intend to host Tomcat.

1.1.2 Derive the public key using openSSL.  This step is not necessary, unless  you want to distribute the public key to others.


openssl rsa -in HOSTNAME-private.pem -pubout  > HOSTNAME-public.pem

1.2 Create a self-signed X509 certificate

openssl req -new -x509 -key HOSTNAME-private.pem -out HOSTNAME-certificate.pem -days 365

Then you will be prompted to enter a few pieces of information, use “.” if you wish to leave the field blank


-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:Indiana
Locality Name (eg, city) []:Bloomington
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Cool Org
Organizational Unit Name (eg, section) []:Cool IT
Common Name (eg, YOUR name) []:Cool Node
Email Address []:.

You will now see your host certificate file HOSTNAME-certificate.pem

UPDATE: The field Common Name is quite important here.  It is the hostname of the machine you are trying to certify with the certificate, which is the name in the DNS entry corresponding to your machine IP.

If your machine does not have a valid DNS entry (in other words, doing a nslookup on the IP of your machine doesn’t give you anything), the host certificate probably won’t work too well for you.  If you are only doing some very minimalistic https connection using only the HttpsURLConnection provided by Java, you can probably get by by disabling the certificate validation as outline towards the end of this article; however, if you use other third-party software packages, you will probably get an exception look like the following:


java.io.IOException: HTTPS hostname wrong:  should be <xxx.yyy.zzz>

This is because many security packages would check for things such as URL Spoofing, and when they do a reverse lookup of the machine IP,  but do not yield the same hostname as what is in the certificate, they think something is fishy and throws the exception.

Part 2. Create a PKCS12 keystore and convert it to a Java keystore

Java keytool does not allow the direct import of x509 certificates with an existing private key, and here is a Java import key utility Agent Bob created to get around that.  However, we can still get it to work even without this utility.  The trick is to import the certificate into a PKCS12 keystore, which Java keytool also supports, and then convert it to the Java keystore format

2.1 Create a PKCS12 keystore and import (or export depending on how you look at it) the host certificate we just created


openssl pkcs12 -export -out keystore.pkcs12 -in HOSTNAME-certificate.pem -inkey HOSTNAME-private.pem

It will ask you for the export password, and it is recommended to provide a password.

2.2 Convert the PKCS12 keystore to Java keystore using Java keytool.


keytool -importkeystore -srckeystore keystore.pkcs12 -srcstoretype PKCS12 -destkeystore keystore.jks -deststoretype JKS

Keytool will first ask you for the new password for the JKS keystore twice, and it will also ask you for the password you set for the PKCS12 keystore created earlier.


Enter destination keystore password:
Re-enter new password:
Enter source keystore password:
Entry for alias 1 successfully imported.
Import command completed: 1 entries successfully imported, 0 entries failed or cancelled

It will output the number of entries successfully imported, failed, and cancelled.  If nothing went wrong, you should have another keystore file: keystore.jks

Part 3. Configure Tomcat to use HTTPS

With the keystore in place, we can now configure Tomcat to communicate via SSL using the certificate.

3.1 Configure Tomcat HTTPS Connector.

Edit CATALINA_HOME/conf/server.xml, where CATALINA_HOME is the base directory of Tomcat.  By default, the HTTPS Connector configuration is commented out.  We can search for “8443” which is the default port number for HTTPS connector, and then either replace the configuration block, or add another block just below.  We are going to use the Coyote blocking connector:


<!--
 <Connector port="8443" protocol="HTTP/1.1" SSLEnabled="true"
 maxThreads="150" scheme="https" secure="true"
 clientAuth="false" sslProtocol="TLS" />
 -->

<Connector port="8443" protocol="org.apache.coyote.http11.Http11Protocol" SSLEnabled="true" maxThreads="150" secure="true" scheme="https" keystoreFile="PATH/TO/keystore.jks" keystorePass="JKS_KEYSTORE_PASSWORD" clientAuth="false" sslProtocol="TLS" />

In the snippet above, PATH/TO/keystore.jks is the path to the Java Keystore we created earlier, and I recommend using the absolute path to eliminate any confusion.  Also provide the keystore password – it is in plain text, so protect server.xml using the correct permission (700).

The Tomcat SSL configuration instruction is a bit misleading and may let us believe both blocking and non-blocking should be configured.  This is not true because the port number can only be used by one connector type.

This configuration enables Tomcat to communicate HTTPS on port 8443.  At this point, it is a good idea to fire up Tomcat and make sure the configuration works using a web browser.

cd CATALINA_HOME
bin/startup.sh

And point your web browser to https://HOSTNAME:8443 to see if Tomcat’s front page shows up.  Since we are using a self-signed certificate, your browser may complain about the certificate being not secure.  Accept the certificate so your browser can display the page.

3.2 Configure Tomcat to redirect HTTP to HTTPS

However, so far, Tomcat still supports HTTP (default port is 8443, but it may have been changed in your situation).  It would be desirable to automatically redirect any requests to the HTTP over to the HTTPS.  The first thing to do is edit CATALINA_HOME/conf/server.xml again, and this time, locate the Connector configuration for HTTP, and modify it so that the “redirectPort” attribute points to the HTTPS port (8443 by default).


<Connector port="8080" protocol="HTTP/1.1"
 connectionTimeout="20000"
 redirectPort="8443" />

Now save server.xml, and edit web.xml, and add the following block to the end of the file, just before the </web-app> tag (in other words, the security-constraint section must be added AFTER the servlet-mapping sections:


<web-app ...>

...

  <security-constraint>
    <web-resource-collection>
      <web-resource-name>All Apps</web-resource-name>
      <url-pattern>/*</url-pattern>
    </web-resource-collection>
    <user-data-constraint>
      <transport-guarantee>CONFIDENTIAL</transport-guarantee>
    </user-data-constraint>
  </security-constraint>
</web-app>

Save this file, restart Tomcat again. This time, open a browser and enter the URL to the normal HTTP port, and see if Tomcat redirects to the HTTPS port.

Part 4. Create a test Java client to talk to Tomcat over SSL

Since we created our own self-signed certificate, if we just use a Java HttpsURLConnection client trying to connect to the Tomcat over SSL, it will not honor the certificate and throw an exception like the following:


Exception in thread "main" javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
 at com.sun.net.ssl.internal.ssl.Alerts.getSSLException(Alerts.java:174)
 at com.sun.net.ssl.internal.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1731)
 at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:241)
 at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:235)
 at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1206)
 at com.sun.net.ssl.internal.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:136)
 at com.sun.net.ssl.internal.ssl.Handshaker.processLoop(Handshaker.java:593)
 at com.sun.net.ssl.internal.ssl.Handshaker.process_record(Handshaker.java:529)
 at com.sun.net.ssl.internal.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:925)
 at com.sun.net.ssl.internal.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1170)
 at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1197)
 at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1181)
 at sun.net.www.protocol.https.HttpsClient.afterConnect(HttpsClient.java:434)
 at sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDelegateHttpsURLConnection.java:166)
 at sun.net.www.protocol.https.HttpsURLConnectionImpl.connect(HttpsURLConnectionImpl.java:133)
 at test.SClient.main(SClient.java:97)
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
 at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:323)
 at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:217)
 at sun.security.validator.Validator.validate(Validator.java:218)
 at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:126)
 at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:209)
 at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:249)
 at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1185)
 ... 11 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
 at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:174)
 at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:238)
 at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:318)
 ... 17 more

To circumvent this problem, we should disable to the certificate validation in the client so we can move forward with the testing.  Add the following block of code in your Java client before creating any HttpsURLConnection (credits of this block of code goes to http://www.exampledepot.com/egs/javax.net.ssl/trustall.html:

//////////////////////////////////////////////////////////////////////////////////////
// this block of code turns off the certificate validation so the client can talk to an SSL
// server that uses a self-signed certificate
//
// !!!! WARNING make sure NOT to do this against a production site
//
// this block of code owes thanks to http://www.exampledepot.com/egs/javax.net.ssl/trustall.html
//

TrustManager[] trustAllCerts = new TrustManager[] {
    new X509TrustManager() {
        public java.security.cert.X509Certificate[] getAcceptedIssuers() {
            return null;
        }

        public void checkClientTrusted(java.security.cert.X509Certificate[] certs, String authType){}

        public void checkServerTrusted(java.security.cert.X509Certificate[] certs, String authType){}
    }
};

SSLContext sslContext = SSLContext.getInstance("SSL");
sslContext.init(null, trustAllCerts, new java.security.SecureRandom());
HttpsURLConnection.setDefaultSSLSocketFactory(sslContext.getSocketFactory());

//
//
// end of block of code that turns off certificate validation
// ////////////////////////////////////////////////////////////////////////////////////

Virtualbox crashes on host memory too low – this time it IS the third party software to blame

Oracle Virtualbox is a great and convenient VM hosting application under GPLv2, and I use it to run Linux on my Windows laptop.  However, a while back, I couldn’t get it to run for more than 5 minutes — for no apparent reason at all, it would pop out an error message saying host memory too low, and the guest Linux would just freeze, and the only thing to do would be powering off the VM.  But as soon as you boot it up, it would happen again.

Several other people encountered the same problem according to the virtualbox bug tracker.  A similar bug report was filed a few years back and the problem was fixed, besides it was a few versions back and people were running Virtualbox on typical 32-bit Windows XP machines with 2GB of memory.  But things are quite different now since most computers are 64-bit equipped with 6~8GB of memory, and there is no reason the host memory should run low (if it could work on a 32-bit host with 2GB of memory, why would it not work on a 64-bit host with 6GB of memory when the guest OS is 32-bit?).

A couple of folks suggested that changing the BIOS settings to enable hardware virtualization would solve the problem, but it has already been set on mine.  Finally, one comment pinpointed the cause – it was Google Crash Handler.  During the course of updating things, I must have enabled “reporting anonymous usage stats” in Chrome.  As soon as I disabled it, VirtualBox start to function again.

So, just as I said earlier, let’s keep blaming the 3rd party software!

The mystery of Windows Experience Index fail to assess disk performance

I have posted this before on another blog space, but decide to repost it here as I intend to use this blog exclusively from now on.

So I bought a new Dell XPS laptop with core i7 running 64-bit Windows 7 almost a year ago.  After getting everything up and running, I couldn’t get Windows Experience Index (WEI) to compute the score and it kept failing at the very last step which was assessing the disk performance, saying something about data being invalid.  Granted this number doesn’t mean much, but still I felt as if something was missing.

To add to the confusion, the disk went bad within just a week (says a lot about the quality of XPS, which is supposed to be a high-end product line).  So at first, it seemed to make sense and I thought no wonder, because the disk was bad.  Then Dell sent out a technician and replaced the disk with a refurbished one (just like buying a new car, as soon as the machine left their warehouse, it started to lose its value and would only deserve refurbished replacement parts), but WEI still would not work.

Searching around a bit online and found a couple of other folks encountered the same problem, but there was no clear explanations nor solutions.  Finally I decided to bite the bullet and sent a help request to Microsoft’s customer support team.  The guy who got back to me immediately started to blame third-party software.  There are three words exactly describing how I felt when I saw his response: predictable, ignorant, and funny.  I guess it was their SOP to immediately pointing the finger at some unspecified “3rd party”, but what he didn’t know was that every time I get a new computer, I wipe out what came with it and do a clean installation using my own OS (a sincere thank to the deal between my university and Microsoft, we get Windows Ultimate edition at an extremely low price), so it was a clean installation of Windows 7 without any other software on it.  Even if it were true, that is, some 3rd party software indeed caused the problem, it didn’t make WEI look any better because it just show how vulnerable WEI can be.

As everything else seemed to be working fine, I decided to let it be since this WEI number really doesn’t mean anything.  Then just a few month ago, I saw another post complaining about this problem, but this time, the gentleman also found the cause.

Just like what I typically do, this gentleman re-installed his own Windows 7, but more importantly, he also moved all User directories to a different partition than C:\, and created symlinks on C:\ to point to the actual directories.  As it turns out, while the symlinks work fine for other applications, it does not work for WEI.  Instead, WEI relies on two environment variables TMP, and TEMP, to dump its I/O data, and by default, both TMP and TEMP point to the temporary directory within the AppData directory under each user.  Since the user directories are relocated to a different partition, and WEI doesn’t honor symlinks, it is unable to find the physical directory pointed to by the env vars and thus the error.  As soon as I reconfigured these two env vars to point to the physical temporary directories instead of through the symlink, WEI started to work.

So yes, keep blaming the 3rd party software.

How to limit certain ports to specific source IPs while keeping all other ports open using iptables

Most online tutorials on iptables suggest to block all ports and DROP all unmatched traffic by default.  While such configuration is most secure and should be done on many home users’ and production hosts, it does not work in our environment.  Many of the machines in our lab are development machines, which means any of us can throw some services or containers on the machine, pick an arbitrary port to host them and access them from somewhere else.  But every once in a while, we feel that the access to certain services should be limited to a few specific source IPs, so we want to block these ports to all traffic except those from trusted nodes, while keeping other ports open so other folks can continue do their work.

Our machines run Redhat Enterprise Linux, and the default iptables rules found in /etc/sysconfig/iptables look like the following:

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -i eth0 -j ACCEPT
...
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

The key here is the line just before the “…”, which says the firewall shall ACCEPT all traffic coming from the network interface eth0, and this is what makes our dev machines quite open.

The line after the “…” and just before “COMMIT” is a secure catch-all that REJECTs all traffic that does not match any rules defined above.  In the case of this particular machine, this line has no effects because the machine has only one network card.  However, if our machine had other network cards (e.g. eth1), then all unmatched traffic coming from these interfaces will be REJECTed.

The difference between DROP and REJECT is that DROP just ignores the request, whereas REJECT sends a message back to the requester.  It is preferably to use DROP to not create extra traffic load to your network.

Now suppose we have a service that listens on TCP ports 65000 and 65443, and we want to limit its access to IPs 100.101.102.103, and 200.201.202.x, where x can be 96~111.  The first thing we want to do is add some extra blank lines before the “ACCEPT all traffic from eth0” line, because all our rules must go before this line into the blank area.

The first two rules are to ACCEPT traffic from these two IP ranges:

...
# RULE 1:  accept requests from my best pal Joe's workstation, besides, he paid me $50 to have access to this service
-A RH-Firewall-1-INPUT -m comment --comment "Joe volunteered to test this service"  -s 100.101.102.103 -p tcp -m multiport --dports 65000,65443 -j ACCEPT

# RULE 2: accept requests from other workstations from our lab.
-A RH-Firewall-1-INPUT -m comment --comment "this service is available to all lab workstations" -s 200.201.202.96/28 -p tcp -m multiport --dports 65000,65443 -j ACCEPT
...

A few things worth noting here. There are two places to add comments. One is the use the typical Linux hash in the rules file itself – this comment is visible only by looking at the file. The other place is using the -m comment extension. This comment will show up if someone runs the command /sbin/iptables -L.

Here we also use the -m multiport extension to combine all ports used by this service in the same rule. Without it, you would have to list each port on its own line using –dport (notice the singular form).

Since RULE 1 involves a specific IP, it is listed after the “-s”.  But in RULE 2 we have a number of IPs.  It happens so the range of the IPs go from 96~111, which in their binary form are 01100000 ~ 01101111, so we can conveniently use a subnet mask of 28 bits to mask out the last 4 bits.   However, if the range of IPs does not fall nicely into a subnet range (which is probably the more common case), you could use the -m iprange exension instead:

# RULE 2 alternative using -m iprange:
-A RH-Firewall-1-INPUT -m comment --comment "this service is available to all lab workstations, including the workstation for our new intern Asok 200.201.202.112" -m --src-range 200.201.202.96-200.201.202.112 -p tcp -m multiport --dports 65000,65443 -j ACCEPT

Now we need to add a rule to DROP all other traffic to these ports. If we don’t add it, the “accepting all traffic on eth0” rule below would still make our service wide open.

# RULE 3: drop all other requests to the service
-A RH-Firewall-1-INPUT -m comment --comment "no one else allowed to use this service" -p tcp -m multiport --dports 65000,65443 -j DROP

Save this file and restart iptables:

> sudo service iptables restart

Below is the complete listing of /etc/sysconfig/iptables

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
...
# RULE 1:  accept requests from my best pal Joe's workstation, besides, he paid me $50 to have access to this service
-A RH-Firewall-1-INPUT -m comment --comment "Joe volunteered to test this service"  -s 100.101.102.103 -p tcp -m multiport --dports 65000,65443 -j ACCEPT
# RULE 2 alternative using -m iprange:
-A RH-Firewall-1-INPUT -m comment --comment "this service is available to all lab workstations, including the workstation for our new intern Asok 200.201.202.112" -m --src-range 200.201.202.96-200.201.202.112 -p tcp -m multiport --dports 65000,65443 -j ACCEPT
# RULE 3: drop all other requests to the service
-A RH-Firewall-1-INPUT -m comment --comment "no one else allowed to use this service" -p tcp -m multiport --dports 65000,65443 -j DROP
...
-A RH-Firewall-1-INPUT -i eth0 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

Reviving this Blog

It dawned on me today that I have this blog space after Microsoft decided to close out their “live spaces” and transferred everything here.  It would be a shame to let it go wasted.  So I have decided to revive this blog and start posting things, meaningful things, on topics such as software development and photography.