ActiveDirectoryMembershipProvider 和引用完整性
过去，当我实现自己的身份验证机制时，我会拥有一个用户表，该表与应用程序的 MySQL 数据库中的其他表有关系。但是，现在我正在考虑使用 ActiveDirectoryMembershipProvider，我发现没有办法在 AD 用户和这些表之间创建类似的关系。
解决这个问题的正常方法是什么？我是否应该接受这样一个事实：有人可能会插入用户 ID 与现有用户不对应的记录？我实际上并不期望这种情况发生，但我习惯于确保数据库级别的完整性。
In the past, when I implemented my own authentication mechanisms I would have a user table with relationships to other tables in my application's MySQL database. However, now that I'm considering using ActiveDirectoryMembershipProvider, I see no way to create similar relationships between AD users and those tables.
What's the normal way to resolve this issue? Should I just accept the fact that someone could potentially insert records with user IDs that don't correspond to existing users? I don't realistically expect this to happen, but I'm used to ensuring integrity at the database level.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为在这种情况下你将不得不放弃数据库引用完整性。只需让应用程序代码检查 Active Directory 帐户是否存在，然后再将记录添加到数据库即可。
理论上，某些用户可以手动进入并键入引用无效 AD 帐户的 SQL INSERT 语句。但实际上，希望您不会向一群用户提供直接表访问权限。如果应用程序代码是访问数据库的唯一内容，应用程序代码在插入行之前验证帐户，并且验证代码经过测试，那么您应该没问题。
为了安全起见，您可以使用每晚的批处理过程来根据 Active Directory 验证引用表中的所有行。如果发现任何不一致之处，它可以向您发送电子邮件。这并不能防止违反完整性的行为，但至少会让您知道这些行为。
I think you'll have to give up on database referential integrity in this case. Just have your application code check for the existence of the Active Directory account before adding the record to the DB.
In theory, some user could go in manually and type a SQL INSERT statement which refers to an invalid AD account. But in practice, hopefully you aren't giving a bunch of users direct table access. If the application code is the only thing accessing the DB, the application code is validating the account before inserting the row, and that validation code is tested, then you should be OK.
Just to be safe, you could have a nightly batch process that validates all rows in your referencing table(s) against Active Directory. If it finds any inconsistency, it can send you an email. This won't prevent integrity violations, but at least it will let you know about them.
我不知道你是否可以通过 MySQL 来做到这一点。如果您使用的是 SQL Server，则可以编写一个触发器来调用 C# dll，以验证它们是否是 AD 成员。然后，如果不是，您可以阻止插入数据库。你也许可以用 MySQL 做这样的事情，但我对 MySQL 的了解相当少。
I don't know of anyway you could do this via MySQL. If you were using SQL server, you could write a trigger that would call a C# dll that would verify that they were an AD member. Then if they weren't you could block the insertion into the DB. You might be able to do something like this with MySQL, but my knowledge of MySQL is pretty slim.