MySQL and the joys of security

I've been running my local MySQL server on Gentoo without hassles for a while and recently tried to connect to it from a remote machine. Obviously there was bound to be some issues and here's how I solved them.

When you first try to connect remotely to the machine, you'll be greeted by this lovely message:

# mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.1' (111)

To get this sucker out of the way, edit your /etc/mysql/my.cnf file and comment the following line:

# bind-address = 127.0.0.1

Note to all the security girls: Your server will now be accessible from any other networked machine. Oooohh nooooo!!

Anyhow, now we try to connect again, and whooopa!! Another error:

mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 1130 (00000): Host 'remote_pc_hostname' is not allowed to connect to this MySQL server

Ah bite me. Ok, so you have to grant his Lordship permissions to connect to thy server. In order to do that logon to sql on the sql server itself:

# mysql --user=root --password=some_password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.70-log Gentoo Linux mysql-5.0.70-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'root'@'remote_pc_hostname';
Query OK, 0 rows affected (0.00 sec)

Now all should be well in fairyland, but alas. The code securo freaks over at Sun have presented ye with another trial.

# mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 1045 (28000): Access denied for user 'root'@'remote_pc_hostname' (using password: YES)

Ok I'm taking the fairytale rubbish too far. Here's what you should've done on the sql server in the first place:

# mysql --user=root --password=some_password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.70-log Gentoo Linux mysql-5.0.70-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'root'@'remote_pc_hostname' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)

Sheesh! You little girls are wasting my time. Take a que from MS SQL Server.

Manaully clean your Gentoo world file

I've been in a love-hate relationship with Gentoo for over 3 years now. Luckily there's a lot more love than hate these days. Well 3 years is a long time and it's time to clean this bad boy up...

There's a lot of articles on-line on how to remove cruft and deprecated packages etc. But I struggled to find one that will help you clean up your world file. Being a bit of a quick fix kind of guy, I hammered this little python script together, which will loop through your world file and find the package description for each entry. The script isn't perfect, but it gets the job done for the most part. I still have to manually edit the world file, but at least now I get a description of each entry as I step through the file.

Here's what you need to do:

Log in as root and fire up a text editor:

nano print_world_file_details.py

And paste the following:

import os
import string
import sys

if __name__ == '__main__':
 f_in = open("/var/lib/portage/world")

 lines = f_in.readlines()

 for line in lines:
     index = line.find("/")
     length = len(line) - 1
  
     if(index > 0):
         print ""
         print "--------------------------------------"
         print line[index+1:length]
         print ""
         cmd = "emerge -s " + line[index+1:length] + " | grep -A7 '" + line[:length] + "' "
         os.system(cmd)
      
     sys.stdin.readline()

 f_in.close()

Exit and save (Ctrl+x, Enter). Run the script:

python print_world_file_details.py

Just hit enter after each lookup and manually remove the entry from your world file. Since the script uses grep to filter the ermerge -s results you might get multiple descriptions for some packages. Like dev-java/java-config will list dev-java/java-config-wrapper too. A slight nuisance, but who cares?

Note: It is not considered safe to update your world file manually, so don't mess around if you don't know what you're doing. Please make a backup before editing.

Get JBoss Seam going on Gentoo Linux with Tomcat

I've been given a web app to do for an open source driven organization. I like the ideology behind open source, but I must say that the complexities involved in just getting started, must be the single biggest hurdle in the adoption of OSS.

Most Java and Linux fanboys might rupture a vain for me saying this, but to get any sort of Java web app going is massive pain in butt. To get Asp.Net going, one merely install visual studio and off you go. You don't even need IIS these days. To get a Java web app (dev environment) going you have to install a web server, an application server, a java framework, an IDE, IDE plugins to support the java framework and perhaps additional frameworks / tools which will allow you to work in a "code-behind" fashion. Then you have to decide on a web frameworks and the choices are endless: Spring MVC, Struts, Seam, JSF, Tapestry, Wicket, Hibernate, Stripes, etc. You can Google until you're blue in the face, but every single framework claims to be the best and all have their own following of groupies. To be honest, it is a big geeky mess!

Unfortunately I don't have the luxury of cleaning up this steaming pile of rubbish, so I'll have to get my hands dirty and figure out what the best solution is through trial and error (very scientific).

