MySQL User management

MySQL user name and operating system user name are completely unrelated, even the maximum length may be different. For example, a UNIX user name is limited to 8 characters. The MySQL user name can be up to 16 characters in length.

MySQL's encrypted password uses its own algorithm. This encryption algorithm differs from the algorithm used by the UNIX logon process. The MySQL password is encrypted with the same method as the password () SQL function. The UNIX password is encrypted with the same method as the Encrypt () SQL function. From version 4.1, MySQL uses a stronger authentication method that provides better password protection during the connection than previous versions. Even if the TCP/IP packet is intercepted or the MySQL database is captured it is also safe (in previous versions, even if the password is saved in encrypted form to the user table, the MySQL server can still be connected by encrypting the password value).

View user account information: SELECT * from Mysql.user;

secure the initial MySQL account

The MySQL installation process includes setting up a MySQL database with authorization tables (only in UNIX cases): In Unix, use the mysql_install_db program to install the authorization table. You can run the program with some installation methods. Otherwise you need to do it manually. The authorization table defines the initial MySQL user account and access rights.

Created two root accounts. These accounts are hyper-user accounts and can perform any action. The password for the initial root account is empty, so anyone can connect to the MySQL server with the root account without any password and have all the permissions. In Unix, two root accounts are used to connect from a native. Must be connected from this computer, one account hostname should be designated as localhost, the other account is the actual host name or IP number.

Two anonymous user accounts have been created and each account has a null user name. The anonymous account does not have a password, so anyone can use the anonymous account to connect to the MySQL server. In Unix, two anonymous accounts are used to connect from a native. Must be connected from this computer, one account hostname should be designated as localhost, the other account is the actual host name or IP number. Two accounts have all the permissions of the test database or other database starting with test.

As you can see, our MySQL database is dangerous if we don't do any action on these initial accounts. All we can do is change the root password, delete the anonymous account or modify the anonymous account password.

To assign a password to the root account:

Method One: Use the Set Password statement

shell> Mysql-u Root
mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD ('newpwd');
mysql> SET PASSWORD for ' root ' @ 'host_name' = PASSWORD ('newpwd');

Method Two: Use Mysqladmin to specify the password for the root account

shell> mysqladmin-u root password "newpwd"
host_name Password "newpwd"

I used this method in the modified H for hostname times error message: ' Host ' *.*.corp ' is not allowed to connect to this MySQL server ', view the online workaround, execute the following authorization statement, Then log out of the MySQL server and execute the above statement successfully.

GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘;FLUSH PRIVILEGES;

Method Three: Modify the user table directly using update

shell> Mysql-u Root
mysql> UPDATE mysql.user SET Password = Password ('newpwd') WHERE user = ' root ';
mysql> FLUSH privileges;

To modify the Anonymous account password:

Method One:

shell> Mysql-u Root
mysql> SET PASSWORD for ' @ ' localhost ' = PASSWORD ('newpwd');
mysql> SET PASSWORD for ' @ 'host_name' = PASSWORD ('newpwd');

To see if it takes effect, you can view select Host,user,password from Mysql.user;

Method Two:

shell> Mysql-u Root
mysql> UPDATE mysql.user SET Password = Password ('newpwd') WHERE user = ';
mysql> FLUSH privileges;

After updating the password directly with update in the user table, you must have the server re-read the authorization table with flush privileges. Otherwise, the changes are not used until the server is restarted.

To delete an anonymous account:

shell> Mysql-u Root
Mysql> DELETE from mysql.user WHERE user = ';
mysql> FLUSH privileges;

Add Account

1. Using the grant statement
2. Direct operation of MySQL authorization form
3. third-party programs that use the MySQL account management feature. PhpMyAdmin is a program

method One:

 shell> MySQL--user=root-p 
 mysql> GRANT all privileges on *. * to ' Monty ' @ ' localhost ' 
   identified by ' Some_pass ' with GRANT OPTION; 
 Mysql> GRANT All privileges on * * to ' monty ' @ '% ' 
