Creating Secure MySQL User Accounts

Introduction

In this project, you will learn how to create a new user in a MySQL database. This is a fundamental skill for database management and administration, as creating and managing user accounts is a crucial aspect of securing and controlling access to your database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access the MySQL database using the sudo command without a password
• How to create a new regular user with a specific username and password
• How to revoke all privileges from the newly created user to disallow remote login

🏆 Achievements

After completing this project, you will be able to:

• Securely create and manage user accounts in a MySQL database
• Understand the importance of user access control in database management
• Apply the skills learned in this project to your own database projects and administration tasks

Access MySQL

In this step, you will learn how to access the MySQL database using the sudo command without any password.

1. Open a terminal on your system.
2. Start the MySQL service by running the following command:

sudo service mysql start

3. Access the MySQL database by running the following command:

sudo mysql

This will allow you to access the MySQL database without a password.

Create a User

In this step, you will learn how to create a regular user named Jane with a password of 123456, and disallow remote login.

1. In the MySQL prompt, run the following SQL script:

CREATE USER 'Jane'@'localhost' IDENTIFIED BY '123456';
REVOKE ALL PRIVILEGES ON *.* FROM 'Jane'@'localhost';

This script will create a new user named Jane with the password 123456, and revoke all privileges from the user.

Verify the User Creation

In this step, you will learn how to verify that the user Jane has been created successfully.

1. In the MySQL prompt, run the following SQL command to switch to the mysql database:

USE mysql;

2. Run the following SQL command to view the list of users:

SELECT user, host FROM user;

This will display the list of users, including the newly created user Jane.

You should see the following output:

MariaDB [mysql]> SELECT user, host FROM user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| Jane        | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)

Your project is now complete! You have successfully created a new user named Jane with the password 123456 and disallowed remote login.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.