6.3.2 Adding User Accounts

To create MySQL accounts, use the account-management statements
intended for creating accounts and establishing their privileges,
such as CREATE USER and
GRANT. These statements cause the
server to make appropriate modifications to the underlying grant
tables. All such statements are described in
Section 13.7.1, “Account Management Statements”.

Note

Direct modification of grant tables using statements such as
INSERT,
UPDATE, or
DELETE is discouraged and done at
your own risk. The server is free to ignore rows that become
malformed as a result of such modifications.

For any operation that modifies a grant table, the server checks
whether the table has the expected structure and produces an
error if not. mysql_upgrade must be run to
update the tables to the expected structure.

Another option for creating accounts is to use the GUI tool
MySQL Workbench. Also, several third-party programs offer capabilities
for MySQL account administration. phpMyAdmin is
one such program.

The following examples show how to use the
mysql client program to set up new accounts.
These examples assume that privileges have been set up according
to the defaults described in Section 2.10.4, “Securing the Initial MySQL Account”.
This means that to make changes, you must connect to the MySQL
server as the MySQL root user, which has the
CREATE USER privilege.

First, use the mysql program to connect to the
server as the MySQL root user:

shell> mysql --user=root mysql

If you have assigned a password to the root
account, you must also supply a --password or
-p option.

After connecting to the server as root, you can
add new accounts. The following example uses
CREATE USER and
GRANT statements to set up four
accounts:

The accounts created by those statements have the following
properties:

Two accounts have a user name of finley.
Both are superuser accounts with full privileges to do
anything. The 'finley'@'localhost' account
can be used only when connecting from the local host. The
'finley'@'%' account uses the
'%' wildcard for the host part, so it can
be used to connect from any host.

The 'finley'@'localhost' account is
necessary if there is an anonymous-user account for
localhost. Without the
'finley'@'localhost' account, that
anonymous-user account takes precedence when
finley connects from the local host and
finley is treated as an anonymous user. The
reason for this is that the anonymous-user account has a more
specific Host column value than the
'finley'@'%' account and thus comes earlier
in the user table sort order.
(user table sorting is discussed in
Section 6.2.6, “Access Control, Stage 1: Connection Verification”.)

The 'dummy'@'localhost' account has no
password (which is insecure and not recommended). This account
can be used only to connect from the local host. No privileges
are granted. It is assumed that you will grant specific
privileges to the account using
GRANT statements.

The first account can access the
bankaccount database, but only from the
local host.

The second account can access the expenses
database, but only from the host
host47.example.com.

The third account can access the customer
database, from any host in the example.com
domain. This account has access from all machines in the
domain due to use of the % wildcard
character in the host part of the account name.