2021-07-23

SQL SERVER学习过程中的笔记代码，每天更新（7.21）

平台：
SQL SERVER 2019 ,
Microsoft SQL Server Management Studio 18
教材与部分代码来源：数据库系统概论（第五版）

今天主要学习了数据库安全性控制与权限
代码如下：

--数据库安全性控制部分

--SQL标准对DAC自主存取控制提供支持，实现方式为SQL的GRANT语句和REVOKE语句
--用户权限由数据库对象和操作类型组成
--授权：定义存取权，使用GRANT语句向用户授予权限，使用REVOKE语句收回已经授予用户的权限
--常用存取权限：SELECT,INSERT,UPDATE,DELETE,REFERENCES,ALL PRIVILEGES(sql server中要使用ALL)，
--CREATE SCHEMA,CREATE TABLE,ALTER TABLE,CREATE VIEW,CREATE INDEX



--GRANT：将对指定操作对象的指定操作权限授予指定的用户。发出该GRANT授权的可以是数据库管理员，或者数据库对象创建者，或者是已经拥有该权限的用户
--接受该权限的可以是一个或者多个具体用户，也可以是PUBLIC全体用户
--若指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。否则用户只能使用该权限而不能传播该权限
/* 语法：
GRANT<权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
TO <用户>[,<用户>]...   (SQL SERVER不支持多个用户一起授权)
[WITH GRANT OPTION];
*/
--sql中禁止循环授权，即被授权者不能把权限再收回给授权者或者祖先


--SSMS中新建用户：安全性-登陆名-新建登录名
--遇到ERROR 4064（无法登录到新建的用户名）的解决方法：新建用户的时候把默认连接的数据库设置为系统的MASTER数据库等
--新建完用户后要手动设置用户所映射到的数据库

--先新建一个U1用户，密码123456
--注意sa是管理员用户，U1只是普通用户，几乎任何权限都没有
--切换到U1用户，这是在U1用户下新建一个查询U1.sql，这个查询就是属于U1的查询而不是属于用户sa的。查询是属于用户的，可以在用户权限下的数据库间切换来实现对各数据库查询
--在用户U1的查询中输入以下代码并执行
/*
SELECT* FROM student;
*/
--出现错误：对象名 'student' 无效。


--在管理员用户sa中的查询中（要进行用户间的切换）把查询STUDENT表的权限授予用户U1
--sql server的语法和书中的标准不一样，不需要标注对象类型
--在该操作中要提前将用户U1设置为与数据库db相映射
/*
GRANT SELECT
ON STUDENT
TO U1;
*/
--更改后在U1用户下，可以使用SELECT查询


--把对student表和course表的全部操作权限授予用户U1
--SSMS不支持给多个用户同时授权
/*
GRANT ALL PRIVILEGES
ON STUDENT
TO U1;
GRANT ALL PRIVILEGES
ON STUDENT
TO U1;
*/


--把对表SC的查询权授予全体用户PUBLIC
/*
GRANT SELECT
ON SC
TO PUBLIC;
*/




--将查询STUDENT表和修改学生学号姓名的权限授予用户U1
/*
GRANT SELECT,UPDATE(Sno,SNAME)
on Student
to U1;
*/



--把对表SC的INSERT权限授予U1用户，并允许U1将此权限再授予其他用户
/*
GRANT INSERT
ON SC
TO U1
WITH GRANT OPTION;
*/




--REVOKE 由管理员或者其他授权者收回权限
--语法：
/*
REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
FROM<用户>[,<用户>]...[CASCADE|RESTRICT  ];
*/


--收回所有用户对表SC的查询权限(对超级管理员无效)
/*
REVOKE SELECT
ON SC
FROM PUBLIC;
*/


--把用户U1对SC表的INSERT权限收回
/*
REVOKE INSERT
ON SC
FROM U1 CASCADE;			--把U1以及由U1授予的权限全部收回
*/





--创建用户:只有超级管理员sa才有权限创建用户
/*
CREATE USER<username>[WITH][DBA|RESOURCE|CONNECT];
*/
--CONNECT权限：用户不能创建新用户，不能创建模式，也不能创建基本表，只能登陆数据库。由sa或其他用户授予他权限.一般创建数据库默认为CONNECT权限
--RESOURCE权限：拥有RESOURCE权限的用户能创建基本表和视图，不能创建新用户和模式。可以把所创建对象的权限授予其他用户
--DBA权限：超级用户，可以创建新用户，创建模式，基表，视图。拥有对所有数据库对象的存取权限并授予一般用户。
--sql server实际使用的格式有区别，这里仅作学习参考
--https://www.cnblogs.com/caijiaming/p/9991216.html 有详细讲解sql server如何创建用户



--数据库角色:被命名的一组与数据库操作相关的权限，本质是权限的集合
--作用：可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权过程(一次给多个用户授权)
--首先用CREATE ROLE创建角色，然后用GRANT语句给角色授权，用REVOKE语句收回授予角色的权限

--创建角色
/*
CREATE ROLE <role_name>;
*/
--刚创建的角色是空的，需要用GRANT来为角色授权

--给角色授权
/*
GRANT <权限>[,<权限>]...
ON <对象类型>对象名
TO <角色>[,<角色>];
*/
--SQL SERVER 中不用写对象类型

--将角色授予其他的角色或者用户
/*
GRANT <角色1>[,<角色2>]...
TO <角色3>[,<用户1>]...
[WITH ADMIN OPTION];
*/
--如果指定了WITH ADMIN OPTION，则获得权限的角色或用户还可以把这种权限再授予其他角色

--收回角色权限
/*
REVOKE<权限>[,<权限>]
ON <对象类型><对象名>
FROM <角色>[,<角色>];
*/


--查看数据库角色，要再对应数据库（如db）的属性里查看，而不是服务器角色

--将一组权限授予U1
--先创建一个角色（及一个权限集合）R1
--CREATE ROLE R1;
--使用GRANT来给R1角色授权
/*
GRANT SELECT,UPDATE,INSERT
ON STUDENT
TO R1;
*/
--使用角色给U1授权
--SQL SERVER 不支持书中以下代码：
/*
GRANT R1
TO U1;
*/
--要使用：
/*
exec sp_addrolemember R1,U1;
*/
--或者
/*
ALTER ROLE R1
ADD member U1;
*/

--也可以一次性通过R1来收回U1的三个授权
--SQL SERVER不支持以下书中代码
/*
REVOKE R1 
FROM U1;
*/
--应该：
/*
exec sp_droprolemember R1,U1;
*/
--或者
/*
ALTER ROLE R1
DROP member U1;
*/



--在角色R1中增加权限
/*
GRANT DELETE
ON STUDENT
TO R1;
*/

--减少R1中的权限
/*
REVOKE DELETE
ON STUDENT
FROM R1;
*/



--视图权限机制,可以定义用户对视图的存取权限
--先创建一个视图
/*
GO
CREATE VIEW CS_STUDENT
AS
SELECT *
FROM student
WHERE Sdept = 'CS';
GO
--再给用户进行授权
GRANT SELECT
ON CS_STUDENT
TO U1;
*/

仍然是遇到了部分教材代码不受SQL SERVER支持的情况。