ANBOB™

近几年数据库安全在生产管理过程中尤为重视，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

打赏

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