Managing & Using MySQL, 2nd Edition

Database Security

Database security controls access to MySQL data via the MySQL database engine. It does not address access to that data through direct access to the database files; system security is responsible for protecting the files.

To MySQL, a user is any connection authenticated to the database engine. In a development environment, MySQL users will likely correspond to developers. In other words, each developer has a personal user ID and password for MySQL authentication during development. This MySQL user has no inherent relationship to the host system user. In other words, MySQL does not use your Unix ID; it uses its own internal user list.^[1]

In a production environment, a user is likely to be an application. The DBA creates a user ID and password to support the application, and database security controls how that application is allowed to interact with MySQL. The application can then pass on its rights to individual users of the application by acting on their behalves to access MySQL.

For example, you might have a database storing the family CD library with a web interface. A single MySQL user—the application—has access to the read, add, delete, and update records in that database. The application may have its own internal security structure for differentiating family members who can make changes and friends who can just read. It cannot, however, give users powers it does not have, such as creating new tables. MySQL knows nothing about these application-specific users—they ...