《数据库应用-数据库原理与SQL Server》课件第8章

第8章数据安全性管理

8.1登录帐户管理8.2用户管理8.3角色管理8.4许可管理小结习题

8.1登录帐户管理

8.1.1SQLServer2005安全机制

SQLServer2005除继承了SQLSewer2000的可靠性、可用性、可编程性、易用性等方面的特点外，还在安全性方面做了很大改进。

SQLServer2005新增的这些安全特性，不仅使SQLServer更加安全而且使安全性更容易理解。其中一些特性将允许开发者在以自己的权限运行时，开发自己所需要的数据库应用程序,这就是所谓的最低权限原则。也就是说,每一个开发者都不需要以SQLServer管理员登录帐户sa的身份运行。其他重要的特性如下所示：

(1) .NETFramework可执行代码的安全性。

(2) SQLServer用户的密码策略。

(3)用户和架构的分离。

(4)授予权限。

(5)元数据的新安全性。

(6)过程化地执行上下文。

(7)证书和密钥的支持。

(8)过程化代码的签名。8.1.2SQLServer2005的验证模式

每个网络用户在访问SQLServer服务器时，都必须经过两个阶段的身份验证：第一个阶段的身份验证是验证用户是否具有连接服务器的资格，SQLServer使用“登录帐户”来标识用户的连接资格；第二个阶段的身份验证是验证用户是否具有访问和操作数据库的资格。下面，首先讨论第一阶段的验证，也就是对登录帐户的验证。

SQLServer2005对登录帐户的验证，有以下两种模式：

(1) Windows验证模式。

(2) SQLServer和Windows混合验证模式。

SQLServer2005对登录帐户的验证过程可以用图8-1表示。图8-1SQLServer2005验证模式8.1.3配置SQLServer2005验证模式

在SQL-MS中，单击展开左侧窗口中的服务器组，选中要进行验证模式设置的服务器，单击右键，打开快捷菜单，选择“属性”命令，打开“服务器属性”对话框，选择其中的“安全性”选项卡，如图8-2所示。图8-2配置验证模式8.1.4创建登录帐户

登录帐户的信息存储在系统数据库master的表syslogins中，当在SQLServer2005系统中增加一个登录帐户时，一般要指定一个默认数据库。为登录帐户指定一个默认数据库并不是在该数据库中创建一个用户帐户，而是使用数据库中的Guest用户帐户访问该数据库。当然也可以为该登录帐户在数据库中创建了一个用户帐户，创建的方法在后面介绍。

1.使用SQL语句

创建登录帐户可以通过执行系统存储过程sp_addlogin实现。其基本语法格式为：

sp_addlogin[@loginame=]'<登录帐户名>'

[,[@passwd=]'<密码>']

[,[@defdb=]'<数据库名>']

[,[@deflanguage=]'<语言>']

实例8-1

新建登录帐户login1，密码为login11，默认数据库为student。

在查询编辑器中输入SQL语句并执行，如图8-3所示。图8-3创建登录帐户

2.使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定的数据库服务器“安全性”文件夹，单击“登录名”节点，右侧窗口显示已存在的登录帐户的信息，如图8-4所示。

(2)选择左侧窗格中“登录名”节点，单击右键，打开快捷菜单，选择“新建登录名”命令，打开“登录名-新建”对话框，如图8-5所示。图8-4“登录帐户”窗口图8-5“登录名-新建”对话框

(3)单击“常规”选项页，在“名称”框中输入新建登录帐户的名称，也可以单击名称文本框后的按钮，选择Windows用户作为登录帐户。同时，在该选项卡上还可以设置登录帐户的身份验证模式、默认语言和默认数据库。

(4)单击“服务器角色”选项页，可以设置该登录帐户对应的服务器角色，在“用户映射”选项页上可以设置该登录帐户的数据库访问许可。角色和访问许可的相关知识将在第8.3和8.4节介绍。

(5)单击“确定”按钮，完成新登录帐户的创建。此时，在右侧窗口中可以看到新创建的登录帐户的相关信息。8.1.5修改登录帐户

1.使用SQL语句

(1)修改登录帐户默认数据库。修改登录帐户默认数据库可以通过执行系统存储过程sp_defaultdb实现。其基本语法格式为：

