The MySQL's root account has all the privileges that one can ever need but it is certainly not something we would like to expose to the world! If someone were to get access to the root user, then the entire database would be compromised. To avoid a security threat of this order, it is a must to consider having a separate user and provide restricted access as dictated by the application design. This page describe ways to add a new user and if needed, to delete an existing user.
In fact, we could have several databases on the same server and from security point of view, it might make more sense to have a different user for each database. Thus, more often that not, we do need additional users besides the root.
We can create new users using the "CREATE USER" command. With "CREATE USER", we can provide the password using the "IDENTIFIED BY" clause. Since only a user with appropriate access can create a new user, we start by logging as the root since root has all access.
[user@codingbison]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> CREATE USER webuser@localhost IDENTIFIED BY "password"; Query OK, 0 rows affected (0.00 sec)
Now, from another terminal, let us open another connection to MySQL and this time login as user webuser and enter the password as "password".
[user@codingbison]$ mysql -u webuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) mysql> mysql> quit Bye [user@codingbison]$ mysql -u webuser ERROR 1045 (28000): Access denied for user 'webuser'@'localhost' (using password: NO)
We should note that when we create a user, providing a password is not necessary. If we were to remove the "IDENTIFIED BY", then we can create a user that does not need to have a password. To recreate the new user, we drop the user from the database first using the "DROP USER" statement.
mysql> DROP USER webuser@localhost; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER webuser@localhost; Query OK, 0 rows affected (0.00 sec)
Now, if we were to login again from another terminal, then we do not need password.
[user@codingbison]$ mysql -u webuser Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
It is important to note that the user is "webuser@localhost". The "@localhost" specifies that the connection would be established only if webuser logins from the localhost; if webuser were to start the connection from a remote machine, then the access would be denied.
However, if we know that the webuser is going to login from another host, let us say, xyz.com or a.b.c.d IP address, then we can ensure that the webuser does not have any access if the user connects from any host other than xyz.com or a.b.c.d IP address.
The above hardening can be useful because it can happen that we are running the application server on a different host other than the database server. For such cases, the user webuser should have login access only from the application server. With CREATE USER statement, we can easily accomplish this: "CREATE USER webuser@a.b.c.d IDENTIFIED BY "password";" or "CREATE USER webuser@xyz.com IDENTIFIED BY "password";". This mechanism restricts the user to login only from the pre-specified machine (host name "xyz.com" with IP address "a.b.c.d" address).