MySQL in a Nutshell (2008)

Part II. SQL Statements and Functions

This part of the book is a complete reference for the version of the SQL language used by MySQL. It divides the SQL statements and functions by the basic functions (scheme design, data manipulation, replication, etc.). Examples use the mysql command-line client, but they are equally valid when issued from the programming APIs discussed in Part IV.

Some of the chapters in this part start with a list of statements grouped by type, as a quick reference. The statements are then listed in alphabetical order. For the more complex statements, to simplify their presentation, I’ve broken the syntax into several sections according to the different uses of the statement.

Here are some general elements of MySQL’s SQL syntax:

§ SQL statements may span multiple lines, but they must end with either a semicolon or \G, unless another character is specified with DELIMITER.

§ When values are enclosed in parentheses, multiple values can usually be specified, separated by commas.

§ Strings and dates must be specified within single or double quotes, unless a date is given as a numeric and is part of a date calculation.

§ Elements of a statement’s syntax are case-insensitive. However, on Unix-type systems, database and table names, as well as filenames, are case-sensitive.

The MySQL statements, clauses, and functions explained in Chapters 4 through 14 are grouped in each chapter, first by statements and clauses, then by functions. They are listed alphabetically within each group. Each statement is given with its syntax and an explanation. Optional clauses and flags are shown in square brackets. Particular components, such as a database or table name, are shown in italics. The vertical bar is used to separate alternative choices and is not part of the statement syntax.

Some statements have alternative syntax structures. These alternatives are usually shown in complete form. The curly braces indicate that one of the choices is required. Examples show how a statement and the various clauses may be used for almost all statements.

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 IDENTIFIED BY clause. You don’t need to use the PASSWORD() function to encrypt the password; this is done automatically. However, if you wish to provide the hash value of the password, precede the password withIDENTIFIED BY PASSWORD. If the password clause is not given, a blank password is assumed and will be accepted. This is a potential security problem and should never be done. If you do this by mistake, use the SET PASSWORD statement to set the password.

Multiple user accounts may be specified in a comma-separated list.

The CREATE USER statement was introduced in version 5.0.2 of MySQL. For previous versions, use the GRANT statement. This new statement operates similarly to the GRANT statement, except that you cannot specify user privileges with the CREATE USER statement. As a result, the process is to create a user with the CREATE USER statement and then to grant the user privileges with the GRANT statement. This two-step process is a more logical process, especially to a newcomer to MySQL. However, you can still use just the GRANT statement to create and set privileges for a new user.

This statement requires CREATE USER privilege or INSERT privilege for the mysql database, which contains user account information and privileges. To remove a user, use the DROP USER statement and possibly also the REVOKE statement:

CREATE USER 'paola'@'localhost'

IDENTIFIED BY 'her_password',

'paola'@'caporale.com'

IDENTIFIED BY 'her_password';

In this example, two user accounts are created along with their passwords, but both are for the same person. The difference is that one allows the user to log into the server hosting the database and to run the mysql client or some other client on the server, the localhost. The other account allows the user to connect from a host named caporale.com using a client from that host. No other host will be allowed for this user.

Name

DROP USER

Synopsis

DROP USER 'user'@'host'

Use this statement to delete a user account for the MySQL server. As of version 5.0.2 of MySQL, this statement will delete the user account and its privileges from all grant tables. The username is given within quotes, followed by the at sign (@) and the host IP address or hostname within quotes. This statement requires a CREATE USER privilege or DELETE privilege for the mysql database, which contains user account information and privileges. Dropping a user account does not affect current sessions for the user account. It will take effect when any sessions opened by the user terminate. Use the KILL statement (explained in Chapter 7) to terminate an open client session for a user that has been dropped.

Some users may have more than one user account (i.e., user and host combinations). You should check the server’s mysql.user table to be sure:

SELECT User,Host

FROM mysql.user

WHERE User LIKE 'paola';

+-------+--------------+

| User | Host |

+-------+--------------+

| paola | localhost |

| paola | caporale.com |

+-------+--------------+

DROP USER 'paola'@'localhost',

'paola'@'caporale.com';