sp_defaultdb[@loginame=]‘<登录帐户>’,[@defdb=]‘<数据库名>’

实例8-2

将登录帐户login1的默认数据库修改为master。

在查询编辑器中输入SQL语句并执行，如图8-6所示。图8-6修改登录帐户默认数据库

(2)修改登录帐户密码。修改登录帐户密码可以通过执行系统存储过程sp_password实现。其基本语法格式为：

sp_password[[@old=]‘<原密码>’,]

{[@new=]‘<新密码>’}

[,[@loginame=]‘<登录帐户>’]

实例8-3

将登录帐户login1的密码由login11改为abcdef。

在查询编辑器中输入SQL语句并执行，如图8-7所示。图8-7修改登录帐户密码

2.使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定的数据库服务器“安全性”文件夹，单击“登录名”节点，指向右侧窗口要修改的登录帐户，单击右键，选择“属性”命令，打开“登录属性”对话框，如图8-8所示。

(2)修改该登录帐户的密码、默认库等属性，最后单击“确定”按钮，完成登录帐户修改。图8-8“登录属性”对话框8.1.6删除登录帐户

1.使用SQL语句

删除登录帐户可以通过执行系统存储过程sp_droplogin实现。其基本语法格式为：

sp_droplogin[@loginame=]‘<登录帐户>’

实例8-4

删除登录帐户login1。

在查询编辑器中输入SQL语句并执行，如图8-9所示。图8-9删除登录帐户

2.使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定的数据库服务器“安全性”文件夹，单击“登录名”节点，指向右侧窗口要删除的登录帐户，单击右键，选择“删除”命令，打开“删除对象”对话框，如图8-10所示。

(2)单击“确定”按钮，完成登录帐户删除。图8-10“删除对象”对话框

8.2用户管理

8.2.1特殊的数据库用户

在SQLServer2005中包含有以下三种特殊的数据库用户，具有特殊的权限和作用。

(1)数据库所有者(dbo)；

(2)数据库对象所有者；

(3) guest。

1.数据库所有者

SQLServer2005数据库的创建者即为该数据库的所有者。此外，固定服务器角色sysadmin的所有成员被映射为每一个数据库的dbo。dbo用户不能从数据库中删除，dbo对数据库具有所有操作权限，并且可以将所拥有的数据库操作权限授予其他用户。

sysadmin成员所创建的数据库对象自动属于dbo，而不属于用户自身。所以，在SQL语句中指定数据库对象时，如果数据库对象为sysadmin角色成员所创建，则使用dbo，否则使用创建者的用户名称指定数据库对象。实例8-5

如果student数据库有stonepf和stone两个用户，其中stonepf属于sysadmin角色，而stone属于db_owner角色。他们分别在student数据库中建立两个数据表Table1和Table2，指定这两个对象时分别使用dbo和stone。

在查询编辑器中输入下列SQL语句并执行：

SELECT*FROMMyDB.dbo.Table1

SELECT*FROMMyDB.stone.Table2

2.数据库对象所有者

SQLServer2005数据库中的对象包括表、索引、视图、默认、规则、触发器、用户定义函数和存储过程等，数据库对象的创建者即成为该数据库对象的所有者。数据库对象所有者隐含具有该对象的所有权限，只有他显式地向其他用户授权后，其他用户才能访问该数据库对象。

在一个数据库中不同用户可以创建同名的数据库对象，所以当用户访问数据库对象时，必须对数据库对象所有者进行指定。如下面语句指定要检索用户stone所创建的Table1表：

SELECT*FROMstone.Table1

3. guest用户

guest用户是SQLServer2005数据库中的一个特殊用户。当某个SQLServer2005登录帐户在数据库中没有建立对应的数据库用户，而又要访问该数据库时，SQLServer2005就查找数据库中是否存在guest用户。若存在该用户，就允许该登录帐户使用guest用户的权限访问数据库，否则将拒绝其访问。SQLServer2005系统数据库master和tempdb中的guest用户帐户不能删除，而其他数据库中的guest用户均可创建或删除。8.2.2创建数据库用户

1.使用SQL语句

在SQLServer2005中，为Windows用户(组)和SQLServer2005登录帐户建立数据库用户的方法相同，可以通过执行系统存储过程sp_grantdbaccess实现。其基本语法格式为：

