#mysql-client shell

MySQL has it`s own OS-independent users and security model, specifically involving the root account and its privileges. So, with this article, we will go over how to deal with the default root account and how to create new users with, and without, root privileges.

Right after the initial installation, you can connect to your MySQL server management shell only from localhost with mysql (not system) root login and, usually, without a password. So the first thing we should look at is how to set it (or, in the case that you set it earlier, reset it). If your root password is empty (usually happens after an automated installation) we`ll use the mysql-client shell to connect to and manage our server. With an empty password, you should use command 'mysql -u'. If you try to add the '-p' option, you will be forced to provide a password which can`t be empty with that flag. So:

mysql -u root

Now, we can run commands below (don`t forget to hit Enter after every line). We will set the password "YourNewPassword" for the mysql ‘root’ user, update information, and return to the system shell. Note that, on your end, you should create your own password. The general guidelines for a strong password involve using 16 symbols or more along with a random arrangement of lower and upper case letters, digits, and special symbols.

UPDATE user SET Password=PASSWORD('YourNewPassword') WHERE User='root'; FLUSH PRIVILEGES; quit;

You can now use the new password to reconnect:

mysql -u root -pYourNewPasswrd

The space here is not and accident;, commands with a space at the beginning will not appear in shell history, so nobody can view our password by just hitting the up arrow key. While the above instructions work in case of no set password, if you do know the root password and want to change it, log in with the old password (paste or type it after request):

mysql -u root -p

And change it with simple request:

UPDATE user SET Password=PASSWORD('YourNewPasswrd') WHERE User='root'; FLUSH PRIVILEGES; quit;

If you don`t know root password,: you must log into your server with system root or sudoer account. First, display mysqld process ID. In my case its equal to 558, and it will be different on your system.

ps -A | grep mysqld Here is  the output and the main process is mysqld.  480 ?        00:00:00 mysqld_safe  558 ?        00:00:01 mysqld

You can stop it with the command below, or by using the mysqld_safe process:

kill 558

Give it some time, then check if it still running:

ps -A | grep mysqld

There should be no output, which would mean that mysqld process has stopped. Now we need to start it again with options:

/usr/bin/mysqld_safe --skip-grant-tables --user=root &

--skip-grant-tables starts mysql without authentication tables and lets us log in without a password.

--user=root added because mysqld must be forced to run under root account (for security reasons).

& runs it without a console output, so you don`t need to log in again, or run a new session.

Now we can connect to mysql without a password:

mysql -u root

And change it

UPDATE user SET Password=PASSWORD('YourNewPassword') WHERE User='root'; FLUSH PRIVILEGES; quit;

Then we must turn mysql back to normal authentication mode with command from system (not mysql) shell:

mysqladmin -h hostname flush-privileges

Now, we can use our new password, and create new users, databases, and change its permissions.

MySQL remote access with root privileges

With root password defined, we can connect to the local mysql server and manage it. But what if we need to connect from the remote host? As with many other systems, MySQL does not provide network access by default to avoid the security risks. It`s better to disable remote privileged access completely, but it can sometimes be an architectural requirement to provide it. The first thing you might want to do is to create a new privileged user with a unique name to prevent a brute force attack. Also, when more than one system administrator maintains the databases, different accounts make the audit and responsibility separation much easier. But before we create something new, let`s take a look at existing users and permissions. Login to mysql shell:

mysql  -u root -p

and display the databases list:

SHOW DATABASES; mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | +--------------------+ 2 rows in set (0.04 sec)

The system database "mysql" contains the table "user" with accounts information. We can display it by sending the request below. You can replace “User,Host” with * to show all data:

SELECT User,Host FROM mysql.user; +------------------+----------------------+ | User             | Host                 | +------------------+----------------------+ | root             | 127.0.0.1            | | root             | ::1                  | | root             | localhost            | +------------------+----------------------+ 3 rows in set (0.00 sec)

You might wonder why are there are three root accounts? MySQL security model assumes that every account has two components: username and the connection source (hostname or IP address). Every computer has a default hostname alias "localhost" and loopback interfaces of two IP protocol versions. MySQL provides local connections between all of them, so there three different accounts. Users with the same name and with the different host can have the same passwords, but different privileges. All copies of the root accounts have no difference by default. The next request shows you the privileges of the locally connected root:

SHOW GRANTS FOR root@localhost; +----------------------------------------------------------------+ | Grants for root@localhost                                                                                                              | +---------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C66BBA52FF0168A9ACE864974DC8936876' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           | +----------------------------------------------------------------+ 2 rows in set (0.00 sec)

Root has all standard rights (ALL PRIVILEGES) on all databases and tables (ON *.* ) and a special one (WITH GRANT OPTION) to give those privileges to others. Now, we will create a new user called Sysadmin1 and allow them to connect from their PC with IP address 192.168.15.200

CREATE USER 'Sysadmin1'@'192.168.15.200'  IDENTIFIED BY 'Long-And-Strong-Password!'; FLUSH PRIVILEGES;

Then give them the appropriate rights:

