Mysql added and deleted user _ MySQL

Add and delete mysql users add and delete mysql Users

(Note: Because commands in the MYSQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * to username @ login host identified by 'password'

First:

Add a user c password of 123123 so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:

Grant select, insert, update, delete on *. * to c @ '%' Identified by '123 ';

Grant all on *. * to c @ '%' Identified by '000000' with grant option;

However, the added users are very dangerous. if someone knows the password of test1, then, he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. the solution is as follows:

First, add a user whose jorcen password is 123456 so that he can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the jorcen password, he cannot directly renew data on the internet either.

Mysql> grant select, insert, update, delete on test. * to jorcen @ localhost Identified by '20140901' with grant option;

Second:

If you do not want jorcen to have a password, you can run another command to cancel the password.

Mysql> grant select, insert, update, delete on test. * to jorcen @ localhost Identified '';

Mysql> grant all on test. * to jorcen @ localhost Identified '';

Last, refresh the permission;

Flush privileges;

Flush privileges;

Flush privileges;

However, the above operations are easy to appear.

ERROR 1045 (28000): Access denied for user 'C' @ '2017. 29.201.6 '(using password: YES)

This problem is solved in this way;

1. create a user;

CREATE USER 'c'@'%' IDENTIFIED BY '123123';

2. authorization;

grant all on *.* to c@'%'

Pri: privileges-Operation permissions of users, such as SELECT, INSERT, and UPDATE (for detailed list, see the end of this article ). use ALL .; databasename-database name, tablename-table name. if you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *.
Example:

GRANT SELECT, INSERT ON test.user TO 'c'@'%'; GRANT ALL ON *.* TO 'c'@'%';

Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION；

Set and change user passwords

Command:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

For the current login user

SET PASSWORD = PASSWORD("newpassword");

Revoke user permissions

Command:

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

Note: privilege, databasename, tablename-same as the authorization section.
Example

REVOKE SELECT ON *.* FROM 'c'@'%';

Note:

Assume that you GRANT the user 'C' @ '%' such authorization (or similar): grant select on test. user TO 'C' @ '%', use revoke select on *. * FROM 'C' @ '%'; the command does not cancel the SELECT operation on the user table in the test database.

Conversely, grant select on * is used for authorization *. * TO 'C' @ '%'; then revoke select on test. user FROM 'C' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.

FOR more information, run the show grants for 'C' @ '%' command.

Delete a user

Command:

DROP USER ‘username’@'host’;

What is the difference between using the drop and delete methods to delete users in mysql?

When I learned how to delete a user by using the drop method, I failed to follow the instructions in the book. then I checked some other information on the Internet to understand the drop method and posted it to share it with you.

(Method 1) drop user name;

Syntax: drop user name;

Purpose: delete an existing user. for example, to delete the user yan, the user yan @ "%" is deleted by default. if there are other users, for example, yan @ "localhost" and yan @ "ip" are not deleted together. If only one user yan @ "localhost" exists, an error is returned when using the statement (drop user yan ";) if you cannot determine the machine name (user name @ machine name), you can search in the mysql user table. The user column corresponds to the user name, and the host column corresponds to the machine name.

(Method 2) delete from user where user = "username" and host = "localhost ";

Delete is also a method for deleting users. for example, to delete a user named yan @ "localhost", you can (delete from user where user = "yan" and host = "localhost ";)

Note: The drop user not only deletes data in the user table, but also deletes content such as db and other permission tables. (Method 2) only deletes the content of the user table. other tables are not deleted. if you name a table with the same name as the deleted user, the permission will be inherited.

A typical database table creation and user creation process:

Create a user for localhost connection and specify the password mysql> create user 'pcom' @ 'localhost' identified by 'aaa7b2249'; Query OK, 0 rows affected (0.00 sec) create a database mysql> create database pcom default character set utf8 collate utf8_bin; Query OK, 1 row affected (0.00 sec) authorize the local user, you do not need to specify the password mysql> grant all on pcom. * to 'pcom' @ 'localhost'; Query OK, 0 rows affected (0.00 sec) authorize users under other IP addresses. note: The password must be specified here, otherwise, you can access mysql> grant a without a password. Ll on pcom. * to 'pcom' @ '192. 168.0.0/255.255.0.0 'identified by 'aaa7b2249'; Query OK, 0 rows affected (0.00 sec) similarly to mysql> grant all on pcom. * to 'pcom' @ '172. 255.0.0/255.255.0.0 'identified by 'aaa7b2249'; Query OK, 0 rows affected (0.00 sec) Done!

Appendix: Operation permissions in MySQL

ALTERAllows use of ALTER TABLE.     ALTER ROUTINE Alters or drops stored routines.     CREATEAllows use of CREATE TABLE.     CREATE ROUTINE Creates stored routines.     CREATE TEMPORARY TABLEAllows use of CREATE TEMPORARY TABLE.     CREATE USERAllows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.     CREATE VIEWAllows use of CREATE VIEW.     DELETEAllows use of DELETE.     DROPAllows use of DROP TABLE.     EXECUTEAllows the user to run stored routines.     FILE Allows use of SELECT… INTO OUTFILE and LOAD DATA INFILE.     INDEXAllows use of CREATE INDEX and DROP INDEX.     INSERTAllows use of INSERT.     LOCK TABLES Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.     PROCESS Allows use of SHOW FULL PROCESSLIST.     RELOAD Allows use of FLUSH.     REPLICATION Allows the user to ask where slave or master     CLIENT servers are.     REPLICATION SLAVE Needed for replication slaves.     SELECTAllows use of SELECT.     SHOW DATABASES Allows use of SHOW DATABASES.     SHOW VIEWAllows use of SHOW CREATE VIEW.     SHUTDOWN Allows use of mysqladmin shutdown.     SUPER Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.     UPDATEAllows use of UPDATE.     USAGE Allows connection without any specific privileges.