Prior to version 5.0.2 of MySQL, the DROP USER statement won’t delete a user that has any privileges set to 'Y'. To eliminate the user account’s privileges, issue the REVOKE statement before using DROP USER:

REVOKE ALL ON *.* FROM 'paola'@'localhost';

DROP USER 'paola'@'localhost';

The ALL option is used to ensure revocation of all privileges. The *.* covers all tables in all databases. Prior to version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:

DELETE FROM mysql.user

WHERE User='paola' AND Host='localhost';

FLUSH PRIVILEGES;

Notice that the FLUSH PRIVILEGES statement is necessary for the preceding DELETE statement to take effect immediately. It’s not necessary after the DROP USER statement, though.

Name

FLUSH

Synopsis

FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[, ...]

Options:

DES_KEY_FILE, HOSTS, LOGS, MASTER, PRIVILEGES, QUERY_CACHE,

STATUS, TABLE, TABLES, TABLES WITHOUT READ LOCK, USER_RESOURCES

Use this statement to clear and reload temporary caches in MySQL. It requires RELOAD privileges. To prevent this statement from writing to the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. A particular cache to flush may be given as an option. Multiple options (see Table 4-1) may be given in a comma-separated list.

As of version 5.1 of MySQL, FLUSH cannot be used in stored functions and triggers, but can be used in stored procedures. As an alternative to the FLUSH statement, you can use the mysqladmin command (see Chapter 16).

Table 4-1. Options for FLUSH statement

Name

GRANT

Synopsis

GRANT privilege[,...] [(column[,...])][, ...]

ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}

TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[REQUIRE NONE |

[{SSL|X509}] [CIPHER 'cipher' [AND]]

[ISSUER 'issue' [AND]]

[SUBJECT 'subject']]

[WITH [GRANT OPTION |

MAX_QUERIES_PER_HOUR count |

MAX_UPDATES_PER_HOUR count |

MAX_CONNECTIONS_PER_HOUR count |

MAX_USER_CONNECTIONS count] ...]

This statement may be used to create new MySQL users, but its primary use is for granting user privileges. Privileges can be global (apply to all databases on the server), database-specific, table-specific, or column-specific. Users can now also be limited by functions and procedures. Additionally, users can be limited by number of connections or by a maximum of resources per hour.

The privileges to grant to a user are listed immediately after the GRANT keyword in a comma-separated list. To restrict a user to specific columns in a table, list those columns in a comma-separated list within parentheses. This is then followed by the ON clause in which the privileges granted may be limited to a database, table, function, or procedure. To limit the privileges to a function, use the FUNCTION keyword; to limit them to a procedure, use the PROCEDURE keyword.

For tables, the keyword TABLE is optional and the default. You can then specify the database to which the privileges relate in quotes, followed by a period (.) and the name of the table, function, or procedure in quotes. You may also use the asterisk wildcard (*) to specify all databases or all tables, functions, or procedures offered by the database.

In the TO clause, give the username (in quotes) and the IP address or host (also in quotes) for which the user account privileges are permitted, separated by an at sign (@). To provide the password for the user account, add the IDENTIFIED BY clause, followed by the user’s password in plain text and enclosed in quotes. To provide the password in encrypted hash form, add the keyword PASSWORD just before the password given. You can use the WITH clause to grant the GRANT OPTION privilege to a user so that that user may execute this statement. The GRANT statement with theIDENTIFIED BY clause can be used to change a password for an existing user.

For an explanation of how to restrict user accounts based on types of connections, see the next section of this statement (GRANT: Type of connection restrictions”). For information on how to restrict user accounts based on the amount of activity for a period of time or the number of connections permitted, see the last section of this statement (GRANT: Time and number of connection limits”). To see the privileges for a given user, use the SHOW GRANTS statement described later in this chapter.

A large variety of privileges may be granted to a user, so a common set of privileges has been combined in the ALL keyword. Here is an example:

GRANT ALL PRIVILEGES ON *.*

TO 'evagelia'@'localhost'

IDENTIFIED BY 'papadimitrou1234'

WITH GRANT OPTION;

