[Guide] How to properly secure mysql server

[embass]

There are many newbies out there that do not secure their mysql server properly (if even).

By not taking security measures you may likely be hacked.

 

In this guide I will show you how to properly secure your mysql server and lower the risk of intrusion.

 

Step 1: Installing navicat

I'll be using Navicat 9.0. Its (in my opinion) the best mysql administration tool you can get.

You can download it from here.

Select non-commercial (free) version for your operating system and install it.

 

 

Step 2: Setting up navicat

Start the program.

On upper left click the hue button "Connection" -> "Mysql" -> New window will open.

 

Type in your info as shown on picture below:

Press ok.

 

You have now created a connection to mysql server and can connect to it.

On left side you will now see your connection listed.

Doubleclick on it. It will open into a list

 

Step 3: Accessing user management

 

If you do not yet have a database then create one:

To create a database:

- right click on your connection -> select "New database". Enter name and press ok (ignore the rest).

 

Select your database (mine is testdatabase). Right click on it and choose "Manage users..." -> New window will open.

 

We want to AVOID using root access at ALL cost.

It is EXTREMELY stupid to be using root account. If someone gets it's possession he will have access to ENTIRE server.

That is why root should ONLY be used at setting up server.

 

For this purpose we will create a new username called "testuser"

 

 

Step 4: Creating new user

Right click on empty space under existing usernames -> press "Add user" -> New window will open.

 

Some clarification on host entry:

 

If you enter 127.0.0.1 in host, "testuser" will only be able to connect from your pc (localhost).

NO OTHER users can connect to your mysql server.

This is the most secure option.

 

Sometimes you need to connect to mysql server through external connection (other pc).

If you have a static ip, simply enter the ip of that pc. For example. 123.123.123.123.

Once you do that, "testuser" will only be able to connect when he is using ip 123.123.123.123

Still quite secure (unless his pc gets compromised).

 

If you do not have a static ip, but still want to connect, you can use the host of your isp and match out your ip.

Example: Let's say your isp is comcast (USA internet company).

Your host will be something like: comcast.dynamic.5624462346.users.comcast.net

That part 5624462346 is unique for you, but it changes every time.

So, we want to allow comcast.%.users.comcast.net

Replace the numbers/letters you wish to ignore with %.

This is not very secure as it can allow other users to connect to your server (and bruteforce password), but it is still better than allowing everyone to connect.

 

If you wish to allow everyone to connect to your server (very, VERY bad), simply put % in hostname.

I do not suggest doing that, ever. It's stupid.

 

Some clarification on password entry:

Make sure to ALWAYS use secure passwords.

Example of a secure password is this:

54zv$%ZC"$&ubCVZV&Q$N%BV64

 

Something to think about:

A word password like "banana" can be cracked (using dictionary) in a matter of minutes.

A word password using letters and numbers like "banana1" can be crached using dictionary/bruteforce in a matter of minutes or few hours at max.

A word password using BIG/small letters and numbers can be crached using bruteforce/dictionary in a matter of hours/days.

A password using BIG/small letters, numbers and special signs can be bruteforced in:

- 4 char letter "4Kk$" -> hours

- 5 char letter "4Kk$#" -> 12+ hours

- 6 char letter "4Kk$#2" -> Few days

- 7 char letter "4Kk$#h" -> A week, give or take few days

- 8 char letter "4Kk$#hf" -> 12-16, give or take few days

- 9 char letter "4Kk$#hf4" -> Two weeks- a month

- 10 char letter and higher "4Kk$#hf4!" -> Few months

Adding any additional letters would resolve in bruteforcing of around half a year or longer.

 

Note: I normally use passwords such as "4z3v57J%BU&V%/NbV/%N(BV". Never got hacked so I can vouch it works.

 

Step 5: Giving access to user

We can either set privileges for "testuser" for entire server (mysql), for specific database (mysql ->l2java) or for specific table (mysql->l2java->users)

 

Combine them as you see them fit.

Few examples below:

If you wish to allow "testuser" to be able to read all the databases, but not create any:

Click on him from user selection -> Global privileges -> Select -> Click "Save"

This way he will have readonly access for ALL databases.

 

Allowing to read only one database (example: testdatabase):

Doubleclick on "testuser" -> Database list will open. -> Select database you wish to give him readonly access to (example: testdatabase). -> "Database specific privileges" -> Select -> Click "Save".

Voila, now he can only read data from testdatabase.

 

Allowing to read only one TABLE in one database (example: users in l2j):

Doubleclick on "testuser" -> Database list will open -> Doubleclick database you wish to give him readonly access to (example: l2j). -> Select TABLE you wish to give him readonly access (example: characters" -> Make a tick at "Select" -> Press Save.

Now he can only access TABLE users on database l2j on your mysql server.

 

You can go even FURTHER!

 

Allowing readonly in Mysql server -> Database: l2j -> Table: characters-> column char_name:

Doubleclick on "testuser" -> Database list will open -> Doubleclick database you wish to give him readonly access to (example: l2j). -> Doubleclick TABLE you wish to give him readonly access (example: characters" -> Select "char_name" -> column specific privileges -> Tick "Select" -> Save.

Doing this, "testuser" will ony have readonly access in "olumn char_name", table "characters", database "l2j" and your mysql server.

He will not be able to do ANYTHING else but read character names. He will not even see any other fields/tables/databases except character_name.

 

 

Very important!

Privileges stack!

If you give "testuser" access to read all databases, and then leave "select" empty in a table, he will still be able to see that table.

You can however give him read only access for entire database, and then write for only table "characters".

This way he will see entire database and read all data, but can only read to "characters"

 

 

---

This guide should help a lot of people secure their servers properly.

I have not seen it written anywhere else, but It is extremely important!!

If you find it too hard and cannot do it yourself, hire me and I'll do it .

 

 

Do NOT leech this article! Do not copy/paste it to other forum/sites without my explicit permission. Not even if you give me credits.

I do not care what the reason is. DO NOT copy, re-post or steal it! Not even parts of it.

If you want ME to post it somewhere else PM ME and I will consider it.

 

You can be damn sure I would find out if it happens and get your sorry ass punished for it.

 

If you want me to correct/add/explain something else, let me know.