实验06 数据库安全管理

实验六数据库安全管理

系别：计算机科学与技术系专业班级：计算机科学与技术5班姓名：学号：

实验日期：2013年11月29日验报告日期：2013年12 月 1 日

实验目的：

理解SQL Server2005安全检查的三个层次：登录名、用户和权限；

理解SQL Server 2005中固定角色与自定义角色并掌握通过角色进行授权的方法；

掌握使用Grant/Revoke语句进行权限管理

实验环境及学时安排：

1．SQL SERVER2005

2．学时：2学时

实验准备：

SQL Server2005的安全检查分三个层次

–登陆名：登陆服务器时进行身份验证；

–用户：访问数据库须是数据库的用户或者是某一数据库角色的成员；

–权限:执行语句时须有执行权限

因此如果要一个用户在一个数据库中执行某条SQL语句必须首先为其创建登陆名（登录名可以选择Windows认证也可以选择SQL Server认证。如果选择前者则要求该账号必须是Windows账号）。接着在数据库中创建数据库用户，并让该用户映射已创建的登录名。最后通过GRANT命令或成为某个角色成员获得该用户执行操作的权限。

实验内容：

1、创建示例数据库/表

●创建jiaoxue数据库，并在该数据库下创建Student、Course、SC三个基本表，分

别在三个表中输入必要的数据（该操作使用前面实验的成果）。本次实验的所有操

作均在该数据库下完成。

略。。。

2、创建登陆名

用户可以通过企业管理器或系统提供的存储过程来进行登录帐户的创建。

●首先在Windows中创建用户John，然后在Management Studio中创建登录名John，

选择Windows身份验证。完成后点击工具栏上的“数据库引擎查询”按钮，在弹出的“连接到数据库引擎”窗口中选择Windows验证，使用刚创建的John账号登陆。检验刚创建的登录名是否成功。。

使用Management Studio创建登录名xs, 密码是123456，选择SQL Server验证方式，默认数据库为master。

点击右键新建登录名

使用Create Login创建登陆账户DB_user，密码是zhimakaimen，SQL Server认证方式，默认数据库为master的帐户。

SQL语句为：

create login DB_user

with password='zhimakaimen',

default_database=master;

3、修改和删除登陆名

已建立的登陆账户信息可以使用系统存储过程来修改默认数据库、默认语言、密码或删除用户。

●使用Alter login将帐户DB_user登录名的默认数据库改为jiaoxue。

●使用Alter login将帐户DB_user的密码改为自己的学号。

alter login DB_user

with default_database=jiaoxue,

password='200741402216';

●使用Drop Login删除用户lvhao。

drop login lvhao;

lvhao从栏目中消失

注意：在SQL Server中删除登陆名时，有很多限制。主要有以下几种情况：

a)系统帐户sa不能被删除；

b)已经映射到数据库用户上的登陆名不能被删除；

c)正在使用或连接的登陆名不能被删除；

d)拥有数据库的登陆名不能被删除

4、创建数据库用户

通过将服务器角色赋予不同的服务器登陆名，可以使之获得服务器级别的权限。服务器角色主要是控制服务器端对请求数据库资源的访问权限，他允许或拒绝服务器登陆名的访问操作，但是在设置具体数据库的管理和操作权限方面，服务器对象的权限设置粒度过大。因此为了实现更细粒度的权限管理，sqlserver还提供了数据库级别的对象：数据库用户（User）、数据角色（Role）、数据库架构（Schema）。这三个对象是针对每一个数据库实例的，因此可以对单个数据库实例进行细化权限划分。

对于拥有服务器角色sysadmin的登陆名对象，它可以SQL Server2005中做任何操作（由此可见服务器角色权限粒度之大）。但对那些没有sysadmin角色的登陆名对象，它需要拥有一个能访问特定数据库实例的数据库用户名（User）以实现对该数据库的操作。比如说为登陆名Kelvin想要访问AdventureWorks数据库，但不具备sysadmin的服务器角色。那么则在AdventureWorks数据库中中创建一个名为user_Kelvin的数据库用户。TSQL：USE AdventureWorks; GO; create user user_Kelvin for login Kelvin; 执行之后，用Kelvin登陆名登录的用户与AdventureWorks下的user_Kelvin用户建立起了关联。

