Managing Users and Privileges - Learning MySQL - page 299

ordinary account that you log in to while writing, reading email, web browsing, and
doing the other things you normally do.
The ordinary account can't access or modify sensitive system-wide files, such as the
system's hardware settings, or the MySQL server logfiles or datafiles. On a single-user
system, having a less privileged account for day-to-day use helps reduce the chances of
doing silly things such as deleting important system files or installing malware by mis-
take. On a corporate or university server, this security is essential: it not only helps
prevent accidental damage or malicious attack, but also helps protect confidential files
and data.
If a system account on your server can access the MySQL configuration, it can bypass
the monitor (and every other MySQL client) and carry out actions directly on the server
or databases. For example, the system root user can manipulate any MySQL instance
on the system, while an ordinary user can manipulate any MySQL instance that runs
under her account. With this access, you can bypass the MySQL server's authentication
and user-management scheme by starting the server with the skip-grant-tables option;
we discuss this and other ways to get around a forgotten root password in “Resetting
Forgotten MySQL Passwords,” later in this chapter. You can also browse data, indexes,
and database structures using a text editor, or just copy the databases elsewhere and
access them using another installation of MySQL. Therefore, you should take the usual
precautions of maintaining physical security of your server, keeping operating system
patches up-to-date, adding a network firewall, using appropriate permission settings
on files and directories, and requiring hard-to-guess passwords. Remember, if your
server is insecure or compromised, your MySQL server is insecure; it doesn't matter
how the MySQL users and privileges are configured. You should be similarly vigilant
about access to your database backups.
Creating and Using New Users
To create a new user, you need to have permission to do so; the root user has this
permission, so connect to the monitor as the root user:
$ mysql --user=root --password= the_mysql_root_password
Now create a new user called allmusic who'll connect from the same system as the one
the MySQL server is running on ( localhost ). We'll grant this user all privileges on all
tables in the music database ( music.* ) and assign the password the_password :
mysql> GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.02 sec)
This instruction creates the new user and assigns some privileges. Now, let's discuss
what we've done in more detail.
The GRANT statement gives privileges to users. Immediately following the keyword
GRANT is the list of privileges that are given, which, in the previous case, is ALL (all simple