MySQL Accounts
Security is a growing concern among system administrators. Any system that stores data critical to a company or organization is vulnerable to security risks. A MySQL database may store thousands of tables of crucial data and can be a major vulnerability if it is not properly secured.
Database Security
Users should be given access only to the data they need to work with directly, and should be given the minimum amount of access to be able to do their jobs. MySQL includes a number of commands that allow you to create users and assign them specific privileges, or abilities.
System Security
Because the MySQL data files for each database are stored in the file system of the underlying operating system, a secure operating system is also important for a secure database server. Be sure that no users except trusted administrators have the ability to directly access the MySQL data files. Anyone who can access these files directly can completely circumvent MySQL's security system.
Physical Security
When you are planning the security of a database server, be sure not to forget the server hardware itself. Despite the most sophisticated security and encryption in MySQL and the operating system, the data is still stored on a hard disk and is vulnerable if there is physical access to the server machine. Client machines should also be kept physically secure if they have the ability to connect without specifying a password each time.
Security Risks
While most people imagine data security as a battle against hackers and malicious vandals, the reality is that most security threats come from inside the company. Well-meaning users who have more access than they should have can accidentally delete or invalidate entire MySQL tables with a single command, and disgruntled employees are an even greater risk. For this reason, avoid giving users more than the minimum privileges they require.
Network Security
Because a MySQL server is often accessed across a local or wide-area network, the security of the network is also important. Malicious users can scan network traffic and view data or passwords as they are transmitted between database clients and servers.
To prevent potential network security issues, MySQL encrypts passwords before sending them across the network. You can also use data compression between MySQL clients and servers, which prevents data from being sent as readable text.
The best network security is provided by a good firewall or proxy server. Because the MySQL server usually does not need to be accessed from everywhere in the world, you can keep it behind the firewall and allow connections only from trusted systems.
Password Guidelines
MySQL relies on passwords for security. While this provides a basic level of security in authenticating users, this system is only as secure as the passwords chosen. To keep the MySQL server secure, be sure to have a set of guidelines in place for assigning passwords.
You should never create users without passwords. Passwords should be as long as possible. MySQL allows passwords up to at least eight characters, depending on the operating system. Avoid passwords that contain names, words that appear in the dictionary, or common abbreviations, as they can be easily guessed.
Logging on as a user
Here at PTI, we launch MySQL and Apache by using a special executable file created by our IT department. Among other things, this special executable logs us on with the default username of "root" and launches the MySQL client window. In the real world, you need to do all that yourself: start up a DOS window (Start > Run > cmd), browse to cd\mysql\bin, and type in the following:
SYNTAX of LOGON to the MYSQL MONITOR -
MYSQL -uusername -ppassword
For example, the following command is what the user 'nancy' with password 'mypass3' would use to start up the MySQL client window:
Example of LOGON to the MYSQL MONITOR -
MYSQL -unancy -pmypass3
When the MySQL server is first installed, there is one default user 'root' with no password. So how do you create new users and set passwords? MySQL includes a sophisticated security system. You can use MySQL commands to create users and grant them privileges for a database or table.
The Grant Tables
Internally, the MySQL server stores its usernames, passwords, and privileges in several tables within the mysql database. This database is created when you install the MySQL server. The user table within this database stores a row for each user and a number of fields that define the basic privileges granted to the user.
The other tables in the mysql database include the host table, which stores privileges specific to particular hostnames, and the db table, which stores privileges granted to users for a specific database. The tables_priv table stores privileges granted for specific table names, and the columns_priv table stores privileges granted for only specific columns of a table.
Default Users
When you install the MySQL server the root user is created by default. This user is granted all privileges for all database and tables, and can create additional users. The root user does not have a password by default, and this is a major security hole. Be sure to change the root password before allowing user to access the MySQL server.
The installation also creates an anonymous user, which allows users on the local host to connect without specifying a username and password. This user is restricted to a database named test or with a name beginning with test_, so this does not represent a serious security risk.
MySQL Users and Privileges
You must specify a username when you use MYSQL client programs such as mysql or mysqladmin. If you are the administrator of the MySQL server, you can create usernames and control the privileges, or permissions, of each user.
You use the GRANT command in MySQL to grant one or more privileges to a user. If the username you specify does not exist, it is created. The REVOKE command is the opposite. This command removes one or more privileges from a user.
A user in MySQL is actually the combination of a username and hostname. If a username is set up with a specific host, the user can only connect from that host. Users can also be configured to allow multiple hosts or all hosts.
The privileges you can grant to a user include most of the different things that can be done with SQL queries, including SELECT, INSERT, and DELETE.
Grant Privileges to Users
MySQL uses its own system of usernames and passwords, unrelated to the underlying operating system. You can use the GRANT command from MySQL to create a username and assign one or more privileges to the user. You can assign privileges for all databases, a single database, a table, or even a single column.
The basic syntax of the GRANT command specifies a privilege type, a table or database name, a username, and a password. The user name can be an existing MySQL user. If it is a new user, the user is added.
SYNTAX of GRANT -
GRANT oneOrMorePrivileges
ON tableOrDatabaseName
TO username
IDENTIFIED BY 'password';
The following GRANT command grants all privileges to the user nancy for the testdb database:
Example of GRANT -
GRANT ALL
ON testdb.*
TO nancy
IDENTIFIED BY 'thepass';
Usernames on MySQL can be a simple name like the above, or a combination of a username, the @ symbol, and hostname. If you specify a hostname, the user can only access MySQL from that host. If you do not specify a hostname, the username will work from any host. You can use the wildcard character, %, as the hostname to explicitly indicate that the user can connect from any host.
You can specify a database name with the * symbol, meaning all tables under that database, a table name under the current database selected with the USE command, or the wildcard *.*, meaning all databases on the server. You can optionally specify a list of columns in parentheses before the ON keyword, and the user will have the privileges you specify for only those columns.
The IDENTIFIED BY clause in the GRANT statement allows you to specify a password for the user. The password will be encrypted and stored in the MySQL user table. If the user has already been created with a previous GRANT statement, you do not need to use the IDENTIFIED BY clause again.
In order to grant privileges to a user, you must be logged in as a user with those privileges and the ability to grant. If you specify WITH GRANT OPTION at the end of the GRANT command, the user will have the ability to grant any privileges they have to other users.
The REVOKE command allows you to revoke one or more privileges from a user. To use this command, specify REVOKE, the privilege type or ALL, the ON keyword, the table or database name, the FROM keyword, and the username.
SYNTAX of REVOKE -
REVOKE oneOrMorePrivileges
ON tableOrDatabaseName
FROM username;
After you have created a user and granted privileges with GRANT, you can change the user&339;s password using the SET PASSWORD command within the MySQL monitor. For example, the following command changes the password for the user fred.
Example of SET PASSWORD
SET PASSWORD FOR fred = PASSWORD('newpass');
MySQL stores passwords in an encrypted form. When you change a password with the SET PASSWORD command, you must use the PASSWORD function to encrypt the new password. MySQL expects the new password to be in encrypted form.
In order to change a user's password, you must either be logged in as the user or as a user with the GRANT OPTION privilege. This allows you to change the password for any user. You can also assign passwords by using the IDENTIFIED BY clause when creating users or adding privileges using the GRANT command, as explained in the previous section.
You can also change a user's password using the mysqladmin password command at the command prompt. In this case, you do not need to use the PASSWORD function. For example, the following command changes the password for the current user:
mysqladmin password 'newpass'
If you specify the -u option with mysqladmin, you can set the password for the specified user. However, this option requires the user's current password. If you need to set a password and do not know the user's current password, user the SET PASSWORD command.
When MySQL is first installed, the root user may be set up with no password or a default password. To secure the MySQL server, you should immediately change the password for this user using SET PASSWORD or mysqladmin password.
You can use the VIEW GRANTS command from the MySQL monitor to find out what privileges have been granted to a particular user. This is useful if you need to check what abilities have been given to a user. For example, the following statement displays the privileges granted to the user fred:
Example of SHOW GRANTS SYNTAX
SHOW GRANTS FOR fred;
The results for SHOW GRANTS are presented in the form of one or more GRANT statements. You can copy these statements and use them to restore the user's privileges in the event of data loss, or use them to create another user with the same privileges. The password in the GRANT statement is shown in the encrypted form.
In some cases a user is configured in MySQL but does not have any privileges. This can happen if you create a user manually in the users table, or if you revoked all of a user's privileges. In this case, when you use SHOW GRANTS, the results show a GRANT USAGE statement. USAGE is a special privilege meaning "no privileges." In other words, the user can connect to the MySQL server but cannot access any databases or tables.
When using SHOW GRANTS, remember that MySQL stores users as a combination of username and hostname. If a username is configured with a specific host, you must specify the hostname to view their privileges. If you have created the user ted@localhost, for example, no privileges will be shown if you use this command:
SHOW GRANTS FOR ted;
Because no hostname is specified, this command looks for a user with access from all hosts, and no user is found. To show the privileges for the correct user, specify the hostname with the @ symbol.
Control Network Access
When you created users on the MySQL server earlier in this chapter, you did not specify a hostname in the GRANT command. This allows the user to connect to the MySQL server from any host on the network. While this is often what you need, when a user will only be connecting from the local host or a specific host, you can give them access only from certain hosts. This greatly reduces the possibility of the user account being used maliciously across the network.
To specify the hostname a user can connect from, use the @ symbol to combine the user name and hostname. For example, the following GRANT command creates a username, henry, that can be used to connect only from the machine running MySQL server:
Example of GRANT TO USER ON a SPECIFIC HOST SYNTAX
GRANT ALL
ON testdb.*
TO henry@localhost
IDENTIFIED BY 'mypass';
MySQL allows multiple users with the same name in the user table, as long as their hostnames are different. For this reason, limiting the user to the local host will only work if you have not previously granted privileges to the same username without specifying a hostname. If you have done this, use REVOKE to remove the privileges for the original user before adding a user with a specified hostname.
You can specify a hostname or IP address that the user can connect from instead of using localhost. For example, the following GRANT command creates a username, sue, that can connect only from a host called example.com:
GRANT ALL
ON testdb.*
TO sue@example.com
IDENTIFIED BY 'password';
If you need to allow access for a user from more than one host, simply repeat the GRANT command for each hostname. You can user the wildcard character % in the hostname to allow a set of host names or IP addresses. When you do this, you must enclose the username and hostname in quotation marks:
GRANT ALL
ON testdb.*
TO 'user1@192.168%';