Module 8: Basic Administration and Backups

From an administrator s perspective, securing a database means several things. You need to protect it by restricting access to the database server itself, and to the tables in its individual databases to only authorized users. You also need to be able to recover from a catastrophic database failure, no matter what the cause. This module will explain how to manage user accounts with regard to access and privileges, as well as how to back up your data using various processes and how to restore your database to health after a failure occurs.

Critical Skill 8.1 Set Privileges

Privilege can be defined as a right granted for a specific reason. Setting privileges lets database administrators maintain the security of the data, while allowing users to access necessary information independently. If an administrator is the only person allowed to access a busy database, requests for information from it can become an overwhelming demand on that administrator s time. Setting privileges so that users are allowed to access only information applicable to their jobs, or to view but not alter the data, is a way for an administrator to delegate specific routine requests for information into an automatic process controlled by user names and passwords.

Understanding the Levels of Security

At its most basic level, setting privileges on a database is a matter of letting some users in and keeping some users out, but the more users you have and the more varied the data you store, the more levels of access you may find necessary. Security can be set at three levels: database, file, and client access.

Database Security

Database security revolves around setting user name /password combinations to allow access to approved users and deny access to all others. At the very least, a password should be set for the root user. If you followed the installation instructions in Module 1, you set the root user password as soon as you made your first connection to your database.

If, at some point, you forget the password for the root user, you can regain access to the database. In MySQL, user security information is stored in grant tables. You can start the server without accessing the grant tables, which means no password is required to log in. Once inside the server, the root user password can be reset, allowing secure access to the database server again. The precise process for resetting the root password depends on your OS. The following sections give specific steps to reset the root password on both Windows- and Linux-based MySQL Servers.

Resetting the Root Password on a Windows System Before you can reset the root password, you must stop the MySQL Server. The following is the procedure for stopping the MySQL Server and resetting the password on a Windows computer.

Open the Control Panel in Windows, double-click the Administrative Tools icon, and then double-click the Services icon. In the Services window, locate the MySQL process, right-click it, and choose Stop from the menu to terminate the MySQL Server. Leave the Services window open ; you will return here to restart the server.
Open a command-line window and enter the following command:
```
 \mysql\bin\mysqld --skip-grant-tables 
```
The cursor will move down a line but will not return a prompt.
Open another command-line window and enter the following commands:
```
 \mysql\bin\mysqladmin -u root flush-privileges password "<  new password  >"\mysql\bin\mysqladmin -u root 
```
MySQL will prompt you for a password. Use the password you entered in place of < new password > in the preceding code.
Go back to the Services window, right-click the MySQL process, and choose Start from the menu. MySQL is now running in normal mode, and your root password has been reset.

You can also stop the MySQL Server by issuing the NET STOP MYSQL command in the command-line window, and restart it by issuing the NET START MYSQL command in the command-line window.

Resetting the Root Password on Linux Before you can reset the root password, you must stop the MySQL Server. The following is the procedure for stopping the MySQL Server, and then resetting the password on a Linux computer.

If the MySQL server is not running, then skip to step 3. If the MySQL server is running it must be terminated by force. Make sure you are logged in to your computer as root and locate the file containing the process identification (PID) number. The .pid file is usually named in one of two formats: mysqld.pid or < hostname >.pid. For instance, if your Linux computer is named Doc, your .pid file may be named Doc.pid or mysqld.pid. The directory where the .pid file is located varies depending on the brand of Linux you are running; the following are common locations:
- /var/run/mysqld
- /var/lib/mysqld
- /var/lib/mysql
- /usr/local/mysql/data
Once you have located the correct directory and ascertained the .pid filename, execute the following command:
```
 kill `cat /<  path to correct directory  >/<  filename  >.pid` 
```
Note
Make sure you use backticks ”the same key as the tilde (~) ”in the preceding command. Single quotes may not work.

