Mysql permission operation, user management, password operation, mysql user management

Mysql permission operation, user management, password operation, mysql user management
Mysql Permissions

Mysql has four tables with control permissions: user table, db table, tables_priv table, and columns_priv table.

The verification process of the mysql permission table is as follows:

• Check whether the connected ip address, user name, and Password exist from the Host, User, and Password fields in the user table.
• After passing identity authentication, the permissions are assigned and verified in the order of user, db, tables_priv, and columns_priv.

Check the global permission table user first. If the user has the corresponding permission Y, the user has the permission Y for all databases and does not check db, tables_priv, columns_priv;

If the value is N, check the database corresponding to this user in the db table and obtain the permission Y in the db;

If N is in the database, check the specific table corresponding to the database in tables_priv, and obtain the permission Y in the table, and so on.

　　

1. query the database username, host, and password

mysql>select user,host,password from mysql.user;

 

2. Display authorization commands of a user

mysql>show grants for rep@'%';

 

3. Run the following command to grant a user the permission to select, insert, update, and delete the database test:

mysql>grant select,insert,update,delete on test.* to user01@'10.200.20.%' identified by '123';

4. commands that grant permissions for backing up the entire instance

mysql>grant lock tables,reload,super,select,show view,trigger,process on *.* to backup@'localhost' identified by '123';

5. commands that grant permissions for remote backup of each database

mysql>grant lock tables,select,show view,trigger,process  on *.* to backup@'10.200.20.18'   identified by '123';

Replication slave permission is required for master-slave replication,

The replication client permission is required to view the replication status.

 

6. Grant all permissions to the superuser and allow the superuser user to grant these permissions to other users.

mysql>grant all privileges on *.* to 'superuser'@'%' identified by '123'  with grant option;

 

 

7. revoke permissions (excluding granting permissions)

Mysql> revoke all privileges on *. * from superuser; # The new permission takes effect only when the superuser initiates the next request after modification.

 

8. revoke the authorization permission

mysql>revoke grant option on *.* from superuser ;

 

9. delete a mysql user

Mysql> drop user 'zhang' @ '%'; or mysql> delete from mysql. user where user = 'zhang' and host = '%'; mysql> flush privileges;

 

Mysql password

9. Change the user password (use the mysqladmin command to modify)

# Mysqladmin-u root password 123456 # When root does not have a password # mysqladmin-u root-p123456 password abcdef # root has a password (-p and password must be connected together) # mysqladmin-u superuser-p123456-h172.23.216.86 password abcdef # superuser's host is %, not localhost

 

10. Change the user password (use the set password command to modify)

mysql>set password for user01@'10.200.20.%'=password('123456');

11. Change the user password (use the grant command to re-authorize the user's connection password)

mysql>grant usage on test.* to user01@'10.200.20.%' identified by '123456';

12. Change the user password (use commands to directly modify the system table)

mysql>update mysql.user set password=password('12345678') where user='user01' and host='10.200.20.%'mysql>flush privileges;

Authorization Summary: 1. When mysqld is started, all authorization tables are read to the memory and take effect. When the server notices that the authorization table is changed, the existing client connection will be affected as follows.

• • The table and column permissions take effect in the next request of the client.
  • The database permission change takes effect in the next USE db_name command.
  • The change of global permissions and password takes effect the next time the client connects.

2. The grant, revoke, or set password command modifies the authorization table. The server automatically reloads the authorization table to the memory.

If you manually modify the authorization table (insert, update, delete), you need to manually flush privileges.

3. The host wildcard '%' of mysql does not include 'localhost ',The 'localhost' and ip' 127. 0.0.1 'are not the same. If "mysql-uroot-h localhost" is used, the socket file is connected by default,

If you use"Mysql-uroot-h 127.0.01", the TCP port is connected.

--------------------------------------------------------------------------- Supplement -------------------------------------------------------------------------

 

1. Differences between grant and create user when creating a user

There are also three methods for creating a user in mysql: grant is described above, but the insert user table and create user method can also be used.

create user 'zhangsan'@'10.200.20.%' identified by '123456';

Users created separately from the @ host:

 

mysql>create user 'zhang@%' identified by '123456';

The user is the same as the user created on the host after @: (this method is proved to be unfeasible. This method is used on the Internet)

 

To confirm the permissions created with create user, let's show grant.

mysql>show grants for 'zhang'@'%';

Usage indicates that you can only connect to and log on without other permissions.

All users created using the create user method can only connect to the database and grant permissions to the user.

 

Ii. Forget the mysql root Password and retrieve it

1. Stop the mysql service first.

#service mysqld stop    

2. Use mysqld_safe to add the parameter -- skip-grant-tables to start msyql.

#mysqld_safe --skip-grant-tables &

 

 

3. log on to mysql. If the account and password are empty, you can log on.

#mysql -uroot

4. Change the root user password and exit.

Mysql> update mysql. user set password = password ('today123') where user = 'root'; mysql> flush privileges; # after this step is completed, the permission table has been loaded into it, after quit, you need the password to log on again.
# To prohibit mysql, you must disable mysql and start mysql again.
Mysql> quit;

 

5. log out of mysql, restart mysql, and log on with a new password.

# Mysqladmin-uroot-ptoday123 shutdown # This method is safer, # ps-ef | grep mysqld # Check the mysql process. If the mysql process cannot be killed, use killall mysqld # service mysqld start # start mysql
# Mysql-uroot-ptoday123 # log on with the new password