sp_grantdbaccess[@loginname=]'<登录帐户>'

[,[@name_in_db=]'<数据库用户名>']

实例8-6

为sttone-i5p0laxy域中的用户adminofdb在student数据库中建立同名的数据库用户。

在查询编辑器中输入SQL语句并执行，如图8-11所示。图8-11创建数据库用户实例实例8-7

为SQLServer2005登录帐户login1在数据库student中建立名为user1的数据库用户。

在查询编辑器中输入SQL语句并执行，如图8-12所示。图8-12创建数据库用户实例

2.使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定的数据库，打开“安全性”节点，单击“用户”节点，右侧窗口显示当前数据库用户的信息，如图8-13所示。

(2)指向左侧窗口中“用户”节点，单击右键，打开快捷菜单，选择“新建用户”命令，打开“数据库用户-新建”对话框，如图8-14所示。图8-13“数据库用户”窗口图8-14“数据库用户-新建”对话框

(3)在“用户名”文本框中输入新建用户的名称，单击“登录名”文本框旁边按钮，打开“选择登录名”对话框，如图8-15所示。图8-15“选择登录名”对话框

(4)单击“浏览”按钮，打开“查找对象”对话框，如图8-16所示。图8-16“查找对象”对话框

(5)选择“login1”用户，单击“确定”按钮，返回“选择登录名”窗口，再单击“确定”按钮，返回“数据库用户-新建”对话框。

(6)同(5)，选择默认架构为“dbo”，然后设置该用户所属的角色。单击“确定”按钮，完成新用户的建立。

刷新“用户”节点，就可以看到刚才创建的“login1”用户帐户。8.2.3删除数据库用户

1.使用SQL语句

数据库用户可以通过执行系统存储过程sp_revokedbaccess实现删除。其基本语法格式为：

sp_revokedbaccess[@name_in_db=]'<数据库用户名>'

实例8-8

删除student数据库中的用户user1。

在查询编辑器中输入SQL语句并执行，如图8-17所示。图8-17删除数据库用户

2.使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定的数据库，单击“用户”节点，指向右侧窗口中要删除的数据库用户，单击右键，打开快捷菜单，选择“删除”命令，打开“删除对象”对话框，如图8-18所示。

(2)单击“确定”按钮，完成数据库用户删除。图8-18“删除对象”对话框

8.3角色管理

8.3.1管理固定服务器角色

1.固定服务器角色

SQLServer2005在安装过程中定义了几个固定角色，可以向这些角色中添加用户以获得相关的管理权限。固定角色包括固定服务器角色和固定数据库角色。其中，固定服务器角色是在服务器的级别上定义的。表8-1列出了固定服务器角色及其所拥有的权限。

2.向固定服务器角色中添加成员

1)使用SQL语句

向固定服务器角色中添加成员，可以通过执行系统存储过程sp_addsrvrolemember实现。其基本语法格式为：

sp_addsrvrolemember[@loginame=]‘<登录帐户名>’，[@rolename=]‘<角色名>’

其中，“角色名”为固定服务器角色。

实例8-9

在student数据库中，把登录帐户login1添加到固定服务器角色sysadmin中。

在查询编辑器中输入SQL语句并执行，如图8-19所示。图8-19添加固定服务器角色成员

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库服务器“安全性”文件夹，单击“服务器角色”节点，则右侧窗口显示出固定服务器角色信息，如图8-20所示。

(2)指向右侧窗口中要添加成员的固定服务器角色，单击右键，打开快捷菜单，选择“属性”命令，打开“服务器角色属性”对话框，如图8-21所示。图8-20“查看服务器角色”窗口图8-21“服务器角色属性”对话框

(3)单击“添加”按钮，打开“选择登录名”对话框，如图8-22所示。图8-22“选择登录名”对话框

(4)单击“浏览”按钮，打开“查找对象”对话框，如图8-23所示。

(5)选择要添加的登录帐户，单击“确定”按钮，返回“选择登录名”对话框，再单击“确定”按钮，返回“服务器角色属性”窗口，如图8-21所示。

(6)单击“确定”按钮，完成服务器角色添加。图8-23“查找对象”对话框

3.从固定服务器角色中删除成员

1)使用SQL语句