You should get a return line that indicates mysqld is ended, although you may need to press the ENTER key to return to the command-line prompt.
Enter the following commands to open MySQL without the grant tables:
```
 mysql_safe --skip-grant-tables & 
```
You should get a return indicating that the mysqld daemon is starting.

Enter the following command to reset the root password:

 mysqladmin -u root flush-privileges password "<  new password  >"

You should be able to log in to MySQL now using the new password by entering the following command and then the new password when prompted.
```
 /usr/bin/mysql -p 
```

File-Level Security

Even if your users have access to your database, there are some areas of information to which you may not want to give them access. Obviously, the data files should be protected. If a user only needs to view data, give that user only viewing privileges. You may want to allow a user to insert data but not to delete data. A user may need access, at whatever level, to one table in a database and not to another. A good administrator examines a new user s role in interfacing with the database before setting up that user s access parameters.

Log files also need to have adequate security. These files can have sensitive information in them that should not be made available to all users. Many log file entries contain harmless information that may not be considered informative to anyone but an administrator; however, a log file that stores queries, for instance, can contain user name and password information and should be protected from general access.

In the Windows and Linux environments, file-sharing criteria can be set within the OS, and user privileges can be set within the MySQL Client.

Client-Access Security

Users can be given access to the MySQL Client in two different categories: server access and query access. Server access, as previously mentioned, involves a user name/password combination that allows access to the MySQL Client that interfaces with the MySQL Server. Query access functions inside the MySQL Client and involves varying levels of privilege regarding the user s use of queries and commands. A user may have all available privileges or a handpicked set of them designed specifically to balance that user s needs and the database s security.

Privileges are set by using the GRANT command, as described in the next section. The privilege settings, in combination with a user name and password, are stored in the grant tables, which MySQL consults whenever a user tries to execute a query. If a user has no privileges on a database, that database will not be listed when that user runs the SHOW DATABASES command. The same is true for tables within databases. If a database has 20 tables, but the user has privileges for only 5 of them, that user will see only those 5 tables listed when she or he runs the SHOW TABLES command.

Granting and Revoking User Privileges

MySQL allows you to grant certain privileges to a user. Creating a user account grants the new user the USAGE privilege as part of the account creation process. The USAGE privilege merely allows the user to log in to the MySQL server; it does not grant any other access. Even if all privileges are taken away from that user, the USAGE grant remains until the user is deleted from the system.

The GRANT function creates new users and grants privileges to existing users. The basic syntax is as follows :

 GRANT <  privilege_type  > [<  column_name or list of names  >] [, <  privilege_type  > [<  column_name or list of names  >]] ON { * *.*  <  database_name  >.*  <  database_name  >.<  table_name  > } TO <  user_name  > IDENTIFIED BY "<  password  >" [WITH GRANT OPTION};

Note	The text in the syntax surrounded by curly braces ({ }) is a list of possible choices, one of which must be selected for the command.

Privileges can be revoked for a variety of reasons. As part of the set up of a new user, you can grant all privileges, and then revoke the ones the user does not need. When a user s job description changes, you may need to revoke some privileges and grant others. In the case of database misuse or a user who is no longer with your company, revoking privileges is the first step to removing the user entirely.

The REVOKE function has a marginally simpler syntax than GRANT , and you can use the SHOW GRANTS FOR < user_name > command to verify the exact syntax needed to revoke a specific privilege.

 REVOKE <  privilege_type  > [<  column_name or list of names  >] [, <  privilege_type  > [<  column_name or list of names  >]] ON { * *.*  <  database_name  >.*  <  database_name  >.<  table_name  > } FROM <  user_name  >;

Privilege Levels

Privileges can take place on four levels: global, database, table, and column.

Global Privileges Privileges granted on a global level apply to all of the databases in your MySQL Server. This includes the mysql database containing information used for MySQL's operation. Rarely do any users, other than those with administrative duties , require access to the mysql database. To grant or revoke all privileges on a global level, use the following partial syntax:

 GRANT ALL ON *.* REVOKE ALL ON *.*

