MySQL Database Application (5) SQL Language (2)

3. Delete redundant accounts in the MySQL system

　　　　 Syntax: drop user “user “@”Host Domain” <----Pay attention to the quotation marks, which can be single or double quotation marks, but they cannot be omitted.

Four. Create MySQL users and grant user permissions

　　　　1. View the grant command through help Help

　　　　　　　　 1) Enter “help grant” in mysql to get the following help information

mysql> help grant

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass< span style="color: #800000;">';

GRANT ALL ON db1.* TO 'jeffrey< span style="color: #800000;">'@'localhost';

GRANT SELECT ON db2.invoice TO 'jeffrey< span style="color: #800000;">'@'localhost';

GRANT USAGE ON *.* TO 'jeffrey< span style="color: #800000;">'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

　　　　　　　　 pass Check the grant command help, you can easily find an example of creating a user and authorizing!

　　　　　　　　 2) The more commonly used method for operation and maintenance personnel to create users is to use the grant command to authorize permissions while creating users. Specific authorization examples are:

grant all on db1.* to 'jeffrey'@'localhost' identified by '< span style="color: #800000;">mypass';

　　　　　　　　 3) The above grant command The help also provides a method to create a user with the create command first, and then use grant authorization, that is, create a user and authorize permissions separately, for example:

create user < span style="color: #800000;">'jeffrey' @'localhost' span> identified by 'mypass ';

grant all on db1.* to 'jeffrey< span style="color: #800000;">'@'localhost';

The above two commands are equivalent to the following command:



grant all on db1.* to 'jeffrey< span style="color: #800000;">'@'loaclhost' identified by 'mypass ';

　　　　2, create a user through the grant command And authorize

　　　　　　　　1) The simple syntax of the grant command is as follows:

grant all privilege on dbname.* [email Protected]'localhost' identified by 'passwd';

　　　　　　　　 2) The list description is as follows:

　　　　　　　　 Description: The above command is to authorize the localhost host to manage all the permissions of the dbname database through the user username, The password is passwd. Among them, username, dbname, passwd can be modified according to business conditions.

　　　　　　　　 3) Operation case 1: Create an oldboy user, have all permissions to the test library, and allow login to the management database from the localhost host, and the password is oldboy123.

mysql> select user,host from  mysql.user;

+------+-----------------------+

| user | host |

+------+-----------------------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

+------+-----------------------+

6 rows in set (0.00 sec)

mysql> grant all privileges on test.* to [email protected]'localhost' identified by 'oldboy123';

Query OK, 0 rows affected (0.28 sec)



mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)



mysql> select user,host from mysql.user;

+--------+-----------------------+

| user | host |

+--------+-----------------------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| oldboy | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

+--------+-----------------------+

7 rows in set (0.00 sec)

　　　　　　　　 View the specific permissions of the authorized user oldboy

mysql> show grants for [email protected];

+----------------------------------------- -------------------------------------------------- --------------------+

| Grants for [email protected] |

+--------------------------------------------- -------------------------------------------------- ----------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515< /span>' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy< span style="color: #800000;">'@'localhost' |

+--------------------------------------------- -------------------------------------------------- ----------------+

2 rows in set (0.00 sec)

　　　　3, create Cooperate with grant

　　　　　　　　 1) First create a user username and password passwd, authorize the host localhost

create user 'username'@'localhost' identified by 'passwd';

　　　　　　　　 2) Then authorize the localhost host to manage all the permissions of the dbname database through the user username without a password.

grant all on dbname.* to 'username'@'loaclhost';

　　　　　　　　 3) Operation case 2: Create an oldgirl user and check the test library Have all permissions, allow login from the localhost host to manage the database, the password is oldgirl123.

　　　　　　　　 4) Practical demonstration

　　　　　　　　 Check the current database user situation, and then execute the corresponding command to create the user as follows: p>

mysql> create user [email protected] identified by 'oldgirl123';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for [emailprotected];

+----------------------------------------- -------------------------------------------------- ---------------------+

| Grants for [email protected] |

+--------------------------------------------- -------------------------------------------------- -----------------+

| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C< /span>' |

+--------------------------------------------- -------------------------------------------------- -----------------+

