Got it

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

Latest reply: Feb 25, 2022 06:46:25 674 27 16 0 0


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


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 Workbench), and the content is given below.


a. MySQL Create User

b. MySQL Drop User

c. MySQL Show Users

d. MySQL User Password Change


MySQL Create User


The MySQL user is a record in the MySQL server's USER database that holds the MySQL account's login credentials, account privileges, and host information. In order to access and manage databases, you must first establish a user in MySQL.


The MySQL Create User statement allows us to create a new database server user account. For new accounts, it supports authentication, SSL/TLS, resource-limit, role, and password management characteristics. It also allows us to choose whether accounts should be restricted or unlocked at the start.


If you want to utilize the Create User statement, you must have the Create User statement global privilege or the INSERT privilege for the MySQL system schema. It throws an error if you try to create a user that already exists. If you use the IF NOT EXISTS clause, however, instead of an error message, the statement issues a warning for each identified user who already exists.


The requirement of MySQL server:


When the MySQL server is set up, it creates a ROOT user account that can only access and manage databases. However, there are situations when you wish to offer others database access without giving them complete control. In that instance, you'll establish a non-root user with specified database access and modification permissions.


Sample Scenario:

Create a user:


       CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';


In the above scenario, the account_name has two parts one is the username, and another is the hostname, which is separated by @ symbol. Here, the username is the name of the user, and the hostname is the name of the host from which the user can connect with the database server.


username@hostname  

or

username@%  


The steps for creating a new user in the MySQL server database are as follows.


Step 1: Open the MySQL server by using the MySQL client tool.


Step 2: Enter the password for the account and press Enter.

Enter Password: ********  


Step 3: Execute the following command to show all users in the current MySQL server.


mysql> select user from mysql.user;  

                                             

1


Step 4: Create a new user with the following command.


mysql> create user pet@localhost identified by 'zaheer@123';


2


Step 5: Now, we will use the IF NOT EXISTS clause with the CREATE USER statement.


mysql> CREATE USER IF NOT EXISTS pet@localhost IDENTIFIED BY ‘zaheer@123’;


New user privileges:


MySQL server provides multiple types of privileges to a new user account. Some of the most commonly used privileges are given below:


ü    ALL PRIVILEGES: It gives a new user account full access to all rights.

ü    CREATE: It permits the creation of databases and tables by the user account.

ü    DROP: It gives the user account the ability to delete databases and tables.

ü    DELETE: It gives the user account the ability to delete rows from a certain table.

ü    INSERT: It gives the user account the ability to add rows to a certain table.

ü    SELECT: It allows a user account to read data from a database.

ü    UPDATE: It allows the user account to make changes to table rows.


·                 Example - Privileges commands.


mysql> GRANT ALL PRIVILEGES ON * . * TO pet@localhost;  

OR

mysql> GRANT CREATE, SELECT, INSERT ON * . * TO pet@localhost;  


·                 Example - Privileges Flush commands.


Purpose of a user account privileges for changes occurs immediately


FLUSH PRIVILEGES;  


·                 Example – Show existing user privileges command.


mysql> SHOW GRANTS for username;  


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


Link:


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



EP 03_04:

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


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:10

good!
View more
  • x
  • convention:

Saqibaz
Created Jan 22, 2022 08:31:09

Thanks for sharing.
View more
  • x
  • convention:

Saqib123
Moderator Created Jan 23, 2022 08:52:52

Thanks for sharing.
View more
  • x
  • convention:

zaheernew
MVE Author Created Jan 23, 2022 19:25:02

  • x
  • convention:

Irshadhussain
Irshadhussain Created Jan 27, 2022 16:43:17 (0) (0)
 
Unicef
MVE Created Jan 24, 2022 01:22:57

WELL DONE
View more
  • x
  • convention:

hanhcao
Created Jan 24, 2022 14:34:38

Good one
View more
  • x
  • convention:

DienLg
Created Jan 25, 2022 05:05:47

Thanks for sharing
View more
  • x
  • convention:

Rumana
Created Jan 26, 2022 07:37:12

well explained
View more
  • x
  • convention:

BWGARM
Created Jan 26, 2022 15:20:29

Very clear explanation.
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.