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 the root user 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 user zhangsan, the password is zhangsan. You can view the new user information 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 The statement authorizes all operation permissions of the zhangsanDb database to the user zhangsan.

You can view the new additions in the mysql.db table 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 the show grants command:

show grants for 'zhangsan';

privilegesCode indicates 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.tableName indicates 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@host means 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: Specific IP Indicates that the user is only allowed to log in from a specific IP.

password Specifies the password for the user to log in

flush privileges Indicates refresh permission changes

Modify password

You can modify the user password by running the following command:

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

Delete user

Run the following command to delete the user:

drop user zhangsan@'%';

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

Common command group

Create a user and grant all permissions to the specified database

Suitable for web applications to create a MySQL user

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

Create the user

zhangsan and assign the database# All permissions of ##zhangsanDB are granted to zhangsan. If you want zhangsan to be able to log in from this machine, you can give localhost additional permissions: <pre class="brush:js;toolbar:false;">grant all privileges on zhangsanDb.* to zhangsan@&amp;#39;localhost&amp;#39; identified by &amp;#39;zhangsan&amp;#39;;</pre>[Related recommendations:

mysql video tutorial

]

The above is the detailed content of A brief discussion on how to add, delete users and authorize in MySQL. For more information, please follow other related articles on the PHP Chinese website!