MySQL DBA Advanced OPS Learning Note-Multiple ways to create MySQL users and authorization in combat

9.8 creating MySQL users and giving them permissions

9.8.1 Help view the grant command

1. Obtain the following help information by entering "assist Grant" in MySQL.

mysql> help grant;……省略部分…….CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;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;……省略部分…….

2. Operators are more commonly used to create users by using the grant command to create a user's permission to authorize the specific authorization example:

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

3. The above Grant command help also provides a way to create a user with the creation command before using grant authorization, that is, create user and authorization permissions separately, as follows:

CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘;

The above two commands are equivalent to one of the following commands

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

9.8.2 creating a user with the grant command and authorizing

The simple syntax for the 1.Grant command is as follows

Grant all privileges on dbname.* to [email protected] identified by ‘passwd’;

2. List description

3. Case: Create user Zhangsan, have all permissions on test library, allow login from localhost host to manage database, password is zhangsan123.

The specific command to implement the above actions is

mysql> grant all privileges on test.* to ‘zhangsan‘@‘localhost‘ identified by ‘zhangsan123‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

Check the specific permissions of the authorized user Zhangsan

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

9.8.3 Create and Grant mate methods

1. First create user username and password passwd, authorize host localhost.

CREATE USER ‘username‘@‘localhost‘ IDENTIFIED BY ‘passwd‘;mysql> create user ‘lisi‘@‘localhost‘ identified by ‘kisi123‘;Query OK, 0 rows affected (0.01 sec)mysql> show grants for ‘lisi‘@‘localhost‘;Grants for [email protected]   GRANT USAGE ON *.* TO ‘lisi‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*686008E0BFD16925072B84AA099EB5BC8375C35B‘ 1 row in set (0.00 sec)

The default permissions are usage, and the permissions of the connection, because there is no permission at this time.

2. Then authorize the localhost host to manage all permissions on the test database via user username, without a password.

mysql> grant all on test.* to ‘lisi‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for ‘lisi‘@‘localhost‘;Grants for [email protected]   

Tip: You can see that the default permissions are the usage-as-a-connection permission, followed by the all permission.

9.8.4 Authorized host remote connection database in LAN

According to the syntax of the grant command, we know that [email protected] location is the host of authorized access to the database, localhost can be replaced with domain name, IP address or IP segment, so to authorize the host in the LAN can be implemented by the following methods.

A. percent-semicolon matching method

[email protected] 01:5945->grant all privileges on test.* to ‘zbf‘@‘192.168.1.%‘ identified by ‘zbf123‘;Query OK, 0 rows affected (0.01 sec)[email protected] 01:5950->show grants for ‘zbf‘@‘192.168.1.%‘;Grants for [email protected]%   GRANT USAGE ON *.* TO ‘zbf‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*E2190B1F46FD9E171DD25B61138EA7F4F4D82B8C‘ GRANT ALL PRIVILEGES ON `test`.* TO ‘zbf‘@‘192.168.1.%‘  2 rows in set (0.00 sec)[email protected] 02:0023->flush privileges;Query OK, 0 rows affected (0.00 sec)

B. Subnet mask configuration method

[email protected] 02:3013->grant all privileges on test.* to ‘wwn‘@‘192.168.1.0/255.255.255.0‘ identified by ‘wwn520‘;Query OK, 0 rows affected (0.01 sec)[email protected] 02:3127->flush privileges;Query OK, 0 rows affected (0.00 sec)

Connect offsite database services via MySQL client:

1. Local mysql–uroot–pzbf666 Connection database equivalent to mysql–uroot–pzbf666–h localhost

2. To remotely connect to the 192.168.1.108 database, the command is Mysql-uwwn-pwwn520-h 192.168.1.108

3. The code that connects the MySQL server via the PHP server is written as

<?php        //$link_id=mysql_connect(‘主机名‘,‘用户‘,‘密码‘)；        $link_id=mysql_connect（‘192.168.1.108‘,‘wwn‘,‘wwn123‘）;        if($link_id){                echo "mysql successful by wwn";        }else{            echo mysql_error();        }?>

9.8.5 What are the permissions that MySQL users can authorize?

What permissions are included in the experiment for all privileges

1. First See which users

[email protected] 03:3751->select user,host from mysql.user;+--------+---------------------------+| user   | host  |+--------+---------------------------+| zbf| 192.168.1.%   || wwn| 192.168.1.0/255.255.255.0 || system | localhost |+--------+---------------------------+

2. Check the permissions of the authorized WWN

[email protected] 03:3920->show grants for ‘wwn‘@‘192.168.1.0/255.255.255.0‘;| Grants for [email protected]/255.255.255.0    

Notice this place test . Thisis also true when we cancel the read-only permission later test .

This time to view or all privileges permissions, no subdivision.

