User access planning for MySQL

Read the MySQL on the user rights and security management, the feeling seems to be not very good understanding, first of all, MySQL connection mode is required to check three elements: User, passwd, host. And this user and passwd is not o above the user and passwd, this is only connected to MySQL server A username and password, and then based on the user, passwd, host permissions to access the corresponding database. This makes contact with a quick two years O classmate still very not accustomed to.

MySQL Login method, note that if you do not write the-h host, the default is H-localhost host authentication.
C:\users\administrator>mysql-h Localhost-u Xiaoyu-p
Enter Password: ******

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| Xiaoyu |

MySQL permissions can be programmed as global level,database level,table level,column level,routine level Five, with the corresponding permission classifications However, in the production environment is still very little too fine partition of authority, generally for the database level, a single application such as Tomcat WebLogic has the corresponding user connected to MySQL database, and root and other administrator users have global Level of all privileges.

For example, you can authorize users in the following ways
Mysql> Grant all on xiaoyu.* to [e-mail protected] identified by ' Xiaoyu_mys
QL ';
Query OK, 0 rows Affected (0.00 sec)

You can now log in to MySQL server in the following way
C:\users\administrator>mysql-h Localhost-u Xiaoyu_mysql-p
Enter Password: ************
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 20

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Test |
| Xiaoyu |
+--------------------+
See at this time with H localhost-u xiaoyu_asm-p xiaoyu_asm can connect to MySQL server to operate Xiaoyu database, and other database is unable to operate.

So what happens if a new user doesn't make a password?
Mysql> Grant all on xiaoyu.* to [email protected];
ERROR 1133 (42000): Can ' t find any matching row in the user table
Mysql> Grant all on xiaoyu.* to [email protected];
ERROR 1133 (42000): Can ' t find any matching row in the user table
Mysql> Grant all on xiaoyu.* to Xiaoyu;
ERROR 1133 (42000): Can ' t find any matching row in the user table
See if the newly created user does not specify a password whether the localhost, IP address, or% wildcard host cannot be created successfully, This is because if you do not specify a password and the Mysql.user table does not have this username and host record row, the above 1133 error is reported.

And with the password can be normal authorization, this need to pay attention to.
Mysql> Grant all on xiaoyu.* to [e-mail protected] identified by ' Xiaoyu ';
Query OK, 0 rows Affected (0.00 sec)

Deleting a user also needs to be removed with the federated host.
mysql> drop user [email protected]
;
Query OK, 0 rows Affected (0.00 sec)

If you do not specify a host, drop user defaults to the form of a wildcard character
Mysql> Grant all on xiaoyu.* to Xiaoyu identified by ' Xiaoyu ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> show grants for Xiaoyu;
+-------------------------------------------------------------------------------
------------------------+
| Grants for [email protected]%
|
+-------------------------------------------------------------------------------
------------------------+
| GRANT USAGE on *. xiaoyu ' @ '% ' identified by PASSWORD ' *2e8f2ecc253867baf7
fb6e8a7b17cfe2eb3da337 ' |
| GRANT all Privileges "Xiaoyu". * to ' xiaoyu ' @ '% '

Do not specify the host to delete the user, the original [email protected]% record has been deleted, do not specify IP or delete the host deleted user is actually [email protected]% users, generally recommended not to delete the user, preferably with a hostname or IP address
mysql> drop user Xiaoyu;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show grants for Xiaoyu;
ERROR 1141 (42000): There is no such grant defined for the user ' Xiaoyu ' on host '% '

Change Password:
So how to change the password, in fact, MySQL user password host login three elements are stored in Mysql.user and mysql.db, and so on five tables, and the above authorization grant and revoke also only operate MySQL under these five tables.

For example if we want to modify [email protected] password for MySQL
Directly modify the records in the Mysql.user table:
mysql> Update Mysql.user set Password=password (' MySQL ') where user= ' Xiaoyu ' and
host= ' 172.16.0.216 ';
Query OK, 1 row affected (0.07 sec)
Rows matched:1 changed:1 warnings:0

mysql> flush Privileges;
Query OK, 0 rows affected (0.01 sec)
Refresh the permissions table into memory, then you can login with a new password, of course [email protected] Before the password is void.
C:\users\administrator>mysql-h 172.16.0.216-u Xiaoyu-pmysql
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 26
Server version:5.5.25 MySQL Community Server (GPL)

Use the Grant/revoke statement to change the password:
Mysql> Grant all on xiaoyu.* to [e-mail protected] identified by ' Xiaoyu ';
Query OK, 0 rows Affected (0.00 sec)

C:\users\administrator>mysql-h 172.16.0.216-u Xiaoyu-pxiaoyu
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 30
Server version:5.5.25 MySQL Community Server (GPL)

Database system security:
As a database is the provision of access and data storage, the corresponding database security measures are very important, the corresponding MySQL database can be used in the following three aspects to construct a secure MySQL database system.

1 Network: Close the external network connection, this also basically eliminate the security threat caused by the external connection, but the database is mostly used as the application access and data storage, communication is generally necessary, can be deployed in MySQL server native direct connection MySQL database, of course, this depends on the system of archiving, Network access is required to allow MySQL server to provide access to a defined local area network, which can also be controlled at the database level, even if an extranet access is provided to control the corresponding host source.

2 Host: Reduce the host unnecessary services, close unnecessary ports, but also restrict the user's permissions to the login host, the MYSQLD data file and log file permissions planning to be as strict as possible, to avoid the intrusion of foreign users through the data file log file recovery.

3 database: Strict control of the source host and user rights, mainly the source of the host need to strictly control, try not to appear wildcard access to MySQL server host.

http://www.dbaxiaoyu.com/archives/577

User access planning for MySQL