Database Reference
In-Depth Information
• Removing and renaming accounts (
DROP
USER
,
RENAME
USER
)
Creating accounts
To create an account, use the
CREATE
USER
statement, which creates a row in the
mysql.user
table. But before you do so, decide these three things:
• The account name, expressed in
'
user_name
'@'
host_name
'
format naming the
user and the host from which the user will connect
• The account password
• The authentication plug-in the server should execute when clients attempt to use
the account
Authentication plug-ins use hashing to encrypt passwords for storage and transmission.
MySQL has several built-in plug-ins from which to choose:
•
mysql_native_password
implements the default password hashing method.
•
mysql_old_password
is similar but uses a hashing method that is less secure and is
now deprecated. Avoid choosing this plug-in for new accounts. If your server has
existing accounts that use it,
Recipe 23.8
discusses how to identify and modify them
to use
mysql_native_password
instead.
•
sha256_password
authenticates using SHA-256 password hash values, which are
cryptographically more secure than hashes generated by
mysql_native_pass
word
. This plug-in is available as of MySQL 5.6.6. It provides security beyond that
afforded by
mysql_native_password
, but additional setup is required to use it.
(Clients must connect using SSL or provide an RSA certificate.)
The
CREATE
USER
statement is commonly used in one of these forms:
CREATE
USER
'
user_name
'@'
host_name
' IDENTIFIED BY '
password
';
CREATE USER '
user_name
'@'
host_name
' IDENTIFIED WITH '
auth_plugin
'
;
The first syntax creates the account and sets its password with a single statement. It also
assigns an authentication plug-in implicitly, sort of:
• Before MySQL 5.6.6, the statement leaves the
plugin
column empty in the
user
table row for the account. It's preferable for the plug-in to be nonempty, for reasons
discussed in
Recipe 23.1
.
• As of 5.6.6, the statement assigns the plug-in named by the
--default-
authentication-plugin
setting (which is
mysql_native_password
, unless you
change it at server startup).