In this article we are going to discuss about
different operations on mysql user like lock, unlock, grant, revoke etc.
- Lock-unlock
- Expire-unexpired
- Check privileges
- Revoke privileges
- Change password
- Rename user (single user, multiple user)
- Drop user
select
user,host,authentication_string,password_expired,account_locked from user where
user like 'test%';
We can all accounts are unlocked and password not expired.
We will perform operations one by one
LOCK and UNLOCK
Let’s lock and unlock test@%
Locking the account
alter user ‘test’@’%’ account lock;
Checking the status
select user,host,authentication_string,password_expired,account_locked
from user where user like 'test%';
Unlocking it again
alter user ‘test’@’%’ account unlock;
This will affect only test@% not test@localhost, both are different users.
EXPIRE
Let’s expire password for 'test'@'localhost' and
revert it
alter user ‘test’@’localhost’ password expire;
Verification
select
user,host,authentication_string,password_expired,account_locked from user where
user like 'test%';
Password reset using values
alter user ‘test’@’localhost’ identified with
‘mysq_native_password’
as ‘*5BA7FD292EB16719F76792ECAF589CE4B62CCC94’;
Verifying status again
select
user,host,authentication_string,password_expired,account_locked from user where
user like 'test%';
CHECK PRIVILEGES
show grants for ‘test2’@’locahost’;
REVOKE PRIVILEGES
In below example grants of test2@localhost has been
revoked
show grants for ‘test2’@’localhost’;
revoke SELECT, INSERT, DELETE CREATE, DROP, RELOAD,
ALETER ON *.* from ‘test2’@’localhost’;
flush privileges;
show grants for ‘test2’@’localhost’;
CHANGING PASSWORD
In below example password of test3@% has been
altered.
select user, host, authentication_string,
password_expired, account_locked from user where user like 'test%';
alter user ‘test3’@’%’ identified by ‘Hudrt#12’;
select user, host, authentication_string,
password_expired, account_locked from user where user like 'test%';
RENAME USER
show grants for ‘test1’@’localhost’;
Single user
In mysql we can rename the user without dropping it but modification needs to be carried wherever it is not getting used, grants will not get altered
Single user- in below example ‘test1’@’localhost’
user has been renamed with ‘xyz1’@’localhost’
rename user ‘test1’@’localhost’ to
‘xyz1’@’localhost’;
show grants for ‘xyz1’@’localhost’;
select user, host, authentication_string,
password_expired, account_locked from user where user like xyz1%';
Multiple user
select user, host, authentication_string,
password_expired, account_locked from user where user like 'test%';
rename user ‘test’@’localhost’ to ‘abc’@’%’,
‘test’@’%’ to ‘abc’@’localhost’;
select user, host, authentication_string,
password_expired, account_locked from user where user like 'test%';
select user, host, authentication_string,
password_expired, account_locked from user where user like 'abc%';
DROP USER
In below example we are dropped abc user
select user,host from user where user=’abc’;
drop user ‘abc’@’localhost’;
select user,host from user where user=’abc’;
drop user ‘abc’@’%’;
select user,host from user where user=’abc’;