删除固定服务器角色的成员，可以通过执行系统存储过程sp_dropsrvrolemember实现。其语句基本语法格式为：

sp_dropsrvrolemember[@loginame=]‘<登录帐户名>’，[@rolename=]‘<角色名>’

实例8-10

把登录帐户login1从固定服务器角色sysadmin中删除。

在查询编辑器中输入SQL语句并执行，如图8-24所示。图8-24删除固定服务器角色成员

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库服务器“安全性”文件夹，单击“服务器角色”节点，指向右侧窗口要删除成员的固定服务器角色，单击右键，打开快捷菜单，选择“属性”命令，弹出“服务器角色属性”对话框，参见图8-21。

(2)单击“删除”按钮，再单击“确定”按钮，完成成员删除。8.3.2管理数据库角色

数据库角色是在数据库级别上定义的，存在于每个数据库中，分为固定数据库角色和用户自定义数据库角色。

1.固定数据库角色

SQLServer2005在安装过程中定义了几个固定数据库角色，固定数据库角色及其所拥有的权限如表8-2所示。

2.建立用户自定义数据库角色

1)使用SQL语句

创建用户自定义数据库角色可以通过执行系统存储过程sp_addrole实现。 其基本语法格式为：

sp_addrole[@rolename=]‘<角色名>’[，[@ownername=]‘<所有者>’]

其中，“角色名”为新角色的名称。“所有者”为新角色的所有者，必须是当前数据库中的某个用户或角色，默认值为dbo。

实例8-11

在student数据库中添加角色role1。

在查询编辑器中输入SQL语句并执行，如图8-25所示。图8-25添加角色

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库，展开“安全性”节点，打开“角色”节点，单击“数据库角色”节点，在右侧窗口显示出固定数据库角色信息，如图8-26所示。

(2)指向左侧窗口“数据库角色”节点，单击右键，打开快捷菜单，选择“新建数据库角色”命令，打开“数据库角色-新建”对话框，如图8-27所示。图8-26“查看固定数据库角色”窗口图8-27“数据库角色-新建”对话框

(3)在“角色名称”框中输入新建角色的名称，单击所有者输入框后的按钮，选择角色的所有者，此处为“db_owner”，可以设置此角色拥有的架构，如果需要，单击“添加”按钮，将适当的用户添加至新建的数据库角色中。

(4)单击“确定”按钮，完成定义用户自定义数据库角色。

3.删除用户自定义数据库角色

1)使用SQL语句

删除用户自定义数据库角色可以通过执行系统存储过程sp_droprole实现。其基本语法格式为：

sp_droprole[@rolename=]‘<角色名>’

实例8-12

在student数据库中删除角色role1。

在查询编辑器中输入SQL语句并执行，如图8-28所示。图8-28删除用户自定义数据库角色

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库，展开“安全性”节点，打开“角色”节点，单击“数据库角色”节点，指向右侧窗口中要删除的角色，单击右键，打开快捷菜单，选择“删除”命令，打开“删除对象”对话框，如图8-18所示。

(2)单击“确定”按钮，完成删除用户自定义数据库角色。

4.向数据库角色中添加成员

1)使用SQL语句

向数据库角色(包括固定数据库角色和用户自定义数据库角色)中添加成员，可以通过执行系统存储过程sp_addrolemember实现。其基本语法格式为：

sp_addrolemember[@rolename=]‘<角色名>’，[@membername=]‘<用户名>’

实例8-13

在数据库student中，把用户user1添加到角色role1中。

在查询编辑器中输入SQL语句并执行，如图8-29所示。图8-29添加数据库角色成员

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库，展开“安全性”节点，打开“角色”节点，单击“数据库角色”节点，指向右侧窗口中要添加成员的数据库角色，单击右键，打开快捷菜单，选择“属性”命令，打开“数据库角色属性”对话框，如图8-30所示。图8-30“数据库角色属性”对话框

(2)单击“添加”按钮，打开“选择数据库用户或角色”对话框，如图8-31所示。图8-31“选择数据库用户或角色”对话框

(3)选择“浏览”按钮，打开“查找对象”对话框。

(4)选择要添加的成员，单击“确定”按钮，返回“选择数据库用户或角色”对话框，再单击“确定”按钮，返回“数据库角色属性”对话框，单击“确定”按钮，完成添加数据库角色成员。

