MS SQL 中对象的架构、所有者 [英] Schema, Owner for objects in MS SQL

问题描述

默认情况下，对象(表、存储过程等)是使用 dbo 所有者/模式设置的(我认为 ms sql 2000 称其为所有者，而 ms sql 2005 称其为模式)

By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema)

所有者/模式实际上是数据库中的角色或用户.我总是保留 dbo 的默认值，但我最近在微软培训书籍中看到了一些例子，其中一些表格和;存储过程有不同的所有者/模式.什么时候这样做有益?为什么?

The owner/schema is really a role or user in the database. I've always left the default of dbo, but I've recently seen some examples in microsoft training books where some of their tables & stored procedures had different owners/schemas. When is it beneficial to do this and why?

推荐答案

当您有安全顾虑时，使用架构特别有益.

The use of schemas is exceptionally beneficial when you have security concerns.

如果您有多个访问数据库的应用程序，您可能不想授予后勤部门访问人力资源记录的权限.因此，您将所有人力资源表放入一个 hr 架构中，并且只允许 hr 角色的用户访问它.

If you have multiple applications that access the database, you might not want to give the Logistics department access to Human Resources records. So you put all of your Human Resources tables into an hr schema and only allow access to it for users in the hr role.

六个月后，物流公司现在需要了解内部费用账户，以便他们可以将所有这些蓝色钢笔调色板发送给正确位置的人员.然后，您可以创建一个存储过程，该过程以有权查看 hr 架构和物流架构的用户身份执行.物流用户永远不需要知道 HR 中发生的事情，但他们仍然可以获取数据.

Six months down the road, Logistics now needs to know internal expense accounts so they can send all of these palettes of blue pens to the correct location people. You can then create a stored procedure that executes as a user that has permission to view the hr schema as well as the logistics schema. The Logistics users never need to know what's going on in HR and yet they still get their data.

您还可以按照 cfeduke 建议的方式使用模式，并仅使用它们在对象浏览器中对事物进行分组.如果你这样做，请小心，因为当你真的只需要一个 dbo.Address 时，你可能最终会创建 Person.Address 和 Company.Address(我不是在敲你的例子，cfeduke，只是用它来说明两者地址表可能相同，也可能不同，那是 YMMV).

You can also use schemas the way cfeduke has suggested and just use them to group things in the object browser. If you are doing this, just be careful because you might end up creating Person.Address and Company.Address when you really just need a single dbo.Address (I'm not knocking your example, cfeduke, just using it to illustrate that both address tables might be the same or they might be different and that YMMV).

这篇关于MS SQL 中对象的架构、所有者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！