服务器角色

固定服务器角色

先说一个重点:所有的固定服务器角色,都不可改变,也无法增减!

首先理解服务器角色是服务器级别的主体,它们具有一些操作权限,操作范围是整个服务器.

与登录名的关系:

• 以某个登录名登录服务器时,需要设定其对服务器的访问权限.
• 权限的设定通过对服务器角色的继承得到,即将登录名添加到服务器角色中.
• 不设定权限的登录名没有权限,sa默认继承sysadmin,即系统管理员,拥有一切权限.

---

以下是所有的固定服务器角色列表:

固定服务器角色	描述
bulkadmin	块管理员,执行块操作的权限,如BULK INSERT
dbcreator	数据库创建者,创建、更改和删除数据库的权限,即ALTER ANY DATABASE
diskadmin	磁盘管理员,修改资源的权限,即ALTER RESOURCES
processadmin	进程管理员,管理服务器连接和状态的权限,如ALTER CONNECTION
securityadmin	安全管理员,修改登录名的权限,即ALTER ANY LOGIN
serveradmin	服务器管理员,更改服务器范围的资源/配置选项和关闭服务器的权限
setupadmin	服务器安装管理员,修改链接服务器的权限,即ALTER ANY LINKED SERVER
sysadmin	系统管理员,所有权限
public	公共角色,没有权限

具体权限分配如下图:

上文说过,固定服务器角色都是不可变的,即,登录名只能通过这几个固定服务器角色设定权限,无法灵活修改.

我们可以用存储过程或相关函数来执行登录名和固定服务器角色之间的授权操作,常用的操作如下:

命令/函数	用法	作用
sp_addsrvrolemember	sp_addsrvrolemember ‘login_name’,‘role_name’	将登录名加入服务器角色
sp_dropsrvrolemember	sp_dropsrvrolemember ‘login_name’,‘role_name’	将登录名移出服务器角色
sp_helpsrvrolemember	sp_helpsrvrolemember ‘role_name’	查看服务器角色下的登录名
IS_SRVROLEMEMBER	IS_SRVROLEMEMBER(‘role_name’,‘login_name’)	判定登录名的服务器角色

一个简单的示例如下:

EXEC sp_addsrvrolemember 'AERO15XV8\Aaron','sysadmin';	--将登录名加入到系统管理员中;
EXEC sp_helpsrvrolemember 'sysadmin';	--查询所有系统管理员;
IF	IS_SRVROLEMEMBER('sysadmin','AERO15XV8\Aaron') = 1	--判定登录名是否为系统管理员;print 'AERO15XV8\Aaron is sysadmin';
EXEC sp_dropsrvrolemember 'AERO15XV8\Aaron','sysadmin';	--从系统管理员中移除登录名;
print 'AERO15XV8\Aaron is sysadmin no longer';

运行结果如下:

输出的消息:

可以使用视图sys.server_principals查看所有服务器角色的详细信息.

管理服务器角色

创建服务器角色

服务器角色的关键字是:SERVER ROLE,使用CREATE关键字创建.创建角色就是为角色命名和指定角色所有登录名的过程,如果没有指定,则默认sa为角色所有者,使用AUTHORIZATION关键字设定角色所有登录名.

可以看到新建了服务器角色:

查看其所有者:

除此之外,我们也可以以已有的服务器角色(包括但不限于固定服务器角色)为模板创建:

只需使用AUTHORIZATION+数据库角色名就可以:

USE AdventureWorks2017;
GOCREATE SERVER ROLE dbcreator_copy AUTHORIZATION dbcreator;	--以dbcreator为模板新建服务器角色;
GO

可以看到,服务器中多了一个角色:

这样创建出来的角色和原来的角色权限一致,在批量导入权限时很方便,且还可以为新建的角色新增权限.

修改 && 删除服务器角色

使用ALTER关键字可以对服务器角色进行修改,修改包括角色名的修改和添加/删除用户的修改,如下:

ALTER SERVER ROLE ROLE0 WITH NAME = ROLE1;	--改变角色的名称;
ALTER SERVER ROLE ROLE1 ADD MEMBER [AERO15XV8\Aveline];	--将登录名加入角色;
GOALTER SERVER ROLE ROLE1 DROP MEMBER [AERO15XV8\Aaron];	--将登录名移出角色;
GO

从下图可以看到,对角色的修改已完成:

