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.
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().
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.