Features

How to add a new user to MySQL server

The command line fu you need to know in order to add a new user to your MySQL server.

Step 1: Add the user
Login to MySQL with the root user password by entering into your terminal mysql -u root -p, then entering your MySQL root password. To create a new user account enter the following:

CREATE USER 'matt'@'localhost' IDENTIFIED BY 'c0MpLiCaTeDp@55w0rd';

To confirm that it works, quit out of MySQL and enter mysql -u matt -p to try and login with the account you've just created. You should see the MySQL prompt if it's been successful.

Step 2: Give the user privileges
I want my account, on a development machine, to have access to everything on all databases.

However, this is not very secure and if you're adding a user account to a proper system you'll want to restrict what databases users can connect to and what the users can do.

If someone manages to execute a command using the user account via, for example, a SQL Injection vulnerability in a web application, you don't want them to be able to drop databases on your server, so you should restrict what the web user can do.

To give our user limited access to the important and widely used commands, SELECT, INSERT, UPDATE and DELETE you can issue the following command, which gives database-wide access to only these commands:

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'matt'@'localhost';

If you want to give root-like privileges, on a local development machine, you can create a user with these privileges using the command:

GRANT ALL ON *.* TO 'matt'@'localhost';

Published: TechPad.co.uk Monday 28 December 2009, 6:31 pm
Views: 17,330 times
Filed under: MySQL server Linux Ubuntu

(No votes yet)