How to create database user and set privileges in MySQL
The MySQL database is a widely used relational database, installation of which comes with different web server bundles like WAMP (Windows) and LAMP (Linux). It can ofcourse also be installed separately. In this WiKi article we will go thru the steps on how to secure a newly created database by creating a new user with only the necessary set of privileges (rights) to use it.
Mobile devices can access databases on web servers as wired computers can, reading and writing data from them e.g. to populate fields or even tables in the application or to send data for remote server database for analysis and storage.
When using a database connection remotely and from via multiple clients using the same account to update a database, it is important that the database user is limited only to perform the needed database operations to keep the database server protected from administrative attempts by a possibly modified code or call.
The phpMyAdmin console is an alternative way to perform these tasks, but it is not in the scope of this article.
This WiKi article assumes, that a MySQL database called “users” does exist. You can replace the database with whatever you have, or you could also check the WiKi article written previously, such database was created in beforehand.
This article is written from the perspective that the MySQL will reside on a local server (localhost).
Follow these steps to add a new user to your database and to set the required administrative privileges:
Start the MySQL server.
If the MySQL server has not yet been started, please do so. In e.g. WAMP this can be done by opening it’s menu (left-click), selecting MySQL, Service, Start/Resume.
Note: if the server is not running, you will not be able to login.
Start The MySQL console (monitor)
It can be opened from the WAMP menu or alternatively enter “mysql” in the command prompt of the directory that the progam resides (e.g. C:\wamp\bin\mysql\mysql5.1.36\bin)
C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u root –p
If you did not issue a “root” user password earlier, then just press “Enter” to bypass, otherwise enter the password you had set.
After a successful login, the MySQL monitor starts and displays the following greeting:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.36-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Add new database user
We will use the SQL command GRANT to both create a new user and to set the needed privileges, this is done in the following way:
mysql> GRANT usage ON *.* TO maveric@localhost
->IDENTIFIED BY '<my_secret_password>';
Query OK, 0 rows affected (0.03 sec)
<my_secret_password> replace with the password you want to use for this user.
The newly created user “maveric” has now capabilities of connecting to the MySQL database server via the host ‘localhost’ using the password given. By this, only connection privileges have been granted, the user is not allowed to perform anything else on the MySQL server. MySQL has the ‘mysql” database within, which contains information of it’s users and databases created. We can execute a test to see how the previously created user is configured within it:
Ensure that the “mysql” database is in use, by issuing the following command:
mysql> use mysql;
Then execute SELECT command that fetches every line from the “user” table of the mysql database:
mysql>SELECT * FROM user;
The following line would tell that a user named ‘maveric’ was found and that priviledges are all set to “N”, equals to No privilege.
| localhost | maveric | *E42F985D6D085A77F534E4CBF980886A6A4B5A01 N | N | N…
Because of the granting privileges method, if there were a “Y” -value for any user's privilege in the user table, user could apply that privilege to any MySQL database. For security, it is mostly best to have set all privileges to “N” within “user” mysql database table.
Grant specific privileges for the user
Let’s assume in this case that we would like to have the user “maveric” to be able to perform certain operations in the newly created database. For this, we would need to GRANT this user rights, e.g. the SELECT, INSERT, UPDATE and DELETE privileges for the “user” database. To do this, issue the following GRANT command on the mysql monitor:
mysql>GRANT SELECT, INSERT, UPDATE, DELETE
->ON users.* TO maveric@localhost;
Note: when typing at MySQL monitor/console, you can write a longer set of commands line by line, which must finish with a semicolon “;”. When you press Enter while entering the MySQL monitor shows “->” to indicate the command you are entering continues on next line.
After a successful grant, the following response appears:
Query OK, 0 rows affected (0.00 sec)
These privilege rights are available for the user “maveric” immediately.
After you have created a new database, and a new user with privileges to manipulate it, you could start to insert data structures, like tables into it. The sequence in this WiKi article does not necessarily mean that you need to create your database in a similar manner, but what is shown here is one of the most common procedures in creating new MySQL users and databases.
A separate WiKi article handles steps on how to create new tables into database, how to select rows from the tables, insert, update and delete information in them.
--13:31, 27 May 2010 (UTC)