Database Reference
In-Depth Information
Many of the techniques shown here require administrative access,
such as the ability to modify tables in the
mysql
system database or
use statements that require the
SUPER
privilege. For this reason, to
carry out the operations described here, connect to the server as
root
rather than as
cbuser
.
23.1. Understanding the mysql.user Table
MySQL stores user account information in tables in the
mysql
system database. The
user
table is the most important because it contains account names and credentials. To
see its structure, use this statement:
SHOW
CREATE
TABLE
mysql
.
user
;
The
user
table columns that concern us here specify account names and authentication
information:
• The
User
and
Host
columns identify the account. MySQL account names comprise
a combination of username and hostname values. For example, in the
user
table
row for a
'cbuser'@'localhost'
account, the
User
and
Host
column values are
cbuser
and
localhost
, respectively. For a
'myuser'@'myhost.example.com'
ac‐
count, those columns are
myuser
and
myhost.example.com
.
• The
plugin
,
Password
, and
authentication_string
columns store authentication
credentials. MySQL does not store literal passwords in the
user
sytem table because
that is insecure. Instead, the server computes a hash value from the password and
stores the hash string.
— The
plugin
column indicates which authentication plugin the server uses to
check credentials for clients that attempt to use the account. Different plug-ins
implement password hashing methods of varying encryption strength. The fol‐
lowing table shows the plug-ins this chapter discusses:
Plug-in Authentication method
mysql_native_password
Native password hashing
mysql_old_password
“Old” native password hashing (deprecated)
SHA-256 password hashing (MySQL 5.6.6 or later)
sha256_password
MySQL Enterprise, the commercial version of MySQL, includes additional plug-
ins for authenticating using PAM or Windows credentials. These enable use of
passwords external to MySQL, such as Unix login passwords or native Windows
services.
— The
Password
column is used if the
plugin
column is
mysql_native_pass
word
or
mysql_old_password
. An empty
Password
value means “no password,”
