To create a user, we first connect to the MySQL server console:
mysql
Let’s see what users are:
select * from mysql.user;
select user,host from mysql.user;
Create a user (where localhost is specified from where the user can connect, you can specify the IP address, localhost – from the local machine where the MySQL server itself, or % from any addresses):
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
If you intend to connect not only locally, you need to comment out the line in my.cnf:
#bind-address = 127.0.0.1
And restart the MySQL server:
sudo service mysql restart
After that, I recommend restricting access to MySQL using IPTables.
See also – Configuring IPTables
To assign the newly created user unlimited permissions to a specific database, execute the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';
If necessary on all bases:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
You can specify specific access rights:
GRANT SELECT ON database_name.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON database_name.table_name TO user@192.168.1.5;
If you want to create a new database:
CREATE DATABASE database_name;
For the changes to take effect, execute:
FLUSH PRIVILEGES;
You can delete the user as follows:
DROP USER 'user'@'localhost';
Example of viewing privileges:
SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS;
SELECT * FROM information_schema.user_privileges;