Database Reference
In-Depth Information
With the second syntax, you must set the password separately using a subsequent
SET
PASSWORD
statement, but because you specify the plug-in explicitly, it's always clear which
one the
user
table row for the account will contain.
To create an account in a way that works consistently for any version of MySQL from
5.5 or later to ensure a designated nonempty
plugin
value, use this approach:
1. Create the account using a
CREATE
USER
statement that names the authentication
plug-in explicitly. Also, set the
old_passwords
system variable to select the pass‐
word hashing method appropriate for the plug-in (this affects the
PASSWORD()
function in the next step). The following sequences show how to do this for each
plug-in:
CREATE
USER
'
user_name
'@'
host_name
' IDENTIFIED WITH '
mysql_native_password
';
SET old_passwords = 0;
CREATE USER '
user_name
'@'
host_name
' IDENTIFIED WITH '
mysql_old_password
';
SET old_passwords = 1;
CREATE USER '
user_name
'@'
host_name
' IDENTIFIED WITH '
sha256_password
'
;
SET
old_passwords
=
2
;
2. Set the account password:
SET
PASSWORD
FOR
'
user_name
'@'
host_name
' = PASSWORD('
password
'
);
The
PASSWORD()
function hashes the password according to the
old_passwords
value just specified.
To assign privileges to the new account, which has none initially, use the
GRANT
statement
described later in this section.
CREATE
USER
fails if the account already exists.
Writing an account-creation helper procedure
To make it easier to create new accounts, we can write a helper stored procedure named
create_user()
that does all the work, given the account username, hostname, pass‐
word, and authentication plug-in:
• It issues the proper
CREATE
USER
statement to specify the plug-in explicitly.
• It sets the password, or, if the password is given as
NULL
, leaves the password unset.
(Presumably you'd specify
NULL
if you intend to assign the password later.)
• Before setting the password, it takes care of setting the
old_passwords
system vari‐
able to the appropriate value for the specified plug-in. It also saves and restores the
current
old_passwords
value, to leave its value in your session undisturbed.
• To implement a policy that users must select their own password, it uses
ALTER
USER
to expire the password immediately. The procedure skips this part if
ALTER
USER
is
