数据库安全及事务操作

一、目的与要求

• 掌握SQL安全机制
• 掌握服务器的安全性的管理
• 掌握数据库用户的管理
• 掌握权限的管理

二、实验内容

1 设计安全机制使得用户王明 只能查找财务部的职工

思路：创建登录SQL账户王明，数据库用户王明DB，创建只包含财务部员工的视图，授予王 查询该视图的权力

--创建SQL server  登录账户 王明
use  实验三
exec   sp_addlogin '王明' ,'123456'
--创建数据库用户 王明DB
exec  sp_grantdbaccess '王明','王明DB'
go
--创建只含有财务部员工信息的视图
CREATE   VIEW  view_FinanceEmp  as
SELECT  *
from   Employees
where Employees.dep_no = 
(select dep_no   from  Department  where dep_name='财务部')
go
--授予王明DB查看该视图的权限
GRANT   SELECT  ON  view_FinanceEmp to  王明DB

2 设计一个角色“role_Emp”,该角色可以查看雇员编号，姓名。并将王明作为成员加入到该角色

思路：

1 创建角色：sp_addrole ‘role_Emp’，创建视图

2 给该角色授权

3 将王明DB加入到该角色

--创建角色
exec  sp_addrole 'role_Emp'
--创建只包含雇员编号，姓名的视图
go
CREATE  VIEW  view_empId_Name  as
select     emp_no,emp_name
from    Employees
go;
select  *   from  view_empId_Name
go
--授予该角色查询的权力
GRANT   select   
on   view_empId_Name
to   role_Emp
--将王明加入到该角色中
exec  sp_addrolemember 'role_Emp','王明DB'

3 用户张明拥有以下权限；他只能查进货表中的信息，并拥有对自己进货表的信息修改的权限，其他表的信息无权查看。

授予他 Purchase 表的查看权限

授予他 purchase表中的一部分（视图）修改的权限

exec sp_addlogin '张明','123456'--创建sql登录账户
exec sp_grantdbaccess '张明','张明DB'--创建数据库用户张明DB
go
create view view_zhangming
as
select * from Purchase
where Emp_no in(select Emp_no from Employeeswhere Emp_name='张明')
go
Grant select on Purchase to 张明DB --授予查询进货表的权限
Grant select,update on view_zahngming to 张明DB --授予修改自己进货的信息的权限

该视图由单表导出，包含主键，无聚集函数，所以修改视图时，基本表也会跟着修改

4 如何使得采购部门的员工都具有这样的权限：能查看进货表的信息，并拥有对自己采购信息的修改

（编写存储过程Pro_purchase 作用：输入参数为员工姓名，从进货表中查找该员工所进的产品，没有则返回，有则相应的在login表中添加账户和密码。并且，创建相应的登录账号和数据库用户）

