mysql 8位随机数字,MySQL 8新特性: Random Password Generation生成随机密码

近几年数据库安全在生产管理过程中尤为重视，MySQL同样和Oracle database一样提供了一些高级安全特性如TDE, Audit, Data masking， Firewall和密码安全管理策略等，生产库中通常有如下要求：

1, 每个用户都要有密码；

2, 并且是复杂密码；

3, 要求定期修改密码；

Oracle database中有密码verify function，目前还没有生成随机密码的功能， 但MySQL 8中提供了该功能，下面测试一下“随机密码生成”特性。

安装MySQL8.0.20 on OEL7

-- Download MySQL from www.mysql.com

$ groupadd mysql

$ useradd -r -g mysql -s /bin/false mysql

$ cd /usr/local

$ tar xvf ~/mysql-commercial-8.0.20-el7-x86_64.tar

$ ln -s mysql-commercial-8.0.20-el7-x86_64 mysql

$ cd mysql

$ mkdir mysql-files

$ chown mysql:mysql mysql-files

--The mysql-files directory provides a convenient location to use as the value for the secure_file_priv

system variable, which limits import and export operations to a specific directory.

$ chmod 750 mysql-files

$ bin/mysqld --initialize --user=mysql

$ bin/mysql_ssl_rsa_setup

$ bin/mysqld_safe --user=mysql &

$ cp support-files/mysql.server /etc/init.d/mysql.server

$ chkconfig --add mysql.server

[root@anbo.com mysql]# bin/mysqld --initialize --user=mysql

2020-05-02T12:19:49.616578Z 0 [System] [MY-013169] [Server] /usr/local/mysql-commercial-8.0.20-el7-x86_64/bin/mysqld (mysqld 8.0.20-commercial) initializing of server in progress as process 2826

2020-05-02T12:19:49.625015Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2020-05-02T12:19:51.874832Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2020-05-02T12:19:54.984593Z 6 [Note] [MY-010454] [Server] A temporary password is

generated for root@localhost: rTMpV-)ZS8o>

$ vi .bash_profile

$ PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/

$ export PATH

[root@anbo.com bin]# ps -ef|grep mysql

root 2910 1828 0 08:20 pts/0 00:00:00 /bin/sh bin/mysqld_safe --user=mysql

mysql 2980 2910 1 08:20 pts/0 00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=oel7db1.err --pid-file=oel7db1.pid

Tip:

OK, MySQL已简单安装完成。

随便密码生成

[root@oel7db1 mysqld.service.d]# mysql -uroot -p

Enter password:

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

Your MySQL connection id is 8

Server version: 8.0.20-commercial

Copyright (c) 2000, 2020, 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> SET PASSWORD FOR 'root'@localhost=PASSWORD('www.anbob.com');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('www.anbob.com')' at line 1

mysql> use mysql

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user root identified by 'www.anbob.com';

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> select user();

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

| user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

mysql> ALTER USER USER() IDENTIFIED BY 'www.anbob.com';

Query OK, 0 rows affected (0.33 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set host='%' where user='root' limit 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select version();

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

| version() |

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

| 8.0.20-commercial |

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

1 row in set (0.00 sec)

mysql> create user anbob@localhost identified by random password;

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

| user | host | generated password |

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

| anbob | localhost | !fwrLwjAtbf{}uUJ8nl! |

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

1 row in set (0.01 sec)

mysql> alter user anbob@localhost identified by random password;

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

| user | host | generated password |

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

| anbob | localhost | (YzSI;ut5kFFI.6OE0dE |

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

1 row in set (0.02 sec)

Tip:

MySQL默认是生成20位的随便字符密码，该长度是有参数generated_random_password_length 控制的。

mysql> show variables like '%random%';

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

| Variable_name | Value |

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

| generated_random_password_length | 20 |

| innodb_random_read_ahead | OFF |

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

2 rows in set (0.00 sec)

mysql> set generated_random_password_length=12;

Query OK, 0 rows affected (0.00 sec)

mysql> alter user anbob@localhost identified by random password;

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

| user | host | generated password |

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

| anbob | localhost | mxsU6KAosBbp |

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

1 row in set (0.01 sec)

mysql> alter user anbob@localhost identified by random password;

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

| user | host | generated password |

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

| anbob | localhost | Jis}.jg;L]_o |

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

— enjoy

打赏

微信扫一扫，打赏作者吧～