MySQL managing users and permissions

Source
5.5.2. Adding User Accounts

You can create MySQL accounts in two ways:

• By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.
• By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.

The preferred method is to use account-creation statements because they are more concise and less error-prone than manipulating the grant tables directly. CREATE USER and GRANT are described in Section 12.4.1, “Account Management Statements”.

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 accounts. These examples assume that privileges have been set up according to the defaults described in Section 2.12.2, “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.

As noted in the examples where appropriate, some of the statements will fail if the server's SQL mode has been set to enable certain restrictions. In particular, strict mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) and NO_AUTO_CREATE_USER will prevent the server from accepting some of the statements. Workarounds are indicated for these cases. For more information about SQL modes and their effect on grant table manipulation, see Section 5.1.7, “Server SQL Modes”, and Section 12.4.1.3, “GRANT Syntax”.

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 will also need to supply a --password or -p option, both for this mysql command and for those later in this section.

After connecting to the server as root, you can add new accounts.

MORE