MySQL - ALTER USER Statement

---

---

MySQL ALTER USER Statement

You can modify an existing account in MySQL using the ALTER USER Statement. Using this statement, you can change the authentication, role, SSL/TLS, resource-limit, and password-management properties.

To execute this statement, the current account needs to have CREATE USER or, ALTER USER privilege.

Syntax

Following is the syntax of the MySQL CREATE USER Statement −

ALTER USER [IF EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']

Example

Assume we have created a user using the CREATE USER statement.

CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

Following query changes the password of the above created user −

ALTER USER 'sample'@'localhost' IDENTIFIED BY 'testpassword';

You can modify the password of the current user using the following query −

ALTER USER USER() IDENTIFIED BY 'MyPassword';

Altering the user comment

Assume we have created a new user by adding a comment as −

drop user sample@localhost; 
CREATE USER 'sample'@'localhost' COMMENT 'Sample information';

You can verify he attributes and comments info as −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE 
USER='sample' AND HOST='localhost';

Output

Following is the output of the above query −

USER	HOST	ATTRIBUTE
sample	localhost	{"comment": "Sample information"}

Following query changes the comment created above −

ALTER USER 'sample'@'localhost' COMMENT 'new information';

You can verify the information about attributes and comments as follows −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE 
USER='sample' AND HOST='localhost';

Output

The above mysql query generates the following output −

USER	HOST	ATTRIBUTE
sample	localhost	{"comment": "new information"}

Altering the user attribute

Assume we have created a new user along with attributes as −

drop user sample@localhost; 
CREATE USER 'sample'@'localhost' ATTRIBUTE 
'{"attr1": "val1", "attr2": "val2"}'; 

You can verify he attributes and comments info as −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE 
USER='sample' AND HOST='localhost';

Output

The above mysql query will produce the following output −

USER	HOST	ATTRIBUTE
sample	localhost	{"attr1": "val1", "attr2": "val2"}

Following query alters the attributes created above −

ALTER USER 'sample'@'localhost' ATTRIBUTE 
'{"attr1": "newval1", "attr2": "newval2"}';

You can verify the information about attributes and comments as follows −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE 
USER='sample' AND HOST='localhost'; 

Output

Following is the output of the above query −

USER	HOST	ATTRIBUTE
sample	localhost	{"attr1": "newval1", "attr2": "newval2"}

Altering the role

We can create a role using the CREATE ROLE statement as −

CREATE ROLE 'TestAdmin', 'TestDeveloper';

Grant privileges to above created roles −

GRANT ALL PRIVILEGES ON * . * TO TestAdmin, TestDeveloper;

Assume we have created a user with default role as shown below −

CREATE USER IF NOT EXISTS testuser@localhost DEFAULT ROLE 'TestAdmin';

Grant privileges to the created user −

GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'localhost';

Following query alters the role of the above created user −

ALTER USER testuser@localhost DEFAULT ROLE 'TestAdmin';

The IF EXISTS clause

If you try to alter a user that doesn't exist error will be generated as shown below −

ALTER USER demo@localhost;
ERROR 1396 (HY000): Operation ALTER USER failed for 'demo'@'localhost'

If you use the IF EXISTS clause along with the ALTER FUNCTION statement as shown below, the specified user will be altered and if a function with the given name, doesn't exist the query will be ignored.

ALTER USER IF EXISTS sample;

The Expire clause

If you use the expire clause the old password it will expire immediately and the user need to choose new password at first connection.

drop user sample@localhost;
CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

Following query alters the above created password.

ALTER USER 'sample'@'localhost' IDENTIFIED BY 'MyPassword' PASSWORD EXPIRE;

You can also set interval to the expire clause as shown below −

drop user sample@localhost;
CREATE USER 'sample'@'localhost'
IDENTIFIED BY 'MyPassword'
PASSWORD EXPIRE INTERVAL 25 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Following query alters the above created password and its interval.

ALTER USER 'sample'@'localhost'
IDENTIFIED BY 'testpassword'
PASSWORD EXPIRE INTERVAL 225 DAY
FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 1;

Locking and unlocking

You can lock and unlock an existing user using the alter statement. Following query locks the user named "sample" −

ALTER USER 'sample'@'localhost' ACCOUNT LOCK;

And the following query unlocks it −

ALTER USER 'sample'@'localhost' ACCOUNT UNLOCK;