MySQL Operation Specifications (Summary)
User permission management
Create user
Command: create user 'username' @ 'host' identified by 'Password ';
Note:
Username
Host specifies the host on which the user can log on. If the local user can use localhost, if you want the user to log on from any remote host, you can use the wildcard %.
Password: the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.
For example, create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';
Note: When creating a user, you can add user information to the user table to add users.
Permission allocation
MySQL permission principle:
Permission control is mainly for security reasons. Therefore, we need to follow the following principles:
1. Grant only the minimum permissions that meet the requirements to prevent unauthorized users. If the user only needs to query, only the select permission should be granted, and the update, insert, or delete permission should not be granted to the user.
2. When creating a user, restrict the user's login host to a specified IP address or Intranet IP address segment.
3. delete a user without a password when initializing the database. Some users are automatically created after the database is installed. By default, these users do not have a password.
4. Set a password that meets the password complexity for each user.
5. Clear unnecessary users on a regular basis. Revoke permissions or delete users.
MySQL permissions:
Permission |
Permission level |
Permission description |
CREATE |
Databases, tables, or Indexes |
Create Database, table, or index Permissions |
DROP |
Database or table |
Delete database or table Permissions |
GRANT OPTION |
Database, table, or stored program |
Grant permission options |
REFERENCES |
Database or table |
|
ALTER |
Table |
Change tables, such as adding fields and indexes. |
DELETE |
Table |
Delete data permission |
INDEX |
Table |
Index permission |
INSERT |
Table |
Insert permission |
SELECT |
Table |
Query permission |
UPDATE |
Table |
Update Permissions |
CREATE VIEW |
View |
Create view Permissions |
SHOW VIEW |
View |
View view Permissions |
ALTER ROUTINE |
Stored Procedure |
Change Stored Procedure Permissions |
CREATE ROUTINE |
Stored Procedure |
Create Stored Procedure permission |
EXECUTE |
Stored Procedure |
Stored Procedure execution permission |
FILE |
File access on the server host |
File Access permission |
CREATE TEMPORARY TABLES |
Server Management |
Create temporary table Permissions |
LOCK TABLES |
Server Management |
Lock table Permissions |
CREATE USER |
Server Management |
Create User Permissions |
PROCESS |
Server Management |
View process Permissions |
RELOAD |
Server Management |
Execute commands such as flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload. |
REPLICATION CLIENT |
Server Management |
Copy permission |
REPLICATION SLAVE |
Server Management |
Copy permission |
SHOW DATABASES |
Server Management |
View database Permissions |
SHUTDOWN |
Server Management |
Disable database Permissions |
SUPER |
Server Management |
Run the kill thread permission |
The distribution of MySQL permissions refers to the permissions that can be set for tables and columns. This can be explained from a table in the official document:
Permission Distribution |
Possible Permissions |
Table Permissions |
'Select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index', 'alter' |
Column permission |
'Select', 'insert', 'update', 'references' |
Process permission |
'Execute ', 'alter Routine', 'Grant' |
MySQL permission control:
1. GRANT command instructions:
Create a superuser fog that only allows local login, and grant permissions to other users with the password test123
Mysql> grant all privileges on *. * to fog @ 'localhost' identified by "test123" with grant option;
GRANT command description:
All privileges indicates ALL permissions. You can also use select, update, and other permissions.
ON is used to specify which databases and tables the Permission applies.
*. * Indicates the database name, and * indicates the table name.
TO: grant permissions TO a user.
Fog @ 'localhost' indicates the fog user. @ is followed by a restricted host, which can be an IP address, IP address segment, domain name, and %. % indicates any location. Note: Here % Some versions do not include the local version. I have previously set % to allow a user to log on anywhere, but cannot log on locally. This is related to the version, when this problem occurs, add a localhost user.
Identified by specifies the user's logon password.
With grant option indicates that the user can GRANT permissions to others.
Note: Some users often do not specify the with grant option when creating operation users. As a result, this user cannot use the GRANT command to create users or GRANT permissions to other users.
2. Refresh Permissions
Use this command to make the permission take effect, especially when you update or delete the permission table user, db, host, and so on. In the past, the permission has not been updated after grant is used. If the permission is changed, use the flush privileges command to refresh the permission.
Mysql> flush privileges;
3. View Permissions
View the permissions of the current user:
Mysql> show grants;
+ --------------------------------------------------------------------- +
| Grants for root @ localhost |
+ --------------------------------------------------------------------- +
| Grant all privileges on *. * TO 'root' @ 'localhost' with grant option |
| Grant proxy on ''@'' TO 'root' @ 'localhost' with grant option |
+ --------------------------------------------------------------------- +
View the permissions of a user:
Mysql> show grants for 'fog' @ '% ';
4. revoke permissions
Mysql> revoke delete on *. * from 'fog' @ 'localhost ';
5. delete a user
Mysql> drop user 'fog' @ 'localhost ';
Query OK, 0 rows affected (0.01 sec)
6. Rename an account
Mysql> rename user 'fog' @ '%' to 'jim' @ '% ';
7. Change the password
1. Use the set password command
Mysql> set password for 'root' @ 'localhost' = PASSWORD ('123 ');
2. Use mysqladmin
[Root @ rhel5 ~] # Mysqladmin-uroot-p123456 password 1234 abcd
Format: mysqladmin-u username-p old password New password
3. Use update to directly edit the user table
Mysql> use mysql
Mysql> update user set PASSWORD = PASSWORD ('1234abcd') where user = 'root ';
Mysql> flush privileges;
For more details, please continue to read the highlights on the next page: