Access Control#

We've now got a MySQL Server installed, but how do we access it? We can login to the server using a local MySQL client. This client will give us a command prompt just like SQLite did in the previous sections.

We can instantiate the mysql client as such: sudo mysql --defaults-file=/etc/mysql/debian.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Using the --defaults-file flag on the mysql command, we've provided all the details the mysql client needs to connect and login to the MySQL server we have running on server.

Let's review the file /etc/mysql/debian.cnf:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = TJaj9tKlsmsjSJPD
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = TJaj9tKlsmsjSJPD
socket   = /var/run/mysqld/mysqld.sock

This file represents an "options file", and is used to configure the mysql client (but not the server, in this case.) We're using this file, generated for us my the mysql-server package when we installed it, because it contains all the details we need to login to the server.

Let's break down the file a bit. We're primarily interested in the [client] section of the configuration file.

1
2
3
4
5

[client]
host     = localhost
user     = debian-sys-maint
password = TJaj9tKlsmsjSJPD
socket   = /var/run/mysqld/mysqld.sock

We have host, which is the hostname or address of the MySQL Server. This is the network address. By default, MySQL Server is configured to only listen on the loopback device, or localhost. This means only local connections on the same system can connect to it. This is for security reasons. Exposing a service to a (public) network is a risky endeavour and one not to be taken lightly.

The user field is populated with the value debian-sys-maint. When I used to manage MySQL Servers many years ago, the default user was the root user. The root still exists, but by default (at least when installing and using MySQL Server on Ubuntu 20.04) it does not have a password defined and as such, it cannot login to the system. Instead, the debian-sys-maint user "replaces" the root user.

The password field is sort of obvious: it's the plaintext password used to authenticate to MySQL Server as the debian-sys-maint user. This is randomly generated when you install the mysql-server package and is different on every system.

And finally, the socket attribute tells the mysql client where the MySQL Server socket is. A socket is used to make a network connection to a service, except it's presented not as a network socket, but as a file socket. You can safely ignore the deeper meaning of this for the time being, but in short, a file socket is for local connections on the same computer, and network sockets are for connections on remote computers, over a network.

By using this file, we can login to our MySQL Server, but can we use these details to login directly?

Yes we can: mysql -u debian-sys-maint -p

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

$ mysql -u debian-sys-maint -p
Enter password: TJaj9tKlsmsjSJPD
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now we can operate this MySQL Server installation.

Add a new user#

Although we're not going to explore the MySQL Server all that much, let's create a less privileged user we could use to only access a single database. This is better than everyone using the super user account debian-sys-maint.

First, as the debian-sys-maint user, create a new database: create database example;

1
2

mysql> create database example;
Query OK, 1 row affected (0.01 sec)

Now create a new user: create user 'example-user'@'localhost' identified by 'badpassword';

1
2

mysql> create user 'example-user'@'localhost' identified by 'badpassword';
Query OK, 0 rows affected (0.01 sec)

Now we have a user called example-user who can only access the server from the localhost. The @'localhost' is literally saying where the user can connect from. If you want the user to be able to connect from anywhere, you can use @'%', which if you remember the SQL section is a wild card character meaning "all".

Let's logout of debian-sys-maint and then login to the server using the new example-user credentials: mysql -u example-user -p

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

$ mysql -u example-user -p
Enter password: badpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

It works! But can we see any databases or do anything? show databases;

1
2
3
4
5
6
7

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.02 sec)

The information_schema is an internal databases managed by MySQL and can be ignored, making the answer no, we cannot. We want to be able to see our example database: use example;

1
2

mysql> use example;
ERROR 1044 (42000): Access denied for user 'example-user'@'localhost' to database 'example'

Let's fix this. Logout from the example-user account and log back into the server using the debian-sys-maint user:

1
2
3
4
5

mysql> exit;
Bye
$ sudo mysql --defaults-file=/etc/mysql/debian.cnf
...
mysql>

Now grant the example-user account access to the example database: grant all privileges on example.* to 'example-user'@'localhost'; flush privileges;

1
2
3
4

mysql> grant all on example.* to 'example-user'@'localhost'; flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Now logout and back in again using the example-user account, and then see what databases we have: show databases;

1
2
3
4
5
6
7
8

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

And we can now use the database: use example;

1
2

mysql> use example;
Database changed

Which in turn means we can create tables (and other objects) inside the database and run other queries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql> create table test(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| test              |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from test;
Empty set (0.02 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Next#

Now we've covered some very basic access control to the MySQL Server we can see what's possible with a few, simple commands. Let's look at the MySQL Server configuration file next and briefly discuss some of its options.