{"id":445,"date":"2011-04-27T12:11:16","date_gmt":"2011-04-27T12:11:16","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=445"},"modified":"2023-12-31T03:02:24","modified_gmt":"2023-12-31T10:02:24","slug":"mysql-create-user","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-create-user\/","title":{"rendered":"MySQL CREATE USER"},"content":{"rendered":"
\n
\"MySQL<\/figure>\n<\/div>\n\n\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CREATE USER<\/code> statement to create a new user in the MySQL database server.<\/p>\n\n\n\n

Typically, MySQL creates the root user account during installation. This root user account has full privileges over the MySQL database server, giving it complete control over all databases, tables, users, and more.<\/p>\n\n\n\n

It’s a good practice to use the root account for administrative functions exclusively. For other tasks, create a new user account and grant the necessary privileges.<\/p>\n\n\n\n

This tutorial focuses on using the CREATE USER<\/code> statement to create a new user in the MySQL database.<\/p>\n\n\n\n

Introduction to MySQL CREATE USER statement<\/h2>\n\n\n\n

To create a new user in the MySQL database, you use the CREATE USER<\/code> statement. <\/p>\n\n\n\n

Here’s the basic syntax of the CREATE USER<\/code> statement:<\/p>\n\n\n

CREATE<\/span> USER<\/span> [IF<\/span> NOT<\/span> EXISTS<\/span>] account_name \nIDENTIFIED<\/span> BY<\/span> 'password'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the account name after the CREATE USER<\/code> keywords. The account name consists of two parts: username<\/code> and hostname<\/code>, separated by the @<\/code> sign:<\/p>\n\n\n
username@hostname<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The username<\/code> is the name of the user while the hostname<\/code> is the name of the host from which the user connects to the MySQL Server.<\/p>\n\n\n\n
The hostname<\/code> part of the account name is optional. If you omit the hostname, the user can connect from any host.<\/p>\n\n\n\n
An account name without a hostname is equivalent to the following:<\/p>\n\n\n
username@%<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the username<\/code> and hostname<\/code> contains special characters, such as spaces or hyphens, you need to enclose the username and hostname separately in quotes, like this:<\/p>\n\n\n
'bob-cat'@'hostname'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In addition to the single quote ('<\/code>), you can use backticks ( `<\/code>) or double quotation mark (\"<\/code>).<\/p>\n\n\n\n
Second, specify the password for the user after the IDENTIFIED BY<\/code> keywords.<\/p>\n\n\n\n
The IF NOT EXISTS<\/code> option creates a new user only if it does not already exist. If the user exists, the statement issues a warning.<\/p>\n\n\n\n
Note that the CREATE USER<\/code> statement creates a new user without any privileges. To grant privileges<\/a> to the user, you use the GRANT<\/code> statement.<\/p>\n\n\n\n
MySQL CREATE USER example<\/h2>\n\n\n\n
First, establish a connection to the MySQL Server using the mysql client tool with the root user account:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Enter the password for the root<\/code> account and press Enter<\/code>:<\/p>\n\n\n
Enter password: ********<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, show the users on the current MySQL Server:<\/p>\n\n\n
SELECT<\/span>\n  user<\/span> \nFROM<\/span> \n  mysql.user;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
+------------------+\n| user             |\n+------------------+\n| mysql.infoschema |\n| mysql.saession   |\n| mysql.sys        |\n| root             |\n+------------------+    <\/code><\/span><\/pre>\n\n\n
Third, create a new user named bob<\/code>:<\/p>\n\n\n
create<\/span> user<\/span> bob@localhost \nidentified<\/span> by<\/span> 'Secure1pass!'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, show all users again:<\/p>\n\n\n
select<\/span> user<\/span> from<\/span> mysql.user;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output will be:<\/p>\n\n\n
+------------------+\n| user             |\n+------------------+\n| bob              |\n| mysql.infoschema |\n| mysql.session    |\n| mysql.sys        |\n| root             |\n+------------------+\n5<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the user with the name bob<\/code> has been created successfully.<\/p>\n\n\n\n
Fifth, open a second session and log in to the MySQL database server with the user bob<\/code>:<\/p>\n\n\n
mysql -u bob -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Input the password for bob<\/code> and press Enter<\/code>:<\/p>\n\n\n
Enter password: ********<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, show the databases<\/a> that bob<\/code> has access:<\/p>\n\n\n
show<\/span> databases<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the list of databases that bob<\/code> can access:<\/p>\n\n\n
+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| performance_schema |\n+--------------------+\n2<\/span> rows in<\/span> set<\/span> (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
create<\/span> database<\/span> bobdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
use<\/span> bobdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
create<\/span> table<\/span> todos(\n   id<\/span> int<\/span> auto_increment primary key<\/span>,\n   title varchar<\/span>(255<\/span>) not<\/span> null<\/span>,\n   completed bool<\/span> default<\/span> false<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Tenth, grant all privileges to the bobdb<\/code> to bob<\/code>:<\/p>\n\n\n
grant<\/span> all<\/span> privileges<\/span> on<\/span> bobdb.* to<\/span> bob@localhost;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Eleventh, go to bob’s session and display all the databases:<\/p>\n\n\n
show<\/span> databases<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, bob<\/code> can see the bobdb<\/code>:<\/p>\n\n\n
+--------------------+\n| Database           |\n+--------------------+\n| bobdb              |\n| information_schema |\n| performance_schema |\n+--------------------+\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Twelfth, select the database bobdb<\/code>:<\/p>\n\n\n
use<\/span> bobdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
show<\/span> tables<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The user bob<\/code> can see the lists<\/code> table:<\/p>\n\n\n
+-----------------+\n| Tables_in_bobdb |\n+-----------------+\n| todos           |\n+-----------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
insert<\/span> into<\/span> todos(title) \nvalues<\/span>('Learn MySQL'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
select<\/span> * from<\/span> todos;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This is the output:<\/p>\n\n\n
+----+-------------+-----------+\n| id | title       | completed |\n+----+-------------+-----------+\n|  1<\/span> | Learn MySQL |         0<\/span> |\n+----+-------------+-----------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
So the user bob<\/code> can do everything with the bobdb<\/code> database.<\/p>\n\n\n\n
Finally, disconnect from the MySQL Server from both sessions:<\/p>\n\n\n
exit<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the CREATE USER<\/code> statement to create a new user in the MySQL Database Server.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Fifteenth, query data<\/a> from the lists<\/code> table:<\/p>\n\n\n
Fourteenth, insert a row<\/a> into the todos<\/code> table:<\/p>\n\n\n
Thirteenth, show the tables<\/a> from the bobdb<\/code> database:<\/p>\n\n\n
Note that you will learn how to grant privileges to a user in the GRANT<\/a><\/code> tutorial.<\/p>\n\n\n\n
Ninth, create a new table<\/a> called todos<\/code>:<\/p>\n\n\n
Eight, select the database<\/a> bobdb<\/code>:<\/p>\n\n\n
Seventh, go to the session of the user root<\/code> and create a new database<\/a> called bobdb<\/code>:<\/p>\n\n\n