In this example, the user evagelia is created and granted all basic privileges because of the ALL keyword. This does not include the GRANT privilege, the ability to use the GRANT statement. To do that, the WITH GRANT OPTION clause is given, as shown here, explicitly to give that privilege to the user. It’s not a good idea to give users this privilege unless they are MySQL server administrators. Table 4-2 later in this chapter lists and describes each privilege.

As mentioned before, a user’s privileges can be refined to specific SQL statements and specific databases. A GRANT statement can also restrict a user to only certain tables and columns. Here is an example that leaves the user fairly limited:

GRANT SELECT ON workrequests.*

TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

GRANT SELECT,INSERT,UPDATE ON workrequests.workreq

TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

Assuming the user jerry does not already exist, the first statement here creates the user and gives him SELECT privileges only for the workrequests database for all of its tables. This will allow him to read from the various tables but not edit the data. The second SQL statement grants jerrythe right to add and change data in the workreq table of the workrequests database. This will allow him to enter work requests and make changes to them. The first statement causes an entry to be made to the db table in the mysql database. The second affects the tables_priv table. An entry is also made to the user table showing the user jerry, but he has no global privileges. This is the equivalent of granting just the USAGE privilege.

GRANT: Type of connection restrictions

GRANT privilege[,...] [(column[,...])][, ...]

ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}

TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[REQUIRE NONE |

[{SSL|X509} [AND]]

[CIPHER 'cipher' [AND]]

[ISSUER 'issue' [AND]]

[SUBJECT 'subject']]

[time and number of connection limits] ...]

A user can also be restricted to certain types of connections with the REQUIRE clause. There are several options that may be given together with the keyword AND. Each option can be used only once in a statement. REQUIRE NONE is the default and indicates that no such restrictions are required. Encrypted and unencrypted connections from clients are permitted from the user that has been properly authenticated.

The REQUIRE SSL option restricts the user account to only SSL-encrypted connections. The mysql client of the user account would start the client with the --ssl-ca option, and also the --ssl-key and --ssl-cert options if necessary:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'

IDENTIFIED BY 'her_password'

REQUIRE SSL;

Use the REQUIRE X509 option to require the user account to have a valid CA certificate. This does not require any specific certificate, though. The mysql client would need to be started with the --ssl-ca, --ssl-key, and --ssl-cert options. To simplify handling of these options, the user can put them in a options file in her home directory on the server (e.g., ~/.my.cnf). The following is a sample of what that options file would contain to conform to the user account restrictions:

[client]

ssl-ca=/data/mysql/cacert.pem

ssl-key=/data/mysql/rusty-key.pem

ssl-cert=/data/mysql/rusty-cert.pem

Use the REQUIRE CIPHER option to require that the user account use a given cipher method:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'

IDENTIFIED BY 'her_password'

REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

REQUIRE ISSUER is used to require the user to supply a valid X.509 certificate issued by the given CA. Although the string given for an issuer may be lengthy, it must be written as one string without an embedded line break:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'

IDENTIFIED BY 'her_password'

REQUIRE ISSUER '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=

cacert.workrequests.com/emailAddress=admin@workrequests.com';

The REQUIRE SUBJECT option requires that the X.509 certificate used by the user account have the given subject:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'

IDENTIFIED BY 'her_password'

REQUIRE SUBJECT '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=

Rusty Osborne/emailAddress=rusty@workrequests.com';

GRANT: Time and number of connection limits

GRANT privilege[,...] [(column[,...])][, ...]

ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}

TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[type of connection restrictions]

[WITH [MAX_QUERIES_PER_HOUR count |

MAX_UPDATES_PER_HOUR count |

MAX_CONNECTIONS_PER_HOUR count |

MAX_USER_CONNECTIONS count] ...]

You can use the WITH clause along with the MAX_QUERIES_PER_HOUR option to specify the maximum number of queries that a user account may execute per hour. The MAX_UPDATES_PER_HOUR option is used to give the maximum number of UPDATE statements that may be issued per hour by the user account. The maximum number of connections by a user account to the server per hour can be set with the MAX_CONNECTIONS_PER_HOUR option. The default values for these three options are all 0. This value indicates that there is no limit or restrictions for these resources. The MAX_USER_CONNECTIONS option is used to set the maximum number of simultaneous connections the given user account may have. If this value is not set or is set to 0, the value of the system variable max_user_connections is used instead. Here is an example of how a user might be limited in such a way:

