SQL Server角色权限配置：实现学生仅查询个人成绩需求

没问题，我来给你梳理一下在SQL Server里实现「每个学生只能查询自己成绩」的具体步骤，这个需求咱们可以通过自定义角色、权限控制加上**行级安全（RLS）**来完美解决，一步步来：

首先每个学生需要对应一个SQL Server登录账号，以及数据库内的用户（如果还没创建的话）。你可以手动创建，也可以批量生成脚本，示例如下：

-- 替换成你的数据库名称
USE YourDatabaseName;
GO

-- 示例：为学生张三创建登录和数据库用户
CREATE LOGIN student_zhangsan WITH PASSWORD = 'StrongPass_123!';
CREATE USER student_zhangsan FOR LOGIN student_zhangsan;

-- 如果有多个学生，批量创建的话可以用这个脚本（从Students表读取学生信息）
DECLARE @batchSql NVARCHAR(MAX) = '';
SELECT @batchSql += 
    'CREATE LOGIN student_' + name_student + ' WITH PASSWORD = ''InitialPass_456!''; ' +
    'CREATE USER student_' + name_student + ' FOR LOGIN student_' + name_student + '; ' +
    'ALTER ROLE StudentRole ADD MEMBER student_' + name_student + ';' + CHAR(10)
FROM dbo.Students;

EXEC sp_executesql @batchSql;

为了方便批量管理所有学生的权限，咱们创建一个专门的StudentRole角色，后续所有学生用户都加入这个角色：

CREATE ROLE StudentRole;
GO

给StudentRole授予两张表的SELECT权限（注意Study results表名有空格，要用方括号包裹）：

GRANT SELECT ON dbo.Students TO StudentRole;
GRANT SELECT ON dbo.[Study results] TO StudentRole;
GO

这一步是关键，通过行级安全策略，让每个学生只能看到自己的记录。首先需要创建一个安全函数，用来判断当前登录用户是否有权限访问某一行数据：

CREATE FUNCTION dbo.fn_StudentAccessCheck(@studentId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS IsAllowed
WHERE EXISTS (
    -- 这里的逻辑要匹配你的用户命名规则，比如用户名是student_xxx，对应Students表的name_student=xxx
    SELECT 1 FROM dbo.Students
    WHERE id_student = @studentId
    AND name_student = SUBSTRING(SUSER_SNAME(), 9, LEN(SUSER_SNAME()))
    -- 如果你的用户名是用ID命名的（比如student_1001），可以改成下面的逻辑：
    -- AND id_student = CAST(SUBSTRING(SUSER_SNAME(), 9, LEN(SUSER_SNAME())) AS INT)
);
GO

-- 给学生角色授予这个函数的执行权限
GRANT EXECUTE ON dbo.fn_StudentAccessCheck TO StudentRole;
GO

接下来为两张表分别创建安全策略：

• 限制Students表只能查看自己的信息：

CREATE SECURITY POLICY dbo.StudentDataPolicy
ADD FILTER PREDICATE dbo.fn_StudentAccessCheck(id_student)
ON dbo.Students
WITH (STATE = ON);
GO

• 限制Study results表只能查看自己的成绩：

CREATE SECURITY POLICY dbo.StudyResultsPolicy
ADD FILTER PREDICATE dbo.fn_StudentAccessCheck(id_student)
ON dbo.[Study results]
WITH (STATE = ON);
GO

用任意学生账号登录SQL Server，执行以下查询测试：

-- 只能看到自己的学生信息
SELECT * FROM dbo.Students;

-- 只能看到自己的成绩记录
SELECT * FROM dbo.[Study results];

• 如果不想让管理员账号受行级安全限制，可以在安全函数里加个判断：OR IS_ROLEMEMBER('db_owner') = 1，这样管理员能查看所有数据。
• 初始密码可以设置得更安全，或者让学生登录后强制修改密码：ALTER LOGIN student_zhangsan WITH CHECK_POLICY = ON; ALTER LOGIN student_zhangsan MUST_CHANGE;

内容的提问来源于stack exchange，提问作者Aymane HATAFI