MySQL on Debian

MySQL is a relational database management system that runs as a server providing multi-user access to a number of databases. It is part of the LAMP software stack, and is used in many high-profile, large-scale web products including Wikipedia, Google and Facebook. The source code is available under GPL, in addition to a variety of proprietary agreements.

Installing MySQL

Let’s go ahead and install the database server with its development libraries and headers

demo@server:~$ sudo aptitude install mysql-server mysql-client libmysqlclient15-dev

As the installation progresses you’ll be prompted to provide a password for the MySQL administrative root user account. This is optional, but we highly recommend setting a password.

Configuration

After the MySQL has been successfully completed, it’s recommended to run mysql_secure_installation, which is a script that helps secure the database server. The script gives you the option to disable root logins from outside localhost, remove anonymous user accounts, and the option to remove the test database. You can also set the MySQL root password here.

demo@server:~$ sudo mysql_secure_installation

That should give you the following

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

By the way, should you forget your MySQL root password, you can set a new one with dpkg

demo@server:~$ sudo dpkg-reconfigure mysql-server-5.0

If you’re curious how you can set the MySQL root password through MySQL alone, here’s how you would achieve that

# stop the database server
demo@server:~$ sudo /etc/init.d/mysql stop

# run the database in safe mode (note the '&')
demo@server:~$ sudo mysqld_safe --skip-grant-tables &

# login to mysql without a password
demo@server:~$ mysql -u root

# switch to the 'mysql' database
USE mysql;

# set a new password for the MySQL root user
UPDATE user SET password=PASSWORD("newpassword") WHERE User='root';

# reload the grant tables with
FLUSH PRIVILEGES;

# logout from the MySQL command-line
\q

# then restart the database server
demo@server:~$ sudo /etc/init.d/mysql restart

Database access

Our database server is freshly installed, but our user hasn’t got access to it quite yet, as can be seen with the following

demo@server:~$ mysql
ERROR 1045 (28000): Access denied for user 'demo'@'localhost' (using password: NO)

The mysql program is the MySQL command-line tool that is a simple SQL shell. It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively – as a filter, for instance – the result is presented in tab-separated format.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can perform. The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with correct privileges.

MySQL access control works in the following manner; (1) the server decides to accept or reject the connection based on the database user name, and whether the user can verify his identity by supplying the correct password. (2) Assuming a connection was established, the server checks each statement the user issue to determine if said user have sufficient privileges to perform them.

