MySQL stores GRANTed privilege info in the mysql database. If your app uses a MySQL database, though, the last thing you want the app to do is directly manipulate that database. In fact, your app should need to hit the mysql database no more than once—when the app first connects to the MySQL server. But if the app isn't to read user permissions from the mysql database, where will it find them?

The application database should include ...

(i) a users table with login name, real name, encrypted password and whatever other identifying info the app requires,

(ii) a permissions table to track permissions for app/database functionalities,

(iii) a users_permissions "bridge" table which maps users to permissions.

(iv) optionally, app code that maps [iii] to the mysql table database.

A famous way of implementing app permissions is called role-based-access. A simple example can be found near the end of www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch05.pdf.

MySQL 8.0 has built-in role support.

Last updated 28 Aug 2024