MySQL: Adding Users To MySQL

Overview

This document describes how to add a new user account to MySQL.

Instructions

The minimal MySQL account will need the following SQL commands run to create the account, assign a password and grant access to a database.

Note that MySQL has a multi-tiered method of granting access to aspects of the database. The user's access is applied, followed by the database/user's access. It is usually a good policy to avoid granting the user any access, then enabling small amounts of access based on the database/user record. Here is typical example:

INSERT INTO user VALUES ('%', 'user', password('password'),
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'N', '', '', '', '', 0, 0, 0);
INSERT INTO db VALUES ('%', 'user', 'user',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'Y',
'N', 'N');

Create a random password, using genrpass as an example. The password will need to be around eight characters and probably ought to avoid the single-quote character (') so it won't be a problem entering in the SQL command.

The access granting parts of the database are described by entering either 'Y' (grant) or 'N' (don't grant). In the example above, the permissions are to allow the following (in order of appearance):

• select rows
• insert rows
• update rows
• delete rows
• create table
• drop table
• index table
• alter table

Other access bits are for more than the casual user. For information on the additional settings, consult a MySQL database manual.

Use the mysql command to insert the two new rows into the mysql database. Once the rows are created, kick the MySQL server to read in the new grant table by executing mysqladmin reload. Also, create the user's new database by executing the command mysqladmin create user.

Mailing the User

Next, mail the user the information about the newly created MySQL account. Be sure to include a message to have them change the initial password. The document Changing Passwords On Database Servers describes this process. An example message to the user might something like this:

ECN has created an account in the MySQL database. Here is your account information:

User "STIMPSON"
Password "YZ0NIIZ2"

Please change your password when you log on. See the document:

https://engineering.purdue.edu/ECN/Resources/KnowledgeBase/Docs/20041206103240

for more information.

Documenting The New User

Complete the operation by documenting the new user in the EGAD database. The EGAD database has a table named DBUSER with the following fields: