Basic Operations on MySQL Users

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

User list can be fetched using query mentioned below in the database

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’;