Creating user accounts on a secured MySQL server

1 comment
After installing a MySQL database server and securing that installation with the mysql_secure_installation tool, you are locked out from remote access to perform any operation on the server. Since we all like the 'R' in RDBMS to stand for remote as well as relational, let's see how we can configure user credentials to provide remote access to the database server but still keep those credentials from providing local access.
Suppose you have a key user named James Bond to whom you want to give remote administration on the MySQL server. However, you don't want him to logon locally on the machine to perform any operations. This way you achieve three levels of security:
  • The credential only allows remote access;
  • You control from which remote computers the user can connect, by configuring rules on a network or server firewall;
  • IF it's a shared server, console access on the machine doesn't give access to the database engine.
First, let's open a terminal session on the server and a login with the mysql command line tool, using our root account:
shell> mysql -u root -p
Now, for the sake of this example, let's create a user account for James Bond and give him full privileges:
mysql> CREATE USER 'jamesbond'@'%' IDENTIFIED BY 'double0seven';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'jamesbond'@'%' WITH GRANT OPTION;
Having set this up, if James Bond tries to login from any remote computer he can because we used the wildcard '%' to specify the user's machine. But he can also login from the local server. To prevent this from happening, we create a second account with the same username and password, but do not grant any permissions:
mysql> CREATE USER 'jamesbond'@'localhost' IDENTIFIED BY 'double0seven';
If the user then tries to login again from the local machine, he can. But isn't able to perform any operations or queries.

1 comment :

  1. Complimentary to this post, you can read this thread on Stack Overflow:
    http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

    ReplyDelete