GRANT ALL ON *.* TO [email protected] WITH GRANT OPTION; FLUSH PRIVILEGES;

And to show the result:

SHOW GRANTS FOR [email protected]; +----------------------------------------------------------------+ | Grants for [email protected]                                                                                                              | +----------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'Sysadmin1'@'192.168.15.200' IDENTIFIED BY PASSWORD '*56EE35F518545AF0F94B2F61CC0D4F0E85C688A0' WITH GRANT OPTION | +-----------+ 1 row in set (0.00 sec)

Now we have our own account and must avoid using connections with root login. But we still can`t connect remotely. We need to edit the config file ‘/etc/mysql/my.cnf’ to tell mysql to listen on external interfaces. Make sure that you setup a firewall beforehand to restrict access to MySQL port 3306. It’s a good idea to disable root account completely, change the default mysql port, install fail2ban to detect bruteforce attacks, and connect to local interface inside the SSH tunnel with a certificate. After all, there really is no such thing as a too much security.

Now, If you are ready, replace the address at the line below to the external IP of your database server:

bind-address            = 127.0.0.1

Then restart the mysql daemon:

Service mysqld restart MySQL regular users

The best practice of MySQL management is: 1) Never use the root account for application connections or even maintenance. 2) Always use different accounts for different applications. 3) Always give as low privileges for an account as you can.

Now we should create some new accounts with different privileges. MySQL uses a database as a top of the logical hierarchy. In the examples below we will define users privileges per database, but you can set it even on selected tables or columns. Let`s connect to our MySQL again:

mysql -u Sysadmin1 -p

Display a databases list with command:

SHOW DATABASES; mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | MySiteDB           | +--------------------+ 3 rows in set (0.04 sec)

Our first regular user MySQLBackup will be able to connect only from the remote backup server with FQDN backup.server1.local and IP address 192.168.10.19 or locally and make a backup of MySiteDB database.

CREATE USER 'MySQLBackup'@'backup.server1.local'  IDENTIFIED BY 'Password4NewUser'; FLUSH PRIVILEGES;

Here we created the username MySQLBackup'@'backup.server1.local, with the password “Password4NewUser”. Then, let's create a second account with IP address of the backup server to prevent connection troubles if DNS fails.

CREATE USER 'MySQLBackup'@'192.168.10.19'  IDENTIFIED BY 'Password4NewUser'; FLUSH PRIVILEGES;

We still can`t connect from the local machine, so I`ll create the third impersonation of it:

CREATE USER 'MySQLBackup'@'localhost'  IDENTIFIED BY 'Password4NewUser'; FLUSH PRIVILEGES;

Now our user has access to the mysql shell, but not to databases. Let`s get them that access:

SHOW GRANTS FOR MySQLBackup@localhost;   +--------------------------------------------------------------------------------------------------------------------+ | Grants for MySQLBackup@localhost                                                                                   | +--------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'MySQLBackup'@'localhost' IDENTIFIED BY PASSWORD '*FCA964D62C4E6D70A25F641EC1858CEDAF4CA3A3' | +--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

This is only USAGE privilege (which is almost like no privileges at all). Let's make a different check. We can now log in with new credentials and try to display the databases:

mysql -u MySQLBackup -p  SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)

There`s no output here about the system or other databases. But we know that there is an existing MySiteDB database, so let's try to switch to it:

mysql> USE MySiteDB; ERROR 1044 (42000): Access denied for user 'MySQLBackup'@'localhost' to database 'MySiteDB'

We can`t! Log out and turn back as Sysadmin1 or root

quit;   mysql -u root -p

Let`s give the read permission ("SELECT" in MySQL terminology) on MySiteDB and all of its tables (.*) for MySQLBackup@localhost account:

GRANT SELECT ON MySiteDB.* TO MySQLBackup@localhost; FLUSH PRIVILEGES;

Then, return to system shell out of MySQL with 'quit;' and use mysqldump utility to try to backup MySiteDB to file:

mysqldump -u MySQLBackup -h localhost -p MySiteDB > mysitebackupdb.sql

mysqldump: Got error: 1044: Access denied for user 'MySQLBackup'@'localhost' to database 'MySiteDB' when using LOCK TABLES

Before a dump, tables must be defended against the changes with a lock, to avoid data inconsistency. Our user has no rights to lock, so we can`t do it. Turn back to MYSQL shell and give the additional permissions:

GRANT LOCK TABLES ON MySiteDB.* TO MySQLBackup@localhost; FLUSH PRIVILEGES;

To initialize the backup from remote host don`t forget to grant access, SELECT, and LOCK TABLES privileges to your user@YourBackupServer Try to make the backup with system shell command again:

mysqldump -u MySQLBackup -h localhost -p MySiteDB > ./mysitebackupdb.sql

Check the content:

cat ./mysitebackupdb.sql

It`s filled with SQL data, and in future we can restore database with the same system 'cat' utility piped with mysql login:

cat ./mysitebackupdb.sql | mysql -u root -p MySiteDB

That's all for today's article. Don't forget to follow us on Twitter and Facebook, and stay tuned for our latest articles. Until next time!