3. Remove the read-only permission (SELECT) for the WWN.

(1) First look at the help, the help provided the syntax, revoke in the SQL language introduction that section has been mentioned, meaning is to cancel the authorization.

[email protected] 03:4001->help revoke……省略……….The REVOKE statement enables system administrators to revoke privilegesfrom MySQL accounts. Each account name uses the format described inhttp://dev.mysql.com/doc/refman/5.1/en/account-names.html.For example:REVOKE INSERT ON *.* FROM ‘jeffrey‘@‘localhost‘;If you specify only the user name part of the account name, a host namepart of ‘%‘ is used.……省略……

(2) Remove the authorization and subdivide all privileges permissions.

[email protected] 03:4909->REVOKE INSERT ON test.* FROM ‘wwn‘@‘192.168.1.0/255.255.255.0‘;Query OK, 0 rows affected (0.00 sec)sys[email protected] 03:5216->flush privileges;Query OK, 0 rows affected (0.00 sec)

(3) A second look at the user's WWN permissions has been broken down.

[email protected] 03:5224->show grants for ‘wwn‘@‘192.168.1.0/255.255.255.0‘;GRANT USAGE ON *.* TO ‘wwn‘@‘192.168.1.0/255.255.255.0‘ IDENTIFIED BY PASSWORD ‘*C9CE90EB588AA17159BB7C612DC7B34259AC0816‘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 ‘wwn‘@‘192.168.1.0/255.255.255.0

Tip: The permissions of the WWN user at this time, all privileges permissions have been broken down. Follow the steps below to get a clearer idea of what the permissions of all privileges are.

(1) We use-e do not log in to the MySQL database directly to see what the user wwn permissions

[[email protected] ~]# mysql -usystem -pzbf666 -e "show grants for ‘wwn‘@‘192.168.1.0/255.255.255.0‘;"|grep -i grant|tail -1GRANT 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 ‘wwn‘@‘192.168.1.0/255.255.255.0‘

(2) After viewing what permissions the user wwn has, we want to filter out the useful ones. The-i parameter of grep is a case-insensitive meaning.

[[email protected] ~]# mysql -usystem -pzbf666 -e "show grants for ‘wwn‘@‘192.168.1.0/255.255.255.0‘;"|grep -i grant|tail -1|tr ‘,‘ ‘\n‘>all1.txt

(3) Well, let's look at the filtered content, which is all the permissions that the user wwn has.

[[email protected] ~]# cat all1.txt -n 1   SELECT 2   UPDATE 3   INSERT 4   DELETE 5   CREATE 6   DROP 7   REFERENCES 8   INDEX 9   ALTER10   CREATE TEMPORARY TABLES11   LOCK TABLES12   EXECUTE13   CREATE VIEW14   SHOW VIEW15   CREATE ROUTINE16   ALTER ROUTINE17   EVENT18   TRIGGER ON

Note: At the time of authorization, the user can be granted the minimum permissions to satisfy the business, rather than blindly authorizing "all privileges"

9.8.6 Enterprise Environment Authorized user rights

1. Database Authorization for blogs, CMS and other products

For Web Connection user authorization as far as possible to minimize the rules, many open source software is the Web interface installation, so during installation, in addition to select,insert,update,delete4 permissions, but also need to create,drop and other more dangerous permissions.

[email protected] 04:5606->grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘192.168.1.%‘ identified by ‘1b23456‘;Query OK, 0 rows affected (0.00 sec)[email protected] 04:5907->flush privileges;Query OK, 0 rows affected (0.00 sec)

Under normal circumstances authorized SELECT,INSERT,UPDATE,DELETE4 permissions, and some open source software, such as Discuzbbs, but also need to create,drop and other more dangerous permissions.

2. After the database table is generated, the Create and drop authorizations are retracted

[email protected] 04:5925->help revokeREVOKE ALL PRIVILEGES, GRANT OPTIONFROM user [, user] ...REVOKE INSERT ON *.* FROM ‘jeffrey‘@‘localhost‘;2   rows in set (0.01 sec)[email protected] 05:1327->REVOKE CREATE,DROP ON blog.* FROM ‘blog‘@‘192.168.1.%‘;Query OK, 0 rows affected (0.00 sec)[email protected] 05:1452->flush privileges;Query OK, 0 rows affected (0.00 sec)[email protected] 05:1543->show grants for ‘blog‘@‘192.168.1.%‘\G;*************************** 1. row ***************************Grants for [email protected]%: GRANT USAGE ON *.* TO ‘blog‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘*************************** 2. row ***************************Grants for [email protected]%: GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO ‘blog‘@‘192.168.1.%‘2 rows in set (0.00 sec)

2018/1/27 0:54:16

MySQL DBA Advanced Operations Learning notes-Create a variety of methods for MySQL users and authorization practice