Database-Level Privileges Privileges granted on a database level apply to the specified databases in your MySQL Server. This includes access to all tables within the specified database and allows you to restrict a user s access to only that database. To grant or revoke all privileges on a database level, use the following partial syntax:

 GRANT ALL ON <  database_name  >.* REVOKE ALL ON <  database_name  >.*

Table-Level Privileges Privileges granted on a table level apply to the specified table in your MySQL Server. This includes access to the specific table and all of its columns within the specified database, and allows you to restrict a user s access to only that table. To grant or revoke all privileges on a table level, use the following partial syntax:

 GRANT ALL ON <  database_name  >.<  table_name  > REVOKE ALL ON <  database_name  >.<  table_name  >

Column-Level Privileges Privileges granted on a column level apply to the specified column within a specific database and table in your MySQL Server. This includes access to only the specific column and allows you to restrict a user s access to all other columns in that table. To grant or revoke all privileges on a column level, use the following partial syntax:

 GRANT ALL (<  column_name  >) ON <  database_name  >.<  table_name  > REVOKE ALL (<  column_name  >) ON <  database_name  >.<  table_name  >

Privilege Types

Table 8-1 lists the types of privileges you can grant. As you peruse the table, notice the commentary on which privileges it might be wise to grant on a restricted basis. Any privilege that has been granted can also be revoked. In many circumstances, it may be easier to grant a new user ALL PRIVILEGES , and then revoke the ones deemed unnecessary, depending on that user's required interaction with the database.

Table 8-1: MySQL Privilege Types
Privilege Type	Description	Comments
ALL	Grants all privileges, except WITH GRANT OPTION	Using the reserved word PRIVILEGES after ALL is optional.
ALTER	Grants use of ALTER TABLE	Renaming tables should be reserved for administrators only whenever possible.
CREATE	Grants use of CREATE TABLE
CREATE TEMPORARY TABLES	Grants use of CREATE TEMPORARY TABLE	Users who access data in order to make reports may need this privilege.
DELETE	Grants use of DELETE	Only users involved in data maintenance should have this privilege.
DROP	Grants use of DROP TABLE	Dropping tables should be reserved for administrators only whenever possible.
EXECUTE	Grants use of stored procedures (new in MySQL version 4.0.2)	Depending on the stored procedures, this privilege may be better reserved for administrators only.
FILE	Grants use of SELECT INTO OUTFILE and LOAD DATA INFILE	Depending on the level of data security desired, this privilege may be better reserved for administrators only whenever possible.
GRANT OPTION	Used to revoke WITH GRANT OPTION
INDEX	Grants use of CREATE INDEX and DROP INDEX	Only users involved in data maintenance should have this privilege.
INSERT	Grants use of INSERT	Only users involved in data maintenance should have this privilege.
LOCK TABLES	Grants use of LOCK TABLES on tables on which the user already has the SELECT privilege	Only users involved in data maintenance should have this privilege.
PROCESS	Grants use of SHOW FULL PROCESSLIST	Viewing the process list should be reserved for administrators only whenever possible.
RELOAD	Grants use of FLUSH	Only users involved in data maintenance should have this privilege.
REPLICATION CLIENT	Grants ability to ask where the slaves/masters are	Viewing replication servers locations should be reserved for administrators only whenever possible.
REPLICATION SLAVE	Grants ability of the replication slaves to read information from master	This privilege should be reserved for administrators only whenever possible.
SELECT	Grants use of SELECT	Any user who needs to view data will need this privilege.
SHOW DATABASES	Grants use of SHOW DATABASES	This privilege shows users only the databases to which they are granted access; most users can have this privilege.
SHUTDOWN	Grants use of MYSQLADMIN SHUTDOWN	This privilege should be reserved for administrators only whenever possible.
SUPER	Grants the user one connection, one time, even if the server is at maximum connections limit, and grants use of CHANGE MASTER , KILL THREAD , MYSQLADMIN DEBUG , PURGE MASTER LOGS , and SET GLOBAL	This privilege should be reserved for administrators only.
UPDATE	Grants use of UPDATE	Only users involved in data maintenance should have this privilege.
USAGE	Grants access to log in to the MySQL Server but bestows no privileges	All users, once defined, have this privilege.
WITH GRANT OPTION	Grants ability for users to grant any privilege they possess to another user	This privilege should be reserved for administrators only whenever possible.