GRANT SELECT ON catalogs.*

TO 'webuser'@'%'

WITH MAX_QUERIES_PER_HOUR 1000

MAX_CONNECTIONS_PER_HOUR 100;

This account is designed for large numbers of users running queries through a web server. The statement creates the webuser user and allows it to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.

To change an existing user account’s resources without changing the account’s existing privileges, you can use the USAGE keyword. Simply enter a statement like this:

GRANT USAGE ON catalogs.*

TO 'webuser'@'%'

WITH MAX_QUERIES_PER_HOUR 10000

MAX_CONNECTIONS_PER_HOUR 100;

In this example, the existing user account has been limited in resources without changing the user account’s privileges. See Table 4-2 for a list of privileges.

Table 4-2. Privileges in GRANT and REVOKE

Name

RENAME USER

Synopsis

RENAME USER 'user'[@'host'] TO 'user'[@'host'][,...]

Use this statement to change the username or the host of an existing user account. It does not change the user privileges or necessarily migrate any privileges to specific databases, events, stored routines, tables, triggers, or views. Here is an example:

RENAME USER 'michaelzabalaoui'@'localhost' TO 'zabb'@'%',

'richardstringer'@'localhost' TO 'littlerichard'@'localhost';

The first user’s name and host have been changed here, whereas the second user’s name only was changed.

Name

RESET

Synopsis

RESET [QUERY CACHE|MASTER|SLAVE]

Use this statement to reset certain server settings and log files. The RELOAD privilege is required to use this statement. The QUERY CACHE option clears the cache containing SQL query results.

Use the MASTER option to reset a master used for replication. This statement must be executed from the master itself. It will start a new binary log file, as well as delete the binary log file names from the index file and delete the contents of the binary log index file. The SLAVE option is used to reset a slave used for replication and must be executed from the slave itself. It will start a new relay log file and delete any existing ones, as well as delete its notation of its position in the master’s binary log. See Chapter 8 on replication for more information on these two options.

Name

REVOKE

Synopsis

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user[, ...]

REVOKE privilege[,...] [(column[, ...])]

ON {[{database|*}.{table|*}] | *}

FROM 'user'@'host'[, ...]

Use this statement to revoke some or all privileges that were granted to a user with the GRANT statement. The first syntax is used to revoke all privileges from a user. Multiple users may be given in a comma-separated list. A list of users and their privileges are stored in the mysql database, in the user table in particular:

REVOKE ALL PRIVILEGES

ON *.*

FROM 'paola'@localhost';

To revoke only some privileges, use the second syntax structure, giving the specific privileges to be removed in a comma-separated list after the keyword REVOKE. For a list of privileges and their descriptions, see Table 4-2 under the description of the GRANT statement earlier in this chapter.

To revoke privileges for specific columns, list the columns within parentheses in a comma-separated list. Privileges that are granted based on columns are stored in the columns_priv table of the mysql database. Privileges may be revoked on a specific table for a specific database. To revoke privileges on all tables of a database, specify an asterisk as a wildcard for the table name. You can do the same for the database name to apply the statement to all databases. Table-specific privileges are stored in the tables_priv table, and database-specific privileges are stored in thedb table.

Name

SET PASSWORD

Synopsis

SET PASSWORD [FOR 'user'@'host'] = PASSWORD('password')

Use this statement to change the password for a user account. The username and host must be given. The change of password will apply only to the given combination of username and host. It won’t apply to other hosts for the same user in the grant tables.

To get a list of user accounts on your server, enter the following SQL statement:

SELECT User, Host FROM mysql.user;

If the FOR clause is not given with the SET PASSWORD statement, the current user account is assumed. The PASSWORD() function will encrypt the password given.

