This tutorial explains how to create a user with a password in MySQL. We’ll use the MySQL Create User command and describe it with the help of examples.
MySQL Create User with Password
This command creates a new MySQL user and grants privileges. Usually, you might be using the root user to access a database. The root user has complete control and can’t be restricted.
Hence. It is mandatory to create users depending on the type of access s/he needs. And that’s where this command is going to help.
The MySQL Create User statement can be used to create new users with a password and can be assigned permissions on a need basis.
MySQL Create User Syntax
It comes in the following forms:
-- MySQL CREATE USER with PASSWORD
CREATE USER [IF NOT EXISTS] [User_Name]
IDENTIFIED [BY/WITH] ['Password'];Here, the User_Name field has two elements: The actual username and the machine hostname.
user_name@host_name e.g. john@myhost.com
Please note the following:
- The user_name is the name of the logged-in user whereas the host_name is the name of the machine running the MySQL DB.
- The host_name subfield is optional. If you remove it, even then the user can establish a connection.
- Both the user_name and host_name can include special chars. You need to quote them accurately: ‘user_name’ or ‘host_name.’
You must supply a password for the user right after the IDENTIFIED BY clause
Also, by using the IF NOT EXISTS option, you can make sure a new user is created when s/he is not available.
You must remember that the MySQL CREATE USER command creates a new user who doesn’t have any privileges. Hence, you need to grant permissions to the new user.
We feel that the following posts would guide you even more along with this tutorial:
Creating Users in MySQL – Examples
Firstly, you must have an active connection with the MySQL DB. So, if this is not the case, then run the following MySQL client command:
mysql -u root -p
If you would have set a root password, then provide the same.
Enter Password : XXXXXXXX
Once the connection is successful, you can list down the available users.
mysql> SELECT user from MYSQL.user;
It will give the following result:
+------------------+ | Users | +------------------+ | mysql.sys | | root | +------------------+
MySQL Create User with Password
Now, try to define a new user known as ‘john’:
mysql> CREATE user john@localhost IDENTIFIED by 'xxYYYxx';
Run the below command to display all users.
mysql> select user from mysql.user;
+------------------+ | Users | +------------------+ | mysql.sys | | john | | root | +------------------+
You have now successfully a new user called John. Now, start another MySQL session and log in as john.
mysql -u john -p
Supply the password for “john” and press the Enter key:
Enter Password : xxYYYxx
In the next step, you list the databases “john” can access.
mysql> SHOW DATABASES;
You might see something like:
Databases ------------------- information_schema
There is no database “john” can use. So, we’ll now create one for him.
mysql> CREATE DATABASE johndb;
Now, select the database you created.
mysql> use johndb;
The database is empty, so create a dummy table named ‘test_table.’
mysql> CREATE TABLE test_table( -> id INT AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) not null, -> married bool default false);
Grant Privileges to New Users
Now, assign the required privileges on the ‘johndb’ to ‘john’:
mysql> GRANT ALL PRIVILEGES on johndb.* to john@localhost;
You can read more about MySQL Grant Privileges here.
Now again, go to the console and run the command to show the databases.
mysql> SHOW DATABASES;
This time, john should be able to list the johndb:
Databases ------------------- johndb information_schema
Now, select the johndb and check the available tables.
mysql> USE johndb; mysql> SHOW TABLES;
You can now confirm that john can list the test_table.
Tables_in_johndb ------------------- test_table
Let’s now see if john can add some records to the test_table.
mysql> INSERT INTO test_table(name) values('John Travolta');
mysql> SELECT * from test_table;The above statements would give the below result:
Records in test_table ------------------------- 1, John Travolta, 0
Now, you can assert that the user john can run any operation on the johndb database.
Now, it’s time to disconnect from the MySQL DB and close the running sessions.
Before You Leave
Trust us that you can now easily create a user in MySQL with a password, and grant privileges. However, you can take up more examples and practice.
While you are on a learning spree, more information is what you need. So, don’t miss on to the below posts.
- Quick Steps to Install MySQL on Windows 7
- Easy Steps to Install MySQL on Ubuntu 16.04
- MySQL Date and Date Functions
Lastly, our site needs your support to remain free. Share this post on social media (Facebook/Twitter) if you gained some knowledge from this tutorial.
Enjoy learning,
TechBeamers.
