MySQL Create User语句-原创手记-慕课网

Summary: in this tutorial, you will learn how to create user in MySQL by using the CREATE USER and INSERT statements.

In MySQL, you can specify not only who can connect to a database server but also from where. Therefore an account in MySQL consists of username and host name where user connects from, that separated by @. For example, if the root user connects from the OutOfMemory.CN host to the database server the account name would be root@OutOfMemory.CN.

This allows you to setup multiple accounts with the same name but connects 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 named user.

Create new users using CREATE USER statement

MySQL provides the 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.

The password is specified after the IDENTIFIED BY clause. The password must be in plain text. 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 , you 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, which means any host.

CREATE USER superadmin@'%' IDENTIFIED BY 'Secured'

The percentage wildcard % has same effect as it is used in the LIKE operator e.g., to allow mysqladmin user to connect from any host to the OutOfMemory.CN you use percentage wildcard % as follows:

CREATE USER mysqladmin@'%.OutOfMemory.CN' IDENTIFIED by 'SecurePass704';

Notice that you can also use the underscrore wildcard _ in the CREATE USER statement.

If you omit the hostname part of the account, MySQL still accepts it and allows 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@hostname', MySQL will create a user with username@hostname username and allow the user to connect from any host, which may not be what you expected.

The MySQL CREATE USER statement only creates a new user and does not grant any privilege 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 named user. By doing this way, you need to use the PASSWORD function to encrypt the password before inserting the user record into the table. See the following example:

INSERT INTO user (host,user,password) VALUES('localhost','dbadmin',PASSWORD('CrEate-User'));

In this tutorial, you have learned various ways to create user in MySQL using the CREATE USER statement and INSERT statement.

MySQL Create User语句

Create new users using CREATE USER statement

Create new user by using INSERT statement

Related Tutorials