使用Management Studio在Jiaoxue数据库中创建数据库用户John，该用户映射登陆帐号John。

右键—>新建用户名

点击确定就完成了

出现了新建的数据库用户名john

使用Create User在Jiaoxue数据库中创建数据库用户DB_user,该用户映射登陆用户DB_user。

use jiaoxue

go

create user DB_user

from login DB_user

go

创建了数据库用户DB_user

5、创建架构（SCHEMA）

使用CREATE SCHEMA/Management Studio创建架构demo。

create schema demo authorization DB_user

/18

6、修改/删除数据库用户

●使用Alter User将用户名DB_user改为自己的isme。（由此可见登陆名和用户名

可以不一致，两者是一种映射关系，并且这种映射关系是可以改变的）

use jiaoxue

alter user DB_user

with name=zhangjt;

●使用Management Studio修改用户isme，使其拥有架构demo。

6、角色管理

角色可以看作一组操作权限的集合。对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只须将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。用户与角色的关系为：一个角色可以包含多个用户；一个用户可赋予多个角色，从而拥有多个角色的权限。

SQL Server2005提供了服务器级别的角色和数据库级别的角色。期中服务器级别的角色不允许用户创建与修改，称为固定服务器角色。而数据库级别的角色包括固定数据库角色、用户自定义角色和应用程序角色。

（1）固定服务器角色

使用系统存储过程sp_helpsrvrole查看所有固定服务器角色，将发现包含以下角色：

exec sp_helpsrvrole;

固定服务器角色服务器级权限

bulkadmin 已授予：ADMINISTER BULK OPERATIONS

dbcreator 已授予：CREATE DATABASE

diskadmin 已授予：ALTER RESOURCES

processadmin 已授予：ALTER ANY CONNECTION、ALTER SERVER STATE

securityadmin 已授予：ALTER ANY LOGIN

serveradmin 已授予：ALTER ANY ENDPOINT、ALTER RESOURCES、ALTER SERVER STATE、ALTER SETTINGS、SHUTDOWN、VIEW SERVER STATE

setupadmin 已授予：ALTER ANY LINKED SERVER

sysadmin 已使用 GRANT 选项授予：CONTROL SERVER

●先使用create login创建SQL Server验证登录名newlogin，然后使用系统存储

过程sp_addsrvrolemember将登录帐号添加为服务器角色dbcreator的成员，从而

使其具有创建数据库的权利。点击“数据库引擎查询”按钮，在弹出的连接到数据

库引擎窗口中使用新创建的newlogin账号登陆服务器，在该服务器下创建数据库，验证新创建的登录名在加入固定服务器角色dbcreator前后权限的变化。

create login newlogin

with password='123';

exec sp_addsrvrolemember'newlogin','dbcreator';

●使用存储过程sp_dropsrvrolemember收回分配给newlogin登录帐户的指定固定服

务器角色dbcreator。

exec sp_dropsrvrolemember'newlogin','dbcreator';

回收分配给newlogin的服务器角色dbcreator,就不能创建数据库了,会出现错误

●使用management studio完成以上操作。

略..

（2）固定数据库角色

每个数据库中都有几个由系统创建的数据库角色-固定数据库角色，这些角色的权限也由系统预先分配。与固定服务器角色不同，这些角色的权限可以修改，它们也能被删除。固定数据库角色的名称及权限见下表：

固定数据库角色数据库级权限服务器级权限

db_accessadmin 已授予：ALTER ANY USER、CREATE SCHEMA 已授予：VIEW ANY DATABASE

db_accessadmin 已使用 GRANT 选项授予：CONNECT

db_backupoperator 已授予：BACKUP DATABASE、BACKUP LOG、CHECKPOINT 已授予：VIEW ANY DATABASE

db_datareader 已授予：SELECT 已授予：VIEW ANY DATABASE

db_datawriter 已授予：DELETE、INSERT、UPDATE 已授予：VIEW ANY DATABASE

