MySQL User Management: Add Users, Authorize, Delete Users

This article introduces the operations and commands of add users, authorize, delete users in MySQL user management.

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

Add user
Log in to the database as the root user and run the following command:

create user zhangsan identified by'zhangsan';

The above command creates the user zhangsan with the password zhangsan. You can view the new user information in the mysql.user table:

Authorization
Command format: grant privilegesCode on dbName.tableName to username@host identified by "password";

grant all privileges on zhangsanDb.* to zhangsan@'%' identified by 'zhangsan';
flush privileges;

The above statement authorizes all operation permissions of the zhangsanDb database to the user zhangsan.

You can view the information of the newly added database permissions in the mysql.db table:

You can also use the show grants command to view the permissions granted to execute the command:

show grants for 'zhangsan';

privilegesCode represents the type of privileges granted. The following types are commonly used [1]:

• all privileges: All privileges.
• select: Read permission.
• delete: delete permissions.
• update: Update permissions.
• create: Create permissions.
• drop: Delete database and data table permissions.

dbName.tableName indicates the specific library or table to which permissions are granted. The following options are commonly used:

• Grant permissions to all databases of the database server.
• dbName.*: Grant permissions to all tables in the dbName database.
• dbName.dbTable: Grant permissions to the dbTable table in the database dbName.

username@host indicates the granted user and the IP address that allows the user to log in. The Host has the following types:

• localhost: Only allow the user to log in locally, not remotely.
• %: Allow remote login on any machine except this machine.
• 192.168.52.32: The specific IP means that only the user is allowed to log in from the specific IP.

password specifies the user's face when logging in.

flush privileges means refreshing permission changes.

change Password
Run the following command to modify the user password

update mysql.user set password = password('zhangsannew') where user ='zhangsan' and host ='%';
flush privileges;

delete users
You can delete a user by running the following command:

drop user zhangsan@'%';

The drop user command will delete the user and the corresponding permissions. After executing the command, you will find that the corresponding records in the mysql.user and mysql.db tables have disappeared.

Common command group
Create a user and grant all permissions to the specified database: suitable for Web applications to create MySQL users

create user zhangsan identified by 'zhangsan';
grant all privileges on zhangsanDb.* to zhangsan@'%' identified by 'zhangsan';
flush  privileges;

create user zhangsan identified by'zhangsan';
grant all privileges on zhangsanDb.* to zhangsan@'%' identified by'zhangsan';
flush privileges;
The user zhangsan is created and all permissions of the database zhangsanDB are granted to zhangsan. If you want to enable zhangsan to log in from this machine, you can give more localhost permissions:

grant all privileges on zhangsanDb.* to zhangsan@'localhost' identified by 'zhangsan';