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.