More than one privilege type, with or without an accompanying column name or list of names, can be included in a single GRANT command, as shown by the syntax before the ON reserved word. The column name or list of names option is used only when setting privileges on the column level. The optional privilege type WITH GRANT OPTION is used only if the user is allowed to pass on the granting of privileges.

Caution

Giving a user the WITH GRANT OPTION in regards to a privilege also allows that user to grant others any other privilege the user might have at the same level as the one where WITH GRANT OPTION is bestowed.

User Names

It is also important to note that in MySQL, the host is part of the user name. If access to the database will always be made, or restricted to, the host that the database is running on, the host can be referred to as localhost , as in root@localhost or lorne@localhost . On the other hand, if a database will always be accessed, or restricted from access, from a network, the host can be specified as part of the local domain. For instance, if the local domain will always be xxx . yyy . zzz .[0-255], you can use a wildcard for the final set of numbers , so that the user name is in the format name @xxx. yyy . xxx .% , as in lorne@%.mydomain.com . You can also use the percent sign wildcard to allow a user to access the database from any host: lorne@% .

The wildcard symbols percent sign and underscore ( % and _ ) are allowed in database names on the global and database levels. If a GRANT command has either of them in the database name, they must be offset by preceding them with a backslash ( \ ) to indicate that MySQL should not interpret them as wildcards. For example, if your database login name is donut_boy , and you are granting privileges on the global or database level, the database name in the GRANT command should be `donut\_boy` .

If user names or host names contain special characters , they must be put between quotes in the GRANT command. For instance, queen_c@sun-e-dale.com has the special characters underscore and dash, so it would need to be typed in as `queen_c`@`sun-e-dale.com` . In other words, the parts of the user name that have special characters must be treated as a string and placed between quotes to indicate that no special meaning should be attached to the special characters, but the at symbol ( @ ) must be outside the quotes so that it is still perceived as a special character connecting a user name and host name.

Grant Information Storage

The mysql database contains five tables that store grant information and are collectively referred to as the grant tables:

The user table stores the users, including their hosts and passwords that allow them to connect to the MySQL Server.
The db table stores the users and the database-specific privileges that exist, indicating whether each user has access to each privilege.
The host table is used in combination with the db table to control access from specific hosts; however, it does not reflect the information generated by GRANT and REVOKE commands, so it usually remains empty and unused.
The tables_priv table stores specific privileges in relation to a specific user and table, and the indicated privilege applies to the entire table.

The columns_priv table stores the specific column and privilege in relation to the specific user, and the indicated privilege applies only to that specific column in that specific table.

Caution

It is possible to change the grant tables without using the GRANT command. That is one of the reasons why the mysql database should have extremely restricted access. The privilege tables should never be altered manually. For consistency and security, always use the GRANT command.

Removal of User Privileges and Users

The basic syntax for the REVOKE command is shown at the beginning of this section. Starting with MySQL version 4.1.2, you will be able to revoke all privileges on all levels, from a user or list of users, with one command, using the following syntax:

 REVOKE ALL PRIVILEGES, GRANT OPTION FROM <  user_name  >[, <  user_name  >, ...];

There is a specific list of privileges that can be used in the privilege type field of the REVOKE function for a table: SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , GRANT OPTION , INDEX , and ALTER . If the REVOKE function is for a column, the privileges specified in the privilege type field can be only SELECT , INSERT , and UPDATE .

Ask the Expert

Why should I restrict my users' access to some of the MySQL commands?

