• Choose a category
• All
• Classic-Fiction

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.