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.

Vista administrator woes

Ok if you're a Vista user you're undoubtely also sick and tired of being bombarded with security pop-ups, which are aimed at keeping the average user from destroying the system with malware.

So you want to be a real administor? Follow these steps:
First, turn off UAC warnings.
1. Go into Control Panel.
2. Open up User Accounts (in classic view) and User Accounts and Family Safety -> User Accounts (in standard view).
3. Click Turn User Account Control on or off and follow the rest of the instructions.

Ok now you got rid of most of the issues, but Vista still with give you a hard time with Services and applications such as SQL Server and Visual Studio. It still won't think you are a full blown administrator and prompt you with the odd warning. Quite annoying.

Finally, Turn off "Run all administrators in Admin Approval Mode"
1. Open a run window, and type in secpol.msc and hit enter.
2. Expans Local Policies -> Security Options.
3. Scroll down to User Account Control: Run all administrators in Admin Approval Mode and disable the damned thing.
4. Restart and all should be well.