最后,还可以更改服务器角色所有者,使用ALTER AUTHORIZATION关键字操作:

ALTER AUTHORIZATION ON SERVER ROLE::ROLE1 TO [AERO15XV8\Aveline];	--更改角色所有者;
GO

可以看到角色的所有者发生了变化:

使用DROP关键字可以删除服务器角色,如下:

DROP SERVER ROLE ROLE1;	--删除角色;
DROP SERVER ROLE dbcreator_copy;
GO

错误信息如下:

显然,不可删除含有登录名的角色,需要将所有角色内成员移出,再删除角色:

EXEC sp_dropsrvrolemember 'AERO15XV8\Aveline','ROLE1'; --移除角色内成员;
GODROP SERVER ROLE ROLE1;	--删除角色;
GO

用户

用户指的是数据库级别的用户,关键字USER,这是SQL server的一大设计特色,即:登录名->用户->数据库.

即登录名无法直接操作数据库对象,必须将登录名映射到具体的数据库用户,再以用户的身份去操作.

创建用户

用户是使用数据库的主体,创建用户的步骤很简单:

• 使用USE关键字指定使用的数据库;
• 使用CREATE关键字,再使用FROM LOGIN或FOR LOGIN指定从哪个登录名创建用户,如下:

USE AdventureWorks2017;
CREATE USER AaronFROM LOGIN [AERO15XV8\Aaron];
CREATE USER AvelineFROM LOGIN [AERO15XV8\Aveline];
GO

如此一来就可以了,我们使用某个登录名登录后,只要其在某个数据库中有映射用户,就可以对该数据库进行适当访问(适当指的是在用户权限范围内的访问),如:

上图是用的是[AERO5XV8\Aaron]登录服务器并对AdventureWork2017数据库进行查询操作的运行截图,实际上此时就是以数据库用户Aaron的身份登录数据库的,可以看到发生了一个错误:没有权限!可以查看登录名[AERO5XV8\Aaron]的用户映射,如下:

发现确实映射到了数据库AdventureWorks2017,至于为什么没有SELECT操作权限,会在后面数据库角色说明.

一般的,如果不存在用户映射,登录名将无法对数据库进行任何操作,但sa除外,它可以默认以dbo身份登录数据库,即database owner,拥有一切数据库操作权限.这应该是SQL server有意的设计,因为sa负责给所有登录名初始化权限、建立用户(新的登录名无权限建立用户,则sa可以为登录名建立用户)等,并且不能为sa建立用户映射!不要多此一举!参见下图:

另外再介绍几个SQL server自带的用户:

数据库用户名称	权限
dbo	拥有所有权限
guest	默认没有权限,默认处于禁用状态

可以使用GRANT CONNECT TO guest启用guest用户,使用REVOKE CONNECT FROM guest禁用guest用户.

如果希望查看数据库中用户的信息,可以使用sys.database_principals目录视图.该目录视图包含了有关数据库用户的名称、ID、类型、架构、创建和最后修改日期等信息,如下:

USE AdventureWorks2017;
GOSELECT * FROM sys.database_principals;
GO

查询运行结果如下:

架构

观察上面的[AERO5XV8\Aaron]的用户映射图片,你会发现一个默认架构的选项,这其实指定的是用户拥有的数据库架构,那什么是架构呢?先定个调:架构是数据库对象的集合,它的关键词是SCHEMA.

是的,架构是数据库对象的集合.也就是将数据库内部一些对象加入到一个集合构成架构.那么,架构的作用?

• 数据库用户登录服务器以后,通过为其设定架构,以确定该用户可操作数据库对象的范围.
• 即,架构限定了数据库用户的"游玩场地(PlayGround)".

如此一来SQL server的设计逻辑又更加清晰了:登录名->用户+架构->数据库.以下是SQL server常见的架构:

架构名	包含内容
dbo	数据库全部对象
public	空,无对象

创建架构

由于SQL server带有的架构较多,难以记住,且实际应用中人们对用户操作范围的界定通常很细致,不同的数据库,也会有不同的结构,因此很难为用户设定一个统一的模板架构(dbo、public除外),所以在实际使用中,我们会更倾向于创建为用户需求定制的架构,这就要介绍一下架构的创建了:

• 使用CREATE SCHEMA创建架构,
• 使用CREATE关键字往架构里新建数据库对象,如CREATE TABLE来建表.
• 上述新建对象同时也会存入数据库里(数据库什么都存,当然也存架构和架构下的所有内容).
• 使用AUTHORIZATION来指明架构的所有者.
• 使用GRANT将对架构内对象操作的权限授予用户,如GRANT SELECT TO,表示将SELECT权限授予用户.

如下是一个简单演示:

USE Temp;
GOCREATE USER Aaron	--新建用户;FROM LOGIN [AERO15XV8\Aaron];
GOCREATE SCHEMA test AUTHORIZATION Aaron	--将架构所有者设置为用户Aaron;CREATE TABLE Contacts(Name nvarchar(10),PhoneNumber char(11)); --架构中建表;
GO

以下是建立架构的代码运行前后,Temp数据库内部结构的对比:

建立架构test前	建立架构test后

可以看到,Temp数据库中多了一个架构test,且在test架构中建立的表Contacts现在以test.Contacts的形式存入了数据库!这说明:

• 用户创建的数据库对象是属于架构的,数据库不仅仅存用户数据,还存一些配置信息(如架构/用户信息).
• 数据库默认不指定所属架构的对象(如上图的表PriceTable)是属于dbo架构的,即databaseowner.
• 架构可以指定其所有用户,如test架构被Aaron所有,dbo架构被dbo用户所有.

正因为数据库什么有关信息都存入,因此我们说数据库是一种自己管理自己的体系.

使用sys.schemas可以查看数据库所有架构.

修改 && 删除架构

使用ALTER关键字可以对架构进行修改,修改架构一般指将架构中的对象转移到其它架构去,无法修改所有者.

使用TRANSFER关键字进行架构间对象转移,注意无需声明转移对象类型,直接指明转移对象名即可,如:

USE Temp;
GOCREATE SCHEMA refer	--创建新架构;
GOALTER TABLE test.Contacts ADD column_temp varchar(20) NULL;	--修改表,添加新列,名为column_temp;ALTER SCHEMA refer TRANSFER test.Contacts	--将test下的表Contacts转移至此架构;
GO

运行结果如图:

可以看到Temp数据库中产生了新架构,并且表Contacts已经从架构test转移到refer,此时我们删除refer和test,使用DROP,关键字代码如下:

USE Temp;
GODROP SCHEMA test;
GODROP SCHEMA refer;
GO

运行结果如下:

可以看到我们删除了架构test,无法删除refer,错误提示也很明显,因为refer中含有对象,因此,我们知道:

无法删除非空的架构!删除架构前要清空!此时我们使用如下语句,就可以删除架构了:

USE Temp;
GODROP TABLE refer.Contacts;
GODROP SCHEMA refer;
GO

用户 && 架构的小结

用户和架构都介绍了就可以介绍如何设置用户的架构,如何修改用户了.

修改 && 删除用户

修改用户体现在两方面:对用户名的修改,对用户拥有架构的修改.一样是使用ALTER关键字:

USE Temp;
GOCREATE SCHEMA test	--创建test架构;CREATE TABLE Contacts(Name nvarchar(10),PhoneNumber char(11));	--创建表;
GOALTER USER AaronWITH DEFAULT_SCHEMA = test,NAME = Alex;
GO

然后再查看架构test的所有者:

使用DROP关键字删除用户:

USE Temp;
GODROP USER Alex;
GO

错误信息如下:

是的,无法删除拥有架构的用户,只需将架构所有权转移即可,使用ALTER AUTHORIZATION关键字:

USE Temp;
GOALTER AUTHORIZATION ON SCHEMA::test TO dbo; --将用户所有架构归还予系统;
GODROP USER Alex
GO

总之就是这样,用户拥有架构,就拥有了对架构内数据库对象操作的可能(至于能如何操作,取决于用户权限).

另: 新建用户时,若未指定其默认架构,则默认用户拥有dbo架构.

数据库角色

是的,数据库角色和服务器角色类似,只不过其作用范围是单个数据库,拥有的权限也不一样.

既然是数据库级别的角色,那么对应的自然是数据库级别的用户了,SQL server两个对应关系必须牢记:

数据库角色 -> (数据库)用户,作用: 为数据库级别的用户分配权限

服务器角色 -> 登录名,作用: 为服务器级别的登录名分配权限

和服务器角色不一样的是,数据库角色分两种,一种是固定的,另一种用户定义的,接下来分别说明.

固定数据库角色

