跨数据库查看权限
我正在使用一个数据库（我们称之为 DB_data），其中包含一系列应用程序的所有表。为了尽量减少升级期间的停机时间，创建了一个外观数据库（我们称之为 DB_facade），它具有 DB_data 中每个表的视图。它还包含针对这些视图工作的所有函数和存储过程。
在尝试锁定 DB_data 中的安全性时，我们对 DB_data 中所有用户的所有表执行了 DENY。所有这些用户也已在 DB_facade 中创建，并具有视图权限。
这里的问题是，由于跨数据库所有权链接，DB_data 中的 DENY 会覆盖 DB_facade 中的 GRANT。
由于潜在的安全问题，我想避免打开这两个数据库的所有权链（尽管在我最初的测试中，这似乎确实纠正了访问问题）。此外，我们正在努力尽量减少对应用程序的影响，因此要求所有访问都通过存储过程和使用证书（例如）是行不通的。
有人对如何处理这个问题有其他建议吗？
谢谢！
I'm working with a database (let's call it DB_data) that contains all of the tables for a series of applications. In an attempt to minimize downtime during upgrades, a facade database (let's call it DB_facade) has been created which has a view for each of the tables in DB_data. It also contains all of the functions and stored procedures, which work against these views.
In trying to lock down security in DB_data we've done a DENY on all of the tables for all of the users in DB_data. All of these users have also been created in DB_facade with permissions to the views.
The problem here, is that because of cross-database ownership chaining the DENYs in DB_data are overriding the GRANTs in DB_facade.
I'd like to avoid turning on ownership chaining for both of these databases because of the potential security issues (although in my original tests, that did seem to correct the access problem). Also, we're trying to minimize impact to the applications, so requiring all access to be through stored procedures and using certificates (for example) wouldn't work.
Does anyone have any other suggestions on how to handle this?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果排除DB_data中表上的DENY，是否有这个问题？如果您没有显式授予这些表的权限，您也许能够获得所需的安全性并通过视图获得访问权限。
Do you have this problem if you exclude the DENY on the tables in DB_data? If you don't explicitly GRANT permissions on these tables, you may be able to get the security you need and get the access rights through the views.
从我所看到和所做的来看，除非明确告知，否则 SQL Server 不会让您拥有任何权限。您应该能够在 DB_Data 中向用户授予 select （或使用角色 datareader ），并且只要它是同一帐户并且映射到两个数据库（您必须在 db_facade 上授予 select 和 exec ）就可以工作很好。
from what i've seen and done, sql server doesn't let you have any permissions unless explicitly told so. You should be able to grant select (or use the role datareader) in DB_Data to the users, and as long as it's the same account and it's mapped to both databases (you'll have to grant select and exec on db_facade) that should work just fine.
您可以在 DB_data 数据库中为 DB_facade 数据库中的每个视图创建一个视图。新视图将有权从表中进行选择。对 DB_data 中的视图进行 GRANT SELECT。将 DB_facade 上的视图更改为从 DB_data 上的视图中选择。并且，表将设置 DENY。
我认识到这样做的一个缺点：用户仍然可以与 DB_data 数据库交互。他们将无法访问表，但可以访问新视图。
You can create a view in the DB_data database for each view in the DB_facade database. The new views would have rights to select from the tables. GRANT SELECT on the views in DB_data. Change the views on DB_facade to SELECT from the views on DB_data. And, the tables would have DENY set.
I recognize one disadvantage to this; the users can still interact with the DB_data database. They wouldn't be able to access the tables, but they could access the new views.