Day 5 – Dot NET On Linux – Installing and Securing MySQL

Intro

MySQL is an open source relational database which requires little or no introduction.  MySQL also provides an ADO.NET data provider for .NET and more importantly Mono.

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Installing MySQL

The current latest version is 6 however LMDE repositories server are referencing 5.5 for both the client and server. Install the server with the following command. If you are using a machine with the server installed you don’t need to install the client.

sudo apt-get install mysql-server

During installation you should be prompted to set a root password; if not we can fill this in later when we secure the the installation.

Starting & Stopping

To manually start and stop MySQL service you can run the following commands. It should be started after installation and automatically after reboot.

sudo  service mysql start
sudo service mysql stop

Automatically start upon booting

To start MySQL during boot-up use the following commands:

sudo insserv mysql

To start a MySQL shell use the following command

To start a MySQL prompt you need to log in. If you were not prompted for a root password earlier this will be blank.

/usr/bin/mysql -u root -p Password

Securing MySQL Server

Seeing as we are developing locally we can tie down the access to usage only on localhost.

We  will disable startup on boot, password protect all user accounts, remove guest user access, remove test access and then finally create a new database with a new user who has administrative rights to this database so we don’t have to log in with root.

Disable MySQL Server at BootUp

I don’t like to have this sort of service started automatically on my personal laptop. The service will more than likely be configured to automatically start irrelevant of our instruction to do so above. To remove the auto start use the following command.

sudo insserv -r mysql

You can use the following commands to start and stop the MySQL service when required.

sudo service mysql start
sudo service mysql stop

Securing Root

You should have been promoted to set a root password during installation. If you were not then use the following command to set a root password replacing ‘NEWPD’ in PASSWORD with your required password. This needs to be done inside the MySQL terminal.

UPDATE mysql.user SET Password = PASSWORD('NEWPD') WHERE User = 'root';
FLUSH PRIVILEGES;

Or

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEWPD');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('NEWPD');
SET PASSWORD FOR 'root'@'host_name' = PASSWORD('NEWPD');

Notice how we are simply setting a field in the mysql database table user. The PASSWORD function encrypts your password. If you omit this your password will be in plain text. You can read about MySQL hashing algorithms here.

Securing Anonymous users

Within a MySQL command prompt search the users table for users without password or usernames

select user, host, password from mysql.user;

In my installation I have anonymous users for localhost and my hostname

You can set passwords for these accounts in the same was as above however I would recommend removing the users. You need to replace MyHostName with your host name if you have an entry displayed from the above query; this is displayed in the host column.

DROP USER ''@'localhost';
DROP USER ''@'MyHostName';

Securing Test databases

MySQL comes with a test database already set up. I am going to create my own so I want to remove this.

DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;

Create a new Database

We will now create a database. We will do this before creating our user so that we can add the permissions for the new database to our user upon creation.

create database DotNetOnLinuxExample;
show databases;

Creating a user.

It is not a good idea to use root in any environment so we are going to create our own user. This will be a user with full privileges to the database schema we just created. When we come to run .Net with MySQL access we will create a new user account with read and write access only.

We are going to create a user which is only accessible on the local host. Replace NEWPD in PASSWORD with your required password.

<pre>INSERT INTO mysql.user (User,Host,Password) VALUES('AdminUser','localhost',PASSWORD('NEWPD'));
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON DotNetOnLinuxExample.* to AdminUser@localhost;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'AdminUser'@'localhost';

You can now log on with the following command.

mysql -u AdminUser -p
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s