Setting Up a MySQL User Account

1.2.1 Problem

You need to create an account to use for connecting to the MySQL server running on a given host.

1.2.2 Solution

Use the GRANT statement to set up the MySQL user account. Then use that account's name and password to make connections to the server.

1.2.3 Discussion

Connecting to a MySQL server requires a username and password. You can also specify the name of the host where the server is running. If you don't specify connection parameters explicitly, mysql assumes default values. For example, if you specify no hostname, mysql typically assumes the server is running on the local host.

The following example shows how to use the mysql program to connect to the server and issue a GRANT statement that sets up a user account with privileges for accessing a database named cookbook. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -p to tell mysql to prompt for a password, and -u root to connect as the MySQL root user. Text that you type is shown in bold; non-bold text is program output:

% mysql -h localhost -p -u root
Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT

After you enter the mysql command shown on the first line, if you get a message indicating that the program cannot be found or that it is a bad command, see Recipe 1.8. Otherwise, when mysql prints the password prompt, enter the MySQL root password where you see the ******. (If the MySQL root user has no password, just press Return at the password prompt.) Then issue a GRANT statement like the one shown.

To use a database name other than cookbook, substitute its name where you see cookbook in the GRANT statement. Note that you need to grant privileges for the database even if the user account already exists. However, in that case, you'll likely want to omit the IDENTIFIED BY 'cbpass' part of the statement, because otherwise you'll change that account's current password.

The hostname part of 'cbuser'@'localhost' indicates the host from which you'll be connecting to the MySQL server to access the cookbook database. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to make connections to the server from another host, substitute that host in the GRANT statement. For example, if you'll be connecting to the server as cbuser from a host named, the GRANT statement should look like this:

mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'' IDENTIFIED BY 'cbpass';

It may have occurred to you that there's a bit of a paradox involved in the procedure just described. That is, to set up a user account that can make connections to the MySQL server, you must connect to the server first so that you can issue the GRANT statement. I'm assuming that you can already connect as the MySQL root user, because GRANT can be used only by a user such as root that has the administrative privileges needed to set up other user accounts. If you can't connect to the server as root, ask your MySQL administrator to issue the GRANT statement for you. Once that has been done, you should be able to use the new MySQL account to connect to the server, create your own database, and proceed from there on your own.

MySQL Accounts and Login Accounts

MySQL accounts and login accounts for your operating system are different. For example, the MySQL root user and the Unix root user are separate and have nothing to do with each other, even though the username is the same in each case. This means they are very likely to have different passwords. It also means you cannot create new MySQL accounts by creating login accounts for your operating system; use the GRANT statement instead.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: