MySQL – Create Users
Table of content
- The MySQL CREATE USERS Statement
- Granting Privileges in MySQL
- Logging as a Different User
- The Expire Clause
- User Comment
- User Attribute
- The IF NOT EXISTS Clause
- Creating User Using a Client Program
”;
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 FilesMySQLMySQL Server 8.0bin> 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 −
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);
To create a user using a NodeJS program, we need to execute the CREATE USER statement using the function named query() as −
sql= " CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ''password''; con.query(sql, function (err, result) { if (err) throw err; console.log(result); });
To create a user in a MySQL database using Java program, we need to execute the CREATE USER statement using the JDBC function named execute() as −
sql = "CREATE USER ''USER_NAME''@LOCALHOST INDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY ''PASSWORD''"; statement.execute(sql);
The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. To create a user into a MySQL database, we need to execute the CREATE USER statement using this function as −
sql = "CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''"; cursorObj.execute(sql)
Example
Following are the client programs to create an user in MySQL −
$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...!
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating User sql = "CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456'';" con.query(sql); //List of users sql = "select user from MySQl.user;" con.query(sql, function(err, result){ if (err) throw err console.log(result) }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { user: ''mysql.infoschema'' }, { user: ''mysql.session'' }, { user: ''mysql.sys'' }, { user: ''root'' }, { user: ''sample'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CreateUsers { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''"; st.execute(sql); System.out.println("User ''Vivek'' created successfully...!"); }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
User ''Vivek'' created successfully...!
import mysql.connector # creating the connection object connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''textx'' ) # Create a cursor object for the connection cursorObj = connection.cursor() cursorObj.execute("CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''") print("User ''newUser'' is created successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
User ''newUser'' is created successfully.
”;