下表展示了所有的固定数据库角色:

角色名	权限
db_owner	db_owner的成员可以执行数据库的所有配置和维护活动，还可以删除 SQL Server中的数据库(在SQL Database和SQL数据仓库中,某些维护活动需要服务器级别权限,并且不能由db_owner执行)
db_securityadmin	db_securityadmin的成员可以修改角色成员身份和管理权限。 向此角色中添加主体可能会导致意外的权限升级
db_accessadmin	db_accessadmin的成员可以为Windows登录名、Windows组和SQL Server登录名添加或删除数据库访问权限
db_backupoperator	db_backupoperator的成员可以备份数据库
db_ddladmin	db_ddladmin的成员可以在数据库中运行任何数据定义语言(DDL)命令
db_datawriter	db_datawriter固定数据库角色的成员可以在所有用户表中添加、删除或更改数据.
db_datareader	db_datareader的成员可以从所有用户表中读取所有数据.
db_denydatawriter	db_denydatawriter的成员不能添加、修改或删除数据库内用户表中的任何数据.
db_denydatareader	db_denydatareader的成员不能读取数据库内用户表中的任何数据.

具体权限分配如下图:

将用户和数据库角色建立联系的常用操作如下:

命令/函数	用法	作用
sp_addrolemember	sp_addrolemember ‘role_name’,‘user_name’	将登录名加入数据库角色
sp_droprolemember	sp_droprolemember ‘role_name’,‘user_name’	将登录名移出数据库角色
sp_helprolemember	sp_helprolemember ‘role_name’	查看数据库角色下的登录名
IS_ROLEMEMBER	IS_ROLEMEMBER(‘role_name’,‘user_name’)	判定登录名的数据库角色

示例如下:

USE Temp;
GOCREATE USER AlexFROM LOGIN [AERO15XV8\Aaron]
GOEXEC sp_addrolemember 'db_owner','Alex';	--将Alex加入到db_owner中;
GOEXEC sp_helprolemember 'db_owner';	--查询db_owner中的所有用户;
GOIF IS_ROLEMEMBER('db_owner','Alex') = 1print 'Alex is db_owner';
GOEXEC sp_droprolemember 'db_owner','Alex';	--从db_owner中移除Alex;print 'Alex is db_owner no longer';
GODROP USER Alex;
GO

运行结果如下:

输出消息如下:

public角色

和固定服务器角色一样,public数据库角色没有任何权限.区别在于,数据库级别的public角色不是固定角色,也就是说我们可以为其分配权限.关于public数据库角色,有以下几点:

• 数据库所有用户默认继承public角色,该集成无法撤销/修改.
• 默认的public角色没有任何权限.
• 为public授权相当于为数据库所有用户授权.

授权操作将在后文权限部分介绍.

---

还记得上文创建用户Aaron却没有对AdventureWorks2017的访问权限吗?为方便叙述,把上面的一张图拿下来:

现在看来,这个问题就很好理解了,用户Aaron继承public角色,没有权限,因此即使其架构是dbo,也无法对数据库进行任何操作.那么解决这个问题有三个思路:

1. 为public授予相应的数据库操作权限.
2. 让用户继承固定数据库角色,使其拥有某些权限.
3. 新建数据库角色,自定义权限,并让用户继承.

从安全性角度考虑,我们最好不要使用方案1,而方案2,3将是很好的方案,接下来使用方案2解决:

USE AdventureWorks2017;
GOEXEC sp_addrolemember 'db_owner','Aaron';
GO

可以看到此时登录名在AdventureWorks下的映射用户Aaron继承了public和db_owner角色:

此时再使用Aaron登录数据库,就可以访问了:

管理数据库角色

创建数据库角色

角色的关键字:ROLE,使用CREATE关键字可以创建数据库角色.创建角色就是为角色命名和指定角色所有用户的过程,如果没有指定,则默认当前操作用户为角色所有者,使用AUTHORIZATION关键字设定角色所有用户.

USE Temp;
GOCREATE ROLE ROLE1;	--新建角色;
GOGRANT CONNECT TO guest;	--启用guest用户;
CREATE ROLE ROLE2 AUTHORIZATION guest;	--新建角色,并指定guest所有;
GO

如下图,可以看到数据库角色建立完成:

除此之外,我们也可以以已有的数据库角色(包括但不限于固定数据库角色)为模板创建:

