2024 RoOUG Security model for the cloud.pptx

How we've evolved the MySQL server security model from a single DBA to the cloud.

1. A MySQL Server Security Model For the Cloud A Case Study Georgi Kodinov MySQL Server General team lead @ Oracle

2. About Me Have been working on MySQL since 2006. Leading one of the MySQL server development teams @ Oracle. Based in Plovdiv, Bulgaria. Spent all my carrier developing database servers. Still loving the cross-section of security and database Copyright © 2024, Oracle and/or its affiliates 2

3. Copyright © 2024, Oracle and/or its affiliates 3 MySQL In the Cloud: The Problem To Solve How is Cloud Different?

4. The Traditional MySQL Model Copyright © 2024, Oracle and/or its affiliates 4 Managed by the user SQL Remote Desktop

5. Copyright © 2024, Oracle and/or its affiliates 5 The 10k Feet View Of the MySQL-in-Oracle-Cloud Architecture Managed by the user Managed by Oracle Control Plane Data Plane SQL RPC RPC

6. Copyright © 2024, Oracle and/or its affiliates 6 How is Cloud Different From On-Premise Security-Wise? Single, All-powerful “root” account Everything! Database End User: "root" Control/Data Plane: " dbadmin " "Unsafe" Operations DML,DDL and ACL Cloud Database Instance RPC

7. CREATE TABLE t1(a INTEGER); INSERT INTO t1 VALUES (1); CREATE DEFINER=root PROCEDURE p1 SELECT 1; CREATE DATABASE db1; CREATE USER foo; GRANT INSERT ON db1.* TO foo; GRANT PROXY ON dbadmin TO root; SET GLOBAL binlog_format =STATEMENT; UNINSTALL PLUGIN firewall; RESET BINARY LOGS; DROP TABLESPACE foo; Safe vs. Unsafe Operations Copyright © 2024, Oracle and/or its affiliates 7 SAFE! Can destabilize the instance

8. GRANT CREATE USER ON *.* TO root; Root> CREATE USER foo IDENTIFIED BY 'bar’; GRANT SUPER, CREATE ROUTINE ON *.* TO root; Root> CREATE PROCEDURE p1 SELECT 1; GRANT DROP TABLE ON *.* TO root; Root> DROP TABLE t1; GRANT DELETE ON *.* TO root; Root> DELETE FROM t1; Root> ALTER USER dbadmin SET PASSWORD = `p0wned`; Root> CREATE DEFINER= dbadmin PROCEDURE sudo_jailbreak SET PERSIST binlog_format = statement; Root> DROP TABLE mysql.user ; Root> UNINSTALL PLUGIN audit_log ; BUT! Copyright © 2024, Oracle and/or its affiliates 8

9. Copyright © 2024, Oracle and/or its affiliates 9 Need More Granularity! And fast!

10. In theory there’s no difference between theory and practice. But in practice there is! Lawrence Peter "Yogi" Berra A baseball player and a philosopher Copyright © 2024, Oracle and/or its affiliates 10

11. Copyright © 2024, Oracle and/or its affiliates 11 Do not grant the right to the end user to cut the branch they are sitting on! Do not grant rights to SQL that can destabilize the server Do the sensitive adjustments via the control plane in a controlled manner Visibility to the end user on what the control plane does as SQL is OK and a Good Thing™ Performance schema Audit log Optimize for the 90%, do not allow severe tweaking Automatic memory management Alerts when memory use goes over a threshold Make the server read only if it’s close to going out of disk Prioritize stability when configuring: e.g. have conservative memory limits Ensure the control plane has priority network access No compiled-in limitations ! Provide tools to solve the issue Principles In Building the Cloud Security Model

12. Copyright © 2024, Oracle and/or its affiliates 12 A SYSTEM_USER privilege to fence off the ACL operations on the control plane user Partial revokes for the DDL/DML operations Make global privileges more granular Introducing the (Minimal Set Of) Tools!

13. Copyright © 2024, Oracle and/or its affiliates 13 CREATE USER foo, bar; GRANT CREATE USER ON *.* TO foo, bar; GRANT SYSTEM USER ON *.* TO foo; Foo> CREATE USER f1; Foo> ALTER USER f1 …; Bar> CREATE USER b1; Bar> ALTER USER b1 …; Bar> ALTER USER f1 …; Foo> ALTER USER b1 …; The SYSTEM_USER Privilege It’s like a secret society!

14. Copyright © 2024, Oracle and/or its affiliates 14 CREATE USER foo; CREATE DATABASE db1, db2; CREATE TABLE db1.t(a INTEGER); CREATE TABLE db2.t(a INTEGER); GRANT SELECT ON *.* TO foo; REVOKE SELECT ON db2.* FROM foo; F oo> SELECT * FROM db1.t; Foo> SELECT * FROM db2.t; CREATE DATABASE db3; CREATE TABLE db3.t(a INTEGER); Foo> SELECT * FROM db3.t; Partial Revokes It’s like GRANT … EXCEPT …

15. It’s all about more access via SQL to the end user Breaking and deprecating powerful role-like privileges SUPER RELOAD Fortifying sudo SQL stored programs usage ALLOW_NONEXISTENT_DEFINER SET_ANY_DEFINER Adding extra access controls to variable handling: SYSTEM_VARIABLES_ADMIN SESSION_VARIABLES_ADMIN PERSIST_RO_VARIABLES_ADMIN SENSITIVE_VARIABLES_OBSERVER Making Privileges More Granular Copyright © 2024, Oracle and/or its affiliates 15

16. Copyright © 2024, Oracle and/or its affiliates 16 Ask Me Anything MySQL Related ;)

17. Copyright © 2024, Oracle and/or its affiliates 17 https://bugs.mysql.com/ https://forums.mysql.com/ https://blogs.oracle.com/mysql/ Useful Links https://dev.mysql.com/doc/refman/8.4/en/ https://dev.mysql.com/doc/dev/mysql-server/latest/ https://dev.mysql.com/doc/index-enterprise.html https://dev.mysql.com/community/ Ways To Interact With the MySQL Development Team

18. Copyright © 2024, Oracle and/or its affiliates 18 Thank You F or using MySQL!