SQL SERVER 2005/2008 中关于架构的理解

SQL SERVER 2005/2008 中关于架构的理解(一)

 

       在一次的实际工作中碰到以下情况，在 SQL SERVER 2008中，新建了一个新用户去访问几张由其他用户创建的表，但是无法进行查询，提示“对象名'CustomEntry' 无效。”。当带上了架构名称之后(如“cus.CustomEntry”)，却又能查询到表中的数据了，但是查询语句是已经写死在了应用程序中的，如果要进行更改，就有很大的工作量， 这是一件很郁闷的事情。于是想从数据库层面上解决此问题，在查询了大量的资料之后，对于SQL SERVER中的架构有所了解，并解决以上问题。

      下面来说说，自己对SQL SERVER 中架构的理解，并在此记录，以备查。

      在SQL SERVER 2000中不存在上面所说的问题，那为什么在2008中会出现这样的事情，这样的设置可以带来哪些好处？导致这一问题的原因主要在于SQL SERVER 2005/2008中多了一个新的概念——架构。

       首先，我们来看一下微软对架构的官方定义:架构(Schema)是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。在这里，我们可以将架构看成一个存放数据库中对象的一个容器。

      架构实际上在SQL SERVER 2000中就已经存在,在SQL SERVER 2000中数据库用户和架构是隐式连接在一起的, 每个数据库用户都是与该用户同名的架构的所有者。当我们使用查询分析器去查询一个表的时候，一个完整的表的名称应该包括服务器名.数据库名.用户名.对象名，而在SQL SERVER 2005/2008中一个表的完全限定名称应该为服务器名.数据库名.架构名.对象名

      在SQL SERVER 2000中的完全限定名称中的“用户名”也是数据库中的用户，也是“架构名”。假如有一个账户df在test数据库中创建了一张表tb1的时候，在查询分析器中应该输入的查询语句为select * from test.df.tb1,也就是说，在SQL SERVER  2000中一张表所属的架构默认就是表的创建者的登录名称，用户可以修改他所创建的所有数据库对象。但在2008中已经将用户和其创建对象所属关联取消了，而加入了一个全新的架构体系。

 

用户架构分离的好处

那么将架构与数据库用户分离对管理员和开发人员而言有什么好处呢？

 

1. 架构管理与用户管理分开。多个用户可以通过角色（role）或组（Windows groups）成员关系拥有同一个架构。在SQL SERVER 2005/2008 中，每个数据库中的固定数据库角色都有一个属于自己的架构，如果我们创建一个表，给它指定的架构名称为 db_ddladmin，那么任何一个属于db_ddladmin中的用户都是可以去查询、修改和删除属于这个架构中的表，但是不属于这个组的用户是没有对这个架构中的表进行操作的权限，有一点必须注意，db_dbdatareader组的成员可以查看所有数据库中的表，db_dbdatawriter组成员可以修改所有数据库中 的表，db_owner组成员可以对数据库所有表进行所有操作，这几个组的成员可以通过角色获取到在数据库中的特殊权限。

2. 在创建数据库用户时，可以指定该用户账号所属的默认架构。 （ 建议大家指定）

3. 删除数据库用户变得极为简单。在 SQL Server 2000 中，用户（User）和架构是隐含关联的，即每个用户拥有与其同名的架构。因此要删除一个用户，必须先删除或修改这个用户所拥有的所有数据库对象，就比如 一个员工要离职要删除他的账户的时候，还得将他所创建的表和视图等都删除，影响过大。SQL SERVER 2005/2008将架构和对象者分离后就不在存在这样的问题，删除用户的时候不需要重命名该用户架构所包含的对象，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。

 

4. 共享缺省架构使得开发人员可以为特定的应用程序创建特定的架构来存放对象，这比仅使用管理员架构（DBO schema）要好。

5. 在架构和架构所包含的对象上设置权限（permissions）比以前的版本拥有更高的可管理性。

6. 区分不同业务处理需要的对象，例如，我们可以把公共的表设置成pub的架构，把销售相关的设置为sales，这样管理和访问起来更容易。

大多数用户在创建对象的时候习惯直接输入对象名而将对象的架构名称省略，在2005/2008 中，会给用户创建的这样的表加上一个缺省的架构，用户如果没有对自己的默 认架构做设置，那缺省架构就是dbo，也就是说，如果一个db_ddladmin的成员在数据库中创建一个没有加上架构名称的表，这个表在数据库中的完整 名称应该是dbo.表名，创建者在数据库中如果不是属于其它特殊组的成员，是不能对自己创建的表进行任何修改和查询的，那就相当于把自己赚的钱存进了别人的银行卡，自己却取不出来。

 

 

7 若不指定默认架构，则为DBO，为了向前兼容，早期版本中的对象迁移到新版本中，早期版本中没有架构的概念的。所以就该对象的架构名就是dbo.在SQL Server 2008中,DBO就是一个架构

8 当查找对象时，先找与用户默认架构相同的架构下的对象，找不到再找DBO的对象

第8点有点难理解，我们来看一张图，通过这张图，大家应该能很显示的理解这一点：

SQL SERVER 2005/2008 中关于架构的理解(二)

