MySQL for Python : Creating Users and Granting Access - Creating users in MySQL

To create a user in MySQL, our user account must have the universal CREATE USER privilege. In general, no user beyond the database administrator should have this as it allows the user to create, remove, rename, and revoke the privileges of users on the database.

Alternatively, if a user has universal INSERT privileges, that user can insert a new user and relevant data into the user table of the mysql database. This method is prone to error and can endanger the stability of the entire MySQL installation if something goes wrong. Therefore, we do not deal with it here.

When creating a user, we need to specify both the user's name or ID and the user's password. The basic syntax for user creation is as follows:

CREATE USER <userid>;

Breaking the statement up by its token, we first tell MySQL that we want to CREATE something. Then we clarify the object being created as a USER account. That account should be named with the given userid. An example of this statement is:

CREATE USER exemplar;

Note that if NO_AUTO_CREATE_USER is enabled in your MySQL configuration, this type of user creation will fail. This is particularly true in SQL_MODE.

While this statement will create a user, it is perhaps the least secure way to do so. MySQL offers two ways of securing user accounts at the time of account creation—passwords and host restrictions.

Forcing the use of a password

To force the use of a password, we need to declare one at the time of creating the user. To do this, we append an IDENTIFIED BY clause to the previous user creation syntax.

CREATE USER <userid> IDENTIFIED BY '<password>';

To use this for our exemplary user, the statement would read as follows:

CREATE USER 'exemplar' IDENTIFIED BY 'MoreSecurity';

This forces the use of a password to log in. In the first user creation statement, if the user did not offer a password, they would still be allowed access simply by using an existing user ID.

To reset a password for an account that is already created, use a SET PASSWORD statement:

SET PASSWORD FOR 'exemplar'@'localhost' = PASSWORD('dogcatcher')

Which hostname you use depends on how the account was created. See the next section for more.

Restricting the client's host

In addition to requiring a password, MySQL also provides the ability to restrict the host from which the login may come. This clarification comes immediately after userid.

CREATE USER <userid>@'<host name>' IDENTIFIED BY '<password>';

If we want the user exemplar to login only from the localhost, the machine on which MySQL is running—we would use the following command:

CREATE USER 'exemplar'@'localhost' IDENTIFIED BY 'MoreSecurity';

If we want the user to log in from http://www.sample.com/ only, we would change the preceding statement to this:

CREATE USER 'exemplar'@'sample.com' IDENTIFIED BY 'MoreSecurity';

In doing so, however, we make it impossible for them to log in from localhost. To ensure that the user can log in from any host, we need to issue two CREATE statements that can effectively create one user for the local host and one for the remote client.

To quantify the host for all hosts, we use MySQL's pattern matching in lieu of a hostname. In order to allow the user to truly login from any host then, we need to issue the following two commands:

CREATE USER 'exemplar'@'localhost' IDENTIFIED BY 'MoreSecurity';
CREATE USER 'exemplar'@'%' IDENTIFIED BY 'MoreSecurity';

You will recall that the first user creation statement did not specify the host to be used by the user. If the hostname is not specified in the CREATE statement, MySQL uses % by default.

The user's identity on a MySQL database is determined by their user ID and the hostname from which they log in. It is possible for these two items to match more than one row in the MySQL user tables. When that happens, MySQL uses the first match it finds.

As MySQL uses its own user tables to validate logins, it is worth asking why we don't simply modify MySQL's own tables instead of issuing CREATE statements. One can do this, and there are instructions for doing so in the MySQL documentation. However, doing so leaves one open to errors and possible data corruption.

If one corrupts the data in the user tables, it is possible to lose the ability to contact the database altogether. This would then require manually restarting MySQL using mysqld_safe:

mysqld_safe --skip-grant-tables &

This method works on Unix-based systems. Windows systems are more complex.