The most common type of failure is user failure. This type of damage is seldom intentional, but unrestricted users may drop or corrupt data by mistake. You can reduce the possibility of user-corrupted data by limiting what users are allowed to do in the database. As of MySQL version 4, an administrator can limit privileges on a per-user basis. Limiting users in this way creates a better security policy and reduces the potential load on the machine. Another way to increase security for your database is to use SSL transport-layer encryption. This prevents your usernames and passwords from being passed in the open across the network.

Once all privileges have been revoked from a user, the user still has the USAGE privilege, which allows the user to log in to the database but not do anything once there. In order to remove the user from the database, you must execute the following commands:

 DELETE FROM mysql.user WHERE USER="<  user_name  >" AND HOST="<  host_name  >"; FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command causes MySQL to refresh the privileges in the cache where they are held in memory, thereby setting the latest version of privileges in place. Since the DELETE syntax for removing users is specific to the user name and host name, you must delete each instance of user permissions. For instance, if a particular user has access privileges from localhost and from the *.sun-e-dale.com host, you must execute a DELETE command for both instances in order to completely remove the user from the system.

Privilege Management

Here, we will look at some common considerations regarding privilege management that every administrator should consider.

Passwords The most important factor in privilege management is that all users should have passwords in order to maintain the security of the database. When creating an administrator user, the GLOBAL privilege is granted by using the following syntax in the GRANT command:

 ON *.*.

The WITH GRANT OPTION If a specific user makes decisions about who has access to a particular database, giving that user the WITH GRANT OPTION privilege allows you to delegate the grant-access process to him or her, saving both of you time and effort. The syntax for doing so follows:

 GRANT ALL ON <  database_name  >.* TO <  user_name  > WITH GRANT OPTION;

Temporary Tables If you have users who need to create temporary tables on a regular basis, the most secure way to allow this is to create a designated database to hold these tables and give those users, or all users, the necessary privileges to that database. Grouping all of the temporary tables within a database that holds no permanent tables removes the chance that a user might inadvertently or intentionally misuse privileges and corrupt the rest of the database.

Administrator-Only Privileges Any users who have modifying privileges in the mysql database can give themselves GLOBAL privileges. As explained earlier, the grant tables where all access- related information is stored are in the mysql database, and these can be edited manually to modify privileges. Therefore, only administrators should be allowed access to the mysql database.

As noted in Table 8-1, several privilege types should be restricted to administrators whenever possible:

Having the FILE privilege allows a user to write to or read a file nearly anywhere in the system. This includes all of the databases.
The ALTER privilege, which allows a user to rename tables, can be used to gain access to a table the user does not have privileges on by changing its name to a table the user does have privileges on.
The PROCESS privilege allows the use of the SHOW FULL PROCESSLIST command, which displays the list of currently running processes. On a busy system, it allows the user to see the text of any currently executing query, including plain text passwords in SET , CHANGE , or GRANT queries. It is not a guaranteed or efficient way to acquire other users passwords, but it should still be guarded against.
The SHUTDOWN privilege causes the entire database to stop running, which can be inconvenient or even catastrophic, depending on what is happening at that moment. Few, if any, users other than administrators should have this privilege.
The DROP privilege allows the removal of an entire table and its data from the database. If the table does not exist on a backup, there is no way to recover it. This privilege is potentially so destructive that it should be reserved for administrator use only.
The SUPER privilege allows the use of several privileges aimed at administrative tasks ; it also allows access to the database even when the maximum number of connections is being used. No one other than an administrator needs this privilege.

Progress Check

1.	What command do you use to create a new user?
2.	What command do you use to give an existing user new privileges?
3.	What command do you use to take away a user s privileges?
4.	When you execute REVOKE ALL , does that remove all privileges?
5.	What command removes the USAGE privilege?

Answers

1.	The GRANT command creates new users.
2.	The GRANT command gives existing users new privileges.
3.	The REVOKE command takes away privileges.
4.	No, the USAGE privilege still remains.
5.	You must use the DELETE command to remove the user from the mysql.user table and then use the FLUSH PRIVILEGES command.