mysql_secure_installation脚本
文件系统权限
tree -puga /opt/mariadb/data/
chown -R mysql: /opt/mariadb
chmod -R 700 /opt/mariadb
检查不安全的密码
SELECT @@old_passwords;
(jlive)[isfdb]>SELECT @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
SELECT Host,User,Password FROM mysql.user;
(jlive)[isfdb]>SELECT Host,User,Password FROM mysql.user;
+-----------+--------+-------------------------------------------+
| Host | User | Password |
+-----------+--------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | zabbix | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| 127.0.0.1 | root | |
| ::1 | root | |
| % | jlive | *C15A2384C61922FBA3DA581D4AD9FF66EA0BEEB3 |
| localhost | foo | |
| localhost | sphinx | *8C702E15DB075B6094596B9AEED93BCC0230A98B |
+-----------+--------+-------------------------------------------+
7 rows in set (0.15 sec)
注意:检查密码为空或少于16位的用户
SSL加密
说明:可以用权威CA签发的SSL证书,这里创建自签名证书
1.创建CA
a.生成CA私钥(key)
root@jlive:ssl_tmp#openssl genrsa -out mariadb-ca.key 4096
Generating RSA private key, 4096 bit long modulus
.......................................................++
..............................................................................................................................................................................................................................................................................................................................................++
e is 65537 (0x10001)
b.通过CA私钥签发自签名CA公钥(pem)
root@jlive:ssl_tmp#openssl req -x509 -new -nodes -days 9999 -key mariadb-ca.key -out mariadb-ca.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Shanghai
Locality Name (eg, city) [Default City]:Shanghai
Organization Name (eg, company) [Default Company Ltd]:ca.cert.com, Ltd.
Organizational Unit Name (eg, section) []:CA
Common Name (eg, your name or your server's hostname) []:ca.example.com
Email Address []:ca@example.com
做实验可以随便填
2.创建服务器证书
a.生成服务器私钥(key)
root@jlive:ssl_tmp#openssl genrsa -out mariadb-server.key 4096
Generating RSA private key, 4096 bit long modulus
..........................++
...++
e is 65537 (0x10001)
b.通过服务器私钥生成证书请求文件(csr)
root@jlive:ssl_tmp#openssl req -new -key mariadb-server.key -out mariadb-server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Shanghai
Locality Name (eg, city) [Default City]:Shanghai
Organization Name (eg, company) [Default Company Ltd]:Tencent.com, Ltd.
Organizational Unit Name (eg, section) []:Development
Common Name (eg, your name or your server's hostname) []:jlive.example.com
Email Address []:jlive@localhost
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
提示:密码可选
c.CA签发服务器公钥(pem)
root@jlive:ssl_tmp#openssl x509 -req -set_serial 01 -days 9999 -CA mariadb-ca.pem -CAkey mariadb-ca.key -in mariadb-server.csr -out mariadb-server.pem
Signature ok
subject=/C=CN/ST=Shanghai/L=Shanghai/O=Tencent.com, Ltd./OU=Development/CN=jlive.example.com/emailAddress=jlive@localhost
Getting CA Private Key
提示:有些ssl加密通信需要指定服务器证书的Common Name,如:grafana
server_cert_name = jlive.example.com
3.创建客户端证书
a.生成客户端私钥(key)
root@jlive:ssl_tmp#openssl genrsa -out mariadb-client.key 4096
Generating RSA private key, 4096 bit long modulus
......................................................................................................................................................................................................................................++
.....................................................................................................++
e is 65537 (0x10001)
b.通过客户端私钥生成证书请求文件(csr)
root@jlive:ssl_tmp#openssl req -new -key mariadb-client.key -out mariadb-client.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Shanghai
Locality Name (eg, city) [Default City]:Shanghai
Organization Name (eg, company) [Default Company Ltd]:client.example.com, Ltd.
Organizational Unit Name (eg, section) []:Development
Common Name (eg, your name or your server's hostname) []:client.example.com
Email Address []:client@example.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
c.CA签发客户端证书(pem)
root@jlive:ssl_tmp#openssl x509 -req -set_serial 02 -days 9999 -CA mariadb-ca.pem -CAkey mariadb-ca.key -in mariadb-client.csr -out mariadb-client.pem
Signature ok
subject=/C=CN/ST=Shanghai/L=Shanghai/O=client.example.com, Ltd./OU=Development/CN=client.example.com/emailAddress=client@example.com
Getting CA Private Key
4.配置SSL认证
说明:将公私钥放到指定目录,这里放在/opt/mariadb目录下
[mysqld]
ssl-ca=/opt/mariadb/mariadb-ca.pem
ssl-key=/opt/mariadb/mariadb-server.key
ssl-cert=/opt/mariadb/mariadb-server.pem
[mysql]
ssl-ca=/opt/mariadb/mariadb-ca.pem
ssl-key=/opt/mariadb/mariadb-client.key
ssl-cert=/opt/mariadb/mariadb-client.pem
改完配置后重启才会生效
root@jlive:~#/etc/init.d/mysql restart
Restarting mysql (via systemctl): [ 确定 ]
5.测试
https://mariadb.com/kb/en/ssl-server-system-variables/
查看状态
STATUS;
SHOW VARIABLES LIKE 'have_ssl';
SHOW STATUS LIKE 'Ssl%';
root@jlive:~#mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW STATUS LIKE 'Ssl%';
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | DHE-RSA-AES256-SHA |
| Ssl_cipher_list | DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:AES128-SHA:AES256-RMD:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA |
| Ssl_client_connects | 0
... ...
25 rows in set (0.01 sec)
创建用户,强制SSL认证
GRANT ALL on test.* TO 'ssluser'@'localhost'
IDENTIFIED BY 'ssluserpassword'
REQUIRE SSL;
或者严格限制签发客户端ISSUER
GRANT ALL on test.* TO 'ssluser'@'localhost'
IDENTIFIED BY 'ssluserpassword'
REQUIRE ISSUER '/C=CN/ST=Shanghai/L=Shanghai/O=client.example.com, Ltd./OU=Development/CN=client.example.com/emailAddress=client@example.com'
AND CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';
root@jlive:~#mysql -u ssluser -p test
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> SHOW STATUS LIKE 'Ssl_session%';
+-----------------------------+---------+
| Variable_name | Value |
+-----------------------------+---------+
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
+-----------------------------+---------+
7 rows in set (0.00 sec)
MariaDB [test]> QUIT
Bye
root@jlive:~#mysql -u ssluser -p --skip-ssl test
Enter password:
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)
连接时还可以通过参数来指定客户端证书路径
mysql -u ssluser –ssl-ca=/opt/mariadb/mariadb-ca.pem \
--ssl-key=/opt/mariadb/mariadb-client.key \
--ssl-cert=/opt/mariadb/mariadb-client.pem test
角色控制
https://mariadb.com/kb/en/roles/
1.创建角色并授权
CREATE DATABASE IF NOT EXISTS test;
CREATE ROLE read_only;
GRANT SELECT ON test.* TO read_only;
GRANT USAGE ON test.* TO read_only;
SHOW GRANTS FOR read_only;
MariaDB [(none)]> SHOW GRANTS FOR read_only;
+-----------------------------------------+
| Grants for read_only |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'read_only' |
| GRANT SELECT ON `test`.* TO 'read_only' |
+-----------------------------------------+
2 rows in set (0.00 sec)
2.将角色赋予某个用户
CREATE USER test_user@'localhost' IDENTIFIED BY 'testpassword';
SHOW GRANTS FOR test_user@'localhost';
GRANT read_only TO test_user@'localhost';
SHOW GRANTS FOR test_user@'localhost';
MariaDB [(none)]> SHOW GRANTS FOR test_user@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT read_only TO 'test_user'@'localhost' |
| GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3.测试
root@jlive:~#mysql -u test_user -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 67
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE test;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'test'
MariaDB [(none)]> SET ROLE read_only;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> USE test;
Database changed
MariaDB [test]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| read_only |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT current_user();
+---------------------+
| current_user() |
+---------------------+
| test_user@localhost |
+---------------------+
1 row in set (0.00 sec)
MariaDB [test]> SHOW GRANTS;
+------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT read_only TO 'test_user'@'localhost' |
| GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |
| GRANT USAGE ON *.* TO 'read_only' |
| GRANT SELECT ON `test`.* TO 'read_only' |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
PAM认证
https://mariadb.com/kb/en/pam-authentication-plugin/
以CentOS 7.1 x64为例
1.创建本地用户
useradd -s /sbin/nologin pamuser
echo pamuser:pampassword|chpasswd
2.安装pam认证插件
INSTALL SONAME 'auth_pam';
pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL
3.创建数据库用户并指定pam认证
CREATE USER pamuser@'localhost' IDENTIFIED VIA pam USING 'password-auth';
GRANT ALL ON test.* TO pamuser@'localhost';
FLUSH PRIVILEGES;
说明:'password-auth'是linux本地PAM模块名称,不同的发行版可能有些区别,centos7中password-auth会调用/etc/shadow中的密码进行认证,还支持LDAP,Active Directory,smart cards,biometric scanner等认证
4.测试
root@jlive:~#mysql -upamuser
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory
root@jlive:~#ln -s /opt/mariadb /usr/local/mysql
root@jlive:~#ls /usr/local/mysql/lib/plugin/dialog.so
/usr/local/mysql/lib/plugin/dialog.so*
root@jlive:~#mysql -upamuser
[mariadb] Password:
ERROR 1045 (28000): Access denied for user 'pamuser'@'localhost' (using password: NO)
不过测试还没有成功,有待后续进一步研究