How to Change Users in MariaDB from the Command Line (Secure Methods)

However, there are workarounds to achieve a similar outcome:

This approach involves opening separate terminal windows and connecting to MariaDB with different usernames in each.

• Session 1 (initial user):

mysql -u username1 -p  # Replace 'username1' with your actual username

• Session 2 (different user):

Open a new terminal window and run:

mysql -u username2 -p  # Replace 'username2' with your desired username

This allows you to work with different user accounts simultaneously.

system command (Less Secure):

Warning: This method is less secure as it involves typing the password within the MariaDB session.

mysql -u current_user -p  # Replace 'current_user' with your actual username

# Enter your password when prompted

USE your_database;  # Replace 'your_database' with the database you want to use

system 'mysql -u desired_user -p your_database';  # Replace 'desired_user' with the target username and 'your_database' with the database

# Enter the password for 'desired_user' when prompted (less secure)

This code first connects to MariaDB with your current user. Then it uses the system command to launch another mysql session with the desired username and database.

1. Using GRANT to extend privileges:

This approach focuses on managing user permissions instead of switching users. If a user requires additional access for specific tasks, you can grant them the necessary privileges without needing to switch accounts entirely.

Here's an example:

mysql -u current_user -p  # Replace 'current_user' with your actual username

# Enter your password when prompted

USE your_database;  # Replace 'your_database' with the database

GRANT SELECT, INSERT ON your_table TO desired_user;  # Replace 'desired_user' and 'your_table' with specifics

FLUSH PRIVILEGES;

This code grants the user desired_user the SELECT and INSERT privileges on the table your_table. This allows them to perform specific actions within the database without needing to switch accounts.

1. Advanced: Using mysql_native_password plugin (MariaDB 10.4+):

Note: This approach requires MariaDB version 10.4 or later and involves some technical knowledge.

MariaDB 10.4 introduced the ability to configure multiple authentication plugins per user account. This allows for a more secure transition strategy. Here's a general idea (consult MariaDB documentation for specifics):

1. Configure two authentication plugins for a user:

   • Existing method (e.g., mysql_native_password).
   • New, more secure method (e.g., sha256_password).

This allows a gradual migration to a more secure authentication method while still providing a temporary fallback option.