1 row in set (0.00 sec)

mysql> grant all on test.* to 'oldgirl'@'localhost< /span>';

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [emailprotected];

+----------------------------------------- -------------------------------------------------- ---------------------+

| Grants for [email protected] |

+--------------------------------------------- -------------------------------------------------- -----------------+

| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C< /span>' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl< span style="color: #800000;">'@'localhost' |

+--------------------------------------------- -------------------------------------------------- -----------------+

2 rows in set (0.00 sec)

　　　　4. Authorize hosts in the local area network to remotely connect to the database:

　　　　　　　 According to the grant command syntax, we know that the [emailprotected]’localhost’ location is the host authorized to access the database, and localhost can be used The domain name, IP address or IP segment is substituted. Therefore, to authorize the hosts in the LAN can be achieved by issuing the following:

　　　　　　　　a. Percent sign matching method

　　　　　　　　b. Subnet mask matching method

mysql> create user [email protected]'10.0.0.0/255.255.255.0' identified by 'test1';

Query OK, 0 rows affected (0.00 sec)



mysql> select user,host from mysql.user;

+---------+------------------------+

| user | host |

+---------+------------------------+

| test | 10.0.0.% |

| test1 | 10.0.0.0/255.255.255.0 |

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| oldboy | localhost |

| oldgirl | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

+---------+------------------------+

10 rows in set (0.00 sec)



mysql> quit

Bye

[[email protected] ~]# mysql -utest1 -ptest1 -h 10.0.0.7

Welcome to the MySQL monitor. Commands end with; or \g.

Your MySQL connection id is 10

Server version: 5.5.32 Source distribution



Copyright (c) 2000, 2013, Oracle and /or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> 

　　　　　　　　 connect to the remote database service through the mysql client:

　　　　　　　　 1) Local mysql -u’root -p’oldboy123′ to connect to the database Equivalent to mysql -uroot -p’oldboy123′ -h localhost

　　　　　　　　 2) To remotely connect to the 10.0.0.7 database, the command is mysql -utest -p’test’ -h 10.0.0.7, if you can To successfully connect, you also need to authorize through the following command on the 10.0.0.7 database server:

　　　　　　　　 grant all on *.* to [email Protected]’10.0.0.%’ identified by’test’;< /p>

　　　　　　　　 3) The code for connecting to the mysql server through the php server is written as;

 //$link_id=mysql_connect(‘host name’,‘user’,‘password’);

 $link_id=mysql_connect(‘10.0.0.7’,‘test’,‘test’) or mysql_error();

 if($link_id){

 echo "musql successful by oldboy!";

 }else{

 echo mysql_error();

}

?>

　　　　5. What are the permissions that MySQL users can authorize

< p>　　　　　　　　 1) First look at the permissions of the oldboy authorized earlier

　　　　　　　　 2) Cancel the read-only permission (SELECT) of oldboy.

mysql> REVOKE INSERT ON test.* FROM ‘oldboy‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [email protected];

+----------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------+

| Grants for [email protected] |

+--------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------------+

| GRANT USAGE ON *.* TO ‘oldboy’@‘localhost’ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515’ |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT,< span style="color: #000000;"> TRIGGER ON `test`.* TO'oldboy'@'localhost' |

+--------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------------+

2 rows in set (0.00 sec)

　　　　　　　　 Tip: At this time, we will check the oldboy user permissions, the ALL PRIVILEGES permissions have been Subdivided, but there is no select permission.

　　　　　　　　 Because, we can conclude that ALL PRIVILEGES includes permissions:

[[email protected] ~]< /span># mysql -uroot -pdubin -e "show grants for [email protected];"|grep -i grant |tail -1 |tr',''\n '>all.txt

[[emailprotected] ~ span>]# cat all.txt -n

 1 SELECT

 2 INSERT

 3 UPDATE

 4 DELETE

 5 CREATE

 6 DROP

 7 REFERENCES

 8 INDEX

 9 ALTER

 10 CREATE TEMPORARY TABLES

 11 LOCK TABLES

 12 EXECUTE

 13 CREATE VIEW

 14 SHOW VIEW

 15 CREATE ROUTINE

 16 ALTER ROUTINE

 17 EVENT

 18 TRIGGER 

