The security responsibilities of the DBA basically amount to managing MySQL users and their privileges. Just as operating systems offer an administrative or “root” user for managing other users, MySQL offers a special user called root who can create other MySQL user accounts and grant them privileges. When you use administrative commands such as mysql or mysqladmin to manage users, make yourself the MySQL root user through the -u option:

$ mysql -u root

There is no relationship between the Unix system’s root and MySQL’s root. Anyone can issue the preceding command—and for that reason, it’s critical for you to assign a password to the MySQL root user as soon as you install MySQL. The following command assigns the string P?:2002:My? as the root password:

$ mysqladmin -u root password 'P?:2002:My?'

After you execute this command, anyone who attempts to administer MySQL as the root user has to use a -p option and enter the password:

$ mysql -u root -p
Enter password: P?:2002:My?

Later in the chapter, we show you how to create other MySQL users through SQL GRANT commands. Always assign passwords to these users when you issue the GRANT commands.

While the bulk of your time will revolve around managing privileges, you will still need to put some thought into how you manage your users. In general, you will need to support three kinds of users:

Individual users are people who use MySQL to develop against it and support it. You should never have MySQL manage the users of the applications run against it; this is the job of the applications themselves. If, for example, you have a MySQL installation supporting several applications in which MySQL must manage the users, you would have a user namespace nightmare; each username would have to be unique across all applications. Furthermore, security issues with one application could potentially compromise the entire MySQL installation. We cover application security in more detail later in the chapter.

Application users are MySQL users that represent specific applications to MySQL. If, for example, you had two web sites using the same MySQL installation to store their data, you might create two separate users to represent those applications. You can use these two separate user IDs to protect each application from the other.

Many database engines support roles . A person who sometimes does DBA work, sometimes does development work, and sometimes migrates applications between environments could have a single user ID with three different roles. When that person connects to the database, she logs in using that single user ID and password, and additionally, specifies a role. As long as that user is connected under the specified role, she can act using only the permissions assigned to that role.

MySQL, however, has no concept of role. Each user has individualized permissions. You therefore have three choices for managing users who play multiple roles on a MySQL server:

The advantage of the first approach is that it is the only single sign on solution. In other words, no matter what work you do in a MySQL environment, you need to remember only a single user ID/password combination. Unfortunately, it leaves open too much risk of wreaking havoc accidentally. An innocent DELETE FROM MYTABLE executed in one context could, for example, end up deleting data from a table in a different context simply because you got your contexts confused.

The second approach stinks on all levels, but it is to some degree the default for MySQL. Your clean install comes with a default DBA user called "root.” If you simply share this one user with all DBAs on a system, you run several security risks. First, if you want to take DBA access away from one of the DBAs, you need to change the password and communicate that change to all the other DBAs. Second, you have very few ways of tracking which DBA performed which action. Finally, you still have to remember different passwords for each role. The one advantage of this approach is you need to connect as a DBA only when doing DBA work. When doing development work, you can connect as a developer and not risk accidentally executing a statement in a developer context that should be run only in a DBA context.

The third approach marries many of the advantages of the first and second approaches. Each user role you create for an individual can share the same password. The user specifies the same username plus his role whenever he connects to the database. Users cannot accidentally perform actions outside the contexts in which they connected. You can then perform operations on all users with a specific role or all roles belonging to a specific user using wildcards:

UPDATE db SET Select_priv = 'Y'
WHERE Db = 'Library' AND User = 'dvl\_%'

The approach you choose really depends on the particulars of your environment. If you are the only one managing your MySQL server, it may work just fine if you have the default “root” user plus a “developer” user. In a complex environment with many different users and normal turnover, the third approach probably makes the most sense. Table 6-1 lists roles common to MySQL installations. We will cover what the different privileges mean later in the chapter.

Table 6-1 talks about different environments. A proper software development process includes the separation of software into separate environments. The number of environments generally depends on the number of developers, the number of applications, and the uptime requirements of the applications. Though cost often causes project teams using Oracle or Sybase to compromise on this issue by forcing teams to reduce the number of environments to save on licensing costs, an advantage of MySQL is that cost should never be a factor.

To ensure a smooth path from development through deployment, a project team needs the following environments and processes to support the migration of code through these environments: