Create MySQL user and grant permissions

Create MySQL user and grant permissions

Why create specific users?

It's a security best practice to create a specific user for each application or database, instead of using root for everything. This limits permissions and reduces risk in case of compromise.

Method 1: From phpMyAdmin

Step 1: Access phpMyAdmin

• Log in to phpMyAdmin
• Log in with a user that has admin permissions (usually root)

Step 2: Go to User accounts

• Click on the User accounts tab

Step 3: Add new user

• Click Add user account

Step 4: Complete user information

• Username: Enter the username (e.g., app_user)
• Host name: Select localhost (for local connections) or % (for connections from any host)
• Password: Enter a secure password
• Confirm password: Enter the same password

Step 5: Grant privileges

You can grant privileges in two ways:

Option A: Global privileges

• Check the privileges you want to grant globally
• Generally not recommended for application users

Option B: Privileges on specific database (recommended)

• Select the database in "Database for user account"
• Or create the account first and then grant privileges on the database

Common privileges:

• ✅ SELECT: Read data
• ✅ INSERT: Insert data
• ✅ UPDATE: Update data
• ✅ DELETE: Delete data
• ✅ CREATE: Create tables
• ✅ DROP: Delete tables
• ✅ ALTER: Modify structure
• ✅ INDEX: Create indexes

For a typical web application:

• Check: SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER
• Don't check: DROP (unless necessary)

Step 6: Save

• Click Go or Continue

Method 2: From terminal

Step 1: Connect to MySQL

mysql -u root -p

Step 2: Create the user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'secure_password';

Example:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'MyPassword123!';

Step 3: Grant privileges on a database

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Example:

GRANT ALL PRIVILEGES ON my_webapp.* TO 'app_user'@'localhost';

Step 4: Apply changes

FLUSH PRIVILEGES;

Grant specific privileges

Instead of ALL PRIVILEGES, you can grant only necessary ones:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER 
ON database_name.* 
TO 'username'@'localhost';

Example:

GRANT SELECT, INSERT, UPDATE, DELETE 
ON my_webapp.* 
TO 'app_user'@'localhost';

This grants only read and write permissions, without being able to delete tables.

Allow connection from any host

If you need the user to connect from another server:

CREATE USER 'username'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';
FLUSH PRIVILEGES;

⚠️ Warning: Allowing connections from % (any host) is less secure. Only use it if necessary and make sure you have a firewall configured.

Verify created users

To see all users:

SELECT user, host FROM mysql.user;

To see privileges of a specific user:

SHOW GRANTS FOR 'username'@'localhost';

Revoke privileges

If you need to remove privileges:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';
FLUSH PRIVILEGES;

Or revoke specific privileges:

REVOKE DELETE ON database_name.* FROM 'username'@'localhost';
FLUSH PRIVILEGES;

Delete a user

If you need to completely delete a user:

DROP USER 'username'@'localhost';
FLUSH PRIVILEGES;

⚠️ Warning: This will permanently delete the user and all their privileges.

Common issues

"Access denied" error when creating user

• Verify that you have admin permissions (root)
• Verify that you're using the correct user

User can't connect

• Verify that username and password are correct
• Verify that the host is correct (localhost vs %)
• Verify that privileges are granted correctly

User doesn't have sufficient permissions

• Verify granted privileges with SHOW GRANTS
• Grant additional necessary privileges

Security tips

• ✅ Create a specific user for each application
• ✅ Grant only necessary privileges (principle of least privilege)
• ✅ Use strong passwords
• ✅ Avoid using % as host unless necessary
• ✅ Regularly review users and their privileges
• ✅ Delete users that are no longer used

Need help?

If you're having trouble creating users or configuring permissions, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host