MySQL User management: Add users, authorize, delete users

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, and the password is Zhangsan. In the Mysql.user table you can see the information for the new user:

Authorized

Command format: Grant Privilegescode on Dbname.tablename to [e-mail protected] identified by "password";

grant all privileges on zhangsanDb.* to [email protected]‘%‘ identified by ‘zhangsan‘;flush privileges;

The above statement authorizes all operation permissions for the ZHANGSANDB database to the user Zhangsan.

In the Mysql.db table, you can view information about the new database permissions:

You can also grant commands to execute by using show grants the command to view permissions:

show grants for ‘zhangsan‘;

Privilegescode represents the type of permission granted, and is commonly used in the following types [1]:

• All privileges: all permissions.
• Select: Read Permissions.
• Delete: Remove permissions.
• Update: Updates permissions.
• Create: Creates permissions.
• Drop: Delete database, data table permissions.

Dbname.tablename represents a specific library or table that grants permissions, and often has the following options:

• . : Grants permissions to all databases on the database server.
• Dbname.*: Grants permissions to all tables in the DbName database.
• Dbname.dbtable: Grants permissions to dbtable tables in database DbName.

[email protected]Represents the granted user and the IP address that allows the user to log on. There are several types of host:

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

Password Specifies the face at which the user is logged on.

Flush Privileges indicates a refresh of permission changes.

The article starts with "blog Park-Chen Shuyi", please respect the original to keep the original link. 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 User

Run the following command to remove the user:

drop user [email protected]‘%‘;

The drop user command deletes users and their corresponding permissions, and after executing the command you will find that the corresponding records for the Mysql.user and mysql.db tables have disappeared.

Common command Groups

Create a user and grant full permissions to the specified database: For Web Apps to create MySQL users

create user zhangsan identified by ‘zhangsan‘;grant all privileges on zhangsanDb.* to [email protected]‘%‘ identified by ‘zhangsan‘;flush privileges;

User Zhangsan is created and all permissions to the database zhangsandb are granted to Zhangsan. If you want Zhangsan to be able to log on from the local computer, you can give the localhost permission more:

grant all privileges on zhangsanDb.* to [email protected]‘localhost‘ identified by ‘zhangsan‘;

MySQL User management: Add users, authorize, delete users