This tutorial explains how to create a user with password in MySQL. We’ll use the MySQL Create User command and describe it with the help of examples.
By using this command, you can introduce a new user and grant him/her the required permissions or rights. 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.
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.
[email protected]_name e.g. [email protected]
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 MySQL CREATE USER command creates a new user who doesn’t have any privileges. Hence, you need to grant him/her the desired access.
We feel that the following posts would guide you even more along with this tutorial:
First of all, you must have an active connection with the MySQL DB. So, if it 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 below result:
+------------------+ | Users | +------------------+ | mysql.sys | | root | +------------------+
Now, try to define a new user known as ‘john’:
mysql> CREATE user [email protected] 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 have to list down the databases that john can access.
mysql> SHOW DATABASES;
You might see something like:
Databases ------------------- information_schema
There is no database that 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);
Now, assign the required privileges on the ‘johndb’ to ‘john’:
mysql> GRANT ALL PRIVILEGES on johndb.* to [email protected];
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.
We hope you should now feel comfortable in using the MySQL CREATE USE with PASSWORD command. However, you can take up more examples and practice.
Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.
Happy Coding !
Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.
How MySQL Ripple binlog server can help you improve the performance of your master server by introducing a proxy server between the master and slave servers.MySQL does not limit the number of slaves that you can connect to the master server in a replication topology.
What MySQL is? What is a Database and Database Management System and we will discuss how to work with databases and process data using MySQL
In this performance benchmark for MySQL on Azure, we put ScaleGrid up against Azure Database. See which provider gives you the most value for your money! Microsoft Azure is one of the most popular cloud providers in the world, and a natural fit for database hosting on applications leveraging Microsoft across their infrastructure. MySQL is the number one open source database that’s commonly hosted through Azure instances. While Microsoft offers their own Azure Database product, there are other alternatives available that may be able to help you improve your MySQL performance.
railsでWebアプリケーションを作成しているのですが、rails sでサーバーを起動した際に、Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) のエラーが出てしまいます