5.从数据库角色中删除成员

1)使用SQL语句

从数据库角色(包括固定数据库角色和用户自定义数据库角色)中删除成员，可以通过执行系统存储过程sp_droprolemember实现。其基本语法格式为：

sp_droprolemember[@rolename=]‘<角色名>’，

[@membername=]‘<用户名>’

实例8-14

把用户user1从角色role1中删除。

在查询编辑器中输入SQL语句并执行，如图8-32所示。图8-32删除数据库角色成员

2)使用SQL-MS

(1)启动SQL-MS，展开左侧窗口中指定数据库，展开“安全性”节点，打开“角色”节点，单击“数据库角色”节点，指向右侧窗口中要添加成员的数据库角色，单击右键，打开快捷菜单，选择“属性”命令，打开“数据库角色属性”对话框，单击选择要删除的成员，如图8-33所示。

(2)单击“删除”按钮，再单击“确定”按钮，完成删除成员。图8-33“数据库角色属性”窗口

8.4许可管理

数据库对象创建后，只有拥有者可以访问该数据库对象，任何其他用户想访问该对象必须获得拥有者的授权，也就是许可。许可分为以下两种类型。

(1)语句许可：数据库拥有者可以授予执行某些SQL命令的许可。这种许可在SQLServer2005中称作语句许可。如拥有者可给将执行CREATETABLE或CREATEVIEWS等语句的许可授予其他用户，这些语句本来只有特定用户(如dbo)可以使用。

(2)对象许可：拥有者可以将数据库对象的许可授予指定的数据库用户，这种许可被称作对象许可。如对表这种数据对象，拥有者可以把对该表执行INSERT、DELETE、UPDATE、SELECT和REFERENCE等的许可授予其他用户。8.4.1使用SQL-MS

1．管理语句许可

(1)启动SQL-MS，指向左侧窗口指定数据库节点，单击右键，打开快捷菜单，选择“属性”命令，打开“数据库属性”窗口，选择“权限”选项页，如图8-34所示。

(2)选中用户，“显式权限”中每个语句对应的复选框，可以设置该用户语句许可。

(3)单击“确定”按钮，完成语句许可设置。图8-34“数据库属性”窗口“权限”选项页

2.管理对象许可

(1)启动SQL-MS，展开左侧窗口指定数据库，指向右侧窗口中指定表，单击右键，打开快捷菜单，选择“属性”命令，打开“表属性”对话框，选择“权限”选项页，如图8-35所示。

(2)单击用户每个语句下面的复选框，设置该用户语句许可。“√”表示授予该用户对该表的该语句许可；空白表示未设置许可。单击“确定”按钮，完成对象许可设置。图8-35“对象属性”对话框8.4.2使用SQL语句

1.授予许可

授予语句许可语句的基本语法格式为：

GRANT{ALL|<语句>[，...n]}

TO<安全帐户>[，...n]

授予对象许可语句的基本语法格式为：

GRANT

{ALL|<对象权限>[，...n]}

{[(<列名>[，...n])]ON{<表名>|<视图名>}

| ON{<表名>|<视图名>}[(<列名>[，...n])]

| ON{<存储过程名>}

| ON{<用户自定义函数名>}

}

TO<安全帐户>[，...n]

[WITHGRANTOPTION][AS{group|role}]实例8-15

把student数据库中的表s的SELECT操作的许可授予public角色，然后再把有关它的INSERT、UPDATE、DELETE操作的许可授予帐户user1。

在查询编辑器中输入SQL语句并执行，如图8-36所示。

实例8-16

把在student数据库上创建表和视图的命令授予用户user1。

在查询编辑器中输入SQL语句并执行，如图8-37所示。图8-36授予对象许可图8-37授予语句许可

2.撤销许可

撤销许可语句的基本语法格式为：

REVOKE{ALL|<语句>[，...n]}

FROM<安全帐户>[，...n]

撤销对象许可语句的基本语法格式为：

REVOKE[GRANTOPTIONFOR]

{ALL|<对象许可>[，...n]}

{[(<列名>[，...n])]ON{<表名>|<视图名>}

|ON{<表名>|<视图名>}[(<列名>[，...n])]

|ON{<存储过程名>}

|ON{<用户自定义函数名>}

}

{TO|FROM}