Got it

The Series of MySQL -2- MySQL User Management - EP 03_04

Latest reply: May 21, 2022 08:40:55 839 33 21 0 0

The Series of MySQL -2- MySQL User Management - EP 03_04


HI Hi, Greetings!  i_f47.gif

Happy January i_f25.gif


Hi Everyone,


Today, I would like to share with you an article about the third episode of Series of MySQL (MySQL User Management), and the content is given below.



a. MySQL Create User

b. MySQL Drop User

c. MySQL Show Users

d. MySQL User Password Change


MySQL User Password Change


MySQL user is a record that provides the login details, account privileges, and host information for the MySQL account that is used to access and manage the database. The username and password are included in the login information. In some circumstances, changing the user password in the MySQL database is required.


You must keep the following facts in mind when changing the password of any user account:


  • The information about the user account you want to modify.

  • The password-changing application for the user whose password you want to change. The application will be unable to connect to the database server if the user account password is reset without altering the application connection string.


We can update the password for a user account in MySQL in three distinct ways, as shown below:


  1. UPDATE Statement

  2. SET PASSWORD Statement

  3. ALTER USER Statement


Let's look at how to change the password for a user account in MySQL using the aforementioned statement in more detail:


UPDATE statement - Change password


This is the first approach to update the user table of the MySQL database by changing the user password. After executing an UPDATE statement, you must use the FLUSH PRIVILEGE statement to reload privileges from the grant table of the MySQL database.

If you want to alter or update the password for a user named pet who connects from localhost and uses the password zaheer123, use the SQL statements below:


mysql> USE mysql;  


mysql> UPDATE user SET password = PASSWORD('zaheer123') WHERE user = 'pet' AND host = 'localhost';  


mysql> FLUSH PRIVILEGES;  


The above statement will not function if you are using MySQL version 5.7.6 or higher. It's because the authentication_string field in the MySQL user table only saves the password. The authentication string column is now included in the UPDATE statement in higher versions, as shown in the following sentence.


mysql> USE mysql;


mysql> UPDATE user SET authentication_string = PASSWORD('zaheer123') WHERE user = 'pet' AND host = 'localhost';  


mysql> FLUSH PRIVILEGES;


SET PASSWORD statement - Change password


The SET PASSWORD statement is the second technique to alter a MySQL database user's password. You must have the UPDATE privilege to change the password for the other account. The SET PASSWORD line utilizes the username@localhost format for the user account.


For reloading privileges from the grant tables of the MySQL database, there is no need to use the FLUSH PRIVILEGES statement. Using the SET PASSWORD command, we may change the password of user account peter:


mysql> SET PASSWORD FOR 'pet'@'localhost' = PASSWORD('zaheer123');  


If you are using MySQL version 5.7.6 or higher, the above statement is deprecated and will not work in future releases. Instead, we need to use the following statement:


mysql> SET PASSWORD FOR 'pet'@'localhost' = zaheer123;  


ALTER USER PASSWORD statement - Change password


The ALTER USER statement is the third method for changing a MySQL user's password. For altering a user account's password, MySQL employs the ALTER USER statement with the IDENTIFIED BY clause. To replace pet password with zhe123, we must use the following syntax.


mysql> ALTER USER pet@localhost IDENTIFIED BY 'zhe123';  


You may need to change the password for the MySQL root account on occasion. In that scenario, you can cause the MySQL database server to stop and restart without utilizing the grant table validation.



You are welcome to like and leave feedback in the comment area.


Link:


EP 03_01:


The Series of MySQL -3- MySQL User Management - EP 03_01



EP 03_02:

The Series of MySQL -3- MySQL User Management - EP 03_02



EP 03_03:

The Series of MySQL -3- MySQL User Management - EP 03_03


Source:

·        https://www.w3schools.com/mySQl/default.asp

·        https://www.mysqltutorial.org/mysql-basics/



M M Zaheer Hussain

Stay Safe!

tt

The post is synchronized to: Author groupzaheernew's Author Collection

  • x
  • convention:

fuzi_yao
Admin Created Jan 22, 2022 08:25:33

good!
View more
  • x
  • convention:

DienLg
Created Jan 23, 2022 03:52:20

Good one
View more
  • x
  • convention:

zaheernew
zaheernew Created Jan 23, 2022 04:43:31 (0) (0)
Awesome  
Saqib123
Moderator Created Jan 23, 2022 08:53:30

Thanks for sharing.
View more
  • x
  • convention:

shakeela
Created Jan 23, 2022 08:55:31

Thanks for sharing.
View more
  • x
  • convention:

hanhcao
Created Jan 23, 2022 09:59:48

Well done
View more
  • x
  • convention:

zaheernew
MVE Author Created Jan 23, 2022 19:24:33

  • x
  • convention:

Unicef
MVE Created Jan 24, 2022 01:23:29

THANKS YOU
View more
  • x
  • convention:

Rumana
Created Jan 26, 2022 07:36:16

well explained
View more
  • x
  • convention:

Sara_Obaid
Created Jan 26, 2022 16:18:19

Well explained. Thank you

View more
  • x
  • convention:

123
Back to list

Comment

You need to log in to comment to the post Login | Register
Comment

Notice: To protect the legitimate rights and interests of you, the community, and third parties, do not release content that may bring legal risks to all parties, including but are not limited to the following:
  • Politically sensitive content
  • Content concerning pornography, gambling, and drug abuse
  • Content that may disclose or infringe upon others ' commercial secrets, intellectual properties, including trade marks, copyrights, and patents, and personal privacy
Do not share your account and password with others. All operations performed using your account will be regarded as your own actions and all consequences arising therefrom will be borne by you. For details, see " User Agreement."

My Followers

Login and enjoy all the member benefits

Login

Block
Are you sure to block this user?
Users on your blacklist cannot comment on your post,cannot mention you, cannot send you private messages.
Reminder
Please bind your phone number to obtain invitation bonus.
Information Protection Guide
Thanks for using Huawei Enterprise Support Community! We will help you learn how we collect, use, store and share your personal information and the rights you have in accordance with Privacy Policy and User Agreement.