Other uses of the privilege system includes handling administrative operations and anonymous users, as well as granting privileges to MySQL-specific functions (such as LOAD DATA INFILE.

The interface to the MySQL privilege system consists of SQL Account Management Statements. The statements included are CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE and SET PASSWORD.

Note that if you modify the grant table with account management statements, the table is immediately reloaded. If you update the table directly, however, with INSERT, UPDATE, or DELETE, you must manually reload the privileges table with FLUSH PRIVILEGES.

The MySQL server stores privilege information in a database named mysql. When the MySQL server starts, it reads the contents of mysql database into memory. Then it base access decisions on the in-memory copies of the grant tables.

By the way, even though database users are completely separate from operating system users, it might be convenient to give them identical names. The reason is that many applications assume the name of the current operating system user by default. Running the mysql command without a database user name, makes the program assume that the database user name is equal to the operating system user name trying to connect.

Creating users

During the MySQL installation process, a database user account named root were created for us in order to bootstrap the database system. This user is a MySQL superuser, and so has full control over the database server and the objects stored in it. We’ll use it to create our database user, and setup privileges.

To create a new database user we need to connect to the database with our administrative root user. Logging in to the database server is done with the mysql command. The command takes several options, the most used form is probably mysql -u username -p db_name. It tells the database server that you want to log in with a user name using a password, and connect to the database db_name.

Log in to the database server with the root account, and provide your password

demo@server:~$ mysql -u root -p

After you enter the root password, you should see something close to

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.0.51a-24+lenny3 (Debian)

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

mysql>

Now that we’re logged on as the administrative user, we can create a new user

# change 'password' to a password of your choosing

mysql> CREATE USER 'demo'@'localhost' IDENTIFIED BY 'password';

Then reload the grants table so that the new privileges take effect

mysql> FLUSH PRIVILEGES;

The demo user can’t currently do anything because permissions are not configured yet. We’ll address that in the next section.

User privileges

It is a good idea to divide users into different categories depending on privileges needed to perform their tasks. The general rule of thumb is to never grant users more access than what they need. With that in mind, we can loosely define three user groups.

Database maintenance users (1) are users that can handle the routine management of the databases and users, but isn’t MySQL administrative users. This approach avoids the dangers of operating with full database administrator rights for tasks that don’t really require it.

A good candidate for a database maintenance user would be your main account. This user group would typically need

mysql> GRANT CREATE, GRANT OPTION, ALTER, DELETE, INDEX, INSERT, 
    -> SELECT, UPDATE, CREATE VIEW, SHOW VIEW, CREATE USER,
    -> SHOW DATABASES ON * . * TO 'demo'@'localhost';

This roughly translates to “give the user the following privileges after the GRANT statement on all databases.” The ON * . * means all databases.

Then reload the grants table so that the new privileges take effect

mysql> FLUSH PRIVILEGES;

In more detail, the maintenance user has the ability on all databases to, among other things:

How much privileges the maintenance user should have will vary on your needs and preferences. You should adjust accordingly. For instance, with the above setup the user isn’t able to delete users, because the DROP USER statement was explicitly left out.

Moving along with our groups, normal users (2) are users that have enough privileges that makes sense for regular use, including features that are used occasionally, but limited to specific databases.

Since the user is restricted to perform actions on databases he has created, we need to bootstrap the user privileges by creating an initial database, then apply the correct privileges.

Create the database newdb with our root or maintenance user

mysql> CREATE DATABASE newdb;

Then grant access to the newdb database to the normal user with

mysql> GRANT SELECT, INSERT, ALTER, UPDATE, CREATE, DELETE, INDEX,
    -> CREATE VIEW, SHOW VIEW ON `newdb` . * TO 'demo'@'localhost';

Being able to list all databases is very convenient. It means we don’t have to log in a user with more privileges every time we forget a database name. The SHOW DATABASES statement is what we’re looking for. And, since we can’t mix global privileges with regular privileges, we need to execute the SHOW DATABASES by itself

mysql> GRANT SHOW DATABASES ON * . * TO 'demo'@'localhost';

The normal user can create new databases, and has the following rights on the newdb database, plus other databases the user creates:

  • retrieve rows from tables in a database (SELECT)
  • insert rows into tables in a database (INSERT)
  • change the structure of or rename tables (ALTER)
  • update table rows (UPDATE)
  • create new tables (CREATE)
  • delete rows from tables in databases (DELETE)
  • create or remove indexes (CREATE INDEX, DROP INDEX)
  • create views (CREATE VIEW)
  • remove views (DROP VIEW)
  • list all database names (SHOW DATABASES)

Finally, we’ve got the project users (3). These users are connected to applications that use a database back-end. They are configured with minimal privileges. That is, they have just enough access to perform program tasks. Furthermore, each application has one dedicated database user.

Say you’ve got a web application using MySQL. In the application you can search

Say you’ve got a simple to do list application that uses MySQL. Then you’ll typically need SELECT (to show items on the list), INSERT (to save new items) and UPDATE (to save changes to existing items) privileges. Maybe the program also allows users to delete their lists and items, which means you’ll need to provide DELETE privileges too.

To get going, you need to create the database, the project user, and specify privileges with your maintenance user.

Create the todolist database

mysql> CREATE DATABASE todolist;
Query OK, 1 row affected (0.00 sec)

Create the project user todo with the password secret

mysql> CREATE USER 'todo'@'localhost' IDENTIFIED BY 'secret';        
Query OK, 0 rows affected (0.00 sec)

Specify privileges

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, INDEX
    -> ON `todolist` . * TO 'todo'@'localhost';

Then reload the grants table so that the new privileges take effect

mysql> FLUSH PRIVILEGES;

On the other hand, if your application only searches for stuff and presents a list of the search query to the user, then you would typically only need SELECT privileges, and as such should only grant SELECT privileges to the project user.

Your mileage will vary. The above user privileges, and user groups are only suggestions to how you may want to set it up. There is also much more you can do with privileges and users than what we’ve been through. You can assign privileges from allowing everything on every database by anyone, to fine tune privileges granting a user only SELECT access on a single column in a single table. The most important thing is to be aware of it, so you can test, tune, and learn.

If you have suggestions on alternate ways of managing user access rights, we would be thrilled to hear about it.

Listing user privileges

Keeping track on all users and their privileges soon become challenging as you add more and more database users. Luckily, MySQL can list all users and their privileges with the SHOW GRANTS statement.

# list all users
mysql> SHOW GRANTS;

# view user "demo@localhost"
mysql> SHOW GRANTS FOR 'demo'@'localhost';

# view grants for the current user
SHOW GRANTS FOR CURRENT_USER();

You can also use SELECT statements on the User table in the “mysql” database, for example

# all with SUPER privileges
mysql> SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

# all with write access to the "newdb" database
mysql> SELECT user, host FROM mysql.db WHERE Db='newdb' AND Select_priv='Y'
    -> UNION SELECT user, host FROM mysql.user WHERE Select_priv='Y';

# all non-local users
mysql> SELECT user, host FROM mysql.user WHERE host != 'localhost';

Creating databases

Deleting users and databases