Data security is built on mutual trust between the database and those who access it. This trust is secured through a process called authentication that happens as soon as a user attempts to log in. Simply put, if the user has the correct credentials – for example a valid username and password – they’re given access to the database. If not, they’re kept out.
While this simple handshake between client and server is often taken for granted, ensuring the process happens at the level of security you need is another story. This guide will introduce you to how MySQL handles authentication, a few examples of authentication methods, and how to secure your database properly with good authentication and authorization policies.
In this article, you will learn about:
Authentication vs. Authorization
Both authentication and authorization are common terms within data security. While both play an important role in clients being able to securely connect to servers and access databases, these terms are not interchangeable.
- Authentication (or “authn”) is the process of client verification, determining whether or not a certain user should be able to connect to a server.
- Authorization (or “authz”) is the process of providing a successfully-authenticated client with the correct privileges to interact with the database.
For example, a database administrator and a sales representative can both be authenticated, but the database admin will have much higher authorization to manage the database. The sales rep only needs enough access to do their job such as adding, updating, or exporting data to make sales calls.
Understanding the relationship between authentication and authorization and their role in data security is crucial to keeping your database safe. These are the keys and map to your data kingdom so being aware of who gets access to them will make or break your security architecture.
Authentication in MySQL
While MySQL authentication is a straightforward process, understanding how it works is important to limit unnecessary authentication errors. Once a MySQL server starts up, it begins authenticating users by referencing the mysql.user table.
There are two main columns MySQL uses to perform these checks while sorting them from highest to lowest priority: Host, which acts as the primary sort value; and User, which acts as a secondary sort value if two accounts originate from the same host. Specific, literal values are given higher priority over wildcard (%) and blank/undefined values (for example, anonymous accounts).
When a user attempts to connect to the server, MySQL authenticates the connection by traversing the list from top to bottom, selecting the first match it finds. Whether or not the connection authenticates is based on a successful handshake – the username and its password match – and if the authentication methods execute properly. If the handshake or method fails, MySQL stops checking until the user makes another attempt.
MySQL Authentication Method Examples
Though the process of MySQL authentication is fundamentally the same regardless of the MySQL database, different databases (or even different user accounts) have different methods, depending on preference, level of security, and other factors the managing organization decides.
Here are a few examples of common MySQL authentication methods:
Authentication’s Role in Data Access Security
Authentication plays a pivotal role in data access security; without authentication, there is no security–or, conversely, there’s so much security that no one can access the information inside, rendering the database useless. Therefore, good data security practices are a result of finding the right balance between authentication, authorization, and building a security architecture around those concepts.
With that in mind, it’s important to ensure that your authentication methods are working properly. A faulty lock isn’t very useful when keeping out intruders. The same applies if your database is using broken or outdated methods that don’t work or are easily countered by bad actors.
For example, MySQL users may accidentally attempt to authenticate with blank User information, marking them as anonymous users. While anonymous users have very few privileges if any, an accidental authentication is still problematic; the user won’t be able to access the information they were supposed to. Conversely, if a user successfully authenticates with the wrong authorization, they’ll be given access to information they shouldn’t be.
Both problems can be solved by catching unwanted inputs or using robust credentials like strong, unique passwords respectively.
MySQL Authentication Best Practices
In addition to catching unwanted inputs and minimizing the ability to authenticate access incorrectly, there are a few other MySQL authentication best practices to consider.
1. Use Strong Passwords and Encryption
Passwords and encryption are what define the keys that make up database authentication. The stronger the passwords and the more complex the encryption, the more secure the authentication process becomes.
Ensure all of your users have robust passwords of at least 15 characters with a mix of alphanumerics and symbols and that these strings don’t appear in common dictionaries. If you’re using MySQL Enterprise, some built-in encryption tools include public/private key generation and digital signature verification/validation among others.
If you want to add an extra layer of protection, make sure the users with the highest privileges have the strongest passwords, encryption, and authentication methods.
2. Grant Users the Least Amount of Authorization Necessary
Also known as the principle of least privilege, make sure that each user is given only the privileges they need to do their job. Like in our initial example, the sales representative doesn’t need access to administrative commands that could change (or accidentally delete) the entire database. These controls are best left in the hands of capable administrators who should be the only ones with that kind of privilege.
Moreover, be sure to keep user privileges up-to-date with their roles within your organization. If they change departments or functions, their access and authorization needs may change as well. It’s all too common for users to have more privileges than they need so be sure to address this often-overlooked security concern.
3. Perform Regular Data Security Audits
Keep access logs to track which users are connecting to your servers, accessing the information within your database, and seeing what they’re doing with it. Not only is this a useful practice to identify misuse and security loopholes, regular auditing is also necessary to remain within compliance with data security laws such as GDPR, CPRA/CCPA, and HIPAA.
With regards to authentication policies, regular audits can give you insight on which authentication methods are working, if users are logging in correctly (i.e. the methods are catching and handling faulty inputs), and whether they have the proper authorization and encryption for their position.
MySQL Security With Satori
Satori helps organizations streamline access to sensitive data stored on MySQL, as well as provision MySQL production servers access. In addition, Satori continuously discovers and maps sensitive data on your MySQL servers, and allows you to enable policies such as data masking in a fast and simple way.
