MySQL Connection Permission Management: A Practical Guide to Resolving Root User Access Restrictions in Non-sudo Environments

Background and Challenges of MySQL Connection Permission Issues

In MySQL database management practice, developers frequently encounter a typical problem: when using the mysql -u root -p command to connect to a local server, the system returns error ERROR 1698 (28000): Access denied for user 'root'@'localhost', while the sudo mysql -u root -p command succeeds. This permission restriction not only affects command-line operations but more seriously hinders database integration in integrated development environments like IntelliJ, as IDEs typically cannot directly invoke sudo privileges.

Root Cause Analysis

The core of this issue lies in MySQL's default authentication mechanism. In many modern MySQL installations, particularly on Debian/Ubuntu-based systems, the root user defaults to using the unix_socket or auth_socket plugin for authentication. This mechanism allows users with system root privileges to log into MySQL without a password, but regular users cannot connect through conventional methods even with the correct password. While this design enhances security, it creates inconvenience in development environments.

Best Practice Solution: Creating Dedicated Users

According to the best answer in the Q&A data (score 10.0), the safest and recommended solution is to create new database users with appropriate grants rather than directly modifying root user configurations. This approach follows the principle of least privilege, meeting development needs while maintaining system security.

Specific operational steps are as follows: First, log into the MySQL server with sudo privileges:

sudo mysql -u root

Then create a new user and set a password:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Next, grant the user access to specific databases:

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

Finally, flush privileges to make changes effective:

FLUSH PRIVILEGES;

After completing these steps, the new user can connect directly using the following command without sudo privileges:

mysql -u newuser -p

Alternative Solution: Modifying Root User Authentication Method

If using the root user for non-sudo connections is necessary, its authentication plugin can be modified. The Q&A data provides two methods:

Method 1: Using ALTER USER statement (score 5.4)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;

Method 2: Directly updating the user table (score 2.4)

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin IN ('unix_socket', 'auth_socket');
FLUSH PRIVILEGES;

Both methods change the root user's authentication from socket authentication to traditional password authentication, enabling it to accept password verification. Note that after modification, the MySQL service needs to be restarted for changes to take full effect.

Advanced User Management Strategies

For scenarios requiring more flexible configurations, the Q&A data also demonstrates a complete user reconstruction approach (score 4.2). This method involves deleting the existing root user and recreating it. Although more complex, it thoroughly resolves permission issues:

-- Delete existing root user
DROP USER 'root'@'localhost';

-- Create new root user (allowing connections from any host)
CREATE USER 'root'@'%' IDENTIFIED BY '';

-- Grant all privileges
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- Flush privileges
FLUSH PRIVILEGES;

After execution, verify user creation with SELECT User,Host FROM mysql.user;, then connect directly using mysql -u root.

Security Considerations and Practical Recommendations

When choosing a solution, convenience must be balanced against security:

1. Creating dedicated users is the best practice, limiting potential security risks and adhering to the principle of least privilege.
2. Modifying root authentication, while convenient, may reduce system security, especially in production environments.
3. Avoid using file permission modification commands like sudo chmod -R 755 /var/lib/mysql/, which not only fail to solve the problem but may introduce security vulnerabilities.
4. When granting privileges, precisely specify databases and operation scopes, avoiding wildcards like *.*.
5. Regularly audit user permissions to ensure no unnecessary access rights exist.

Integrated Development Environment Configuration

After resolving permission issues, configuring database connections in IntelliJ IDEA becomes straightforward:

1. Open the Database tool window
2. Add a new MySQL data source
3. Enter hostname (localhost), port (3306)
4. Use the newly created username and password
5. Test connection and apply configuration

With this configuration, developers can directly execute SQL queries and manage database structures within the IDE without switching between terminal and IDE, significantly improving development efficiency.

Conclusion

MySQL connection permission management is a fundamental yet critical aspect of database development. By creating dedicated users and configuring permissions appropriately, developers can achieve seamless database access across various environments while maintaining system security. The multiple solutions provided in this article each have applicable scenarios, and developers should choose the most suitable method based on specific needs and security requirements. Remember, good permission management not only solves current technical problems but also forms an important foundation for building secure, maintainable systems.