My client already has Tomcat running, so luckily I don't have to bother about figuring out which application server is the best. A further requirement is that the web framework should be quite lightweight. It seems as if JBoss' Seam framework isn't too much of a brute, so I'll go with that and probably get burned half-way in.

Even though I detest overcomplicated systems, I've grown quite fond of Gentoo. Once you've mastered this beast, it is quite pleasant to work with. (Perhaps I'll have the same feelings towards Java web apps once I've finished here, but for now it's not looking good.) So I'll run through the steps of getting Seam going on Gentoo.

To start off with, we'll need JDK (or JRE). Login as root, emerge sun-jdk (in my case version 1.6.0.07) and logout. After that we need to get Tomcat.

Note: You can try to get Tomcat going through portage, but you'll end up pulling your hair out, because Seam uses the naming-factory-dbcp.jar, which is not included in the build for some bizarre reason.

cd ~
mkdir applications
cd applications
wget http://apache.mirrors.hoobly.com/tomcat/tomcat-6/v6.0.18/bin/apache-tomcat-6.0.18.tar.gz
tar -xf apache-tomcat-6.0.18.tar.gz

Now let's set the environmental variables needed:

nano ~/.bashrc

and add these lines:

export JRE_HOME=/opt/sun-jdk-1.6.0.07
export TOMCAT_HOME=/home/yourname/applications/apache-tomcat-6.0.18
export CATALINA_HOME=$TOMCAT_HOME
export CATALINA_BASE=$TOMCAT_HOME

Note: You might want to emerge the tomcat-native package, since it will add the Apache Tomcat Native library. I'm not sure if Seam needs this, but I noticed Tomcat having a bit of a fit when it couldn't find it. The message in the $TOMCAT_HOME/logs/catalina.out was "INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path"

One can also configure an admin user, but this is not necessarily required. Edit the tomcat-users.xml file like this:

nano $TOMCAT_HOME/conf/tomcat-users.xml

<tomcat-users>
<role rolename="manager">
<user username="root" password="irock" roles="manager">
</user>

One can now start Tomcat:

cd $TOMCAT_HOME/bin
./startup.sh

You should now be able to access Tomcat on http://localhost:8080

Tomcat will display $TOMCAT_HOME/webapps/ROOT/index.html

So we can now follow Seam's Tomcat guide, which I had to modify a bit:

1. Go into the jboss-seam/examples/jpa directory
2. Copy the lib/hsqldb.jar into $TOMCAT_HOME/lib
3. Run ant tomcat6 to build the application
4. Stop Tomcat $TOMCAT_HOME/bin/shutdown.sh
5. Remove previous any exiting deployments: rm -r $TOMCAT_HOME/webapps/jboss-seam-jpa
6. Copy war file: cp dist-tomcat6/jboss-seam-jpa.war $TOMCAT_HOME/webapps
7. Start Tomcat $TOMCAT_HOME/bin/starup.sh
8. Access the app at http://localhost:8080/jboss-seam-jpa

And Bob's you're uncle!

Linux: shutdown: you must be root to do that!

In order to use the console commands shutdown, poweroff, halt or reboot, one has to be a super user. One might see messages such as:

shutdown: you must be root to do that!
poweroff: must be superuser.
reboot: must be superuser.
halt: must be superuser.

In some cases the user can simply be added to the wheel group. This should give the user enough privileges to shut the machine down. A more reliable approach is to make use of Sudo, which was specifically designed to allow ordinary users to execute certain super-user-only commands.

The following section will explain how to setup sudo to allow other users to the machine down through the command line under the Gentoo distro.

Install Sudo
Login as su:

su -

Emerge Sudo:

emerge app-admin/sudo

Configure Sudo
Run visudo:

visudo

Enter the following:

# Runas alias specification
YourUserName  ALL=(root) NOPASSWD: /sbin/reboot
YourUserName  ALL=(root) NOPASSWD: /sbin/halt
YourUserName  ALL=(root) NOPASSWD: /sbin/poweroff
YourUserName  ALL=(root) NOPASSWD: /sbin/shutdown

Replace YourUserName with the user name which requires shutdown privileges.

See this guide on how to create a group which allows all its users to shut the machine down.