Create new user in MySQL and give it full access to one database

Creating a New MySQL User with Database-Specific Privileges

Learn how to securely create a new user in MySQL and grant them full access to a single, specified database, ensuring proper access control.

Managing user accounts and their permissions is a fundamental aspect of database administration. When working with MySQL, it's crucial to create dedicated users for applications or specific tasks rather than using the root account. This practice enhances security by adhering to the principle of least privilege, limiting potential damage if an account is compromised. This article will guide you through the process of creating a new MySQL user and granting them comprehensive access to a single database.

Understanding MySQL User Accounts and Privileges

MySQL's security model is based on users and privileges. A user account is defined by a username and the host from which they can connect (e.g., 'myuser'@'localhost' or 'myuser'@'%'). Privileges determine what actions a user can perform (e.g., SELECT, INSERT, UPDATE, DELETE) and on which database objects (e.g., specific databases, tables, or columns). Granting ALL PRIVILEGES on a specific database means the user can perform any operation within that database but not on others.

graph TD
    A[Connect to MySQL as Root] --> B{CREATE USER 'username'@'host' IDENTIFIED BY 'password';}
    B --> C{FLUSH PRIVILEGES;}
    C --> D{GRANT ALL PRIVILEGES ON `database_name`.* TO 'username'@'host';}
    D --> E{FLUSH PRIVILEGES;}
    E --> F[New User with Database Access Created]

Flowchart for creating a new MySQL user and granting database privileges.

Step-by-Step: Creating a User and Granting Database Access

The process involves connecting to your MySQL server, creating the user, and then assigning the necessary privileges. It's important to replace placeholders like username, password, host, and database_name with your actual values.

1. Connect to MySQL as Root

First, you need to log in to your MySQL server as a user with sufficient privileges (typically root) to create new users and grant permissions. You can do this via the command line:

2. Create the New User

Use the CREATE USER statement to define your new user. Remember to choose a strong password and specify the host from which the user will connect. 'localhost' is common for local applications, while '%' allows connections from any host (use with caution).

3. Grant Privileges to a Specific Database

Now, grant ALL PRIVILEGES on your target database to the newly created user. The * after database_name. signifies all tables within that database. The backticks ` around the database name are important if your database name contains special characters or is a reserved keyword.

4. Flush Privileges

After making changes to user permissions, it's good practice to FLUSH PRIVILEGES to reload the grant tables and ensure the changes take effect immediately.

5. Verify User Access (Optional but Recommended)

Log out as root and try connecting as the new user to confirm that they can access only the intended database and perform operations as expected.

mysql -u root -p

-- 1. Create the new user
CREATE USER 'my_app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- 2. Grant all privileges on a specific database
GRANT ALL PRIVILEGES ON `my_application_db`.* TO 'my_app_user'@'localhost';

-- 3. Flush privileges to apply changes
FLUSH PRIVILEGES;

-- Optional: Verify access
-- EXIT;
-- mysql -u my_app_user -p
-- USE my_application_db;
-- SHOW TABLES;
-- SELECT CURRENT_USER();

SQL commands to create a user and grant database-specific privileges.

Revoking Privileges and Deleting Users

Just as important as granting privileges is the ability to revoke them or remove users when they are no longer needed. This helps maintain a clean and secure database environment.

-- Revoke all privileges from a user on a specific database
REVOKE ALL PRIVILEGES ON `my_application_db`.* FROM 'my_app_user'@'localhost';

-- Delete a user
DROP USER 'my_app_user'@'localhost';

-- Flush privileges after changes
FLUSH PRIVILEGES;

SQL commands for revoking privileges and deleting a user.