This statement does not need to be followed by a FLUSH PRIVILEGES statement. It will automatically update the privileges cache for the new password. If you updated your server from a version before 4.1 to a new version, you may have problems changing a user account’s password and cause the user account’s password to become invalid. You may need to run the mysql_fix_privilege_tables utility to change the Password column in the user table in the mysql database. See Chapter 16 for more information on this utility.

Here is an example of changing a user account’s password:

SET PASSWORD FOR 'kenneth'@'localhost' = PASSWORD('his_password');

Name

SHOW GRANTS

Synopsis

SHOW GRANTS [FOR 'user'[@'host']]

This SQL statement displays the GRANT statement for a given user. If the FOR clause is not given, the current user account is assumed. If the username is given without reference to a particular host, the wildcard % is assumed. Otherwise, the username should be followed by the host as shown here:

SHOW GRANTS FOR 'russell'@'localhost'\G

*************************** 1. row ***************************

Grants for russell@localhost:

GRANT ALL PRIVILEGES ON *.*

TO 'russell'@'localhost'

IDENTIFIED BY PASSWORD '57fa103a3c5c9f30'

WITH GRANT OPTION

The resulting statement is what would be entered to create the user russell for the host localhost, with the given privileges including the WITH GRANT OPTION flag.

Name

SHOW PRIVILEGES

Synopsis

SHOW PRIVILEGES

This statement provides a list of privileges available, along with the context of each one (e.g., server administration) and a description. The output is not based on the user. Instead, it’s a complete listing of the privileges that may be assigned to a user. This statement is available as of version 4.1 of MySQL.

Functions in Alphabetical Order

The following are MySQL functions in alphabetical order related to security and user account maintenance.

Name

AES_DECRYPT()

Synopsis

AES_DECRYPT(string, password)

This function decrypts text that was encrypted using the Advanced Encryption Standard (AES) algorithm with a 128-bit key length, reversing the AES_ENCRYPT() function. The function unlocks the encrypted string with the password given as the second argument. It returns NULL if one of the given parameters is NULL. This is available as of version 4.0.2 of MySQL. Here is an example:

SELECT AES_DECRYPT(personal, 'my_password') AS Personal

FROM teachers

WHERE teacher_id='730522';

+----------+

| Personal |

+----------+

| text |

+----------+

In this example, the value for the personal column is decrypted using the password given. The result is just the plain text of the column.

Name

AES_ENCRYPT()

Synopsis

AES_ENCRYPT(string, password)

This function encrypts a given string using the AES algorithm with a 128-bit key length. It locks the encrypted string with the password given as the second argument. The function returns NULL if one of the given parameters is NULL. It’s available as of version 4.0.2 of MySQL. The results of this function can be reversed with AES_DECRYPT(). Here is an example:

UPDATE teachers

SET personal = AES_ENCRYPT('text', 'my_password')

WHERE teacher_id = '730522';

Name

CURRENT_USER()

Synopsis

CURRENT_USER()

This function returns the username and the host that were given by the user for the current MySQL connection. There are no arguments for the function. It may not always return the same results as USER(). Here is an example:

SELECT CURRENT_USER( ), USER( );

+-----------------+------------------+

| CURRENT_USER( ) | USER( ) |

+-----------------+------------------+

| ''@localhost | russel@localhost |

+-----------------+------------------+

In this example, the user logged in to the mysql client with the username russel (missing one “l” in the name), but because there isn’t an account for that user, the client logged in with the anonymous (i.e., '') account.

Name

DECODE()

Synopsis

DECODE(string, password)

This function decrypts a given string that was encrypted with a given password. See the ENCODE() function later in this chapter:

SELECT ENCODE(pwd, 'oreilly')

FROM teachers

WHERE teacher_id = '730522';

This function decrypts the contents of the pwd column and unlocks it using the oreilly password, which was used to encrypt it originally using ENCODE().

Name

DES_DECRYPT()

Synopsis

DES_DECRYPT(string, [key])

This function decrypts text that was encrypted using the triple Data Encryption Standard (DES) algorithm with a 128-bit key length, reversing the DES_ENCRYPT() function. It returns NULL if an error occurs. The function will work only if MySQL has been configured for Secure Sockets Layer (SSL) support. It is available as of version 4.0.1 of MySQL. Here is an example:

