MySQL Create User
Summary: In this tutorial, you will learn how to create user in MySQL by using CREATE USER and INSERT statements.
In MySQL you specify not only who can connect to a database server but also from where. Therefore account in MySQL consists of username and hostname, where user connect from, which separated by the character ‘@’. If user ‘root’ connects from host ‘mysqltutorial.org’ to the database server the account name would be ‘root’@’mysqltutorial.org’. This allows you to setup multiple accounts with the same name but connect from different hosts and have different privileges. In addition, you have control to setup account to connect from either specific or broad a set of hosts. The username and host are stored in the grant table called user.
Create new user by using MySQL CREATE USER statement
MySQL provides you CREATE USER statement to allow you to create a new user in a database server. The syntax of the CREATE USER statement is as follows:
CREATE USER user IDENTIFIED BY password
User account in the format ‘username’@’hostname’ is followed by the CREATE USER clause. The password is specified after the IDENTIFIED BY clause. The password must be in plaintext. MySQL will encrypt the password for you when it saves the user into the user table. For example, to create a new user dbadmin that connects from localhost with the password ‘CrEate-User’ we use the CREATE USER statement as follows:
CREATE USER ‘dbadmin’@‘localhost’ IDENTIFIED BY ‘CrEate-User’;
To allow user to connect from any host you use the wildcard ‘%’ that means any host.
CREATE USER ‘superadmin’@’%’ IDENTIFIED ‘Secured’;
The wildcard ‘% ‘works like in the SQL LIKE statement. So in order to allow user to connect from any host in the mysqltutorial.org you use wildcard ‘%’ as follows:
CREATE USER ‘mysqladmin’@’%.mysqltutorial.org’ IDENTIFIED ‘SecurePass704’;
Note that you can also use the wildcard ‘_’ in the CREATE USER statement as well. If you omit the hostname part of the account, MySQL still accepts it and allow user to connect from any host. The quote is very important especially when the account contains special characters such as ‘-‘ or ‘%’. If you accidentally quote the account name like ‘username@hosname’, MySQL will create a user with username username@hosname and allow you to connect from any host that is not expected. MySQL CREATE USER statement only creates a new user and does not grant any privileges to that user. If you want to grant privileges to the user you use the MySQL GRANT statement.
Create new user by using INSERT statement
A less common way to create user is using INSERT statement to insert a new record into the grant table user. By doing this way, you need to use PASSWORD function to encrypt the password by yourself before insert it into the table.
INSERT INTO user (host,user,password)
VALUES('localhost','dbadmin',PASSWORD(‘CrEate-User’));
In this tutorial, you’ve learned various way to create user in MySQL by using CREATE USER and INSERT statement.
Tags: MySQL create user, MySQL add user, MySQL user
Tutorial about: MySQL create user, MySQL add user, MySQL create user example
Tutorial Like MySQL Create User