MySQL in a Nutshell [47]
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 the db 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.
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