->     identified by ' Some_pass ' with GRA NT OPTION; 
 mysql> GRANT reload,process on *. * to ' admin ' @ ' localhost '; 
 mysql> GRANT USAGE on *. * to ' dummy ' @ ' localhost ';  

 has full permissions to do anything. An account (' Monty ' @ ' localhost ') is only used when connecting from the local computer. Another account (' Monty ' @ '% ') can be used to connect from other hosts. 
   mysql_install_db  The anonymous user account for the created localhost will be preempted. As a result, Monty will be treated as an anonymous user. 
     one account has username admin, no password. This account is only used to connect from the local computer. Reload and process management permissions are granted. These permissions allow the Admin user to perform  mysqladmin reload ,  mysqladmin refresh , and  mysqladmin flush-    xxx   commands, 
 and  mysqladmin processlist . Permission to access the database was not granted. You can add such permissions through the GRANT statement. 
 An account has a user name dummy, no password. This account is only used to connect from the local computer. No permissions were granted. With the usage permission in the GRANT statement, you can create an account without granting any permissions. It can set all global permissions to ' N '. Assume that you will grant specific permissions to the account at a later time. 
 
method Two: Create the same account directly with the INSERT statement, and then use flush privileges to tell the server to overload the authorization table

shell> MySQL--user=root MySQL
Mysql> INSERT into user
 -VALUES (' localhost ', ' Monty ', PASSWORD (' Some_pass '),
' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ');
mysql> INSERT into user
VALUES ('% ', ' Monty ', PASSWORD (' Some_pass '),
' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ');
mysql> INSERT into user SET host= ' localhost ', user= ' admin ',
-reload_priv= ' y ', process_priv= ' y ';
mysql> INSERT into User (Host,user,password)
VALUES (' localhost ', ' dummy ', ');
mysql> FLUSH privileges;
the reason to use flush privileges when you create an account with insert is to tell the server to reread the authorization table. Otherwise, the change will be noticed only after restarting the server. With GRANT, you do not need to use flush privileges. 
use the password () function with insert to encrypt the password. The GRANT statement encrypts the password for you and therefore does not require password (). 
The ' Y ' value enables account permissions. For the admin account, you can also use the more readable Insert extension syntax (using set). When you INSERT the user table, the default value for columns that are not specified is ' N '. 
note that to set up a Superuser account, you only need to create a user table entry with the permission column set to ' Y '. The user table permission is a global permission, so the entry is no longer required for other authorization tables. (This sentence actually I do not understand how to understand, understanding of children's shoes can share?) )

Example:
example creates 3 accounts, allowing them to access a dedicated database. The user name of each account is custom and the password is obscure. 
shell> MySQL--user=root MySQL
mysql> GRANT select,insert,update,delete,create,drop on bankaccount.*-' custom ' @ ' localhost ' identified by ' Obscure ';
mysql> GRANT select,insert,update,delete,create,drop on expenses.*-' custom ' @ ' whitehouse.gov ' identified by '  Obscure '; 
mysql> GRANT select,insert,update,delete,create,drop on customer.*-' custom ' @ ' Server.domain ' identified by ' Obscure ';
  These 3 accounts can be used to: the 1th account can access the BankAccount database, but only from the local computer.   A 2nd account can access the expenses database, but only from the host whitehouse.gov. A 3rd account can access the customer database, but only from the host Server.domain. 

If you want a user to access all machines from a given domain (for example, mydomain.com), you can use the GRANT statement with the '% ' wildcard in the host portion of the account name:
mysql> GRANT ... On * * to ' myname ' @ '%.mydomain.com ' identified by ' mypass ';


Delete user account

1.drop UserMYSQL1--Delete MYSQL1 user account
2.drop user [email protected]
Delete the account used to connect from other hosts MYSQL1, you can actually write the drop user' mysql1 ' @ '% '.
Delete the account MYSQL1 used to connect to the MySQL server locally.
3.revoke all privileges on * * FROM [email protected]
Remove all permissions except logins from the local connection MySQL server account MYSQL1, note that grant can create a user table login entry (add a record in the Mysql.user table) while authorizing a new user, but revoke can only cancel the appropriate permission.
The user is not removed from the User logon table.
4.delete from mysql.user where host= ' localhost ' and user= ' MYSQL1 ';
Flush privileges;
Removed directly from the Mysql.user table.

MySQL User management