Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Create Users

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

 Create Users
In MySQL, you can create multiple user accounts to access the database, each with specific authentication detail such as password. These users can be granted specific privileges using SQL statements like CREATE USER for authentication when creating a new user, and GRANT and REVOKE for assigning and removing administrative privileges, respectively.
The MySQL CREATE USERS Statement
We can create a new user account using the CREATE USER Statement in MySQL. To execute this statement, the current account must have the CREATE USER privilege or the INSERT privilege for the MySQL system schema.
Syntax
Following is the syntax of the MySQL CREATE USER statement 
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';
Where,
user_name is the name of the user you need to create.
hostname specifies the host from which the user can connect.
password is the user's password.
Example
In the following query, we are creating a user named 'sample' who can only connect from the 'localhost' host and sets their password as '123456'. Make sure that you have logged in with a user with admin privileges (root) 
CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';
Output
The output will be displayed as 
Query OK, 0 rows affected (0.12 sec)
Verification
You can verify the list of users using the following query 
SELECT USER FROM MySQL.USER;
The table will be displayed as shown below 
USER
mysql.infoschema
mysql.session
mysql.sys
myuser
openkm
root
sample
Granting Privileges in MySQL
You can grant all privileges to the created user using the GRANT ALL statement. This allows you to give specific permissions to users for actions like accessing databases, tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.
Syntax
Following is the syntax to grant all privileges in MySQL 
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
Example
The following query grants the user 'sample' full privileges to perform any action on any database or table when connecting from the 'localhost' host, giving complete control over the MySQL server locally 
GRANT ALL PRIVILEGES ON * . * TO 'sample'@'localhost';
Output
The output will be displayed as 
Query OK, 0 rows affected (0.02 sec)
Logging as a Different User
To log in as a different user in MySQL, you should first exit the current MySQL session if you are already logged in and then execute the command -u user_name -p in your system's command prompt or terminal, not within the MySQL shell itself.
Example
Here, we are executing the -u sample -p command. After running the command, you will be prompted to enter the password for the specified user. Enter the correct password to log in as shown below 
mysql -u sample -p
Enter password: ******
Output
This will log you in as the sample user with the appropriate privileges and permissions as shown below 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL
﻿
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
﻿
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
﻿
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
The Expire Clause
If you use the expire clause, the old password (current password) will expire immediately and the user need to choose new password at first connection.
Example
Here, we are first removing the existing user 'sample'@'localhost' 
DROP user sample@localhost;
We are now creating a new user 'sample'@'localhost' with the password 'MyPassword' while immediately expiring the password, forcing the user to set a new password upon the first login 
CREATE USER 'sample'@'localhost' 
IDENTIFIED BY 'MyPassword' PASSWORD EXPIRE;
Now, if you log in as a newly created user, an error will be generated. So, to login as newly created user, open command prompt browse through bin folder of the MySQL directory and execute the following command 
C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u sample@localhost -p
Enter password: **********
Any MySQL command execution at this point will trigger an error message as shown below 
select now(); 
The output obtained is as shown below 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Since the password is expired, the above error message is generated. To make this right we need to change (reset) the password using the following command 
SET PASSWORD='passwordtest'; 
Following is the output produced 
Query OK, 0 rows affected (0.34 sec)
You can also set an interval for the EXPIRE clause to implement periodic password changes as shown below 
DROP user sample@localhost;
CREATE USER 'sample'@'localhost'
   IDENTIFIED BY 'MyPassword'
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
After executing the above code, we get the following output 
Query OK, 0 rows affected (0.20 sec)
User Comment
You can add comments to the user while creating a user in MySQL using the COMMENT clause. This provides additional information or context about the user.
Example
In the following example, we are first removing the existing 'sample'@'localhost' user. Then, we are creating a new 'sample'@'localhost' user while adding a comment to describe the user 
drop user sample@localhost;
CREATE USER 'sample'@'localhost' COMMENT 'Sample information';
Output
The result obtained is as shown below 
Query OK, 0 rows affected (0.10 sec)
Verification
You can verify the attributes and comments info using the SELECT query given below 
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER='sample' AND HOST='localhost';
The result produced is as shown below 
USER
HOST
ATTRIBUTE
sample
localhost
{"comment": "Sample information"}
User Attribute
You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a user account. These attributes can store additional information about the user.
Example
In here, we are first removing the existing 'sample@localhost' user. Then, we are creating a new 'sample'@'localhost' user with attributes 'attr1' and 'attr2' set to 'val1' and 'val2,' respectively, associated with the user account 
DROP user sample@localhost;
CREATE USER 'sample'@'localhost' 
ATTRIBUTE '{"attr1": "val1", "attr2": "val2"}'; 
The result obtained is as shown below 
Output
Query OK, 0 rows affected (0.09 sec)
Verification
You can verify the attributes and comments info using the SELECT query given below 
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER='sample' AND HOST='localhost';
The result obtained is as shown below 
USER
HOST
ATTRIBUTE
sample
localhost
{"attr1": "val1", "attr2": "val2"}
The IF NOT EXISTS Clause
If you try to create a user with an existing name, an error will be generated. To prevent this error and ensure the user is created only if it does not already exist, you can use the "IF NOT EXISTS" clause.
Example
In the example below we are creating a user 'sample@localhost' without the "IF NOT EXISTS" clause 
CREATE USER 'sample@localhost';
We can see in the below output that an error is generated 
ERROR 1396 (HY000): Operation CREATE USER failed for 'sample@localhost'@'%'
However, if we use the "IF NOT EXISTS" clause along with the CREATE statement, a new user will be created, and if a user with the given name already exists, the query will be ignored 
CREATE USER IF NOT EXISTS 'sample@localhost';
Following is the output obtained 
Query OK, 0 rows affected, 1 warning (0.01 sec)
Creating User Using a Client Program
In addition to creating a user into MySQL Database using the MySQL query, we can also create using a client program.
Syntax
Following are the syntaxes to create a MySQL user in various programming languages 
PHPNodeJSJavaPython
The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. To create a user in MySQL, we need to execute the CREATE USER statement using this function as 
$sql = "CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
$mysqli->query($sql);
Example
Following are the client programs to create an user in MySQL 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "CREATE USER 'Revathi'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
if($mysqli->query($sql)){
   printf("User created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();     
Output
The output obtained is as follows 
User created successfully...!
﻿
﻿
﻿
﻿
﻿
﻿

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Create Users

The MySQL CREATE USERS Statement

Syntax

Example

Output

Verification

Granting Privileges in MySQL

Syntax

Example

Output

Logging as a Different User

Example

Output

The Expire Clause

Example

User Comment

Example

Output

Verification

User Attribute

Example

Output

Verification

The IF NOT EXISTS Clause

Example

Creating User Using a Client Program

Syntax

Example

Output