MySQL database user and Rights management

MySQL database user and Rights Management skill objectives

• Mastering MySQL User management
• Add an Administrative user
• Change Password and forget password change

The user authorization database is a very important link in the information system, and it is very important to manage it reasonably and efficiently. Typically, the administrator with the highest privileges creates different administrative accounts and assigns different operations permissions, handing them over to the appropriate managers using User Management 1: New user New User's command format is as follows "Create user ' username ' @ ' host ' [ identified by [PASSWORD] ' PASSWORD '] #大写是固定格式大括弧是一个整体再写命令的时候没有

• Username The user name that will be created
• host Specifies that the user is allowed to log on to the host terminal, which can be an IP address, a network segment, a specified local user localhost, and a wildcard% if the user can log on from any remote host
• Password setting the password for login

The following is the user password created after the MySQL installation, the password displayed in the database is saved in the form of ciphertext, greatly enhanced security

mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |+-----------+-------------------------------------------+-----------+2 rows in set (0.01 sec)

Create a new user

mysql> create user ‘accp‘@‘localhost‘ identified by ‘123123‘;Query OK, 0 rows affected (0.01 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

The delete user command format is as follows Drop "username ' @ ' host '

mysql> drop user ‘accp‘@‘localhost‘; #删除accpQuery OK, 0 rows affected (0.00 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || bent      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

User rename, formatted as follows rename user ' Old_user ' @ ' host ' to ' New_user ' @ ' host '

Mysql> select User,authentication_string,host from User; #这边我们把bent重命名为accp +-----------+-------------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | Bent | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec) mysql> Rename user ' bent ' @ ' localhost ' to ' accp ' @ ' localhost '; Query OK, 0 rows Affected (0.00 sec) mysql> Select User,authentication_string,host from user;+-----------+------------ -------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec)

Set Password to User 1: Set password for current user sets Password=password (' PASSWORD ')

Mysql> select User,authentication_string,host from User; +-----------+-------------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec) Mysql> Set Password=password (' 123123 '); #当前用户是root我把root用户密码改为了 "123123" compared to the root password above confidential the difference between query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> Select User, Authentication_string,host from user;+-----------+-------------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec)

2: Use Super Admin root to modify other user password, format as follows set PASSWORD for ' username ' @ ' host ' =password (' PASSWORD ');

Mysql> Select User,authentication_string,host from user;+-----------+------------------------------------------ -+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec) Mysql> Set Password for ' accp ' @ ' localhost ' =password (' 951116 '); #同样对比一下密文密码的区别Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> Select User,authentication_string,host from user;+- ----------+-------------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *e56a114692fe0de073f9a1dd68A00eeb9703f3f1 | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec)

Forgot Root Password Resolution

[[email protected] ~] systemctl stop Mysqld.service #关闭服务 [[email protected] ~] NETSTAT-NTAP | grep 3306 #查看端口有没有关闭 [[email protected] ~] MySQL--skip-grant-tables #会出现以下代码不要去动它重新开一个终端2018 -06-28t02 : 16:16.399381z 0 [note]-':: ' resolves to ':: '; 2018-06-28t02:16:16.399402z 0 [note] Server sockets created on IP: ':: '. 2 018-06-28t02:16:16.400217z 0 [Note] innodb:loading buffer pool (s) from/usr/local/mysql/data/ib_buffer_ pool2018-06-28t02:16:16.401959z 0 [Note] innodb:buffer pool (s) load completed at 180628 10:16:162018-06-28t02 : 16:16.410638z 0 [Note] executing ' SELECT * from INFORMATION_SCHEMA. TABLES; ' To get a list of TABLES using the deprecated partition engine. You could use the startup option '--disable-partition-engine-check ' to skip this check. 2018-06-28t02:16:16.410661z 0 [Note] Beginning of List of non-natively partitioned tables2018-06-28t02:16:16.423678z 0 [N OTE] End of List of non-natively partitioned tables2018-06-28t02:16:16.423748z 0 [Note] mysqld:ready for Connections. Version: ' 5.7.17 ' socket: '/usr/local/mysql/mysql.sock ' port:3306 Source distribution

[[email protected] ~] mysql -u root #直接这样登录跳过密码选项Welcome to the MySQL monitor.  

