User & Security Statements

User access and privileges can be global (i.e., apply to all databases on the server), it 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 hogging and the indirect denial of service to other users. Thus, you can limit the number of connections or the maximum of 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.

AES_DECRYPT( )

This MySQL function decrypts text that was encrypted using the AES algorithm with a 128-bit key length, reversing the AES_ENCRYPT() function.

AES_ENCRYPT( )

This MySQL 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.

CREATE USER

This MySQL statement is fairly straightforward: it's used to create a user, but doesn't allow privileges to be granted with it. For that, you would use the GRANT statement.

CURRENT_USER( )

This MySQL function returns the user name and the host that were given by the user for the current MySQL connection. It may not always return the same results as USER( ).

DECODE( )

This MySQL function decrypts a given string that was encrypted with a given password. See also the ENCODE( ) function.

DES_DECRYPT( )

This MySQL function decrypts text that was encrypted using the triple DES algorithm with a 128-bit key length, reversing the DES_ENCRYPT() function.

DES_ENCRYPT( )

This MySQL function returns encrypted text using the triple DES algorithm with a 128-bit key length.

If a user has no privileges at all—meaning, after you've revoke all of a user's privileges with the REVOKE statement—you can use this statement to then remove a user from the MySQL completely.

ENCODE( )

This MySQL 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.

ENCRYPT( )

This MySQL function returns encrypted text using the C-language crypt function.

GRANT

This is probably the most used SQL statement for creating users and granting them privileges in MySQL. While is often used for creating users, it's main function is to set a user's privileges for a database system, specific databases, or specific tables.

MD5( )

This MySQL function use an MD5 128-bit checksum to return a 32-character hash value of string from the RFC 1321 standard.

OLD_PASSWORD( )

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

PASSWORD( )

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

If a MySQL administrator simply wants to change the user name or the host of an existing MySQL user account, but not the user account's privileges, an administrator can use this MySQL statement to do that easily.

RESET

Use this MySQL statement to reset certain server settings and log files.

RESET

Use this MySQL statement to reset certain server settings and log files.

REVOKE

This MySQL statement is used to easily revoke speciified privileges from a user without necessarily revoking all privileges or having to drop a user and create the user again with less privileges.

SESSION_USER( )

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

SET PASSWORD

Use this MySQL statement to change the password for a user account.

SHA( )

This MySQL function returns the Secure Hash Algorithm (SHA) 160-bit checksum for the given string. The results are a string composed of 40 hexadecimal digits.

SHOW GRANTS

This MySQL statement displays the GRANT statement for a given user.

SHOW PRIVILEGES

This MySQL 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.

SYSTEM_USER( )

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

USER( )

This MySQL function returns the user name and the hostname for the current MySQL connection. The function takes no arguments. It's synonymous with SESSION_USER( ) and SYSTEM_USER( ).