MariaDB Security

MariaDB Security

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)

不过测试还没有成功，有待后续进一步研究