You can create MySQL accounts in two ways:
The preferred method is to use GRANT statements, because they are more concise and less error-prone. GRANT is available as of MySQL 3.22.11; its syntax is described in the MySQL Language Reference.
Another option for creating accounts is to use one of several available third-party programs that 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 users. These examples assume that privileges are set up according to the defaults described in Section 2.4.5, "Securing the Initial MySQL Accounts." This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative 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'll also need to supply a --password or -p option for this mysql command and also for those later in this section.
After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:
mysql> GRANT ALL PRIVILEGES ON *.* TO
The accounts created by these GRANT statements have the following properties:
As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables:
shell> mysql --user=root mysql mysql> INSERT INTO user
The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes will go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary.
The reason for using the PASSWORD() function with INSERT is to encrypt the password. The GRANT statement encrypts the password for you, so PASSWORD() is unnecessary.
The 'Y' values enable privileges for the accounts. Depending on your MySQL version, you may have to use a different number of 'Y' values in the first two INSERT statements. (Versions prior to 3.22.11 have fewer privilege columns, and versions from 4.0.2 on have more.) For the admin account, the more readable extended INSERT syntax using SET that is available starting with MySQL 3.22.11 is used.
In the INSERT statement for the dummy account, only the Host, User, and Password columns in the user table record are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is equivalent to what GRANT USAGE does.
Note that to set up a superuser account, it is necessary only to create a user table entry with the privilege columns set to 'Y'. user table privileges are global, so no entries in any of the other grant tables are needed.
The next examples create three accounts and give them access to specific databases. Each of them has a username of custom and password of obscure.
To create the accounts with GRANT, use the following statements:
shell> mysql --user=root mysql mysql> GRANT
The three accounts can be used as follows:
To set up the custom accounts without GRANT, use INSERT statements as follows to modify the grant tables directly:
shell> mysql --user=root mysql mysql> INSERT INTO user (Host,User,Password)
The first three INSERT statements add user table entries that allow the user custom to connect from the various hosts with the given password, but grant no global privileges (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual when you modify the grant tables directly, you tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.
If you want to give a specific user access from all machines in a given domain (for example, mydomain.com), you can issue a GRANT statement that uses the '%' wildcard character in the host part of the account name:
mysql> GRANT ...
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user (Host,User,Password,...)
blog comments powered by Disqus