Tuesday, April 11, 2006

Tips on MySQL Accounts in Sguil VM

In an otherwise unremarkable book on MySQL, I found good advice on database accounts and authentication.

Here is what the accounts look like in the Sguil VM I just released.

taosecurity:/home/analyst$ mysql -u root -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.18

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

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host, password from user;
+-------+-----------------------------+-------------------------------------------+
| user | host | password |
+-------+-----------------------------+-------------------------------------------+
| root | localhost | *7561F5295A1A35CB8E0A7C46921994D383947FA5 |
| root | taosecurity.taosecurity.com | |
| | taosecurity.taosecurity.com | |
| | localhost | |
| sguil | localhost | *B31EC1E21F3433DC60AF70BEFD8F40A01D6B77E3 |
+-------+-----------------------------+-------------------------------------------+
5 rows in set (0.02 sec)

Hmm. Although I set passwords for the root and sguil users, I only did so involving logins from localhost. There are also two "null" users with no password at all.

Let's fix the root passwords first.

mysql> update user set password = password('r00t') where user = 'root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> select user, host, password from user;
+-------+-----------------------------+-------------------------------------------+
| user | host | password |
+-------+-----------------------------+-------------------------------------------+
| root | localhost | *7561F5295A1A35CB8E0A7C46921994D383947FA5 |
| root | taosecurity.taosecurity.com | *7561F5295A1A35CB8E0A7C46921994D383947FA5 |
| | taosecurity.taosecurity.com | |
| | localhost | |
| sguil | localhost | *B31EC1E21F3433DC60AF70BEFD8F40A01D6B77E3 |
+-------+-----------------------------+-------------------------------------------+
5 rows in set (0.04 sec)

Now I delete the unneeded accounts.

mysql> delete from user where user = '';
Query OK, 2 rows affected (0.02 sec)

mysql> select user, host, password from user;
+-------+-----------------------------+-------------------------------------------+
| user | host | password |
+-------+-----------------------------+-------------------------------------------+
| root | localhost | *7561F5295A1A35CB8E0A7C46921994D383947FA5 |
| root | taosecurity.taosecurity.com | *7561F5295A1A35CB8E0A7C46921994D383947FA5 |
| sguil | localhost | *B31EC1E21F3433DC60AF70BEFD8F40A01D6B77E3 |
+-------+-----------------------------+-------------------------------------------+
3 rows in set (0.01 sec)

I plan to incorporate these changes in future VMs.

I learned one other cool tip from that database book. I can check to see how I am connected to the database using the status command.

mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.18, for portbld-freebsd5.4 (i386) using 4.3

Connection id: 6
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: more
Using outfile: ''
Using delimiter: ;
Server version: 5.0.18
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 22 hours 10 min 25 sec

Threads: 3 Questions: 2991 Slow queries: 0 Opens: 11 Flush tables: 1
Open tables: 45 Queries per second avg: 0.037
--------------

You can see that I am using a Unix socket when connected to localhost.

I can still connect to localhost, but force a TCP connection using the following syntax.

taosecurity:/home/analyst$ mysql -u root -p --protocol=tcp
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.18

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

mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.18, for portbld-freebsd5.4 (i386) using 4.3

Connection id: 7
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: more
Using outfile: ''
Using delimiter: ;
Server version: 5.0.18
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 22 hours 11 min 19 sec

Threads: 3 Questions: 2996 Slow queries: 0 Opens: 0 Flush tables: 1
Open tables: 45 Queries per second avg: 0.038
--------------

That's not earth-shattering, but I thought it was interesting.

No comments: