MySQL in a Nutshell [24]
To save space, some of the examples are shown without their results. Occasionally, when the results are shown, the typical ASCII table format is not shown because the statement is executed with a \G ending instead of the usual semicolon. In order to focus on the particulars of the statements and clauses, the statements are fairly straightforward and do not make much use of the many built-in functions available with MySQL. Explanations of any functions used, though, can be found in other chapters.
Chapter 4. Security and User Statements and Functions
User access and privileges can be global (i.e., apply to all databases on the server), or they can be database-specific, table-specific, or column-specific. In version 5 of MySQL, users can also be limited to particular functions and procedures.
In addition to security-related SQL statements, users can be limited in their use of MySQL resources in order to prevent the monopolization of resources and the indirect denial of service to other users. Thus, you can limit the number of connections or the maximum resources per hour for a user.
The primary information regarding user access and privileges is stored in a set of regular MyISAM tables, known as the grant tables, that reside in the mysql database on the server. The tables are:
user
Global privileges
db
Database-specific privileges
tables_priv
Table-specific privileges
columns_priv
Column-specific privileges
Several other tables provide fine-tuning for user access and security. Execute SHOW TABLES FROM mysql; to get a list on your server. You can manipulate the data in these tables directly with standard SQL statements, such as INSERT, UPDATE, and DELETE, followed by the FLUSH PRIVILEGES statement to update the server’s cache. However, it’s recommended that you use specialized SQL statements to manage users and assign access rights:
CREATE USER
To create new users
GRANT
To create a user account, assigning privileges for a new user account, or assigning privileges to an existing user
REVOKE
To remove privileges
RENAME USER
To change a user’s name
SET PASSWORD
To change a password
DROP USER
To delete a user’s account
All of these statements are described in this chapter. This chapter also lists and explains MySQL functions related to user maintenance and several related to database and network security.
Statements and Functions
The following is a list of security and user statements that are covered in this chapter:
CREATE USER, DROP USER, FLUSH, GRANT, RENAME USER, RESET, REVOKE, SET PASSWORD, SHOW GRANTS, SHOW PRIVILEGES.
The following related functions are covered in this chapter as well. They are explained in detail after the SQL statements:
AES_DECRYPT( ), AES_ENCRYPT( ), CURRENT_USER( ), DECODE( ), DES_DECRYPT( ), DES_ENCRYPT( ),ENCODE( ), ENCRYPT( ), MD5( ), OLD_PASSWORD( ), PASSWORD( ), SESSION_USER( ), SHA( ), SHA1( ), SYSTEM_USER( ), USER( ).
SQL Statements in Alphabetical Order
The following is a list of MySQL statements and clauses in alphabetical order related to security and user account maintenance. The examples in this particular chapter have no theme to them and could be found in any organization using a MySQL database.
Name
CREATE USER
Synopsis
CREATE USER 'user'[@'host']
[IDENTIFIED BY [PASSWORD] 'password'] [, ...]
This statement creates new user accounts on the MySQL server. The username is given within quotes, followed by the at sign (@) and a host IP address or hostname within quotes. For accessing MySQL locally, use the host of localhost. The IP address is 127.0.0.1. Use the percent sign (%) wildcard as the host to allow a client with the specified username to connect from any host. If no host or @ is given, the percent sign is assumed.
The user password is given in plain text within quotes, preceded by the