How to Control Access to Your MySQL Data - dummies

By Steve Suehring, Janet Valade

You need to control access to the information in your MySQL 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.

Fortunately, 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:

  • An account name

  • A hostname — the machine from which the account can access the MySQL server

  • A password

  • A set of privileges

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’s 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 privileges 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. You can set up an account with all privileges, no privileges, 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 change that information.

When a user attempts to connect to MySQL and execute a statement, 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’s 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 privileges to execute the specified statement. If it does, MySQL executes the statement.

Any statement that you send to MySQL can fail either because the connection is rejected in the first step or because the statement isn’t permitted in the second step. An error message is returned to help you identify the source of the problem.