MySQL Create a new user and authorize the example

There are two ways to add new users to MySQL: By using the grant statement or by directly manipulating the MySQL authorization table, the better approach is to use the grant statement, which is simpler and rarely goes wrong. The format of the grant statement is as follows:

GRANT permission on the database. Datasheet to User name @ login host identified by "password"

For example, add a new username named Phpuser, and the password is a string "brophp". So that he can log on any host, and all databases have query, insert, modify, delete permissions. First log in as root, and then enter the following command:

GRANT select,insert,update,delete on *.* to phpuser@ "%" identified by "brophp"

But this new user is very dangerous, and if the hacker knows the user's phpuser password, he can log on to your MySQL database on any computer on the Internet and do whatever you want with your data. The workaround is to authorize the login only on a particular machine or machines when adding users. For example, the above example should be changed to only allow login on localhost, and the database mydb can be queried, inserted, modified, deleted, and so on, so that even if the hacker knows the Phpuser user's password, the MyDB database cannot be accessed from other machines on the network. can only be accessed through a Web page on the MySQL host. The commands you enter are as follows:

GRANT select,insert,update,delete on Mydb.*to phpuser@localhost identified by "brophp"

Example

Add a user custom, he can connect from the host localhost, Server.domain and whitehouse.gov. He only wants to access the BankAccount database from the localhost, access the expenses database from whitehouse.gov, and access the customer database from all 3 hosts. He wants to use the password stupid from all 3 hosts.

To use the GRANT statement to set permissions for a user, run these commands:

shell> MySQL--user=root MySQL

Mysql> GRANT Select,insert,update,delete,create,drop
On bankaccount.* to custom@localhost identified by ' stupid ';
Mysql> GRANT Select,insert,update,delete,create,drop
On expenses.* to custom@whitehouse.gov identified by ' stupid ';
Mysql> GRANT Select,insert,update,delete,create,drop
On customer.* to custom@ '% ' identified by ' stupid ';

==============================================
Permission information is stored in the MySQL database (that is, in a database named MySQL) with user, DB, host, Tables_priv, and Columns_priv tables.

Setting up and changing user passwords

Command: Set PASSWORD for ' username ' @ ' host ' = PASSWORD (' newpassword '); If the current login user uses SET PASSWORD = PASSWORD ("NewPassword");

Example: SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

Revoke user Rights

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-with authorization section.

Example: REVOKE SELECT on *.* from ' pig ' @ '% ';

Note: If you are giving the user ' pig ' @ '% ' authorization to be like this (or similar): GRANT select on Test.user to ' pig '% ', then use revoke SELECT on *.* ' pig ' @ '% '; command not to withdraw Pin the user's SELECT action on the Users table in the test database. Conversely, if the authorization is using Grant SELECT on *.* to ' pig '% ', then revoke select on Test.user from ' pig ' @ '% '; Revoke SELECT permissions for the user table in the test database.

Specific information can be used to show the grants for ' pig ' @ '% '; View.

Delete User

Command: DROP USER ' username ' @ ' host ';