MySQL account and permission management

MySQL initial Account Management

The initial MySQL account is as follows:

[Root @ lx16 ~] # Mysql-u root
Mysql> select host, user, password from mysql. user;
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Lx16 | root |
| 127.0.0.1 | root |
|: 1 | root |
| Localhost |
| Lx16 |
| Localhost | root |
+ ----------- + ------ + ---------- +

MySQL has two types of Initial users:

1. Root Super account: Has all permissions and can do anything.
2. Anonymous Account: Anyone can connect to the server through it, but it has little permissions.

By default, these accounts do not have any passwords. Therefore, to ensure security, we must first set a password for all root accounts.

The first way to SET a PASSWORD is to use the set password statement. If we want to SET a PASSWORD for 'root' @ 'localhost', run the following command:

Mysql> set password for 'root' @ 'localhost' = password ('*****');

The second method to set a password is to directly update the user permission table. The advantage of this method is that you can set a password for multiple accounts at the same time. The following statement can modify the password of all root accounts at a time:

Mysql> update mysql. user set password = password ('***') where user = 'root ';
Mysql> flush privileges;

If you use the update method, you must explicitly tell the server to reload the permission table (flush privileges)

We strongly recommend that you delete anonymous accounts. The delete statement is as follows:

Mysql> drop user ''@ 'localhost ';
Mysql> drop user ''@ 'lx16 ';

After the preceding operations are performed, the data in the user permission table is as follows:

Mysql> select host, user, password from mysql. user;
+ ----------- + ------ + --------------------------------------------- +
| Host | user | password |
+ ----------- + ------ + --------------------------------------------- +
| Lx16 | root | * 578EC7851088AC1F2A67B100540344B03BD2BA99 |
| 127.0.0.1 | root | * 578EC7851088AC1F2A67B100540344B03BD2BA99 |
|: 1 | root | * 578EC7851088AC1F2A67B100540344B03BD2BA99 |
| Localhost | root | * 578EC7851088AC1F2A67B100540344B03BD2BA99 |
+ ----------- + ------ + --------------------------------------------- +

Create a new account

MySQL requires that you not only specify who (user_name) can connect, but also specify where (host_name) to connect, that is, even if the two accounts have the same name, if they will be connected from different clients, you also need to create an account for them.

You can use the following two wildcards to flexibly configure host name restrictions:

1. '%'-Match any multiple characters
2. '-Configure a character

The test account can be connected from any IP Address
Create user 'test' @ '%' identified '***';
The test account can only be connected locally
Create user 'test' @ 'localhost' identified '***';
The test account can only be connected from the '192. 192.% 'CIDR block.
Create user 'test' @ '192. 192.% 'identified '***';
You can also use the IP mask.
Create user 'test' @ '192. 168.2.2

Permission management

To authorize an account, you must use the Grant statement. If the account already exists, Grant the statement to authorize it. If the account does not exist, create the Grant statement first, and then authorize it.

You can use the show grants statement to obtain your own permissions:

Mysql> show grants;
+ Certificate --------- +
| Grants for root @ localhost |
+ Certificate --------- +
| Grant all privileges on *. * TO 'root' @ 'localhost' identified by password' * 578EC7851088AC1F2A67B100540344B03BD2BA99 'with grant option |
+ Certificate --------- +

You can also use show grants for to obtain permissions of other users:

Mysql> show grants for ''@ 'localhost ';
+ -------------------------------------- +
| Grants for @ localhost |
+ -------------------------------------- +
| Grant usage on *. * TO ''@ 'localhost' |
+ -------------------------------------- +

The above shows two types of special permissions. One is ALL (the PRIVILEGES keyword can be omitted later), indicating the permissions for ALL operations (but not Grant permissions, grant permissions are granted by with grant option), USAGE, and non-permission.

In some rare cases, we may need more detailed permission control. MySQL can authorize permissions on columns. The following statement indicates that the select permission for the entire table is given to test, however, only the update permission for the two columns (street, city) is granted to it:

Grant select, update (street, city) on sampdb. member to 'test' @ 'localhost ';

Recommended reading:

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL + KeepAlived + LVS single-point write master/Master synchronization high-availability architecture Experiment

MySQL master/Master synchronization Configuration

MySQL master-slave copy notes in CentOS 6.3