MySQL Permissions

Once MySQL has been successfully installed, you should immediately set a password for the root user. Until you have done so, anyone can access your databases and have administrative-level privileges. Windows users who followed the steps earlier for installing MySQL can skip this step, since the root user was created when the MySQL configuration wizard was run.

Once you've established the root user's password, you can begin establishing the users who will regularly access the database (for example, from PHP scripts). It is very insecure to use the root user for general purposes, so everyone should create some new MySQL users for regular use.

Setting the root user password

The mysqladmin utility, as the name might imply, is used to perform administrative-level tasks on your database. These include stopping MySQL, setting the root user's password, and more. (Some of the things you can do with mysqladmin can also be accomplished more directly within the mysql client, though.)

One of the first uses of mysqladmin is to assign a password to the root user. When MySQL is installed, there is no such value established. This is certainly a security risk that ought to be remedied before you begin to use the server. Just to clarify, your databases can have several users, just as your operating system might. The MySQL users are different from the operating system users, even if they share a common name. Therefore, the MySQL root user is a different entity than the operating system's root user, having different powers and even different passwords (preferably but not necessarily).

Most important, understand that the MySQL server must be running for you to use mysqladmin.

Again, if you ran the MySQL configuration wizard on Windows, you have already established a root user's password and can skip ahead to the next sequence of steps.

To assign a password to the root user

1.	Log on to your system from a command-line interface. For Mac OS X and Linux users, this is just a matter of opening the Terminal application. For Windows users, you'll need to choose Start > Run, then enter cmd, and click OK.
2.	Move to the mysql/bin or just mysql directory, depending upon your operating system. cd /usr/local/mysql (Unix or Mac OS X) or cd C:\mysql\bin (Windows) On some operating systems, you cannot access the mysqladmin utility directly. Therefore, you should go to one directory below it. On Windows you can head immediately into the bin directory.
3.	Enter the following, replacing thepassword with the password you want to use (Figure A.20): bin/mysqladmin u root password 'thepassword (Unix or Mac OS X) Figure A.20. Establishing a password for the root user. or mysqladmin u root password 'thepassword (Windows) Keep in mind that passwords within MySQL are case-sensitive, so Kazan and kazan are not interchangeable. The term password that precedes the actual quoted password tells MySQL to encrypt that string. If you see an error with this step, you could also try one of the following: ./bin/mysqladmin u root password 'thepassword or /path/to/bin/mysqladmin u root password 'thepassword

Creating users and privileges

After you have MySQL successfully up and running, and after you've established a password for the root user, it's time to begin adding other users. To improve the security of your applications, you should always create new users for accessing your databases, rather than continue to use the root user at all times.

The MySQL privileges system was designed to restrict access to only certain commands on specific databases by individual users. This technology is how a Web host, for example, can securely have several users accessing several databases, without concern. Each user within the MySQL system can have specific capabilities on specific databases from specific hosts (computers). The root userthe MySQL root user, not the system'shas the most power and is used for creating subusers, although subusers can be given rootlike powers (inadvisably so).

When a user attempts to do something with the MySQL server, MySQL will first check to see if the user has the permission to connect to the server at all (based upon the username, the user's password, and the information in the user table of the mysql database). Second, MySQL will check to see if the user has the permission to run the specific SQL statement on the specific databasesfor example, to select data, insert data, or create a new table. To determine this, MySQL uses the db, host, user, tables_priv, and columns_priv tables, again from the mysql database. Table A.1 lists the various privileges that can be set on a user-by-user basis.

Table A.1. The list of privileges that can be assigned to MySQL users.

MySQL Privileges
PRIVILEGE	ALLOWS
SELECT	Read rows from tables.
INSERT	Add new rows of data to tables.
UPDATE	Alter existing data in tables.
DELETE	Remove existing data from tables.
INDEX	Create and drop indexes in tables.
ALTER	Modify the structure of a table.
CREATE	Create new tables or databases.
DROP	Delete existing tables or databases.
RELOAD	Reload the grant tables (and therefore enact user changes).
SHUTDOWN	Stop the MySQL server.
PROCESS	View and stop existing MySQL processes.
FILE	Import data into tables from text files.
GRANT	Create new users.
REVOKE	Remove the permissions of users.

There are a handful of ways to set users and privileges within MySQL, but I prefer to do it manually, using the mysql client and the GRANT command. The syntax goes like this:

 GRANT privileges ON database.* TO  password' 

For the privileges aspect of this statement, you can list specific privileges from the list in Table A.1, or you can allow for all of them using ALL (which is not prudent). The database.* part of the statement specifies which database and tables the user can work on. You can name specific tables using the database.tablename syntax or allow for every database with *.* (again, not prudent). Finally, you can specify the username and a password.

The username has a maximum length of 16 characters. When creating a username, be sure to avoid spaces (use the underscore instead) and note that usernames are case-sensitive. The password has no length limit but is also case-sensitive. The passwords will be encrypted within the mysql database, meaning they cannot be recovered in a plain text format. Omitting the IDENTIFIED BY 'password' clause results in that user not being required to enter a password (which, once again, should be avoided).

Finally, there is the option of limiting users to particular hostnames. The hostname is either the name of the computer on which the MySQL server is running (localhost being the most common value here) or the name of the computer from which the user will be accessing the server. This can even be an IP address, should you choose. To specify a particular host, change your statement to

 GRANT privileges ON database.* TO  hostname IDENTIFIED BY  'To allow for any host, use the hostname wildcard character (%).
 
 GRANT privileges ON database.* TO  password' 

 
As an example of this process, I will create a new user with specific privileges for a database called sitename. The following instructions will require using the mysql client or a similar interface to MySQL. I discuss how to access this tool in detail in Chapter 4, "Introduction to SQL and MySQL."
 
To create new users
 
1. 
Log in to the mysql client as the MySQL root user.

 If you are using Windows, the MySQL installer created a shortcut to the mysql client under Programs > MySQL. If you are using Mac OS X or Unix, you'll need to use a Terminal application and type

 
 /usr/local/mysql/bin/mysql -u root -p 

 If MySQL was not installed in that directory, you'll need to change your pathname accordingly.

 If you don't feel like messing with all of this, you can use phpMyAdmin or any of the other interface tools listed in Appendix C, "Resources."

2. 
Create the sitename database, if it does not already exist.

 
 CREATE DATABASE sitename; 

 Creating a database is quite easy, using the preceding syntax. This is also discussed in Chapter 4.

3. 
Create a user that has basic-level privileges on the sitename database (Figure A.21).

 
 GRANT SELECT, INSERT, UPDATE, DELETE  sitename.* TO 'username@'localhost'  IDENTIFIED BY 'password; 

 
Figure A.21. Creating a user that can perform basic tasks on one database.
 

 The generic username user can browse through records (SELECT from tables) and add (INSERT), modify (UPDATE), or DELETE them. The user can only connect from localhost (from the same computer) and can only access the sitename database.

4. 
Apply the changes (Figure A.22).

 
 FLUSH PRIVILEGES; 

 
Figure A.22. The FLUSH PRIVILEGES command must be run for the user changes to take effect.
 

 The changes just made will not take effect until you have told MySQL to reset the list of acceptable users and privileges, which is what this command will do. Forgetting this step and then being unable to access the database using the newly created users is a common mistake.

 
 Tips
 
Any database whose name begins with test_ can be accessed by any user who has permission to connect to MySQL. Therefore, be careful not to create databases named this way unless it truly is experimental.
There is an even more manual way to create new users: by running INSERT commands on the user and other mysql database tables. This is only for the more experienced users who fully comprehend the relationships among the user, db, and other mysql tables.