SELECT DES_DECRYPT(credit_card_nbr, 0)

FROM orders

WHERE order_nbr = '8347';

In this example, the value for the credit_card_nbr column is decrypted using the first key string in the key file. See the description of DES_ENCRYPT() next for more information on key files.

Name

DES_ENCRYPT()

Synopsis

DES_ENCRYPT(string, [key])

This function returns encrypted text using the triple DES algorithm with a 128-bit key length. It returns NULL if an error occurs. The function is available as of version 4.0.1 of MySQL.

This function requires MySQL to be configured for SSL support. In addition, a key file must be created and the mysqld daemon must be started with the --des-key-file option. The key file should be set up with a separate key string on each line. Each line should begin with a single-digit number (0–9) as an index, followed by a space before the key string (e.g., key_number des_string).

The key given as the second argument to the function can either be the actual key to use for encryption or a number that refers to a key in the key file. If the second argument is omitted, the function uses the first key in the key file:

UPDATE orders

SET credit_card_nbr = DES_ENCRYPT('4011-7839-1234-4321')

WHERE order_nbr = '8347';

The results of this function can be reversed with DES_DECRYPT().

Name

ENCODE()

Synopsis

ENCODE(string, password)

This function encrypts a given string in binary format and locks it with the password. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD() instead. Here is an example:

UPDATE teachers

SET pwd = ENCODE('test', 'oreilly')

WHERE teacher_id = '730522';

The function here encrypts the word test and locks it with the oreilly password. The results are stored in the pwd column for the chosen teacher. To unlock the results, use the DECODE() function with the same password.

Name

ENCRYPT()

Synopsis

ENCRYPT(string[, seed])

This function returns encrypted text using the C-language crypt function. A two-character string may be given in the second argument to increase the randomness of encryption. The resulting string cannot be decrypted. You should not use this function for the password column in the usertable of the mysql database. Use PASSWORD() instead. Here is an example:

UPDATE teachers

SET pwd = ENCRYPT('test', 'JT')

WHERE teacher_id = '730522';

Name

MD5()

Synopsis

MD5(string)

This function uses a Message-Digest algorithm 5 (MD5) 128-bit checksum to return a 32-character hash value of string from the Request for Comments (RFC) 1321 standard. Here is an example:

SELECT MD5('Test') AS 'MD5( ) Test';

+----------------------------------+

| MD5( ) Test |

+----------------------------------+

| 0cbc6611f5540bd0809a388dc95a615b |

+----------------------------------+

Name

OLD_PASSWORD()

Synopsis

OLD_PASSWORD(string)

This function encrypts a given string based on the password encryption method used prior to version 4.1 of MySQL. The result cannot be decrypted. Here is an example:

UPDATE teachers

SET pwd = OLD_PASSWORD('test')

WHERE teacher_id = '730522';

Name

PASSWORD()

Synopsis

PASSWORD(string)

This function encrypts a password given as an argument. The result cannot be decrypted. This function is used for encrypting data in the password column of the user table in the mysql database. Here is an example:

UPDATE teachers

SET pwd = PASSWORD('test')

WHERE teacher_id = '730522';

Name

SESSION_USER()

Synopsis

SESSION_USER()

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SYSTEM_USER() and USER().

Name

SHA()

Synopsis

SHA(string)

This function returns the Secure Hash Algorithm (SHA) 160-bit checksum for the given string. The result is a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA1(). Here is an example:

SELECT SHA('test');

+------------------------------------------+

| SHA('test') |

+------------------------------------------+

| a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 |

+------------------------------------------+

Name

SHA1()

Synopsis

SHA(string)

This function returns the SHA 160-bit checksum for the given string. The result is a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA().

Name

SYSTEM_USER()

Synopsis

SYSTEM_USER()

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SESSION_USER() and USER().

Name

USER()

Synopsis

USER()

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SESSION_USER() and with SYSTEM_USER(). Here is an example:

SELECT USER( );

+-------------------+

| USER( ) |

+-------------------+

| russell@localhost |

+-------------------+