Transcript 学习情境8：学生成绩数据库的安全性控制

《网络数据库技术》
—— 省级精品课程
http://www1.qzct.net/yjj
学习情景8
学生成绩数据库的安全性控制
主讲：余建军
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
技能目标：

掌握登录账号管理

掌握数据库用户管理

掌握角色管理

掌握权限管理
知识目标

理解SQL Server认证模式
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[背景知识]数据库应用系统用户权限管理
应用系统验证
方式
用户权
限管理
:
数据库管理系
统验证方式：
与操作系统集成
的验证方式 ：
所有用户对于后端的数据库的连接和使用,用相同
帐号(经常是数据库对象的所有者帐号)
每一个用户都要在数库中建立有相应的帐号,
用户使用自已的帐号连接和使用数库。
使用操作系统的身份标识连接和操作数库
库,本质上是数据库管理系统的验证方式
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
任务1 学生成绩管理系统用户权限控制设计
(数据库用户部分)
方案1：所有管理系统用户（所有学生、教师、秘书）使
用同一数据库登陆帐号（及所关联的数据库用户---使用数
据库的所有者）--做好学生成绩管理系统代码的安全控制。
方案2：管理系统用户按身份（学生、教师、秘书）使用
三种不同的数据库登陆帐号（及所关联的数据库用户）：
做好学生成绩管理系统代码的安全控制。
方案3：每个管理系统用户分别建立数据库登陆帐号（及
所关联的数据库用户）
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
最终方案设计：方案2和方案3整合
不同二级学院（信息工程学院）教学秘书分别建立一个账户；
所有教师都建立各自的账户；
所有班级学生都建立一个账户，供该班所有学生共用；
学生成绩数据库的管理员（具有对student数据库的所有控制权限）
衢州职业技术学院
网络数据库技术
表
http://www1.qzct.net/yjj
D
T
T-CLASSC
select select
select
select
select
（自己
班级）
（自己
班级）
（自己班
级）
（自己班
级）
（自己班
级）
C
S
SC
Select
select
（自己班
级）
（自己班
级）
CLASS
SC_TEMP
权限
身份
学生
权
限
控
制
教师
select
select
（自己学院） （自己学
院）
select
Insert
（自己
上的课
程）
select
（自己学
院）
select （自己 Select
上的课程）
（自己学
Insert
院）
delete （自
己上的课程）
Select
Update
（自己）
Select
（自己学
院）
秘书
Select
Update
Delete
Insert
（自己学院）
select
（自己
学院）
Select
Update
Delete
Insert
（自己学
院）
Select
update
（自己学
院）
Select
Update
Delete
Insert
（自己学
院）
Select
Update
Delete
Insert
（自己学
院）
管理
员
是所有表的所有者和数据库的创建者
Select
Update
Delete
Insert
（自己学
院）
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
任务2 创建学生成绩数据库的管理员
创建管理员的登录账号studentdb，初始密码654321(以sa登陆)
方法一：使用SQL-EM
SQL-EM→安全性→登录→右键→新建登录（名称studentdb，用 sql
server 身 份 验 证 ， 密 码 654321 ， 默 认 数 据 库 student ， 服 务 器 角 色 选
database creators,数据库用户名studentdb，其角色为student 数据库的
db_owner
方法二：使用SQL语句
(1) exec sp_addlogin ‘studentdb’,‘123456’,‘student‘
创建 登陆名
(2)exec sp_addsrvrolemember ‘studentdb’,‘dbcreator‘
填加到角色
(3) use student
exec sp_grantdbaccess ‘studentdb’,‘studentdb‘ 创建 数据库用户
(4) exec sp_addrolemember 'db_owner', 'studentdb‘填加到角色
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.1]SQL Server安全机制
服务器级安全: 登录数据库服务器
安全管理
数据库级安全: 访问数据库
数据库对象级安全：操作数据库对象
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.2]登录管理
1. 认证模式
Windows认证模式：Windows用户
混合认证模式： Windows用户＋SQL Server用户
2. 设置认证模式
SQL-EM→数据库服务器→右键→属性→安全性
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.3]创建登录账号
登录账号类型：标准账号、Windows用户账号、 Windows组账号
1. 使用SQL-EM
SQL-EM→安全性→登录→右键→新建登录
2. 使用SQL语句
语句格式：sp_addlogin '<登录账号>','<密码>','<数据库>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.4]用户管理
登录账号(Login Name)：登录数据库服务器
SQL Server账号
用户账号(User Name)：使用数据库
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.5]默认数据库用户
1. dbo
数据库创建者即为dbo(数据库所有者)，拥有数据库最高权限
新建数据库中只有一个用户dbo，不能删除
sa、固定服务器角色自动关联dbo
2. guest
所有登录账号自动拥有guest用户的权限
新建数据库中没有guest用户
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.6]创建数据库用户
1. 使用SQL-EM
SQL-EM→数据库→用户→右键→新建数据库用户
2. 使用SQL语句
语句格式：sp_grantdbaccess '<登录账号>'[,'<数据库用户>']
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.7]角色管理
角色：不同角色拥有不同权限
不同用户共同拥有的权限
方便权限管理
固定服务器角色
角色
固定数据库角色
自定义数据库角色
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.8]固定服务器角色
固定服务器角色：服务器级别，实现登录账号分组管理
固定服务器角色
权限
sysadmin
可在SQL Server中执行任何活动
serveradmin
可设置服务器范围的配置选项，关闭服务器
setupadmin
可管理链接服务器和执行某些系统过程
securityadmin
可管理登录和CREATE DATABASE权限,读取错误日志和更改密码
processadmin
可以管理在 SQL Server 中运行的进程
dbcreator
可以创建、更改和删除数据库
diskadmin
可以管理磁盘文件
bulkadmin
可以执行BULK INSERT语句
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.9]管理固定服务器角色
服务器角色在安装时定义，不能修改，只能添加删除成员
1.添加成员
SQL-EM:SQL-EM→安全性→服务器角色→右键→属性
SQL语句:sp_addsrvrolemember '<登录账号>','<服务器角色>'
2.删除成员
SQL-EM:SQL-EM→安全性→服务器角色→右键→属性
SQL语句:sp_dropsrvrolemember '<登录账号>','<服务器角色>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识2.10]固定数据库角色
固定数据库角色：数据库级别，实现数据库用户分组管理
固定数据库角色
权限
db_owner
在数据库中有全部权限
db_accessadmin
可添加或删除用户
db_securityadmin
可管理全部权限、对象所有权、角色和角色成员
db_ddladmin
可以添加、修改和删除数据库中的对象
db_backupoperator
可对数据库进行备份
db_datareader
可查看数据库内任何用户表中的所有数据
db_datawriter
可更改数据库内任何用户表中的所有数据
db_denydatareader
不能查看数据库中的数据
db_denydatawriter
不能更改数据库中的数据
Public角色：所有数据库用户都属于Public角色
固定数据库角色在安装时定义，不能修改只能添加删除成员
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
把数据库用户添加到固定数据库角色中
（1） SQL-EM：
SQL-EM：SQL-EM→数据库→用户→用户名→右键→属性→
添加到特定数据库角色
（2）SQL语句：
添加成员 sp_addrolemember '<角色>','<用户>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关技能2.1]修改登录账号
1. 使用SQL-EM
SQL-EM→安全性→登录→登陆名 →右键→属性
2. 使用SQL语句
修改密码：sp_password '<原密码>','<新密码>','<登录账号>'
修改默认数据库：sp_defaultdb '<登录账号>','<默认数据库>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关技能2.2]删除登录账号
1. 使用SQL-EM
SQL-EM→安全性→登录账号→右键→删除
2. 使用SQL语句
语句格式：sp_droplogin '<登录账号>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关技能2.3]删除数据库用户
1. 使用SQL-EM
SQL-EM→数据库→用户→右键→删除
注 不能删除拥有数据库对象的用户
2. 使用SQL语句
语句格式：sp_revokedbaccess '<数据库用户>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
任务3 创建学生成绩数据库的信息工程学院的
教师帐号
1. 创建信息工程学院的教师角色XGteacher
方法一：使用SQL-EM
SQL-EM→数据库→student →角色→右键→新建数据库角
色
方法二：使用SQL语句
use student
exec sp_addrole 'XGteacher'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识3.1]数据库角色
如果一组用户具有相同权限，可以通过自定义数据库角色实现
1. 创建自定义数据库角色
SQL-EM：SQL-EM→数据库→角色→右键→新建数据库角色
SQL语句：添加角色 sp_addrole '<角色名>'
添加成员 sp_addrolemember '<角色>','<用户>'
2.删除自定义数据库角色
SQL-EM：SQL-EM→数据库→角色→右键→删除
SQL语句：sp_droprole '<角色名>'
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
2. 给XGteacher角色分配权限
（1）建立C 、S、 CLASS、D、T-CLASS-C表的信息工程学院的视图
create view XG_c as select * from c where dno=(select dno from d where
dname='信息工程学院')
create view XG_s as select * from s where classno in (select classno from
class where dno=(select dno from d where dname='信息工程学院'))
create view XG_class as select * from class where dno=(select dno from d
where dname='信息工程学院')
create view XG_d as select * from d where dname='信息工程学院‘
create view Xg_T_CLASS_C as select * from T_CLASS_C
where classno in (select classno from class where dno=(select dno from
d where dname='信息工程学院'))
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
（ 2 ） 将 XG_C
、 XG_S 、 XG_CLASS 、 XG_D 、
XG_T_CLASS_C视图的SELECT权限赋给XGteacher角色
方法一：使用SQL-EM
SQL-EM→数据库→student →角色→ XGteacher →右键→ 属性(权限设
置)
方法二：使用SQL
grant select on XG_C to XGteacher
grant select on XG_CLASS to XGteacher
grant select on XG_S to XGteacher
grant select on XG_D to XGteacher
grant select on XG_T_CLASS_C to XGteacher
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识3.2] 权限管理
通过账号可以访问数据库，但对数据库对象操作必须拥有权限
默认权限：
系统自动授予，包括固定服务器、数据库角色
对数据库对象的访问权限
权限 对象权限：
语句权限：
执行管理类语句的权限
语句权限控制语句本身，所涉及数据库对象由对象权限决定
引例：设置数据库库用户权限
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识3.3]对象权限
1. 使用SQL-EM
SQL-EM→数据库→对象→右键→属性
实例 授予public对student表s的select操作权限
该例实际将student表s的select操作权限授予student的所有用户
2. 使用SQL语句
授予、撤销、禁止对象权限：GRANT|REVOKE|DENY
ALL|<对象权限> ON <对象名> TO <用户>|<自定义数据库角色>
实例 授予user02对student表s的insert、delete、update操作权限
撤销与禁止权限的区别是：撤销还可从角色中继承，而禁止不能
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识3.4]语句权限
1. 使用SQL-EM
SQL-EM→数据库→右键→属性
实例 授予public对student的create table语句权限
create database语句权限在master中设置
2. 使用SQL语句
授予、撤销、禁止语句权限：GRANT|REVOKE|DENY
ALL|<对象权限> TO <用户>|<自定义数据库角色>
实例 授予user02对student的create table、 create view语句权限
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
（3）创建教师‘马文龙’的登陆帐号和数据库用户，并作为
XGteacher角色的成员（其他教师帐号创建类似）
方法一：使用SQL-EM
SQL-EM→安全性→登录→右键→新建登录（名称mwlT，用 sql
server 身 份 验 证 ， 密 码 654321 ， 默 认 数 据 库 student ， 数 据 库 用 户 名
mwlT ，其角色为student 数据库的XGteacher。
方法二：使用SQL语句
(1) exec sp_addlogin ‘mwlT’,‘123456’,‘student‘
创建 登陆名
(2) use student
exec sp_grantdbaccess ‘mwlT’,‘mwlT‘ 创建 数据库用户
(4) exec sp_addrolemember ' XGteacher ', ' mwlT‘填加到角色
学生用户权限分配用(1)(2)(3)三步类似操作即可
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
（4）给教师‘马文龙’的数据库帐号分配T，SC，SC_temp表的控制权
限
①建立T（自己信息），SC（自己所上课信息）， SC_temp表的视图
use student
create view Wen_t as select * from t where tname='马文龙'
create view Wen_sc as select * from sc where cno in
(select cno from T_Class_C,t where t.tno=T_Class_C.tno and tname='马文
龙')
create view Wen_sc_temp select * from sc_temp where cno in
(select cno from T_Class_C,t where t.tno=T_Class_C.tno and tname='马文
龙')
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
②将Wen_t视图的SELECT和update权限， Wen_sc视图的select 、insert
权限， Wen_sc_temp视图的select 、 insert 、 delete 、 update权限 赋给
mwlT 用户
grant select, update on Wen_t to mwlT
grant select, insert on Wen_sc to mwlT
grant select, insert ,delete,update on Wen_sc_temp to XGteacher
衢州职业技术学院
网络数据库技术
http://www1.qzct.net/yjj
[相关知识3.5] SQL Server安全管理机制
注册
数据库 访问
数据库
操作 数据库
服务器
登录账号
对象
数据库用户
衢州职业技术学院
权限及角色