db_ddladmin 已授予：ALTER ANY ASSEMBLY、ALTER ANY ASYMMETRIC KEY、

ALTER ANY CERTIFICATE、ALTER ANY CONTRACT、ALTER ANY

DATABASE DDL TRIGGER、ALTER ANY DATABASE EVENT、NOTIFICATION、ALTER ANY DATASPACE、ALTER ANY FULLTEXT

CATALOG、ALTER ANY MESSAGE TYPE、ALTER ANY REMOTE

SERVICE BINDING、ALTER ANY ROUTE、ALTER ANY SCHEMA、

ALTER ANY SERVICE、ALTER ANY SYMMETRIC KEY、

CHECKPOINT、CREATE AGGREGATE、CREATE DEFAULT、CREATE

FUNCTION、CREATE PROCEDURE、CREATE QUEUE、CREATE

RULE、CREATE SYNONYM、CREATE TABLE、CREATE TYPE、

CREATE VIEW、CREATE XML SCHEMA COLLECTION、REFERENCES

已授予：VIEW ANY

DATABASE

db_denydatareader 已拒绝：SELECT 已授予：VIEW ANY

DATABASE db_denydatawriter 已拒绝：DELETE、INSERT、UPDATE

db_owner 已使用 GRANT 选项授予：CONTROL 已授予：VIEW ANY DATABASE

db_securityadmin 已授予：ALTER ANY APPLICATION ROLE、ALTER ANY ROLE、

CREATE SCHEMA、VIEW DEFINITION

已授予：VIEW ANY

DATABASE

●使用系统存储过程sp_helpdbfixedrole浏览所有的固定数据库角色的相关内容。exec sp_helpdbfixedrole;

●使用Management Studio将用户isme添加到db_datareader固定数据库角色中，

从而使该用户可以查询jiaoxue数据库中的数据（注：db_datareader角色已被赋予Select权限）。该操作也可使用存储过程sp_addrolemember实现。

使用Management Studio操作

右键—>属性

勾住db_datareader选项,完成

SQL语句完成

use jiaoxue

exec sp_addrolemember'db_datareader','zhangjt';

●使用Management Studio从db_datareader固定数据库角色中删除isme用户，从

而收回对jiaoxue数据库的SELECT权限。该操作也可使用存储过程

sp_droprolemember实现。

使用Management Studio完成

略......

使用SQL语句完成

use jiaoxue

exec sp_droprolemember'db_datareader','zhangjt';

（3）自定义数据库角色

●使用Management Studio创建数据库角色Teacher，并使其拥有架构demo。

右键->新建数据库角色

完成

7.用户的权限管理

在数据库中使用grant/revoke/deny实现用户权限的管理，使其具有/不具有处理当前数据库中的数据或执行特定的Transact-SQL 语句权利。

●通过grant授予Teacher角色对student表的SELECT权限以及对SC表的全部权限

use jiaoxue

grant

select on student to Teacher;

grant

all privileges on SC to Teacher;

●通过gran授予用户isme创建基本表、视图的权限

use jiaoxue

grant

create table to zhangjt

grant

create view to zhangjt;

●通过gran授予用户isme查询course表的权限，且该权限可以传播

use jiaoxue

grant

select on course to zhangjt

with grant option;

●将isme加入到角色Teacher中，从而继承对Student的SELECT权限

exec sp_addrolemember'Teacher','zhangjt';

●通过deny拒绝isme从Teacher继承对Student的SELECT权限

use jiaoxue

deny elect on student to zhangjt;

●收回isme对course表的select权限

use jiaoxue

revoke select on course from zhangjt cascade;

●通过执行相关语句验证grant/revoke/deny执行前后权限的变化

grant 授予

revoke 回收

deny 拒绝

实验报告：

1.完成实验所用到的T-SQL语句

2.操作时出现的错误及原因分析

Ps：要理解好各个层次的安全性措施

服务器级别：

（1）登录名（2）服务器角色-----固定服务器角色

数据库级别：

（1）用户名（2）数据库角色-----固定数据库角色