Paypal的MySQL安全和标准化

下载 8

无羡

发布于

3631

人观看

#信息技术

数据安全在贝宝的数据库基础设施中起着至关重要的作用。在这个演示中，我们将讨论PayPal如何强制执行数据安全性。将涵盖以下领域：
-应用程序和数据库实例以及数据库到数据库实例之间的SSL加密连接
-数据库登录与LDAP集成，用于用户身份验证和授权
-数据库访问和元数据/对象修改的企业审核
-使用自定义SSL密钥和密码管理、密码轮换保护应用程序登录
-避免密码暴露的方法，例如使用MySQL连接字符串
MySQL标准化对Percona XtraDB在PayPal中的挑战我们如何处理
—不同操作系统上不同版本的MySQL
—具有超级用户权限的应用程序用户
—mysql-commercial与percona-xtradb集群不兼容

展开查看详情

1 . MySQL Security and Standardization at PayPal Stacy Yuan & Yashada Jadhav • 29th May 2019

2 . Over 15 years of experience on various flavors Stacy Yuan of relational databases. Focus on performance tuning, code reviews, database deployment and infrastructure management for MySQL In her spare time, she enjoys reading books and doing some volunteer work. © 2019 PayPal Inc. Confidential and proprietary.

3 . 7+ years of being in a relationship with Yashada Jadhav databases Presented previously at Percona Live and Oracle Open World Prior to PayPal, worked at Amazon Web Services and Yahoo! I share random database thoughts at - https://mysql.dbgeekgirl.com © 2019 PayPal Inc. Confidential and proprietary.

4 . MySQL Standardization at PayPal Agenda MySQL Security • Password Management • Auditing for database objects • Encrypted connections • Migration to Percona XtraDB at PayPal © 2019 PayPal Inc. Confidential and proprietary.

5 .At PayPal, we put people at the center of everything we do.

6 .MySQL at PayPal Internal Apps 3rd Party Build, Test and Release Tools OSS Backend databases Custom Monitoring Site DBaaS Site Facing Use Cases Database as a Service © 2019 PayPal Inc. Confidential and proprietary.

9 .Premium Architecture AZ1 AZ2 Application Application Connection Cache Connection Cache Local Read Replica Remote Read Master Replica Scaling Replica Scaling Replica © 2019 PayPal Inc. Confidential and proprietary.

10 .MySQL Standardization at PayPal Challenges, Journey and What's Next

11 .MySQL Standardization at PayPal Challenges One DBA Team – Multiple Applications Hardware • Automation is a challenge and an opportunity Heterogenous Set ups Bare Metal Operating System • Different hardware, software and architecture Servers configurations Ubuntu Operational Challenges VMs 14.x, 16.x, 18.x MySQL Versions • Migrating ”non-standard” setups to standard RHEL 6.x, 7.x MySQL Percona MySQL Community 5.7 Cloud Edition 5.6, 5.7 OEL 7.x VMs © 2018 PayPal Inc. Confidential and proprietary.

12 .MySQL Standardization at PayPal Journey Deployment • Ansible for more scalable deployment • Deployment scripts for different OS versions Best Practices • Common standards across databases • Recommended MySQL parameter settings • MySQL design standards Monitoring • In house monitoring and alerting • TICK and PMM for OS and MySQL metrics High Availability • GitHub’s Orchestrator and VIP Performance Tuning • Performance Schema, PMM and Slow query log Backups • Percona Xtrabackup and mysqldump with onsite and offsite storage © 2018 PayPal Inc. Confidential and proprietary.

13 .MySQL Standardization at PayPal What’s next Change Automation Connection Multiplexer & Router Benchmarking MySQL 8.0 InnoDB Encryption at Rest Binary Log and Relay Log Encryption at Rest Dual Password Support & Password Rotation Role Based Access Control © 2018 PayPal Inc. Confidential and proprietary.

14 .MySQL Security at PayPal

15 .MySQL Password Management

17 .Password Strength Passwords can be your weakpoint. MySQL’s Password Validation Plugin Enabling the validate_password plugin • Activate the plugin in my.cnf file and restart MySQL server plugin-load-add = validate_password.so validate-password = FORCE_PLUS_PERMANENT • OR without restart mysql> install plugin validate_password soname 'validate_password.so'; Query OK, 0 rows affected (0.03 sec) • Why validate-password setting? mysql> uninstall plugin validate_password; ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be unloaded © 2019 PayPal Inc. Confidential and proprietary.

18 .Password Strength Plugin Variables validate_password_policy mysql> show global variables like Policy Test Performed 'validate_password%'; 0 or LOW Length +--------------------------------------+--------+ 1 or MEDIUM Length; numeric, | Variable_name | Value | lowercase/uppercase, and special +--------------------------------------+--------+ characters | validate_password_check_user_name | OFF | 2 or HIGH Length; numeric, | validate_password_dictionary_file | | lowercase/uppercase, and special characters; dictionary file | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | mysql> select password ("Abcdefg1"); | validate_password_policy | MEDIUM | ERROR 1819 (HY000): Your password does not satisfy the current policy requirements | validate_password_special_char_count | 1 | mysql> select password("Abcdef1@"); +--------------------------------------+--------+ +-------------------------------------------+ 7 rows in set (0.00 sec) | password("Abcdef1@") | +-------------------------------------------+ | *035C6A33B68D295FDE7BAA22AB7DC5733E136BD8 | +-------------------------------------------+ © 2019 PayPal Inc. Confidential and proprietary. 1 row in set, 1 warning (0.00 sec)

