How to Restrict MySQL User Access to a Single Database?

Secure MySQL User Access: Limiting to a Single Database

Database security is paramount. This guide demonstrates how to create a MySQL user account with access restricted solely to a specific database, a crucial task for database administrators.

First, establish the target database using: CREATE DATABASE dbTest;

User Account Creation and Privilege Assignment

Create the new user account with the command:

<code class="language-sql">CREATE USER 'new_user'@'localhost';</code>

Replace 'new_user' with your desired username. 'localhost' restricts access to connections originating from the same server; adjust this to a specific IP address or '%' for all hosts if needed.

Next, grant the user full privileges on the dbTest database:

<code class="language-sql">GRANT ALL PRIVILEGES ON dbTest.* TO 'new_user'@'localhost' IDENTIFIED BY 'strong_password';</code>

Remember to replace 'strong_password' with a robust, unique password.

Command Breakdown:

• GRANT: The core command for assigning privileges.
• ALL PRIVILEGES: Grants all standard database privileges (SELECT, INSERT, UPDATE, DELETE, etc.). Consider using more specific grants for enhanced security if needed.
• dbTest.*: Targets all tables and objects within the dbTest database.
• TO 'new_user'@'localhost': Specifies the user and the allowed host.
• IDENTIFIED BY 'strong_password': Sets the user's password.

This process ensures the new user can only access and manipulate data within the designated dbTest database, significantly improving your database's security posture. Always remember to regularly review and adjust user privileges as needed.

The above is the detailed content of How to Restrict MySQL User Access to a Single Database?. For more information, please follow other related articles on the PHP Chinese website!