　　　　　　　　 Therefore, it can be said that the permissions of ALL PRIVILEGES include:

 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO'oldboy'@'localhost'

 That is, when authorizing, you can authorize users to meet the minimum business requirements, instead of blindly authorizing "ALL PRIVILEGES". 

　　　　6. How does the enterprise production environment authorize user permissions?

　　　　　　　　1) Database authorization for blogs, CMS and other products:

　　　　　　　　 The principle of minimization should be adopted for the authorization of web connection users. Many open source software is installed through the web interface, so In addition to the 4 permissions of select, insert, update, and delete during installation, more dangerous permissions such as create and drop are also required.

mysql> grant select,insert,update,delete,create,drop on blog.* to'blog'@'10.0.0.%' identified by'oldboy' ;

Query OK, 0 rows affected (0.00 sec)

　　　　　　　　 Under normal circumstances, the four permissions of select, insert, update, and delete can be authorized. Some open source software, For example, discuzbbs, you also need create, drop and other dangerous permissions

　　　　　　　　 2) After the database table is generated, the create and drop authorizations must be withdrawn:

mysql > grant select,insert,update,delete,create,drop on blog.* to'blog'@'10.0.0.%' identified by'oldboy';

Query OK, 0 rows affected (0.00 sec)



mysql> revoke create on blog.* from ‘blog‘@‘10.0.0.%‘;

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [email protected]‘10.0.0.%‘;

+----------------------------- -------------------------------------------------- -----------------------------+

| Grants for [email protected]10.0.0.% |

+--------------------------------------------- -------------------------------------------------- -------------+

| GRANT USAGE ON *.* TO ‘blog‘@‘10.0.0.%‘ IDENTIFIED BY PASSWORD ‘*7495041D24E489A0096DCFA036B166446FDDD992‘ |

| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `blog`.* TO ‘blog‘@‘10.0.0.%‘ |

+--------------------------------------------- -------------------------------------------------- -------------+

2 rows in set (0.00 sec)

mysql> help grant

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass< span style="color: #800000;">';

GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘;

GRANT SELECT ON db2.invoice TO ‘jeffrey‘@‘localhost‘;

GRANT USAGE ON *.* TO ‘jeffrey‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 90;

grant all on db1.* to ‘jeffrey‘@‘localhost‘ identified by ‘mypass‘;

create user ‘jeffrey‘@‘localhost‘ identified by ‘mypass‘;

grant all on db1.* to ‘jeffrey‘@‘localhost‘;

以上俩条命令相当于下面一条命令：



grant all on db1.* to ‘jeffrey‘@‘loaclhost‘ identified by ‘mypass‘;

grant all privilege on dbname.* [email protected]‘localhost‘ identified by ‘passwd‘;

mysql> select user,host from mysql.user;

+------+-----------------------+

| user | host                  |

+------+-----------------------+

| root | 127.0.0.1             |

| root | ::1                   |

|      | localhost             |

| root | localhost             |

|      | localhost.localdomain |

| root | localhost.localdomain |

+------+-----------------------+

6 rows in set (0.00 sec)

mysql> grant all privileges on test.* to [email protected]‘localhost‘ identified by ‘oldboy123‘;

Query OK, 0 rows affected (0.28 sec)



mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)



mysql> select user,host from mysql.user;

+--------+-----------------------+

| user   | host                  |

+--------+-----------------------+

| root   | 127.0.0.1             |

| root   | ::1                   |

|        | localhost             |

| oldboy | localhost             |

| root   | localhost             |

|        | localhost.localdomain |

| root   | localhost.localdomain |

+--------+-----------------------+

7 rows in set (0.00 sec)

mysql> show grants for [email protected];

+---------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]                                                                                   |

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘oldboy‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515‘ |

| GRANT ALL PRIVILEGES ON `test`.* TO ‘oldboy‘@‘localhost‘                                                      |

+---------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

create user ‘username‘@‘localhost‘ identified by ‘passwd‘;

grant all on dbname.* to ‘username‘@‘loaclhost‘;

mysql> create user [email protected] identified by ‘oldgirl123‘;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for [email protected];

