MariaDB 10.4 Reverse Privileges (DENY)

如何从表太多的数据库中排除一个表访问?在Mariadb和MySQL的早期版本中,必须单独授予对所有其他表的访问权限。这不会缩放,尤其是在频繁创建和删除表的情况下。
反向特权解决了这个问题和许多其他用例。只要使用一条SQL语句,就可以确保用户永远无法访问资源,无论授予了哪个角色或其他特权。
反向特权在真空中不起作用,因此在深入研究拒绝访问资源的具体问题之前,我们将从全面特权系统的广阔视野开始。

展开查看详情

1. MariaDB 10.4 Reverse Privileges Vicențiu Ciorbaru Software Engineer @ MariaDB Foundation vicentiu@mariadb.org * © 2018 MariaDB Foundation *

2. Agenda ■ MariaDB privilege system ■ Privilege system tables ■ Drawbacks ■ Reverse privileges © 2018 MariaDB Foundation

3.Where did the idea come from? © 2018 MariaDB Foundation

4.Where did the idea come from? © 2018 MariaDB Foundation

5.Where did the idea come from? REALLY?! © 2018 MariaDB Foundation

6.Where did the idea come from? There has to be a better way! © 2018 MariaDB Foundation

7.Where did the idea come from? There has to be a better way! Not really :( © 2018 MariaDB Foundation

8. MariaDB access control internally ■ MariaDB's tiered privilege system: ■ Global Privileges ○ GRANT SELECT ON *.* TO SuperUser; ■ Database (Schema) Privileges ○ GRANT SELECT ON wordpress.* TO WPAdmin; ■ Table Privileges ○ GRANT UPDATE ON wordpress.users TO WPAdmin; ■ Column Privileges ○ GRANT SELECT (user, content) ON wordpress.posts TO WPUser; ■ Other Privileges © 2018 MariaDB Foundation

9. MariaDB access control internally ■ MariaDB's tiered privilege system: Less Specific ■ Global Privileges ○ GRANT SELECT ON *.* TO SuperUser; ■ Database (Schema) Privileges ○ GRANT SELECT ON wordpress.* TO WPAdmin; ■ Table Privileges ○ GRANT UPDATE ON wordpress.users TO WPAdmin; ■ Column Privileges ○ GRANT SELECT (user, content) ON wordpress.posts TO WPUser; More Specific ■ Other Privileges © 2018 MariaDB Foundation

10. MariaDB access control internally ■ When the server receives a query ○ Compute the necessary privileges to run the query ○ Are we running with privileges enabled? ○ Check the privilege cache ○ Check the user's global privileges ○ Check the user's database privileges ○ Check the user's table privileges ○ Check for specific column privileges. ○ Repeat the process for current active role, if any © 2018 MariaDB Foundation

11. MariaDB access control internally ■ All privilege definitions are additive ■ If you want to restrict access to a resource ○ You need to grant access to everything else BUT that resource… ■ What happens when the schema changes? ○ Update all users… © 2018 MariaDB Foundation

12. MariaDB access control internally ■ The more objects there are, the bigger the system tables get. ■ The bigger the system tables are, the slower privilege checking becomes. ■ Contributions to MySQL 8.0 from Eric Herman aimed to address a performance slowdown when things running with too many users. https://mysql.wisborg.dk/2018/10/26/mysql-server-8-0-13-thanks -for-the-11-facebook-and-community-contributions/ © 2018 MariaDB Foundation

13. Where did the idea come from? ■ We need a solution to "block" access ○ Refactoring the application is not a "solution" in many cases ■ Low overhead! ■ Make it play nice with the current system and give more control to the DBA. © 2018 MariaDB Foundation

14. Where did the idea come from? ■ Is there a precedent? © 2018 MariaDB Foundation

15. Where did the idea come from? ■ Is there a precedent? ■ SQL Server has similar functionality! © 2018 MariaDB Foundation

16. Where did the idea come from? ■ Is there a precedent? ■ SQL Server has similar functionality! DENY { ALL [ PRIVILEGES ] } | <permission> [ ( column [ ,...n ] ) ] [ ,...n ] [ ON [ <class> :: ] securable ] SQL Server Syntax TO principal [ ,...n ] [ CASCADE] [ AS principal ] [;] © 2018 MariaDB Foundation

17. Where did the idea come from? ■ Is there a precedent? ■ SQL Server has similar functionality! DENY { ALL [ PRIVILEGES ] } | <permission> [ ( column [ ,...n ] ) ] [ ,...n ] [ ON [ <class> :: ] securable ] SQL Server Syntax TO principal [ ,...n ] [ CASCADE] [ AS principal ] [;] ■ But SQL server behaviour is hard to explain. ○ A grant will undo a deny. ?! © 2018 MariaDB Foundation

18. How shall we do it? ■ We want to make it easy for users to understand. ■ Easy to reason about it! ○ No "hidden" side effects. ○ No Host Table shenanigans ■ Goal: be able to explain the solution in one sentence. ■ Solution: ○ DENY will trump everything! © 2018 MariaDB Foundation

19. How shall we do it? ■ Once a DENY command has been run, that user (role) will not have access to the resource, unless the DENY is specifically revoked. ■ Any additional grants, aside from explicitly removing the DENY, can not grant access. © 2018 MariaDB Foundation

20. Denying privileges ■ The project is still under development ○ New foundation developers - Rutuja Surve ■ Syntax similar to GRANT / REVOKE: DENY <priv> ON <resource> TO <user> ■ How to undo a deny? REVOKE DENY <priv> ON <resource> FROM <user> © 2018 MariaDB Foundation

21. Denying privileges ■ The project is still under development ○ New foundation developers - Rutuja Surve ■ Syntax similar to GRANT / REVOKE: DENY <priv> ON <resource> TO <user> ■ How to undo a deny? REVOKE DENY <priv> ON <resource> FROM <user> Can we do better? © 2018 MariaDB Foundation

22. Denying privileges ■ How will the new algorithm work: ■ Run a second pass through the user's grants ○ If there is a DENY present, the query will not be allowed (or will return partial results) ■ Performance impact? ○ Practically none for GLOBAL, DATABASE and TABLE privileges (checked at the same time) ○ Slightly more work if specific column denies. ■ Offset by fewer column entries overall. © 2018 MariaDB Foundation

23. Denying privileges ■ Details: ○ Only users with SUPER privilege or READ access to system tables can view denies with SHOW GRANTS. ○ Roles with DENY will block the user's grants too when active. ○ Host table is already removed in 10.4, any entries will be migrated via reverse privileges if possible. © 2018 MariaDB Foundation

24. Denying privileges ■ MariaDB 10.4 is still in Alpha ■ Feedback now can still be incorporated before Beta release. © 2018 MariaDB Foundation

25.Sponsors MariaDB Foundation ensuring open development and collaboration. We cannot fulfil our mission without our members and sponsors! © 2018 MariaDB Foundation

26.Rate My Session © 2018 MariaDB Foundation

27. Thank You! Contact me at: vicentiu@mariadb.org Blog: mariadb.org/blog © 2016 MariaDB Foundation