MariaDB 10.4 Reverse Privileges (DENY)

下载 0

空白

发布于

1547

人观看

#信息技术

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

展开查看详情

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

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

0点赞

0收藏

0下载