+----------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]                                                                                   |

+----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘oldgirl‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*2CADADD54086D5EB4C9F10E0430084D7F179885C‘ |

+----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> grant all on test.* to ‘oldgirl‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [email protected];

+----------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]                                                                                   |

+----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘oldgirl‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*2CADADD54086D5EB4C9F10E0430084D7F179885C‘ |

| GRANT ALL PRIVILEGES ON `test`.* TO ‘oldgirl‘@‘localhost‘                                                      |

+----------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> create user [email protected]‘10.0.0.%‘ identified by ‘test‘;

Query OK, 0 rows affected (0.00 sec)



mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)



mysql> quit

Bye

[[email protected] ~]# mysql -utest -ptest -h 10.0.0.7

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.32 Source distribution



Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.



mysql> 

mysql> create user  [email protected]‘10.0.0.0/255.255.255.0‘ identified by ‘test1‘;

Query OK, 0 rows affected (0.00 sec)



mysql> select user,host from mysql.user;

+---------+------------------------+

| user    | host                   |

+---------+------------------------+

| test    | 10.0.0.%               |

| test1   | 10.0.0.0/255.255.255.0 |

| root    | 127.0.0.1              |

| root    | ::1                    |

|         | localhost              |

| oldboy  | localhost              |

| oldgirl | localhost              |

| root    | localhost              |

|         | localhost.localdomain  |

| root    | localhost.localdomain  |

+---------+------------------------+

10 rows in set (0.00 sec)



mysql> quit

Bye

[[email protected] ~]# mysql -utest1 -ptest1 -h 10.0.0.7

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.5.32 Source distribution



Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.



mysql> 

        //$link_id=mysql_connect(‘主机名‘,‘用户‘,‘密码‘)；

        $link_id=mysql_connect(‘10.0.0.7‘,‘test‘,‘test‘) or mysql_error();

        if($link_id){

                echo "musql successful by oldboy!";

        }else{

                echo mysql_error();

        }

?>

mysql> show grants for [email protected];
+———————————————————————————————- —————–+
| Grants for [email protected] |
+—————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘oldboy‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515‘ |
| GRANT ALL PRIVILEGES ON `test`.* TO ‘oldboy‘@‘localhost‘ |
+—————————————————————————————————————+
2 rows in set (0.00 sec)

mysql> REVOKE INSERT ON test.* FROM ‘oldboy‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [email protected];

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]                                                                                                                                                                                                    |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘oldboy‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515‘                                                                                                                  |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO ‘oldboy‘@‘localhost‘ |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

[[email protected] ~]# mysql -uroot -pdubin -e "show grants for [email protected];"|grep -i grant |tail -1 |tr ‘,‘ ‘\n‘ >all.txt

[[email protected] ~]# cat all.txt -n

     1     SELECT

     2     INSERT

     3     UPDATE

     4     DELETE

     5     CREATE

     6     DROP

     7     REFERENCES

     8     INDEX

     9     ALTER

    10     CREATE TEMPORARY TABLES

    11     LOCK TABLES

    12     EXECUTE

    13     CREATE VIEW

    14     SHOW VIEW

    15     CREATE ROUTINE

    16     ALTER ROUTINE

    17     EVENT

    18     TRIGGER 

    GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO ‘oldboy‘@‘localhost‘ 

    即在授权时，可以授权用户最小的满足业务需求的权限，而不是一味的授权“ALL PRIVILEGES”。

mysql> grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘10.0.0.%‘ identified by ‘oldboy‘;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘10.0.0.%‘ identified by ‘oldboy‘;

Query OK, 0 rows affected (0.00 sec)



mysql> revoke create on blog.* from ‘blog‘@‘10.0.0.%‘;

Query OK, 0 rows affected (0.00 sec)



mysql> show grants for [email protected]‘10.0.0.%‘;

+------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]10.0.0.%                                                                                   |

+------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO ‘blog‘@‘10.0.0.%‘ IDENTIFIED BY PASSWORD ‘*7495041D24E489A0096DCFA036B166446FDDD992‘ |

| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `blog`.* TO ‘blog‘@‘10.0.0.%‘                                |

+------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)