架构的作用与示例

      用户与架构（schema）分开，让数据库内各对象不再绑在某个用户账号上，可以解决SQL SERVER 2000及以前版本中“用户离开公司＂问题，也就是在拥有该对象的用户离开公司，或离开该职务时，不必要大费周章地更改该用户所有的对象属于新的用户所有。另外，也可让 DBA 在安装某个套装软件时，设置该套装软件所用的数据库对象都属于某个特定的架构，容易区别。也就是说，在单一数据库内，不同部门或目的的对象，可以通过架构区分不同的对象命名原则与权限。

       在 SQL Server 2005 /2008中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。并且可以在架构中创建具有用户友好名称的对象，明确指示对象的功能。例如，除了 cus.app.entry.customEntry 外，您还可以创建名为 cus.app.manifest.customEntry 的架构。因为“manifest”不是用户，所以从数据库中删除用户后，无需更改此名称。这就简化了数据库管理员和开发人员的工作。

       SQL Server 2005/2008 还引入了“默认架构”的概念，用于解析未使用其完全限定名称引用的对象的名称。在 SQL Server 2000 中，首先检查的是调用数据库用户所拥有的架构，然后是 DBO 拥有的架构。在 SQL Server 2005 /2008中，每个用户都有一个默认架构，用于指定服务器在解析对象的名称时将要搜索的第一个架构。可以使用 CREATE USER 和 ALTER USER 的 DEFAULT_SCHEMA 选项设置和更改默认架构。如果未定义 DEFAULT_SCHEMA，则数据库用户将把 DBO 作为其默认架构。

       下面的显示SQL Server权限层次结构的图可能会给我们一个直观的认识：

 

       SQL Server 2005/2008 Database Engine 管理着可以通过权限进行保护的实体的分层集合。这些实体称为“安全对象”。在安全对象中，最突出的是服务器和数据库，但可以在更细的级别上设置离散权限。SQL Server 通过验证主体是否已获得适当的权限来控制主体对安全对象执行的操作。

 

安全对象关系如下图：

 

下面举个具体的示例来说明以一下架构的作用。

 

--命令对架构进行操作

use master

go

setuser

go

--创建测试数据库

create database schTest

go 

create login df with password='sj1234',default_database=schTest

create login xhl with password='sj1245',default_database=schTest

go

use schTest

go

 -- 创建两个用户时没有指定属于哪个架构

create user df for login df 

create user xhl for login xhl

-- 这个表没指定属于哪个架构属于默认DBO 架构

go

create table tb1 (姓名 varchar(8),性别 char(2))

 go

--这个表就属于sch架构

create schema sch

go

create table sch.tb2(姓名 varchar(8),性别 char(2),年龄 int)

go

-- 赋予schTest这个用户查询sche架构中的对象的权限. 

grant select on schema::sch to df 

go

setuser 'df'  --切换用户df

 

select * from  tb2

-- 此时报告"对象名无效" 是因为没有指定tb2的架构,系统默认为dbo,而我们的tb2属于sch架构.

 

--带上架构名称，就可以查询了

go

select * from sch.tb2

go

setuser  -- 切换到sa

---切换到xhl

setuser 'xhl'

 

--不能查询,是因为没有权限

 

select * from sch.tb2 

go

setuser --切换sa

--给df用户赋默认架构

alter user df with default_schema=sch

go

setuser 'df'  --切换df

-- 此时不需要指定sch 也可以了,如果架构中还有其他对象，也可以查询

select * from tb2

 

go

setuser  --切换sa

--创建第三张测试表，同样的是sch架构下

create table sch.tb3 (id int,uname varchar(8))

go

--切换用户df

setuser 'df'

---可以进行查询

select * from tb3

go

---但是无法进行数据插入，因为没有插入权限

insert into tb3 values (1,'abcde')   --拒绝了insert权限

go

setuser

--赋插入权限

grant  insert on schema::sch to df

--切换用户df

setuser 'df'

go

---可以进行数据插入

insert into tb3 values (1,'abcde')   --OK! 

---查询结果

select * from tb3

GO

Grant alter on schema::sch to df --  使schTest 这个用户对所有的架构都有可更改的能力。

--错误

--无法对sa、dbo、实体所有者、information_schema、sys 或您自己授予、拒绝或撤消权限。

GO

Use master


 go

Grant control server to df  -- 使schTest这个用户能够控制服务器。

--错误

--无法对sa、dbo、实体所有者、information_schema、sys 或您自己授予、拒绝或撤消权限。

setuser

go

use schtest

go

--创建架构sch1

create schema sch1

 

go
-- 修改对象的架构tb2表的架构由sch 转移到sch1

alter schema sch1 transfer sch.tb2

go

--创建一个新用户，同时指定默认schema,默认属于DBO 

create login yhy with password='sj1234',default_database=master

GO

use schTest

GO

create user yhy for login yhy with default_schema=sch --属于sch

--切换用户yhy

setuser 'yhy'

--查询表,对象名'tb2' 无效。

select * from tb2

go

setuser

--赋架构权限

grant select on schema::sch1 to yhy

--切换用户yhy

setuser 'yhy'

go

select * from tb2   ---还是无效，因为不在同一个schema

go

setuser

go

alter user yhy with default_schema=sch1   --更改yhy的默认架构

GO

--由于yhy不是当前用户，所以无法进行查询

select * from tb2  

--显示当前用户

GO

select user

GO

--切换用户yhy

setuser 'yhy'

-- 此时就可以进行查询了,如果架构中还有其他对象，也可以查询

go

select * from tb2  

--显示当前用户

select user

go

--切换用户

Setuser

--切换用户为df

setuser 'df'

-- 此时由于tb2的架构由sch变为了sch1，所以df就不可以查询tb2了

go

--查询报错
select * from tb2  


--显示当前用户
select user

 

go

setuser

go

use master

go

---删除用户

drop user df

drop user xhl

drop user yhy

---删除登录名

drop login df

drop login yhy

drop login xhl

--删除数据库

drop database schTest