只需使用AUTHORIZATION+数据库角色名就可以:

USE Temp;
GOCREATE ROLE db_owner_copy AUTHORIZATION db_owner;	--以db_owner为基础创建一个数据库角色;
GO

可以看到,在数据库中多了一个角色:

这样创建出来的角色和原来的角色权限一致,在批量导入权限时很方便,且还可以为新建的角色新增权限.

修改 && 删除数据库角色

使用ALTER关键字可以对数据库角色进行修改,修改包括角色名的修改和添加/删除用户的修改,如下:

USE Temp;
GOCREATE USER AlexFROM LOGIN [AERO15XV8\Aaron];
GOCREATE USER AvelineFROM LOGIN [AERO15XV8\Aveline]
GOALTER ROLE ROLE1 WITH NAME = ROLE0;	--改变角色的名称;
ALTER ROLE ROLE0 ADD MEMBER Alex;	--将Alex用户加入角色;
GOALTER ROLE ROLE2 DROP MEMBER guest;	--将guest用户移出角色;
ALTER ROLE ROLE2 ADD MEMBER Aveline;	--将Aveline用户加入角色;
GO

从下图可以看到,对角色的修改已完成:

还可以更改数据库角色所有者,使用ALTER AUTHORIZATION关键字操作:

USE Temp;
GOALTER AUTHORIZATION ON ROLE::ROLE0 TO dbo;	--将角色ROLE0的所有用户改为dbo;
GO

使用DROP关键字可以删除数据库角色,如下:

USE Temp;
GODROP ROLE db_owner_copy;
GO

注意: 和服务器角色一样,不允许对含有用户的数据库角色进行删除.

应用程序角色

应用程序角色和其他角色一样,被用于授权用户.它是一个数据库级别的角色,和数据库角色的区别有三点:

• 默认情况下应用程序角色不包含任何成员.
• 默认情况下应用程序角色处于非激活状态.
• 应用程序角色拥有密码,只有拥有密码的用户能够继承其权限.
• 应用程序角色拥有架构,可以限定用户的活动范围.
• 用户继承应用程序权限后将失去原有的权限,只拥有对应的应用程序权限.

创建应用程序角色

应用程序角色的关键字是: APPLICATION ROLE,使用CREATE创建,创建时应该设置密码:

USE Temp;
GOCREATE APPLICATION ROLE APR WITH PASSWORD = '12345678',DEFAULT_SCHEMA = test;
GO

以下是运行结果:

注: 上面的代码设定了默认架构,如果不设定,则默认使用dbo架构.

---

激活应用程序角色

以下两个存储过程分别用来激活/停用应用程序角色:

存储过程名	行为
sp_setapprole ‘role_name’,‘password’	激活应用程序角色
sp_unsetapprole @cookie varbinary(8000)	停用应用程序角色

• 激活后当前登录数据库的用户继承其权限,并失去原有的数据库用户权限.

• 激活后的应用程序角色一直生效,直到断开连接或设定为非激活为止.

DECLARE @cookie varbinary(8000);  
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'  , @fCreateCookie = true, @cookie = @cookie OUTPUT;  
-- The application role is now active.  
SELECT USER_NAME();  
-- This will return the name of the application role, Sales11.  
EXEC sp_unsetapprole @cookie;  
-- The application role is no longer active.  
-- The original context has now been restored.  
GO  
SELECT USER_NAME();  
-- This will return the name of the original user.   
GO 

---

修改 && 删除应用程序角色

可以使用ALTER APPLICATION ROLE来修改应用程序角色的名称、架构和密码:

USE Temp;
GOALTER APPLICATION ROLE APRWITH NAME = PRA,DEFAULT_SCHEMA = dbo,PASSWORD = '987654321';
GO

使用DROP APPLICATION ROLE来删除应用程序角色(不能删除含有架构/用户的应用程序角色):

USE Temp;
GODROP APPLICATION ROLE PRA;
GO

---

总结一下:

• 角色是权限的集合
• 架构是数据库对象的集合
• 实际用户 -> 登录名 + 角色 -> 数据库用户 + 角色 -> 数据库

最后附上一张图,说明用户、角色、架构的关系:

提示: 使用sp_helprole 'rolename’可以查看任意角色信息.

以上就是关于用户、角色、架构的所有内容,关于权限的操作,请关注下文.

上一篇: 登录名
下一篇: 管理权限