Log in and change user password

  mysql> Update mysql.user set Authentication_string=password (' 123123 ') where user= ' root '; #修改root密码Query OK, 1 row affected, 1 Warning (0.00 sec) Rows matched:1 changed:1 warnings:1mysql> flush privileges; #刷新数据库Query OK, 0 rows affected (0.01 sec) [[email protected] ~]# mysql-u root-p123123mysql: [Warning] Using a pass Word on the command line interface can insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.7.17 Source distributioncopyright (c) $, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.  

Authorization control again in MySQL, permission settings are important, and assigning permissions can clearly divide the responsibilities. Managers only need to focus on completing their own tasks, the most important thing is to ensure the security of the System Data 1: Grant permissions (1): Permissions control mainly for security reasons, the need to follow the following principles 1): only to meet the needs of the minimum permissions to prevent misoperation and do bad Things 2): Create a user limit the user's login host, General restrictions specify IP or intranet IP segment 3): Delete users without password when initializing the database, MySQL installation will automatically create a user without password 4): Set the password for each user to meet the requirements 5): Regularly clean up unwanted users (2): Grant permissions to use the Grant command, The command format is as follows in the grant permission list on the library name. Indicate to user @ host address [identified by ' Password '] command is very clear, is to specify that the user allows it to manipulate some tables, have the appropriate permissions for these tables to demonstrate how grant is used

mysql> grant select on ×××表.×××信息 to ‘accp‘@‘localhost‘ identified by ‘123123‘; Query OK, 0 rows affected, 1 warning (0.00 sec)

The above command means that the user ACCP can log on to the host localhost, the connection password is 123123, it has the SELECT permission to the database (XXX table. XXX information) login ACCP user to verify the following

[[email protected] ~]# mysql -u accp -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> insert into imployee_英航客户表.×××信息 values (2,‘张三‘,‘广州珠海‘,‘18888888‘);ERROR 1142 (42000): INSERT command denied to user ‘accp‘@‘localhost‘ for table ‘×××信息‘

The display SELECT statement works fine, but the INSERT statement does not have sufficient permissions when the user and host name are not present in the list, the user and host name are automatically created and the original password is automatically overwritten if the user password is not the same as the original password

Mysql> select User,authentication_string,host from User; +-----------+-------------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | ACCP | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost |+-----------+-------------------------------------------+-----------+3 rows in Set (0.00 sec) # Only three users in the list of users at this time, do not have user rights in a user list mysql> grant select OnXxx table. XXX information to ' benet ' @ ' localhost ' identified by ' 1223123 '; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> Select User,authentication_string,host from user;+-----------+--- ----------------------------------------+-----------+| User | authentication_string | Host |+-----------+-------------------------------------------+-----------+| Root      | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost | | Mysql.sys | *thisisnotavalidpasswordthatcanbeusedhere | localhost | | Benet | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | localhost | | ACCP | *0db339632b48910f8f0bef61bd7ead4441267e6e | localhost |+-----------+-------------------------------------------+-----------+4 rows in Set (0.00 sec) # The above automatically created the Benet user login password for ' 123123 '

The following settings Benet user limit the original password is 123123, I changed the limit password to the new password ' 321321 ' and then look at the original password can login

mysql> grant insert on ×××表.×××信息 to ‘benet‘@‘localhost‘ identified by ‘3221321‘;Query OK, 0 rows affected, 1 warning (0.00 sec)[[email protected] ~]# mysql -u benet -p123123mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user ‘benet‘@‘localhost‘ (using password: YES)#提示你输入正确的登陆密码

View User rights show GRANTS for ' username ' @ ' host address '

mysql> show grants for ‘accp‘@‘localhost‘;+------------------------------------------------------------------------------+| Grants for [email protected]                                                    |+------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘accp‘@‘localhost‘                                     || GRANT SELECT ON "×××表"."×××信息" TO ‘accp‘@‘localhost‘            |+------------------------------------------------------------------------------+2 rows in set (0.00 sec)

Revoke user Rights revoke permissions list on database name. Table name from user @ host address

mysql> revoke select on ×××表.×××信息 from ‘accp‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for ‘accp‘@‘localhost‘;+------------------------------------------+| Grants for [email protected]                |+------------------------------------------+| GRANT USAGE ON *.* TO ‘accp‘@‘localhost‘ |+------------------------------------------+1 row in set (0.00 sec)

Revoke all user permissions revoke all on the database name. Table name from user @ host address

MySQL database user and Rights management