sql server学习记录

--为了作业的进行，先创建登录账号u1,u2,u3,u4,u5,u6,u7,王平，张明，赵玲
create login u1 with password='123456',default_database=xuankexitong
create login u2 with password='123456',default_database=xuankexitong;
create login u3 with password='123456',default_database=xuankexitong;
create login u4 with password='123456',default_database=xuankexitong;
create login u5 with password='123456',default_database=xuankexitong;
create login u6 with password='123456',default_database=xuankexitong;
create login u7 with password='123456',default_database=xuankexitong;
create login 王平 with password='123456',default_database=xuankexitong;
create login 张明 with password='123456',default_database=xuankexitong;
create login 赵玲 with password='123456',default_database=xuankexitong;
--创建用户u1,u2,u3,u4,u5,u6,u7,王平，张明，赵玲
create user u1 for login u1 with default_schema=[jiagou];
create user u2 for login u2 with default_schema=[jiagou];
create user u3 for login u3 with default_schema=[jiagou];
create user u4 for login u4 with default_schema=[jiagou];
create user u5 for login u5 with default_schema=[jiagou];
create user u6 for login u6 with default_schema=[jiagou];
create user u7 for login u7 with default_schema=[jiagou];
create user 王平 for login 王平 with default_schema=[jiagou];
create user 张明 for login 张明 with default_schema=[jiagou];
create user 赵玲 for login 赵玲 with default_schema=[jiagou];
--要对xuankexitong数据库下的表授权给其它用户，只有在该数据库下才有效
use xuankexitong;
--4.1查询student表授权给用户u1
grant select on [jiagou].student to u1;

--4.2把对student表和course表的全部操作权限授予用户u2和u3(而实际SQL SERVER中却不可以对多个表权限赋给多个用户)
grant all privileges on object :: [jiagou].student to u2,u3;
grant all privileges on object :: [jiagou].course to u2,u3;

--4.3把对表sc的查询权限授予所有用户
grant select on object :: [jiagou].sc to public;

--4.4把查询student表和修改学生学号的权限给用户u4
grant select,update(sno) on object ::[jiagou].student to u4;

--4.5把对表sc的insert权限授权给u5用户，并允许将此权限再授予给其它用户
grant insert on object :: [jiagou].sc to u5 with grant option; 

--4.6 u5用户将自己的权限传给u6用户 (在账号u5中操作)
grant insert on object :: [jiagou].sc to u6 with grant option;

--4.7 u6将此权限授予u7 (在账号u6中操作)
grant insert on object :: [jiagou].sc to u7;

--4.8 把用户u4修改学生学号的权限收回
revoke update(sno) on object :: [jiagou].student to u4;

--4.9收回所有用户对表sc的查询权限
revoke select on [jiagou].sc to public;

--4.10把用户u5对sc表的插入权限收回(因为u5拥有可以向其它用户授予这项权限，所以如果要收回，就要用来连带关键字cascade)
revoke insert on [jiagou].sc to u5 cascade;

--4.11通过角色来实现将一组权限授予一个用户  

  --创建一个角色R1(role)
  create role R1;

  --使角色R1拥有student表的select,update，insert权限
  grant select,update,insert on [jiagou].student to R1;

  --将角色授予王平，张明，赵玲 (不能同时给多个用户贴标签) add member -添加成员
  alter role R1 add member 王平;
  alter role R1 add member 张明;
  alter role R1 add member 赵玲;

  --也可以一次性通过角色R1收回它们的权限
  alter role R1 drop member 王平;
  alter role R1 drop member 张明;
  alter role R1 drop member 赵玲;

--4.12角色的权限修改 (在原来权限的基础之上增加了delete权限)
grant delete on [jiagou].student to R1;

--4.13使R1减少select的权限
revoke select on [jiagou].student to R1;

--4.14建立计算机系学生的视图，把对该视图的select权限授予王平，把该视图上的所有操作权限授予张明
 --创建计算机系学生的视图
 create view cs_student as select *from [jiagou].student where sdept='cs'; 

 --把查询权限授予王平
 grant select on object :: cs_student to 王平;

 /*
  ALL权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了ALL权限。
  无法对 sa、dbo、实体所有者、information_schema、sys 或您自己授予、拒绝或撤消权限
 */
 --把该视图上的所有操作全授予张明
 grant all privileges on cs_student to 张明;

 /*
 1.创建Server Audit   创建审计必须在master数据库中进行 否则报错
 2.启用SQL Server审计
 3.移动到目标数据库,创建服务器审计规范
 4.启用审计规范
 5.查看审计数据
 */
--4.15对修改sc表结构或修改sc表数据的操作进行审计
 --创建server audit
 USE master;
 create server audit ServerAudit to file (filepath = 'E:\bat\audit\', maxsize = 50MB,max_files=10,reserve_disk_space=ON)  
 with(queue_delay=1000,on_failure=continue);

 --激活ServerAudit
 alter server audit ServerAudit with (state=on);

 --移动到目标数据库,创建服务器审计规范
  use xuankexitong;
  create database audit SPECIFICATION SC_notStandard
  for server audit ServerAudit
  add(DATABASE_OBJECT_CHANGE_GROUP),--任何用户对于SC表的表结构修改都被审计
  add(select,insert,delete,update on [jiagou].SC by public);

  --启用审计规范
   alter database audit SPECIFICATION SC_notStandard with (state=on);
     
  --修改表结构和查看表数据
  select *from [jiagou].sc where cno=1;
  select*from [jiagou].sc;
  alter table [jiagou].sc add newcolumn varchar(20);
  alter table [jiagou].sc drop column newcolumn;

   --查看审计数据(从结果可以得到：谁在什么时候做了什么)
  select event_time,
  succeeded,
  server_principal_name,
  [object_name],
  [statement]
  from sys.fn_get_audit_file('E:\bat\audit\*', DEFAULT, DEFAULT);

--4.16取消对sc表的一切审计
  --1.关闭审计
  alter database audit SPECIFICATION SC_notStandard with (state=off);
 
  --2.删除审计
  drop database audit SPECIFICATION [SC_notStandard] -- Audit Name为要删除审计规则的名称