写在前面：这个是我自己学习数据库系统概论写的笔记，大部分都是学习的时候有不懂的用AI问答搞明白之后再整理总结出来的知识点，供以后复习以及需要的小伙伴学习。如果有错的还请大家指正，祝大家都能学习进步！
注：1.根据实际需求自定义的内容有用户名、密码、表名、数据库名等即可。2.文章的代码是SQL Sever的代码
1. 用户身份鉴别
1.定义：
身份鉴别就是确认你是不是你自己。比如，进自己家的时候，得用钥匙或者指纹才能打开门，这就是验证身份的过程。在数据库里，系统要确认你输入的用户名和密码是不是正确，才能让你进入数据库。
2.常见的身份验证方法：
- 用户名/密码验证：最常见的方式，输入用户名和密码，数据库确认是否正确。
- 双因素认证（2FA）：除了密码，还需要用手机收到的验证码或一个动态令牌来验证身份。
- 生物识别：像指纹、面部识别等。
- 智能卡：例如银行卡、门禁卡等。
3.举例：
3.1在 SQL Server 中创建用户身份：
这段代码就是在 SQL Server 中创建一个用户，让它能够通过用户名和密码登录数据库，并授予它读取和写入数据的权限。
-- 创建登录名
CREATE LOGIN TestUser WITH PASSWORD = 'TestPassword123';
-- 创建用户并分配给数据库
USE YourDatabase;
CREATE USER TestUser FOR LOGIN TestUser;
-- 给用户授予读取和写入权限
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;3.2代码解析（方便以后复习看）：
3.2.1创建登录名：
-- 创建登录名
CREATE LOGIN TestUser WITH PASSWORD = 'TestPassword123';
-
固定格式部分：
CREATE LOGIN：这是固定的 SQL 命令，表示你要创建一个登录名。WITH PASSWORD = 'TestPassword123'：这是你给新用户设置的密码。
- 自命名的变量：
TestUser：这是你创建的登录名，你可以修改为任何你希望的用户名。'TestPassword123'：这是用户的密码，你可以设置成任何密码。
3.2.2创建用户并给其分配数据库：
-- 创建一个用户并关联到 SQL 登录名
USE YourDatabase; -- 切换到你要操作的数据库
CREATE USER TestUser FOR LOGIN TestUser;
-
固定格式部分：
USE YourDatabase;：USE命令用于切换到指定的数据库。在这个例子中，YourDatabase是你操作的数据库名称。CREATE USER：创建一个数据库用户，绑定到之前创建的登录名。
-
自命名的变量：
YourDatabase：这个是你自己的数据库名称，你需要替换成实际的数据库名。TestUser：你创建的用户的名称（通常和登录名相同）。如果你有多个用户，可以用不同的名字。
3.2.3给用户授予读取和写入权限 ：
-- 给用户 TestUser 授予读取数据的权限
ALTER ROLE db_datareader ADD MEMBER TestUser;
-- 给用户 TestUser 授予写入数据的权限
ALTER ROLE db_datawriter ADD MEMBER TestUser;
-
固定格式部分：
ALTER ROLE：修改角色的成员，表示将某个用户添加到角色中。db_datareader：这是一个内置角色，赋予用户读取数据的权限。db_datawriter：这是另一个内置角色，赋予用户写入数据的权限。
-
自命名的变量：
TestUser：你要授权的用户，可以根据需求替换为你实际创建的用户名。db_datareader和db_datawriter：这两个是 SQL Server 提供的内置角色。你不需要修改它们，但你可以根据需求创建自己的自定义角色（如data_viewer、admin）。
2. 存取控制
1.定义：
存取控制是指验证了用户身份之后，决定该用户能访问哪些资源。打个比方，你进了自己家，门打开了，但是你能进哪个房间（书房、卧室、厨房）得看你是不是有权限。
2.常见的存取控制方式：
- 自主存取控制（DAC）：用户自己决定谁能访问自己的资源。比如，你可以决定把某个文件共享给别人。
- 强制存取控制（MAC）：系统严格规定谁能访问什么数据，用户不能随便更改这些权限。
- 基于角色的存取控制（RBAC）：根据你在公司里的角色来决定你能做什么，比如经理能做的事情和普通员工是不一样的。
3.在 SQL Server 中，使用角色来控制权限：
- SQL Server 有一些内置角色，比如：
db_datareader：可以读取数据库中的所有数据。db_datawriter：可以修改数据库中的数据。db_owner：完全控制数据库的所有权限。
你可以根据用户的角色来决定他们能做什么。假设我们要创建一个名为 db_manager 的角色，并为这个角色授予一些管理数据库表格的权限（例如：允许该角色管理整个数据库模式 dbo 下的表格）。同时，将用户 TestUser 添加到这个角色中，这样 TestUser 就可以执行相应的操作了。这段代码会让 TestUser 用户拥有读取和写入数据库的权限。
-- 1. 创建一个自定义角色 db_manager
CREATE ROLE db_manager; -- 角色名可以修改为其他名称，例如 admin_role
-- 2. 将用户 TestUser 添加到 db_manager 角色
ALTER ROLE db_manager ADD MEMBER TestUser; -- 用户名可以修改为其他用户名，例如 john_doe
-- 3. 授予 db_manager 角色 ALTER 权限（可以修改 dbo 模式下的表格）
GRANT ALTER ON SCHEMA::dbo TO db_manager; -- 你可以修改 dbo 为其他模式名称，如 sales、hr
3.1.创建自定义角色
-- 创建一个新的角色
db_manager CREATE ROLE db_manager;-
固定格式部分：
CREATE ROLE db_manager;：CREATE ROLE是 SQL Server 的固定命令，用来创建一个新的角色。db_manager是你自定义的角色名，可以根据需要修改。
-
自命名的变量：
db_manager：这是角色的名称，你可以自定义为admin_role、data_analyst_role等，根据不同的应用场景修改。
3.2.将用户添加到角色中
-- 给用户 TestUser 添加一个 db_manager 角色
ALTER ROLE db_manager ADD MEMBER TestUser;-
固定格式部分：
ALTER ROLE db_manager ADD MEMBER TestUser;：ALTER ROLE是 SQL Server 的固定命令，用来修改角色。ADD MEMBER表示将用户添加到角色中。
-
自命名的变量：
db_manager：你要修改的角色名称，可以是任意你创建的角色名称。TestUser：你要添加到角色中的用户名，必须是数据库中已存在的用户。如果你要添加不同的用户，只需修改为相应的用户名。
3. 3给角色授予权限（管理表格权限）
-- 给 db_manager 角色授予 ALTER 权限（可以修改数据库表格）
GRANT ALTER ON SCHEMA::dbo TO db_manager;-
固定格式部分：
GRANT ALTER ON SCHEMA::dbo TO db_manager;：GRANT是 SQL Server 的固定命令，用来授予权限。ALTER是权限类型，表示允许修改表格或其他数据库对象。SCHEMA::dbo表示你授予权限的数据库模式（schema）是dbo，这是数据库默认的模式。TO db_manager表示将这些权限授予db_manager角色。
-
自命名的变量：
ALTER：这是你授予角色的权限类型，可以是其他权限类型，如SELECT、INSERT、DELETE、EXECUTE等，具体取决于你希望角色能够执行哪些操作。SCHEMA::dbo：dbo是默认的数据库模式名，你可以修改为其他模式（例如：sales、hr等）。db_manager：这是你要授予权限的角色名。
4.扩展：多个角色和更复杂的权限控制
假设你要创建多个角色并分别授予不同的权限，以下是一个更加完整的示例：
-- 1. 创建自定义角色：管理员角色 db_admin 和普通用户角色 db_user
CREATE ROLE db_admin;
CREATE ROLE db_user;
-- 2. 将用户添加到相应的角色中
ALTER ROLE db_admin ADD MEMBER AdminUser;
ALTER ROLE db_user ADD MEMBER NormalUser;
-- 3. 授予 db_admin 角色完全控制权限（db_owner）
GRANT CONTROL ON SCHEMA::dbo TO db_admin;
-- 4. 授予 db_user 角色只读权限（db_datareader）
GRANT SELECT ON SCHEMA::dbo TO db_user;db_admin角色被授予CONTROL权限，这意味着它可以执行所有操作（完全控制）。db_user角色只被授予SELECT权限，表示只能读取数据，不能修改或删除数据。
5.总结
在 SQL Server 中：
- 固定格式的部分：
CREATE ROLE、ALTER ROLE、GRANT、SCHEMA::dbo等是 SQL 的固定命令，你需要按照这些格式编写。 - 自命名的变量：角色名、用户名、权限类型和模式名称等是可以自定义的。你可以根据实际需求命名角色、用户，并设置合适的权限。
3. 存取控制模型：DAC、MAC、RBAC
1. DAC（自主存取控制） — 自由控制自己的数据资源
1.定义：
DAC 允许用户完全控制自己拥有的数据或资源。在数据库中，这意味着用户对自己创建的对象（如表格、视图、存储过程等）有完全的访问控制权。用户可以决定谁能查看、修改、删除这些数据。
2.扩展：
- 在 DAC 模型下，数据的所有者有权控制谁能够访问他们的数据。
- 数据的所有者可以通过权限授予（GRANT）和权限撤回（REVOKE）来管理其他用户对自己数据的访问。
3.代码示例：
假设你有一个表格 Employees，并希望将读取和修改权限授予某个用户 TestUser。
-- 假设你已经创建了 Employees 表
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name NVARCHAR(100), Salary DECIMAL );
-- 用户创建表格后可以授予其他用户访问权限
-- 授予 TestUser 读取权限
GRANT SELECT ON Employees TO TestUser;
-- 授予 TestUser 修改权限
GRANT UPDATE, DELETE ON Employees TO TestUser;
-- 如果要撤回权限，可以使用
REVOKE REVOKE UPDATE, DELETE ON Employees FROM TestUser;-
固定格式部分：
GRANT SELECT：授予用户读取权限，SELECT是固定的 SQL 权限类型。GRANT UPDATE, DELETE：授予用户修改和删除权限。REVOKE：撤回之前授予的权限。
-
自命名的变量：
Employees：这是你创建的表格名称，可以更改为任何你创建的表格名。TestUser：用户的名称，你可以将其更改为实际的用户名。SELECT、UPDATE、DELETE：这些是 SQL Server 中的权限类型，可以更改为其他权限，如INSERT、EXECUTE等。
4.权限控制：
- DAC 允许用户决定权限的传递，也就是你可以允许某个用户将自己的权限授予其他用户，或者撤回已授予的权限。
例如：
-- 授予 TestUser 将权限传递给其他用户的能力
GRANT SELECT ON Employees TO TestUser WITH GRANT OPTION;
-- 用户 TestUser 可以将自己的 SELECT 权限授予其他用户
GRANT SELECT ON Employees TO AnotherUser;WITH GRANT OPTION允许用户将自己获得的权限进一步传递给其他用户。
2. MAC（强制存取控制） — 系统强制控制权限
1.定义：
在 MAC 模型中，用户不能自主决定谁能访问他们的资源。权限的授予和访问是由管理员或系统通过安全标签来强制执行的。数据库中的权限是基于用户的安全级别或敏感性标签来控制的。
2.扩展：
- MAC 通常用于需要高安全性的环境，如军事、政府部门等，在这些环境中，系统会根据用户的角色和安全级别来决定访问权限。
- 在 SQL Server 中，虽然没有直接的 MAC 模型实现，但可以通过使用安全级别来模拟。
3.代码示例：
你可以通过创建角色并控制角色对数据库模式的访问来模拟 MAC：
-- 创建一个角色 db_admin（管理员）
CREATE ROLE db_admin;
-- 创建一个普通角色 db_user
CREATE ROLE db_user;
-- 创建一个用户，并分配到 db_admin 角色
CREATE USER AdminUser FOR LOGIN AdminUserLogin;
ALTER ROLE db_admin ADD MEMBER AdminUser;
-- 创建普通用户，并分配到 db_user 角色
CREATE USER NormalUser FOR LOGIN NormalUserLogin;
ALTER ROLE db_user ADD MEMBER NormalUser;
-- 管理员（db_admin）角色可以访问所有表格，普通用户（db_user）只能读取数据
GRANT CONTROL ON SCHEMA::dbo TO db_admin; -- 管理员可以控制整个 dbo 模式
GRANT SELECT ON SCHEMA::dbo TO db_user; -- 普通用户只能读取 dbo 模式中的数据-
固定格式部分：
CREATE ROLE db_admin、CREATE ROLE db_user：创建数据库角色。GRANT CONTROL：授予 db_admin 角色对整个数据库模式的完全控制权限。GRANT SELECT：授予 db_user 角色只读权限。
-
自命名的变量：
db_admin、db_user：你创建的角色名，表示管理员和普通用户。AdminUser、NormalUser：你创建的数据库用户，可以根据需求进行更改。SCHEMA::dbo：指定数据库模式，dbo是数据库的默认模式，你可以选择其他模式。
3. RBAC（基于角色的存取控制） — 根据用户角色授予权限
1.定义：
RBAC 模型通过用户的角色来控制权限。在这种模型中，用户的权限不是直接授予的，而是通过分配到一个角色来间接获得权限。角色通常是根据用户的职能、职位或责任来定义的。
2.扩展：
- SQL Server 使用角色来管理权限。例如，
db_datareader角色可以让用户读取数据库中的所有数据，db_datawriter角色可以让用户写入数据。 - 角色的管理是集中式的，管理员只需要管理角色权限，而不需要为每个用户单独设置权限。
3.代码示例：
-- 创建一个角色 db_manager（管理员角色）
CREATE ROLE db_manager;
-- 将用户 AdminUser 添加到 db_manager 角色
ALTER ROLE db_manager ADD MEMBER AdminUser;
-- 创建另一个角色 db_employee（普通员工角色）
CREATE ROLE db_employee;
-- 将用户 EmployeeUser 添加到 db_employee 角色
ALTER ROLE db_employee ADD MEMBER EmployeeUser;
-- db_manager 角色有完全控制权限
GRANT CONTROL ON SCHEMA::dbo TO db_manager;
-- db_employee 角色只能查询数据
GRANT SELECT ON SCHEMA::dbo TO db_employee;-
固定格式部分：
CREATE ROLE db_manager、CREATE ROLE db_employee：创建自定义角色。GRANT CONTROL：授予管理员角色完全的控制权限。GRANT SELECT：授予普通员工角色读取数据的权限。
-
自命名的变量：
db_manager、db_employee：你创建的角色名称，可以根据实际情况命名。AdminUser、EmployeeUser：实际的数据库用户名称。SCHEMA::dbo：指定数据库模式名，通常是dbo，但你可以根据需要更改。
4.关于权限赋予（GRANT）、权限收回（REVOKE）、权限传递（WITH GRANT OPTION）
1.权限赋予、收回及是否允许用户将权限赋给他人的功能，就是在 DAC 和 RBAC 中控制权限时的关键点。以下是如何进行这些操作的 SQL 代码：
1.1权限赋予（GRANT）
-- 将 SELECT 权限授予用户 TestUser
GRANT SELECT ON dbo.Employees TO TestUser;
-
固定格式部分：
GRANT：这是 SQL Server 中用来授予权限的关键字，固定格式，不可以修改。SELECT：这是权限类型，表示授予用户对某个对象（如表、视图等）的查询权限。权限类型可以是SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）等。ON：这是 SQL Server 中的固定格式，表示对哪个对象（如表、视图等）授予权限。TO：用来指定权限授予给谁，后面跟着用户名或角色名。
-
自定义变量：
dbo.Employees：这是你要授予权限的数据库对象。在这个示例中，dbo.Employees是表名，dbo是表所在的数据库模式（schema）。你可以根据需要更改为你实际的表名或视图名。TestUser：这是你授予权限的用户或角色名。你可以将其替换为数据库中实际的用户名或角色名。
1.2权限收回（REVOKE）
-- 撤销 TestUser 对 Employees 表的 SELECT 权限
REVOKE SELECT ON dbo.Employees FROM TestUser;-
固定格式部分：
REVOKE：SQL Server 中用来撤销权限的固定关键字。SELECT：权限类型，表示你要撤销的权限类型。可以是SELECT、INSERT、UPDATE等。ON：表示要撤销权限的对象。FROM：表示从哪个用户或角色撤回权限。
-
自定义变量：
dbo.Employees：指定要撤销权限的数据库对象。在此例中是Employees表，你可以根据实际情况修改为其他表或视图名。TestUser：指定撤回权限的目标用户或角色名。可以修改为实际的用户或角色。
1.3权限传递（WITH GRANT OPTION）
-- 授予 TestUser 权限，并允许他将权限传递给其他用户
GRANT SELECT ON dbo.Employees TO TestUser WITH GRANT OPTION;-
固定格式部分：
GRANT：固定关键字，用来授予权限。SELECT：权限类型，表示授予查询数据的权限。你可以根据需求选择其他权限类型，如INSERT、UPDATE等。ON：固定关键字，用来指定授予权限的对象。TO：固定关键字，用来指定用户或角色。WITH GRANT OPTION：这个选项是用来允许用户将自己获得的权限进一步授予给其他用户。WITH GRANT OPTION是固定格式，不能修改。
-
自定义变量：
dbo.Employees：这是你授予权限的数据库对象（表、视图等），你可以修改为实际的对象名。TestUser：这是你要授予权限的用户或角色名。可以根据实际情况修改为你要赋予权限的目标用户。
1.4收回传递权限
-- 收回 TestUser 将权限传递给其他用户的能力
REVOKE GRANT OPTION FOR SELECT ON dbo.Employees FROM TestUser;-
固定格式部分：
REVOKE：固定命令，用来撤销之前授予的权限或权限的传递能力。GRANT OPTION：固定关键字，表示撤销用户将其权限传递给其他用户的能力。FOR：固定格式，表示撤销哪个权限的传递能力。SELECT：权限类型，表示收回对SELECT权限的传递能力。可以是其他权限类型。ON：表示哪个对象的权限被撤销。FROM：表示撤销权限传递的用户或角色。
-
自定义变量：
dbo.Employees：这是你要收回权限的数据库对象，通常是表或视图名。TestUser：这是你收回权限传递能力的用户或角色。可以更改为任何目标用户。
2.总结：
2.1存取控制模型：DAC、MAC、RBAC：
- DAC：用户可以控制自己拥有的资源，决定谁可以访问自己的数据。
- MAC：系统或管理员控制访问权限，用户无法自己决定谁能访问数据。
- RBAC：用户的权限由角色决定，角色通常基于职位或职责来分配。
2.2权限赋予和收回：
你可以通过 GRANT 和 REVOKE 来管理用户的权限。如果你希望用户能将权限传递给他人，可以使用 WITH GRANT OPTION，反之，通过 REVOKE GRANT OPTION 来撤回权限传递能力。
5. 安全审计和入侵检测*
1.入侵检测定义：
入侵检测是指监控并记录数据库中的访问活动，防止非法访问。SQL Server 提供了审计功能，允许你跟踪哪些用户在什么时间做了哪些操作。
2.启用 SQL Server 审计功能：
这样，SQL Server 会记录用户登录、失败登录等事件，管理员可以查看这些日志，确保没有不正常的访问。
-- 创建一个审计
CREATE SERVER AUDIT Audit_TestUser
TO FILE (FILEPATH = 'C:\AuditLogs\') -- 审计日志保存的路径
WITH (ON_FAILURE = CONTINUE); -- 如果审计失败继续运行
-- 创建审计规格（定义要审计的事件）
CREATE SERVER AUDIT SPECIFICATION AuditSpec_TestUser
FOR SERVER AUDIT Audit_TestUser
ADD (FAILED_LOGIN_GROUP), -- 审计失败的登录事件
ADD (SUCCESSFUL_LOGIN_GROUP) -- 审计成功的登录事件
WITH (STATE = ON); -- 启动审计-
固定格式部分：
CREATE SERVER AUDIT：创建一个审计任务，这个任务将记录特定的事件（如用户登录、失败的登录尝试等）。TO FILE (FILEPATH = 'C:\AuditLogs\')：指定日志文件存储的路径，通常是你机器上一个合适的文件夹。CREATE SERVER AUDIT SPECIFICATION：定义哪些事件需要被记录到审计日志中。WITH (STATE = ON)：启用审计功能。
-
自命名的变量：
Audit_TestUser：这是你给审计任务起的名字。你可以用任何名字，但通常会根据审计目标命名，例如Audit_LoginEvents。'C:\AuditLogs\'：这是审计日志文件保存的路径，应该替换成你希望存储日志的路径。FAILED_LOGIN_GROUP和SUCCESSFUL_LOGIN_GROUP：这是审计日志的事件类型。你可以根据需求选择更多的事件进行审计。
6.总结：
1.用户身份鉴别：
在 SQL Server 中，你可以通过用户名和密码验证用户身份，也可以通过 Windows 身份验证来确认用户。
2.存取控制：
确保用户在登录后只能访问他们有权限的数据。SQL Server 提供了基于角色的存取控制（RBAC），以及自主存取控制（DAC）和强制存取控制（MAC）的模拟。
3.审计与安全：
通过审计功能，可以追踪和记录数据库中的操作，帮助管理员监控和防范安全问题。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
