Managing users in MySQL gives you the ability to control what users can and cannot do.
- Create user accounts with different privileges that are appropriate to their function.
- Avoid using the root account – Constrain compromised applications and protect against mistakes during routine maintenance.
- Ensure data integrity by proper assignment of individual user privileges. Permit authorized users to do their work. Prevent unauthorized users from accessing data beyond their privileges.
User Account Verification
When you connect to a MySQL server and execute a query, it authenticates you and authorizes your activity.
- Authentication: Verifies the user’s identity. This is the first stage of access control. You must successfully authenticate each time you connect. If you fail to authenticate, your connection fails and your client disconnects.
- Authorization: Verifies the user’s privileges. This second stage of access control takes place for each request on an active connection on which authentication has succeeded. For every request, MySQL determines what operation you want to perform, and then checks whether you have sufficient privileges to do so.
View User Account Settings
– Query the mysql database to view user identification info:
mysql> SELECT user, host, password -> FROM mysql.user WHERE user='root'; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | 127.0.0.1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | | root | ::1 | *2447D497B9A6A15F2776055CB2D1E9F86758182F | +------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
View all user info, including privileges:
mysql> SELECT * FROM mysql.user\G ***************** 1. row ***************** ... Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y ...
The mysql database contains the information for all user accounts on the server. To view this information, run the SELECT statements shown above. The value Y in a *_priv field indicates that the privilege is enabled. The root account has full access. All of its privilege columns have the value Y.
As well as privileges, the user table contains other information that is useful in the authentication process. For example, you can see in the following output that the tester user:
- Has a password (visible in encrypted form in the Password column), and this password is not expired (indicated by the N in the password_expired column)
- Has no defined resource limits (indicated by the 0s in the max_* columns)
- Does not have any SSL or x509 settings (indicated by the blank values in the ssl_* and x509_* columns)
- Uses the mysql_native_password plugin to authenticate (The plugin name is listed in the plugin column.)
*************************** 1. row ***************************
Host: localhost
User: testuser
Password: *14E65567ABDB5135D0CFD9A70B3032C179A49EE7
Select_priv: Y
Insert_priv: N
...
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
Native Authentication
When you connect to a MySQL server using the native password authentication plugin (the default authentication mechanism), it matches the username that you specified, the host from which you are connecting, and your password against rows in the mysql.user table to determine whether you can connect and perform actions.
To connect to the local server using the mysql client, specify the username and password for the account that you want to use:
shell> mysql -u[username] -p[password]
To connect to a server that is not installed on your client’s local host, provide the host name of the server to which you are connecting:
shell> mysql -u[username] -p[password] -h[server_host]
Creating a User Account
An account name consists of a username and the name of the client host from which the user must connect to the server. Account names have the format ‘user_name’@’host_name‘. Usernames can be up to 16 characters long. You must use single quotation marks around usernames and host names if these contain special characters, such as dashes. If a value is valid as an unquoted identifier, the quotes are optional. However, you can always use quotes.
For example, use the CREATE USER…IDENTIFIED BY statement to build an account:
– For a user named jim
– To connect from localhost
– Using the password Abc123
CREATE USER 'jim'@'localhost' IDENTIFIED BY 'Abc123';
Avoid possible security risks when creating accounts:
- Do not create accounts without a password.
- Do not create anonymous accounts.
- Where possible, avoid wildcards when you specify account host names.
Host Name Patterns
Use a host pattern containing the % or _ wildcard characters to set up an account that enables the user to connect from any host in an entire domain or subnet. If you omit the host part of an account name when writing an account management statement, MySQL assumes a host name of %.
A host value of %.example.com matches any host in the example.com domain. A host value of 192.168.% matches any host in the 192.168 subnet. A host value of % matches any host, permitting the user to connect from any host. Use an IP address with a subnet mask to enable the user to connect from any host with an address within that subnet. For example, a value of 10.0.0.0/255.255.255.0 matches any host with 10.0.0 in the first 24 bits of its IP address.
Avoid using wildcards in host names except where it is strictly necessary and properly audited to avoid abuse or accidental exposure. Run periodic checks as follows:
mysql> SELECT User, Host FROM mysql.user WHERE Host LIKE '%\%%';
Username and host name examples:
- john@10.20.30.40
- john@’10.20.30.%’
- john@’%.ourdomain.com’
- john@’10.20.30.0/255.255.255.0′
To specify an anonymous-user account (that is, an account that matches any username), specify an empty string for the username part of the account name:
mysql> CREATE USER ''@'localhost';
Avoid creating anonymous accounts, especially ones that have no password (as in the above example). This helps avoid security risks that would come from opening up access to the MySQL installation. If a host matches two or more patterns, MySQL chooses the most specific pattern.
Setting the Account Password
The most common way to change an existing account’s password without changing any of its privileges is to use the SET PASSWORD statement. For example, to set the password for jim on the local host to NewPass, use the following statement:
mysql> SET PASSWORD FOR jim@localhost = PASSWORD('NewPass');
If you are logged in as a non-root user and your user does not have the UPDATE privilege for the mysql database, you can change only your own password. Do this by using the SET PASSWORD statement without the FOR clause:
mysql> SET PASSWORD = PASSWORD('NewPass');
When using SET PASSWORD, use the PASSWORD() function to encrypt the password. Note that the CREATE USER statement automatically encrypts the password that you provide, so you do not need to use the PASSWORD() function when creating a user with CREATE USER.
Use the following mysqladmin commands to set passwords from the shell:
shell> mysqladmin -u root password 'rootpass' shell> mysqladmin -u root -h host_name password 'rootpass'
In the preceding examples, ‘rootpass’ represents the root password and ‘host_name’ is the name of the host from which the root account accesses the MySQL server.
Confirming Passwords
Assign strong, unique passwords to all user accounts. Avoid passwords that can be easily guessed. Use the following SELECT statement to list any accounts
without passwords:
SELECT Host, User FROM mysql.user WHERE Password = '';
To identify duplicate passwords:
SELECT User FROM mysql.user GROUP BY password HAVING count(user)>1;
To expire any users password, use below query:
ALTER USER jim@localhost PASSWORD EXPIRE;
To issue the SELECT statements in the examples above, you must connect with a user account with SELECT privileges on the mysql schema or mysql.user table. You can have multiple accounts that apply to a specific username. For example, if the user jim logs in from two locations and you set up accounts for each location, such as jim@localhost and jim@’192.168.14.38′, both accounts identified as jim might have the same password.
You can expire a user’s password with the ALTER USER…PASSWORD EXPIRE statement. If your password expires, you must change your password using a SET PASSWORD statement the next time you log in. All statements you execute that do not start with SET return an error until you change your password, as in this example:
mysql> SELECT * FROM City WHERE 1=2; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('new_pwd');
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM City WHERE 1=2;
Empty set (0.00 sec)
Manipulating User Accounts
Use the RENAME USER statement to rename a user account:
RENAME USER 'jim'@'localhost' TO 'james'@'localhost';
The above query changes the account name of an existing account or Changes either the username or hostname parts of the account name, or both.
Use the DROP USER statement to remove a user account:
DROP USER 'jim'@'localhost';
The above query revokes all privileges for an existing account and then removes the account. It also deletes all records for the account from any grant table in which they exist.