go
create proc proc_stu_grant
@Emp_name varchar(30)
as
begin
declare @view varchar(20) --视图名
declare @str  varchar(255) --执行语句
if exists (select Pur_no from Purchasewhere Emp_no in (select Emp_no from Employeeswhere Emp_name=@Emp_name))--判断该员工是否有进货信息beginset @view =@Emp_name + '_v'set @str='create view '+@view+'asselect * from Purchase where Emp_no in (select Emp_no from Employeeswhere Emp_name='''+@Emp_name+''')' --创建自己采购的信息视图语句exec(@str)exec sp_addlogin @Emp_name,'123'  --创建登录账户exec sp_grantdbaccess @Emp_name,@Emp_name --创建员工用户set @str='Grant select on Purchase to '+@Emp_name --授予查询进货表的权限语句exec(@str)set @str='Grant select,update on '+@view+' to '+@Emp_name--授予查询修改自己进货的信息的权限语句exec(@str)end
else print '没有该员工的进货信息！！'
end

exec   proc_stu_grant  '李斌'
select  *  from  李斌_v

小结：

(1)创建用户 CREATE USER ‘user_name’@‘host’ IDENTIFIED BY ‘password’；
user_name：要创建用户的名字。
host：表示要这个新创建的用户允许从哪台机登陆，如果只允许从本机登陆，则填‘localhost’,如果允许从远程登陆，则填‘%’
password：新创建用户的登陆数据库密码，如果没密码可以不写。 (2)删除用户 命令：DROP USER
‘user_name’@‘host’ (3)撤销用户权限： 命令：REVOKE privileges ON
database.tablename FROM ‘username’@‘host’

5 银行转账问题

CREATE TABLE bank   --创建账户表，存放用户的账户信息
(customerName CHAR(10),  --顾客姓名currentMoney MONEY     --当前余额
)
go
--添加约束：根据银行规定，账户余额不能少于1元，否则视为销户
ALTER TABLE bank  ADD CONSTRAINT CK_currentMoney   CHECK(currentMoney>=1)
go
--张红开户，开户金额为1000元；李明开户，开户金额1元
INSERT INTO bank(customerName,currentMoney)   VALUES('张红',1000)
INSERT INTO bank(customerName,currentMoney)   VALUES('李明',1)

写出用事务解决银行转账的存储过程：

go
create proc Bank_proc 
@send char(10),--付款人姓名
@receive char(10),--收款人姓名
@money money--转账金额
as
begin
set nocount on --不返回计数
begin try--捕捉异常开始
begin tran--事务开始
update bank set currentMoney=currentMoney-@moneywhere customerName=@send --更新转出账户的金额
update bank set currentMoney=currentMoney+@moneywhere customerName=@receive --更新转入账户的金额
print '成功转账'+str(@money)+'元。'
commit tran --正常提交事务
end try
begin catchprint'账户余额不足，转账失败！'rollback tran --回滚事务
end catch --捕捉异常
end

测试数据：
exec pro_bank ‘张红’ , ‘李明’，200

exec pro_bank ‘李明’,‘张红’ ,1000

小结：

有四个命令用于控制事务： （1）COMMIT：提交更改； （2）ROLLBACK：回滚更改；
（3）SAVEPOINT：在事务内部创建一系列可以 ROLLBACK 的还原点； （4）SET TRANSACTION：命名事务；
事务的保存： SAVE TRANSACTION 保存点名称 --自定义保存点的名称和位置 ROLLBACK TRANSACTION
保存点名称 --回滚到自定义的保存点

6 数据库字段的加密和解密

（1）创建读者表的空表名为 reader_bak,增加一字段 ：身份证号

select * into Reader_bak
from Reader 
where 1=2alter table Reader_bakadd 身份证号 varbinary(150)
select * from Reader_bak

（2）创建数据库主密钥

create master keyencryption by password='1128'

结果：

SELECT * FROM sys.symmetric_keys

（3）建立证书一，该证书使用数据库主密钥来加密

create certificate Cert_Demo
withsubject=N'cert encryption by database master key',start_date='2020-5-20',expiry_date='2020-6-20'

结果：

（4）向reader_bak 插入一条数据，借书证号：210 身份证：123456789123456789，并对身份证加密

insert Reader_bak(Read_no,身份证号)values('210',encryptbycert(cert_id(N'Cert_Demo'),N'350211199611020045'))

（5）将加密的身份证解密显示处理

select convert(nvarchar(50),decryptbycert(cert_id(N'Cert_Demo'),身份证号))
from Reader_bak

（6）总结对数据库字段加密和解密的理解

1. SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥) 
ENCRYPTBYASYMKEY() --非对称密钥
ENCRYPTBYCERT()   --证书加密
ENCRYPTBYKEY()   --对称密钥
ENCRYPTBYPASSPHRASE()  --通行短语（PassPhrase）加密
2.
--创建证书
CREATE CERTIFICATE CertTest 
with SUBJECT = 'Test Certificate'
GO
--创建非对称密钥
CREATE ASYMMETRIC KEY TestAsymmetricWITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'pa$$word'; 
GO
--创建对称密钥
CREATE SYMMETRIC KEY TestSymmetricWITH ALGORITHM = AES_256ENCRYPTION BY PASSWORD = 'pa$$word';
GO

7 给出一个用户表，创建表的代码如下

create    table   users(
Unumber INT IDENTITY(1,1),
Uname   varchar(20),
sex  char(2),
Upassword   char(10),
CONSTRANIT PK_Unum PRIMARY KEY(Unumber),
CONSTRAINT CK_sex  CHECK(sex='男'  OR sex='女')，
CONSTRAINT CK_Upassword CHECK (LEN(Upassword)>=6)
)

要求创建一个存储过程，实现插入用户表数据时，插入的密码自动转换成明文（用SQL的内置函数实现MD5加密），在存储过程中捕捉Check约束或触发器约束中抛出异常。这样可以比较方便的实现数据校验，并且减少存储过程中的多对多数据的判断）

go
create proc proc_insert_user
@name varchar(20),
@sex char(2),
@pw char(10)
as
beginif(len(@pw)<6)beginraiserror('输入的密码长度小于6，请重新输入！',16,1)returnend
declare @errorMessage nvarchar(100)
begin try
insert into users(Uname,sex,Upassword)
values(@name,@sex,substring(sys.fn_sqlvarbasetostr (HashBytes('MD5',@pw)),3,32))
end try
begin catch
--返回一个指向包含错误消息的字符串的指针；如果无错误指示，则返回NULL。
set @errorMessage=ERROR_MESSAGE()
--通过charindex 若能找到对应的字符串，则返回该字符串的位置，否则返回0
if(charindex('CK_sex',@errorMessage)!=0)
beginraiserror('性别输入错误，请重新输入！',16,1)
end
end catch
end

exec proc_insert_user ‘小张’,‘1’,‘123456’

exec proc_insert_user ‘小张’,‘男’,‘1’

exec proc_insert_user ‘小张’,‘男’,‘123456’

三、遇到的问题及解决方法

第三问中：

最后一个@dbname无法识别

猜测是因为变量@dbname 字符串有引号

局部变量的作用域是其被声明时所在批处理

四、实验总结

1 SQL的三层安全机制

SQL运行安全防线	只有用户登录成功，才能与SQL server 建立一次连接
SQL数据库的安全防线	每个数据库都有自己的用户和角色，该数据库只能由它的用户和角色访问，从而避免非法用户对数据库的访问
SQL数据库对象的安全防线	权限管理，合法用户必须在自己的权力范围内进行数据操作。

2 事务管理

事务控制语句

--#定义事务的开始
BEGIN  TRANSACTION  T1
--#提交事务，结束一个事务
COMMIT TRAN  
--# 回滚事务到事务的开头或一个保存点
ROLLBACK  TRAN 
--# 在事务中设置一个保存点，是一个事务内的部分操作回滚
SAVE TRAN  save_name  
--# @@ERROR     记录数据库最新错误的消息
--#@@TRANSCTION    初始值为0   每次BEGIN TRAN 则加1，commit 时减1，rollback则直接回到0  

封锁机制 sp_lock

事务日志

3 三层安全机制 存储过程总结

添加SQL server服务器登录帐户	exec sp_addlogin ‘wmy’ ,‘123456’ sp_droplogin
添加某数据库的访问用户	exec sp_grantdbaccess ‘wmy’,‘wmyDB’
删除某数据库的用户	exec sp_revokedbaccess ‘wmyDB’
对SQL server服务器拥有所有操作权sysadmin	exec sp_addsrvrolemember ‘wmy’ ,‘sysadmin’
取消某用户的对SQL server服务器拥有的所有操作权	exec sp_dropsrvrolemember ‘wmy’,‘sysadmin’
对某数据库有任意操作权限db_owner	exec sp_addrolemenber ‘db_owner’,‘wmyDB’
授予某数据库用户某种权力	GRANT SELECT ON TABLE1 TO wmyDB DENY INSER ,UPDATE ,DELETE ON TABLE1 TO wmyDB
数据库创建角色	exec sp_addrole ‘moniter_role’(角色名称)
为某用户赋予某角色	exec sp_addrolemember ‘monitor_role’,‘wmy’