The Series of MySQL -3- MySQL User Management - EP 03_01
HI Hi, Greetings! 
Happy January 
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;
Step 4: Create a new user with the following command.
mysql> create user pet@localhost identified by 'zaheer@123';
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!
