MySQL allows us to specify which user account can connect to a database server. The user account details in MySQL contains two information – username and host from which the user is trying to connect in the format username@host-name.
If the admin user is connecting through localhost then the user account will be admin@localhost.
MySQL stores the user account in the user grant table of the mysql database.
The CREATE USER statement in MySQL allows us to create new MySQL accounts or in other words, the CREATE USER statement is used to create a database account that allows the user to log into the MySQL database.
The syntax for the CREATE USER statement in MySQL is:
CREATE USER user_account IDENTIFIED BY password;
- user_account: It is the name that the user wants to give to the database account.The user_account should be in the format ‘username’@’hostname’
- password:It is the password used to assign to the user_account.The password is specified in the IDENTIFIED BY clause.
Below are the different ways in which CREATE USER statement can be used:
Creating a new user: For creating a new user “gfguser1” that connects to the MySQL database server from the localhost with the password “abcd”, the CREATE USER statement should be used in the following way.
CREATE USER gfguser1@localhost IDENTIFIED BY 'abcd';
Note: The create user statement only creates a new user,it does not grant any permissions to the user.
Creating more than one user: For creating more than one new user that connects to the MySQL database server from the localhost, the CREATE USER statement should be used in the following way.
CREATE USER 'gfguser2'@'localhost' IDENTIFIED BY 'efgh', 'gfguser3'@'localhost' IDENTIFIED BY 'uvxy';
The above code creates two new users with username “gfguser2” and “gfguser3” with passwords “efgh” and “uvxy” respectively.
Allowing a user account to connect from any host: To allow a user account to connect from any host, the percentage (%) wildcard is used in the following way.
To allow the user account to connect to the database server from any subdomain of the “mysqltutorial.org” host, then the percentage wildcard % should be used as follows:
Viewing permissions of an User Account: The “Show Grants” statement is used to view the permissions of a user account.The show grants statement is used in the following way:
SHOW GRANTS FOR user-account;
The *.* in the output denotes that the “gfguser1” user account can only login to the database server and has no other privileges.
- MySQL | Change User Password
- MySQL | DROP USER
- MySQL | RENAME USER
- Mysql | User-defind Variables
- MySQL | USER( ) Function
- MySQL | Common MySQL Queries
- CREATE and DROP INDEX Statement in SQL
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement
- SQL | INSERT IGNORE Statement
- SQL | Case Statement
- SQL | DESCRIBE Statement
- SQL | MERGE Statement
- MERGE Statement in SQL Explained
- Delete statement in MS SQL Server
- Reverse Statement Word by Word in SQL server
- SELECT INTO statement in SQL
- Difference between Row level and Statement level triggers
- Select statement in MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.