Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to add and delete users and authorization in MySQL, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Let's take a look at user management in MySQL, introduce how to add users, authorize and delete users, and don't use root users to manage application data directly.

Add user

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

Create user zhangsan identified by 'zhangsan'

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

Select User, Host, Password from mysql.user where User = 'zhangsan'; 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 rights of the zhangsanDb database to the user zhangsan. Exe.

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

Select User, Db, Host, Select_priv, Insert_priv, Update_priv, Delete_priv from mysql.db where User = 'zhangsan'

You can also view the commands that are granted to execute through the show grants command:

Show grants for 'zhangsan';privilegesCode indicates the type of permission granted, and the following types are commonly used [1]

All privileges: all permissions

Select: read permission

Delete: delete permission

Update: update permissions

Create: create permission

Drop: delete database and data table permissions

DbName.tableName represents a specific library or table that grants permissions, and the following options are commonly used

..: grant permissions to all databases on this database server

Permissions granted by dbName.*: 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 the user is allowed to log in. There are several types of Host

Localhost: only this user is allowed to log in locally, not remotely

%: allow remote login on any machine other than this machine

192.168.52.32: a specific IP indicates that only the user is allowed to log in from a specific IP.

Password specifies the password when the user logs in flush privileges means refresh permission change change password

Run the following command to change the user's password:

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

Run the following command to delete the user:

Drop user zhangsan@'%'

The drop user command deletes the user and the corresponding permissions. After executing the command, you will find that the corresponding records of the mysql.user table and the mysql.db table have disappeared.

Common command groups create users and grant full 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

The user zhangsan is created and all permissions for the database zhangsanDB are granted to zhangsan. If you want to enable zhangsan to log in locally, you can give localhost more permissions:

Grant all privileges on zhangsanDb.* to zhangsan@'localhost' identified by 'zhangsan'; on how to add, delete users and authorization in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.