How to Control Access to Your MySQL Data?

You need to control access to the information in your database. You need to decide who can see the data and who can change it. Imagine what would happen if your competitors could change the information in your online product catalog or copy your list of customers — you’d be out of business in no time flat. Clearly, you need to guard your data. MySQL provides a security system for protecting your data. No one can access the data in your database without an account. Each MySQL account has the following attributes:

• A name
• A hostname — the machine from which the account can access the MySQL server
• A password
• A set of permissions

To access your data, someone must use a valid account name and know the password associated with that account. In addition, that person must be connecting from a computer that is permitted to connect to your database via that specific account.
After the user is granted access to the database, what he or she can do to the data depends on what permissions have been set for the account. Each account is either allowed or not allowed to perform an operation in your database, such as SELECT, DELETE, INSERT, CREATE, or DROP. The settings that specify what an account can do are privileges, or permissions. You can set up an account with all permissions, no permissions, or anything in between. For instance, for an online product catalog, you want the customer to be able to see the information in the catalog but not be able to change it. When a user attempts to connect to MySQL and execute a query, MySQL controls access to the data in two stages:

1. Connection verification: MySQL checks the validity of the account name and password and checks whether the connection is coming from a host that is allowed to connect to the MySQL server by using the specified account. If everything checks out, MySQL accepts the connection.
2. Request verification: After MySQL accepts the connection, it checks whether the account has the necessary permissions to execute the specified query. If it does, MySQL executes the query. Any query that you send to MySQL can fail either because the connection is rejected in the first step or because the query is not permitted in the second step. An error message is returned to help you identify the source of the problem.