A brief discussion on how to add, delete users and authorize in MySQL

This article will introduce you to the user management in MySQL and introduce the methods of adding users, authorizing and deleting users. I hope it will be helpful to you!

Do not directly use therootuser to manage application data. [Related recommendations:mysql video tutorial]

Add user

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

create user zhangsan identified by 'zhangsan';

The above command creates userzhangsan, the password iszhangsan. You can view the new user information in themysql.usertable:

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 The statement authorizes all operation permissions of thezhangsanDbdatabase to the userzhangsan.

You can view the new additions in themysql.dbtable Database permission information:

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

You can also view the command executed by permission grant through theshow grantscommand:

show grants for 'zhangsan';

privilegesCodeindicates the type of permission granted. , the following types are commonly used [1]

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

dbName.tableNameindicates the specific library or table to which permissions are granted. Commonly used ones include the following Several options

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

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

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

passwordSpecifies the password for the user to log in

flush privilegesIndicates refresh permission changes

Modify password

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

Delete user

drop user zhangsan@'%';

drop userThe command will delete the user and the corresponding permissions. After executing the command, you You will find that the corresponding records of themysql.usertable andmysql.dbtable have disappeared.

Common command group

Create a user and grant all permissions to the specified database

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

zhangsanand assign the database# All permissions of ##zhangsanDBare granted tozhangsan. If you wantzhangsanto be able to log in from this machine, you can givelocalhostadditional permissions:

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