sql server 2005用户权限设置深入分析

关于什么是用户权限,最简单的定义可能是,“用户能做什么和不能做什么。”在这里,简单的定义就相当不错了。


用户的权限分为3类

l 登录的权限;

l 访问特定数据库的权限;

l 在数据库中具体的对象上执行特定操作的权限。

既然我们已经看过了创建登录账户,这里将把重点放在登录账户能够拥有的特定权限上。


22.3.1 授予访问特定数据库的权限

如果想要一个用户可以访问数据库,你需要做的第一件事情是授予用户访问那个数据库的权限。可以在management studio中,通过把用户加入到服务器的数据库结点的用户成员中来实现。如果要用t-sql来添加用户,需要使用create user或遗留的存储过程sp_grantdbaccess。

注意,当你在数据库中create一个用户时,实际上,那些许可权限被存储在数据库中,并映射到那个用户的服务器标识符上。当还原数据库时,可能不得不在还原数据库的地方,重新把用户权限映射到服务器标识符。


1.create user

create user命令把新用户添加到数据库中。用户可以源自现有的登录名、证书或非对称密钥,用户也可以是只能在当前数据库中的本地用户。其语法如下:

create user <用户名>

[ { { for | from }

{

login <登录名>

| certificate <证书名>

| asymmetric key <密钥名>

}

| without login ]

[ with default_schema = <模式名> ]

对于这些元素,我们概略看一下其中一些元素的含义是什么:

选  项

说  明

login

想要授予访问当前数据库的权限的登录名


certificate

与用户关联的证书的逻辑名称。注意,必须已经使用create certificate命令创建了证书

asymmetric key

与用户关联的非对称密钥的逻辑名称。注意,必须已经使用create asymmetric key命令创建了密钥

without login

创建只能在当前数据库中活动的用户。可以用它来建立特定的安全上下文,但是,该用户不能映射到当前数据库之外的登录名,也不能访问任何其他的数据库

with default_schema

设立不是默认的“dbo”的模式,以作为当前用户的默认模式


2.sp_grantdbaccess

这是遗留的方法,用来授予登录名到特定数据库的访问权限。其语法如下:

sp_grantdbaccess [@loginame =] <‘登录名’>[, [@name_in_db =] <‘数据库中的别名’>

注意,授予的是当前数据库的访问权限——即是说,你 必须确保想要用户能够访问的数据库是发出该命令时的当前数据库。登录名是用来登录到sql server中的实际的登录id。参数name_in_db允许给该用户另外的识别名称。这个别名只适用于此处的数据库——其他所有的数据库仍将使用该登 录id的默认名称,或者使用在授予用户那个数据库的访问权限时所定义的别名。定义别名将影响身份识别函数,如user_name()。系统级别的函数(如 system_user)将返回基础的登录id。


22.3.2 授予数据库中对象的权限

好吧,用户拥有了登录名,并且,能够访问你想要他或她可以访问的数据库,那么,是否现在就万事大吉了呢?如果事情真有那么简单就好了!现在当然还没有一切就绪。

在用户能够访问什么的问题上,sql server给了我们级别相当精细的控制。多数时候,一些信息是希望用户能够访问到的,但是,数据库中也有另一些信息是不希望用户访问的。例如,你可能想 要客户服务人员能够查看和维护订单信息,但是可能不希望他们乱看工资信息。或许,反之亦然——你需要人力资源人员能够编辑雇员记录,但是,或许不想要他们 在交易上给某人很大的折扣。

sql server允许你给sql server中一些不同的对象指派一组不同的权限。能够为其指派权限的对象包括表、视图和存储过程。触发器隐含具有创建它们的人的权限。

对象上的用户权限分为6种不同的类型。

用户权限

说  明


select

允许用户“看到”数据。如果用户拥有该权限,则用户能够在其被授予权限的表或视图上运行select语句


insert

允许用户创建新的数据。具有这种权限的用户能够运行insert语句。注意,与许多系统不同,具有insert能力并不一定意味着拥有select权限


update

允许用户修改已有的数据。具有这种权限的用户能够运行update语句。类似于insert语句,具有update能力并不一定意味着拥有select权限。


delete

允许用户删除数据。具有这种权限的用户能够运行delete语句。同样,具有delete能力不一定意味着拥有
select权限

references

在要插入行的表中有引用另一个表的外键约束,而用户在那个表上没有select权限,references权限允许用户插入行


execute

允许用户execute指定的存储过程

在你正在把权限指定到其上的特定的表、视图或存储过程中,可以在需要时混合搭配这些权限。

可以在management studio中指派这些权限,你只需导航到服务器的“安全性”结点的“登录名”选项上。在用户上右击,并选择“属性”。根据你是在数据库中还是在安全性结 点中,打开的对话框将有所不同,但是,无论哪一种情况,都能够得到设置权限的选项。使用t-sql指派权限会使用三个命令,了解这三个命令是有益的,即使 你只准备通过management studio来指派权限(术语是相同的)。


1.grant

grant把对象上指定的访问权限给予指定的用户或角色,对象是grant语句的主体。

grant语句的语法如下所示:

grant

all [privileges] | <权限>[,…n]

on

<表名或视图名>[(<列名>[,…n])]

|<存储过程或扩展存储过程名>

to <登录id或角色名>[,…n]

[with grant option]

[as <角色名>]

all关键字表示你想要授予的是适用于那个对象类型的所有权限(execute绝不适用于表)。如果不使用all关键字,则需要提供一个或多个具体的权限,这些具体的权限是针对那个对象想要授予的。

privileges是一个新的关键字,它除了提供ansi-92兼容性外没有实际的功能。

on关键字用作一个占位符,以说明接下来的是想要授予其权限的对象。注意,如果你是在表上授予权限,可以通过明确说明受影响的列的列表来指定下至列级的权限——如果不提供具体的列,则认为将影响所有的列。

在对列级权限的看法 上,微软似乎做的是些表面的事情。能够说一个用户可以在特定的表上进行select,但仅限于在该表中特定的列上进行select,这似乎很酷,然而,在 列级权限的使用中以及微软为实现列级权限所做的工作中,确实让安全性处理太过错综复杂了。鉴于此,近来关于该主题的文献,以及我从内部人士那里得到的消 息,似乎都表明微软想要丢弃列级安全性了。在使用上他们建议——如果需要限制用户只能看到特定的列,请改为考虑使用视图。

to语句所做的事情正如你期望的那样——它指定想要把该访问权限授予谁。被授予权限的可以是登录id或角色名。

with grant option允许你向其授予访问权限的用户也能向其他用户授予访问权限。

由于使用该选项 后,要了解谁获得了访问什么的权限,将很快变得十分痛苦,因此,我建议避免使用该选项。当然,你总是可以进入到management studio中来查看对象上的权限,但那是被动反应的方式而非积极主动的方式——你是在查找当前访问级别上哪里出错了,而不是事先停止不希望发生的访问。

最后,但并非最不重要的,是as关键字。该关键字处理的是一个登录名属于多个角色的问题。

接下来,我们来看一、两个例子。后面将看到,我们已 经创建的testaccount账户,基于其是public角色(所有的数据库用户都属于的东西,并且,无法从中移除)中的成员而拥有了一些访问权限。然 而,尚有大量的项目是testaccount不具有访问权限的(由于public是testaccount唯一属于的角色,因此,public也不具有那 些权限)。

先从以testaccount用户登录开始。然后在region表上尝试一个select语句:

很快,你将收到来自sql server的消息,告知:你正在尝试去到你所不应该访问的地方。

单独以sa登录——如果你愿意,也可以在同一个查询编辑器实例中,通过选择菜单“文件”→“连接”,来完成这件事情。然后,为新的连接选择“sql server身份验证”,并用正确的密码以sa身份登录。现在,执行grant语句:

接着,切换回testaccount连接(要记住,以什么用户进行连接的信息显示在连接窗口的标题栏中),然后,再尝试执行select语句:这一次,得到了好得多的结果:

我们继续尝试另外的语句。这一次,我们在employeeterritories表上运行相同的测试和命令:

该语句执行失败——这同样是由于你不具备相应的权限所致,因此,授予用户该表上的权限:

然后,再次运行select语句,一切进展顺利:

不过,若要再添加一点变化,尝试在这个表中执行insert:

sql server立即会让我们走开——我们不具备必要的权限,因此,授予用户相应的权限(使用sa连接):

现在,再次运行insert语句:

一切进展顺利。


2.deny

deny明确阻止用户获得目标对象上指定的访问 权限。deny的关键所在是,它将覆盖任何grant语句。由于用户可以属于多个角色(马上将对此进行讨论),因此,一个用户可能属于被授予了访问权限的 角色,但同时又受deny的影响。如果用户个人的权限和基于角色成员身份所获得的权限混合在一起,deny和grant同时存在于其中,那么deny总是 优先的。简言之,如果用户或用户所属的任何角色在权限问题上有deny出现,则用户将不能使用在那个对象上的访问权限。

其语法很复杂繁多,看上去与grant语句一样:

deny

all [privileges]|<权限>[,…n]

on

<表名或视图名>[(列名[,…n])]

|<存储过程或扩展存储过程名>

to <登录id或角色名>[,…n]

[cascade]

同样,all关键字表明,想要拒绝授予该对象类型上所有可用的权限(execute绝不适用于表)。如果不使用all关键字,则需要提供一个或多个具体的权限,这些具体的权限是针对想要拒绝授予权限的对象的。

privileges依然是新关键字,并且,除了提供ansi-92兼容性外没有任何实际的功能。

on关键字用作一个占位符,以说明接下来的是想要拒绝授予其权限的对象。

到此为止,所有的事情都与grant语句几乎一样。 cascade关键字与grant语句中的with grant option相对应。cascade告诉sql server,如果用户在with grant option规则下授予了其他人访问权限,则对于所有这些人,也拒绝他们的访问。

为了在deny上运行一个例子,我们使用testaccount登录名尝试执行一个简单的select语句:

运行该语句后,将返回大约9条记录。在我们不曾授予testaccount该权限时,它是如何获得访问权限的呢?原因是,testaccount属于public,而public被授予了employees上的访问权限。

假如我们不希望testaccount能够访问employees。无论什么原因,testaccount是一个例外,并且我们不希望该用户查看那些数据——我们只需发出deny语句(记住要使用sa登录名来运行deny):

当再次用testaccount登录名运行select语句时,将得到一个错误——你不再能够访问。此外还要注意,由于我们使用了all关键字,因此,也拒绝将public所拥有的insert、delete和update访问权限授予testaccount。

注意,deny是sql server 7.0中新增的语句。在6.5版本中有拒绝授予权限的概念,但是,其实现方式是不同的。在6.5版中,不是使用deny,而是发出两次revoke语句。新的deny关键字让事情更加清晰明了。


3.revoke

revoke将消除以前发出的grant或deny语句的影响。可以把该语句想成是有针对性的“撤销”语句。

revoke的语法混合了grant和deny语句:

revoke [grant option for]

all [privileges] | <权限>[,…n]

on

<表名或视图名>[(列名 [,…n])]

|<存储过程或扩展存储过程名>

to | from <登录id或角色名>[,…n]

[cascade]

[as <角色名>]

实际上,这里要做的说明与对grant和deny语句的说明相同——然而,我将在这里再次讲述,以免你为了快速查找有关revoke的说明而向前翻阅本书。

同样,all关键字表明,想要撤销在该对象类型上所有可用的权限。如果不使用all关键字,则需要提供一个或多个具体的权限,这些具体的权限是针对那个对象想要撤销的权限。

privileges除了提供ansi-92兼容性外,依然没有任何实际的作用。

on关键字用作一个占位符,以说明接下来的是想要撤销其权限的对象。

cascade关键字与grant语句中的with grant option相对应。cascade告诉sql server,如果用户在with grant option规则下授予了其他人访问权限,则对于所有这些被授予权限的人,也将撤销他们的访问权限。

同样,as关键字只是用来说明想要基于哪个角色发出这一命令。

我们使用sa连接,撤销授予的到northwindsecure中的region表的访问权限。

执行完该语句后,testaccount将不能再在region表上运行select语句。

为了撤销deny,我们同样也发出一个revoke语句。这一次,将重新获得到employees表的访问权限:

现在,我们已经了解所有这些命令是如何针对单个用户来控制访问权限的,接下来,看这样一种方法,该方法通过分组管理来极大简化对这些权限的管理。


22.3.3 用户权限和语句级别的许可

用户许可权限并不仅仅局限于数据库中的对象上——它们也能扩展到某些其他的语句,这些语句不直接与任何特定的对象束缚在一起。sql server允许你对运行几种不同的语句的许可权限进行控制,这些语句包括:

l create database;

l create default;

l create procedure;

l create rule;

l create table;

l create view;

l backup database;

l backup log。

到现在为止,除了两个备份命令外,其他所有的这些命令我们都已经在操作中见过了。(备份命令要做什么是不言自明的,因此,眼下不准备在这上面花费时间,我们将在第24章中讨论它们——只需记住,它们是你能够在语句级别进行控制的东西。)

那么,我们如何指派这些许可权限呢?实际上,现在你 已经见过grant、revoke和deny针对对象的运作,那么,在语句级别的许可权限上你也已经有了相当的了解。从语法构成上说,它们与对象级别的许 可权限基本相同,除了它们更加简单(你不必填入那样多的东西)。其语法如下:

grant <all | 语句[,…n]> to <登录 id>[,…n]

很简单吧?接下来,通过验证我们的测试用户尚没有权力执行create,以进行一次快速的试验。确保以testaccount登录,然后,运行下面的命令(在下面的语句中,不要忘记把aristotle转换到你的域名):

我们运行上面的命令是完全行不通的:

现在,使用sa账户(或者其他具有northwindsecure的dbo权力的账户)登录到sql server中。然后,运行命令以授予许可权限:

你会得到命令成功执行的确认消息。然后,再次尝试运行create语句(记住使用testaccount登录):

这一次一切顺利。

在对象级别的许可权限上,deny和revoke也以同样的方式工作。

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