19 .Password Expiration Setting password to expire after N days Setting password expiration on global level • A password expiration policy for all accounts can be set using the MySQL setting default_password_lifetime SET GLOBAL default_password_lifetime = 180; Setting password to never expire • Passwords can be set to never expire on a global setting and password expiration policies can be set for individual accounts SET GLOBAL default_password_lifetime = 0; Setting password expiration for individual accounts mysql> CREATE USER 'yashada'@'%' IDENTIFIED BY "Abcdef1@" PASSWORD EXPIRE INTERVAL 90 DAY; Query OK, 0 rows affected (0.03 sec) © 2019 PayPal Inc. Confidential and proprietary.

20 .Password Expiration What happens when password expires? • When password expires, the server restricts the client to a “sandbox” mode mysql> SELECT 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER USER() identified by 'Abcdef1@'; Query OK, 0 rows affected (0.04 sec) mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.01 sec) © 2019 PayPal Inc. Confidential and proprietary.

21 .Password Expiration Forcing users to change password at login • Create users with expired password. mysql> CREATE USER 'yashada'@'%' IDENTIFIED BY "Abcdef1@”; Query OK, 0 rows affected (0.03 sec) mysql> alter user 'yashada'@'%' PASSWORD EXPIRE; Query OK, 0 rows affected (0.03 sec) • When user will try to login, the server will restrict client to “sandbox” mode and no operations can be performed till the user sets a password. mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. © 2019 PayPal Inc. Confidential and proprietary.

22 .Avoiding Password Exposure Avoiding password exposure • In the DBA operations world there are always challenges with passwords. How do you use the password for login, automation and operation scripts in a way that does not expose the password. • One of the MySQL utilities that addresses some of these questions is mysql_config_editor • mysql_config_editor enables you to store authentication credentials in a login path file named .mylogin.cnf in the current user's home directory. Installing mysql_config_editor • To install mysql_config_editor, all you need is the MySQL client installed. © 2019 PayPal Inc. Confidential and proprietary.

23 .Avoiding Password Exposure Set up log in paths using mysql_config_editor ~> mysql_config_editor set --login-path=monitor --user=monitor --password Enter password: Using login-path to log in to MySQL • To use this path to log in to MySQL – ~> mysql --login-path=monitor Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 554 © 2019 PayPal Inc. Confidential and proprietary.

24 .Avoiding Password Exposure How mysql_config_editor works? • mysql_config_editor creates an encrypted file in the user’s home director called .mylogin.cnf ~> pwd /root ~> ls -lhtr .mylogin.cnf -rw-------. 1 root root 324 Apr 13 16:03 .mylogin.cnf ~> cat .mylogin.cnf t +~ !2 Z Yf 8<A + Q { ; 7 _ U ] vf [ U◌ ֩ Y˟ e. # Lb Z ˫ x m 2 苐 ʈ% - T |?G9 $Q t N le 5 G< 7 Bh ㎫| '@y e. # Lb Z ˫ x m 2 ~> © 2019 PayPal Inc. Confidential and proprietary.

25 .Avoiding Password Exposure Using strong passwords without having to remember them • The advantages of not having to remember or enter passwords are quite a few. For example, now a password can be a secure non dictionary string, that meets password validation rules. • The password for monitor can be B*kA2aBntGYdvJaf. It can be set up in mysql_config_editor once and can be used without remembering the string using - mysql --login-path=monitor Using the login path in utilities without exposing the password • MySQL Utilities ~> mysqladmin --login-path=mysqlconn ping mysqld is alive • Backup innobackupex --login-path=mysqlconn /backups xtrabackup: Transaction log of lsn (14148224090) to (14148224138) was copied. 190413 16:32:06 completed OK! © 2019 PayPal Inc. Confidential and proprietary.

26 .Avoiding Password Exposure Using the login path in utilities without exposing the password • Automation Scripts [root@ ~]# mysql --login-path=mysqlconn -e "show slave status \G" | grep -i "Slave_IO_Running:" | awk -F':' '{print $2 }'|sed -e 's/^[ \t]*//’ Yes Caveat – It’s not THAT secure • We can read the contents of the encrypted log in file using the my_print_defaults utility. [root@ ~]# my_print_defaults -s monitor --user=monitor --password=B*kA2aBntGYdvJaf --host=XXXX --port=3306 my_print_defaults is a part of standard MySQL install. © 2019 PayPal Inc. Confidential and proprietary.

27 .Storing and Retrieving Application Passwords Keymaker software, generates a key with which password can be retrieved. • Application passwords should not be hardcoded Application fetches password from keystore via keymaker API, and stores it • Application passwords and certificates are in local encrypted cache stored in a central key store Central Application • Application passwords are retrieved via Keystore API calls to the key store • What if key store becomes unreachable? DBA stores the password in central keystore DBA shares the key with the developers who store the key in the application using a protected package © 2019 PayPal Inc. Confidential and proprietary.

28 .LDAP Authentication MySQL Server can be configured to use LDAP to authenticate users Proxy user support • LDAP authentication can return to MySQL a user name different from the operating system user, based on the LDAP group of the external user. • For example, an LDAP user named stacy can connect and have the privileges of the MySQL user named dba_user, if the LDAP group for stacy is dba_users. Percona’s PAM Authentication Plugin • Percona PAM Authentication Plugin acts as a mediator between the MySQL server, the MySQL client, and the PAM stack. © 2019 PayPal Inc. Confidential and proprietary.

29 .LDAP Authentication Installing the PAM plugin • To install the PAM plugin – mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; Query OK, 0 rows affected (0.08 sec) mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so'; Query OK, 0 rows affected (0.05 sec) Configure Percona PAM to authenticate to LDAP • Configure Percona PAM to authenticate to LDAP by creating /etc/pam.d/mysqld with the following content – auth required pam_ldap.so audit account required pam_ldap.so audit © 2019 PayPal Inc. Confidential and proprietary.

1点赞

1收藏

8下载