What is a MySQL user?
MySQL databases lie in the basis of content-driven websites and their security is of highest importance to website owners. The management of MySQL databases is usually executed through third-party programs such as phpMyAdmin, which ensures a quick and easy database administration process. Although using phpMyAdmin is of great help to developers, they are also recommended to be familiar with the MySQL command line and basic MySQL management commands like create/delete users.
In MySQL the USER is actually a record in the 'USER' table of the MySQL server which contains the login information (username and password), the corresponding account privileges, and the host information for a MySQL account.
Why create a user in MySQL
When the installation of a MySQL Server completes there is a ROOT user account only. By default the ROOT user doesn't have any password set. Because the ROOT user account has all the privileges for the MySQL server, it is highly recommended to add a ROOT password immediately when the installation finishes.
Example: How to set password for a ROOT account on the MySQL5 Server
$mysqladmin -u root password your-new-password$sudo /etc/init.d/mysql restart
Also because of security reasons it is not recommended to use your MySQL server's ROOT account for common tasks. To make your MySQL server more secure you can use your ROOT account and create another MySQL USER for your web applications.
How to add a new account in MySQL by using CREATE USER?
There are different ways to create a new user account in MySQL, but all of them require you to establish a connection to the server via a root account or an administration account with the appropriate privileges.
The user creation process continues with inserting the new user's values such as host, username and password into the user table in the MySQL database, containing the log-in details of everyone who has any level of access to the database.
The CREATE USER command is available only in MySQL 5 (5.0.2) and newer releases, and it follows this syntax:
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']In order to create a user 'michael' with a password 'mypassword123' you need to establish a connection to the MySQL server as root and to execute this query:
An example of how to Create a User in MySQL5
CREATE USER michael IDENTIFIED BY PASSWORD 'mypassword123';Then open and check the MySQL user table and you will find a new record in it. The newly created user has all the privileges set to No, so this user can do nothing in the DB. To add some privileges we can use the GRANT command as follows:
An example of how to GRANT (add) privileges to a newly created user
GRANT SELECT,INSERT,UPDATE,DELETE ON my_db.* TO 'michael'@'localhost';Now we have added only the most important privileges to the user. With the setting above the newly created MySQL user is good to run a Joomla, Wordpress or a PHPBB3 script. However, with such settings this user is not able to install them as it cannot create tables. To add all privileges to the user you don't have to list all of them but you can use the ALL shortcut as follows:
How to GRANT all privileges to a user
GRANT ALL ON my_db.* TO 'michael'@'localhost';How to add a user in MySQL by using GRANT?
As you can see from the examples above the creation of a user using the CREATE USER statement is not that complicated. Actually there is a simpler way to create a user and grant privileges. Using the GRANT command you can achieve the same results in one step only:
How to Create a User in MySQL using GRANT
GRANT ALL ON my_db.* TO 'my_user'@localhost IDENTIFIED BY 'my_pass';For more detailed information on how to use the GRANT statement, please visit the 'MySQL grant' article in your hosting encyclopedia or the MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/grant.html).
Create a user in MYSQL using the INSERT statement
In the examples above we have used dedicated commands to create a user in MySQL. Sometimes the abovementioned methods are not convenient, or impossible to be used due to permission limitations, so there is a possibility to add a new MySQL user by directly editing the 'mysql.user' MySQL table on your server. In this case you need to insert a new record into the table with a normal INSERT query:
An example of how to Create a User in MySQL using the INSERT statement
INSERT INTO user (Host,User,Password) VALUES('localhost','my_user',PASSWORD('my_pass'))If you want to add some privileges with the same command the query will look like this:
An example of how to create a user with INSERT and SELECT privileges by editing the 'mysql.user' table
INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)VALUES('localhost','my_user',PASSWORD('my_pass'),'Y','Y');
For a detailed overview of the INSERT statement and more examples, visit our 'MySQL insert' article.
How to create a MySQL user with NTC Hosting's Control Panel
Creating users in MySQL on the NTC Hosting servers has been facilitated to the maximum. Now you can create users with the very set-up of MySQL databases directly from your web hosting Control Panel. Simply go to the MySQL Databases section, where you should specify the new database details - database name and database password in the corresponding fields. Then simply hit the 'Create SQL Database' button and your new database/username is ready. Note that on our hosting platform your database name matches your database username. Easy, isn't it! For your convenience and for more detailed examples (tutorial videos and screenshots) about how to create a MySQL user and database with NTC Hosting's Control Panel, please visit the 'MySQL create table' article.
Resources: