Tech Journal Back to Tech Journal

How are users and permissions stored in MySQL servers?

The privileges are stored in the database named mysql - just select on the tables to see them.

In the case where you are trying to define another user and grant him access to a database, the most common reason to have access denied, is to have the wrong allow host defined for the user. Remember, the user stupid@silly isn't the same as stupid@localhost on the same machine! (It's all a matter of how they access the server.)

To add a user, and grant only certain priviliges:

> grant select,insert,update on databasename.* to username@host identified by 'password';

To force a reload of the privileges table (which must be done after modifying the table, before the the changes will have an effect):

> flush privileges;

Back to Tech Journal