Configure MySQL User Privileges on Ubuntu VPS: Complete Guide

Understanding MySQL User Privileges for VPS Security

Database security starts with proper user privilege management. When you configure MySQL user privileges correctly on your Ubuntu VPS, you limit each application's access to only the data it needs.

This reduces attack surface and prevents accidental data loss.

MySQL uses a hierarchical permission system. Global privileges affect all databases. Database-specific privileges control access to individual databases. Table and column privileges provide granular control over specific data sets.

Prerequisites and Initial Setup

You'll need root access to your Ubuntu VPS and MySQL already installed. Connect to your server via SSH and verify MySQL is running:

sudo systemctl status mysql

If MySQL isn't running, start it:

sudo systemctl start mysql
sudo systemctl enable mysql

Log into MySQL as root to begin user management:

sudo mysql -u root -p

For Hostperl VPS hosting customers, we ensure MySQL is properly configured and secured during server provisioning. This gives you a solid foundation for user privilege management.

Creating MySQL Users with Specific Privileges

Never use the root user for applications. Create dedicated users with minimal necessary privileges instead.

Here's how to create a user for a WordPress application:

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';

For users connecting from specific IP addresses:

CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'AnotherSecurePassword!';

Use wildcard for subnet access (use cautiously):

CREATE USER 'devuser'@'192.168.1.%' IDENTIFIED BY 'DevPassword456!';

Each user should have a strong password. MySQL 8.0 defaults to the caching_sha2_password plugin. This provides better security than the legacy mysql_native_password.

Granting Database-Level Privileges

Grant privileges systematically. Start with database-level permissions for most applications.

Give a user full control over a specific database:

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wpuser'@'localhost';
FLUSH PRIVILEGES;

For read-only access to reporting databases:

GRANT SELECT ON analytics_db.* TO 'reporter'@'localhost';
FLUSH PRIVILEGES;

Development environments often need broader access:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON dev_db.* TO 'devuser'@'192.168.1.%';
FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command reloads the grant tables. This makes changes take effect immediately. Skip this only if you're making multiple changes in sequence.

Table and Column-Level Permission Control

Some applications need granular control. Grant privileges on specific tables when database-wide access is too broad:

GRANT SELECT, INSERT, UPDATE ON ecommerce_db.orders TO 'orderapp'@'localhost';
GRANT SELECT ON ecommerce_db.customers TO 'orderapp'@'localhost';
FLUSH PRIVILEGES;

Column-level privileges work for sensitive data fields:

GRANT SELECT (customer_id, order_date, total), UPDATE (status) ON ecommerce_db.orders TO 'statusupdater'@'localhost';
FLUSH PRIVILEGES;

This approach works well for third-party integrations that only need specific data access. Payment processors might need order totals but not customer addresses.

Managing Administrative Privileges Safely

Some users need administrative capabilities without full root access. Create backup users with appropriate privileges:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPassword789!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

For users who need to create databases but not modify system tables:

CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY 'AdminPassword456!';
GRANT CREATE, DROP, ALTER, INDEX, REFERENCES ON *.* TO 'dbadmin'@'localhost';
FLUSH PRIVILEGES;

Never grant FILE, PROCESS, SUPER, or SHUTDOWN privileges unless absolutely necessary. These provide system-level access that can compromise security.

Our MySQL backup automation guide shows you how to use properly privileged users for automated database backups.

Viewing and Auditing Current Privileges

Regular privilege audits prevent security gaps. View all users and their hosts:

SELECT user, host FROM mysql.user;

Check specific user privileges:

SHOW GRANTS FOR 'wpuser'@'localhost';

See all grants for the current user:

SHOW GRANTS;

Identify users with dangerous privileges:

SELECT user, host FROM mysql.user WHERE Super_priv = 'Y' OR File_priv = 'Y';

Run these queries monthly. Document who has administrative access and why. Remove unused accounts promptly.

Revoking and Modifying User Privileges

Remove privileges when they're no longer needed. Revoke specific privileges:

REVOKE DELETE ON wordpress_db.* FROM 'wpuser'@'localhost';
FLUSH PRIVILEGES;

Remove all privileges for a database:

REVOKE ALL PRIVILEGES ON old_db.* FROM 'devuser'@'192.168.1.%';
FLUSH PRIVILEGES;

Delete users completely when projects end:

DROP USER 'olduser'@'localhost';

The DROP USER command automatically removes all privileges and the user account. Always verify the user is no longer needed before running this command.

Securing Remote Database Connections

Remote connections require extra security considerations. Always use specific IP addresses when possible:

CREATE USER 'remoteapp'@'203.0.113.50' IDENTIFIED BY 'RemotePassword123!';

Configure MySQL to bind to specific interfaces in /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address = 10.0.1.5

Use SSL for remote connections. Generate SSL certificates and require them:

CREATE USER 'ssluser'@'%' IDENTIFIED BY 'SSLPassword456!' REQUIRE SSL;

Check if SSL is working:

SHOW STATUS LIKE 'Ssl_cipher';

If you're migrating to a new VPS, our VPS migration planning guide covers database user privilege transfer as part of the complete migration process.

Common Privilege Configuration Mistakes

Avoid granting ALL PRIVILEGES unless absolutely necessary. Many applications only need SELECT, INSERT, UPDATE, and DELETE privileges.

Limit CREATE and DROP privileges to administrative users.

Don't use '%' as the host unless you understand the security implications. This allows connections from any IP address. Attackers can exploit this if credentials are compromised.

Never store database passwords in plaintext files. Use environment variables or secure credential management systems instead.

Web applications should connect using dedicated users, not root.

Watch for privilege escalation through DEFINER rights on stored procedures and functions. Users can gain privileges they don't directly have through these objects.

FAQ

What's the difference between GRANT and FLUSH PRIVILEGES?

GRANT assigns privileges to users. FLUSH PRIVILEGES reloads the grant tables to make changes take effect immediately. FLUSH PRIVILEGES is required after direct manipulation of mysql.user table but not after GRANT statements in newer MySQL versions.

Can I grant privileges to users that don't exist yet?

No, you must create the user first with CREATE USER before granting privileges. Attempting to grant privileges to non-existent users will result in an error.

How do I allow a user to grant privileges to other users?

Add WITH GRANT OPTION to your GRANT statement: GRANT SELECT ON db.* TO 'user'@'host' WITH GRANT OPTION. Use this carefully as it allows privilege delegation.

What happens to user privileges when I drop a database?

Database-specific privileges remain in the mysql.db table even after dropping the database. Clean up these orphaned privileges manually with REVOKE statements.

How can I create a user that can only connect during business hours?

MySQL doesn't have built-in time-based access controls. Implement this at the application level or use triggers with custom logic to enforce time-based restrictions.