使用触发器和 FK 约束维护计算记录表有哪些缺点？
我有一个包含角色的表。可以将多个用户分配给一个角色。可以将权限分配给角色，从而向该角色的所有用户授予该权限。
当我向拥有 50000 人的角色分配权限时，我实际上创建了 500 个权限分配。
对于分配给角色的每个权限，我指定了在确定具有该角色的每个人拥有什么类型的权限访问时必须进行的复杂计算。例如，如果我将“READ_EMAIL”权限分配给“ACCOUNTANT”角色，那么在执行此操作时，我还包含一个变量来指定允许哪些邮箱类型，因为存在多种邮箱类型，并且会计师应该只拥有访问其中的某个群体。
因此，当我想找出哪些人有权访问哪些特定邮箱时，我不仅必须加入我的权限、角色和用户表，而且还需要进行查找，原因在此处很难解释，那就是时间消耗并且无法变得更快。
我遇到的问题是，当我在视图中公开所有计算的权限分配（连接所有这些表+进行复杂、耗时的计算）时，需要非常非常长的时间。
我突然想到，我可以简单地创建一个用户角色权限表，该表通过将用户分配给角色或将权限分配给角色来激活的触发器来填充。每当将权限分配给角色时，它都会为具有该角色的每个人插入一条记录，并在那时进行复杂的查找，将 500 条记录放入该表中。每当用户被分配给角色时，它都会生成任何权限+复杂的查找。该表将有外键到角色分配表和权限分配表，并进行级联删除。
角色分配的权限很少见，因此如果速度大大减慢也没关系。对我的表的 99.99999% 的访问是 SELECT。
这种方法有什么缺点吗？我需要实时数据。我只是设计自己的提交物化视图吗？还有其他建议吗？
I have a table containing roles. Many users can be assigned to a role. Permissions can be assigned to a role, granting all users to that role with the permission.
When I assign a permission to a role that has 500 hundred people, I've effectively created 500 permission assignments.
With each permission that is assigned to a role I specify a complex calculation that must take place when determining what type of permission access each individual with that role has. For example, if I assign a "READ_EMAIL" permission to an "ACCOUNTANT" role, at the time I do that I also include a variable that specifies which mailbox types are being permitted, as there are multiple mailbox types and the accountants should only have access to a certain group of them.
So when I want to find out which individuals have access to which specific mailboxes I have to not only join my permissions, roles and users tables, but I need to do a lookup that for reasons which are hard to explain in the space here is time consuming and cannot be made faster.
The problem I run into is that when I expose all calculated permission assignments in a view (joining all those tables + doing the complex, time-consuming calculation), it takes a very very long time.
It occurred to me that I could simply create a user-role-permission table which is populated via triggers activated by assigning a user to a role or by assigning a permission to a role. Whenever a permission is assigned to a role it would insert a record for each individual having that role and would do the complex lookup at that time, putting 500 records into this table. Whenever a user is assigned to a role it would generate any permissions + complex lookups. There would be foreign keys from this table to the role assignment table and the permission assignment tables, with cascading deletes.
Permission to role assignment is rare, so it's fine if that is slowed down greatly. 99.99999% of the access to my tables is SELECT.
Any drawbacks to this method? I need real-time data. Am I just engineering my own on-commit materialized view? Any other suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来确实像是您正在设计自己的提交物化视图。是否有理由不能在此处仅使用提交时的物化视图来缓存结果？
刷新物化视图失败将导致事务的提交操作失败，从而强制回滚。因此数据和物化视图实际上不可能不同步。当然，基于触发器的解决方案最终更有可能出现错误（特别是在多个会话中同时进行更改的情况下）。我宁愿把同步逻辑的编写留给Oracle。
It does sound like you're engineering your own on-commit materialized view. Is there a reason that you couldn't just use an on-commit materialized view here to cache the results?
A failure refreshing the materialized view would cause the transaction's commit operation to fail which would force a rollback. So it's not really possible for the data and the materialized view to get out of sync. It's certainly far more likely that a trigger-based solution would end up with bugs (particularly if there are changes being made in multiple sessions simultaneously). I would much rather leave the writing of the synchronization logic to Oracle.