Securing Your MariaDB Database: User Permissions and Best Practices

User Management Statements

• SET PASSWORD
  This statement allows you to set or change the password for a user account.
• REVOKE
  This statement takes away previously granted privileges from a user.
• GRANT
  This statement assigns specific privileges to a user. Privileges control what actions a user can perform on the database, such as selecting data, inserting new rows, or creating tables.
• DROP USER
  This statement removes a user account from the database.
• CREATE USER
  This statement creates a new user account in the database. You specify the username and optionally the host from which the user can connect. Passwords are typically set with a separate statement.

The mysql.user Table

MariaDB stores information about user accounts in a special table called mysql.user. This table holds details like the username, host, and a hashed version of the password. You cannot directly modify this table, but the user management statements mentioned earlier interact with it behind the scenes.

• Least Privilege
  Grant users only the minimum privileges they need to perform their tasks. This minimizes the damage if a user account is compromised.
• Strong Passwords
  Always enforce strong passwords for your user accounts to prevent unauthorized access.

---

Create a User

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'StrongPassword123';

This code creates a new user named new_user who can connect from the local machine (localhost). It sets the password to StrongPassword123 (replace with a strong password).

Grant Privileges

GRANT SELECT, INSERT ON my_database.* TO 'new_user'@'localhost';

This code grants the new_user permission to select data (SELECT) and insert new rows (INSERT) into any table within the database named my_database.

Revoke Privileges

REVOKE INSERT ON my_database.users FROM 'new_user'@'localhost';

This code removes the INSERT privilege for the new_user on the users table specifically within the my_database.

Set Password

SET PASSWORD FOR 'new_user'@'localhost' = PASSWORD('NewStrongerPassword');

This code changes the password for the new_user to NewStrongerPassword. Remember to use a strong and unique password.

Check User Grants

SHOW GRANTS FOR 'new_user'@'localhost';

This code displays all the privileges currently granted to the new_user connecting from the local machine.

• These examples use basic privileges. MariaDB offers a wide range of privileges you can grant or revoke based on your specific needs.
• Remember to replace placeholders like new_user, StrongPassword123, and my_database with your desired values.

---

1. Roles
   

MariaDB supports roles, which are named sets of privileges. You can assign roles to users instead of directly granting individual privileges. This can simplify administration and provide a more modular approach to access control.

1. Authentication Plugins
   

MariaDB allows customization of user authentication through plugins. These plugins can define different methods for verifying user credentials beyond the traditional username and password combination.

1. External User Management
   

For larger deployments, you might integrate MariaDB with an external user directory service like LDAP or Active Directory. This allows you to manage user accounts centrally and leverage existing authentication mechanisms.

Choosing the Right Approach

The best approach depends on your specific needs and the complexity of your database environment.

• For enhanced security or integration with existing authentication systems, consider authentication plugins or external user management.
• If you need more granular control over access or have many users, roles can provide better organization.
• For simple setups with a few users, directly managing users with CREATE USER and GRANT statements might suffice.