Skip to main content

SQL Server 2014 SP3 中为用户设置严格权限

要在 SQL Server 2014 SP3 中为用户 sqluser 设置严格权限，使其：

✅ 只能访问 SZHW 数据库中的 SZCIS 表
✅ 可以执行：SELECT（列出）、INSERT（添加）、UPDATE（编辑）
❌ 不能 DELETE（删除数据）
❌ 不能 ALTER（修改表结构）
❌ 不能访问其他任何表或对象

✅ 完整操作步骤（使用 T-SQL）

🔹 步骤 1：确保用户已存在（登录名 + 数据库用户）

-- 如果还没有登录名，先创建（在 master 数据库中）
USE master;
CREATE LOGIN [sqluser] WITH PASSWORD = 'YourStrongPassword123!';

-- 切换到目标数据库
USE [SZHW];

-- 创建数据库用户（映射登录名）
CREATE USER [sqluser] FOR LOGIN [sqluser];

⚠️ 如果用户已存在，跳过此步。

🔹 步骤 2：显式拒绝所有高危权限（安全兜底）

-- 拒绝对整个 dbo 架构的 ALTER 权限（防止改表结构）
DENY ALTER ON SCHEMA::dbo TO [sqluser];

-- 拒绝对整个 dbo 架构的 CONTROL 权限（防止获得完全控制）
DENY CONTROL ON SCHEMA::dbo TO [sqluser];

-- 拒绝对整个 dbo 架构的 DELETE 权限（防止删数据）
DENY DELETE ON SCHEMA::dbo TO [sqluser];

✅ 这些 DENY 是全局性限制，优先级高于后续的 GRANT。

🔹 步骤 3：仅授予对 SZCIS 表的必要权限

-- 只允许对 SZCIS 表进行 SELECT, INSERT, UPDATE
GRANT SELECT, INSERT, UPDATE ON OBJECT::dbo.SZCIS TO [sqluser];

💡 注意：这里只授权给特定表，而不是整个架构。

🔹 步骤 4：（可选）验证权限

测试允许的操作（用 sqluser 账号连接后执行）：

-- ✅ 应成功
SELECT * FROM dbo.SZCIS;
INSERT INTO dbo.SZCIS (Part_Number, Description) VALUES ('TEST001', 'Test');
UPDATE dbo.SZCIS SET Description = 'Updated' WHERE Part_Number = 'TEST001';

测试禁止的操作：

-- ❌ 应失败：权限被拒绝
DELETE FROM dbo.SZCIS WHERE Part_Number = 'TEST001';

-- ❌ 应失败：无法修改表结构
ALTER TABLE dbo.SZCIS ADD NewColumn NVARCHAR(50);

-- ❌ 应失败：无法访问其他表
SELECT * FROM dbo.OtherTable;  -- 假设存在其他表

🔒 权限设计原理说明

操作 如何实现限制
不能删数据 DENY DELETE ON SCHEMA::dbo
不能改表结构 DENY ALTER ON SCHEMA::dbo
不能访问其他表 不授予任何其他表的权限（默认无权限）
只能操作 SZCIS GRANT ... ON OBJECT::dbo.SZCIS（精确到表）

✅ SQL Server 默认遵循 “最小权限原则”：未显式授予权限 = 无权限。

🛡️ 额外安全建议

1. 不要将用户加入任何角色

  • ❌ 不要加入 db_datareader、db_datawriter 等内置角色
  • 这些角色会自动授予整个数据库的读写权限，违背你的需求

2. 定期审计权限

-- 查看用户权限
SELECT 
    p.permission_name,
    p.state_desc,
    o.name AS object_name
FROM sys.database_permissions p
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE grantee_principal_id = USER_ID('sqluser');

3. 使用专用账号

  • 为不同应用/用户创建独立账号，避免共用 sa 或通用账号

📌 最终完整脚本（一键复制）

-- 1. 创建登录名（如不存在）
USE master;
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'sqluser')
    CREATE LOGIN [sqluser] WITH PASSWORD = 'YourStrongPassword123!';

-- 2. 在 SZHW 数据库中创建用户
USE [SZHW];
CREATE USER [sqluser] FOR LOGIN [sqluser];

-- 3. 拒绝高危权限（全局限制）
DENY ALTER ON SCHEMA::dbo TO [sqluser];
DENY CONTROL ON SCHEMA::dbo TO [sqluser];
DENY DELETE ON SCHEMA::dbo TO [sqluser];

-- 4. 仅授权 SZCIS 表的必要操作
GRANT SELECT, INSERT, UPDATE ON OBJECT::dbo.SZCIS TO [sqluser];

✅ 执行后，sqluser 将严格受限于你的要求。

❓常见问题

Q：能否限制只能修改某些字段？
A：SQL Server 不支持列级 UPDATE 权限。
✅ 替代方案：创建一个 视图（View），只包含可编辑字段，并在视图上授权。

Q：用户能执行存储过程吗？
A：默认不能。如需调用，需单独授权：

GRANT EXECUTE ON dbo.YourProc TO [sqluser];

完成以上配置后，你的 sqluser 账号将安全地满足业务需求！如有其他场景，欢迎继续提问。

No Comments
Back to top