基础脚本

创建一个只能执行 SELECT 查询的只读用户，标准做法如下：

CREATE LOGIN onlyque WITH PASSWORD = '123456';
USE test;
GO
CREATE USER onlyque FOR LOGIN onlyque;
USE test;
GO
GRANT SELECT ON SCHEMA::dbo TO onlyque;

这个脚本能跑通，但在生产环境下存在几个风险。

生产环境强化版

-- ============================================
-- Create Read-Only Query User for test database
-- ============================================

-- Step 1: Create login at server level (with strong password)
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'onlyque')
BEGIN
    CREATE LOGIN onlyque 
        WITH PASSWORD = 'YourStr0ngP@ss2026'
             , DEFAULT_DATABASE = test
             , CHECK_POLICY = ON
             , CHECK_EXPIRATION = ON;
    PRINT 'Login [onlyque] created.';
END
ELSE
    PRINT 'Login [onlyque] already exists.';
GO

-- Step 2: Create database user
USE test;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'onlyque')
BEGIN
    CREATE USER onlyque 
        FOR LOGIN onlyque 
        WITH DEFAULT_SCHEMA = dbo;
    PRINT 'User [onlyque] created in [test].';
END
ELSE
    PRINT 'User [onlyque] already exists in [test].';
GO

-- Step 3: Grant read-only permissions
GRANT SELECT ON SCHEMA::dbo TO onlyque;

-- Step 4: Explicitly deny write operations (defense in depth)
DENY INSERT, UPDATE, DELETE ON SCHEMA::dbo TO onlyque;
GO

-- Step 5: Verify permissions
SELECT 
    dp.name AS principal_name,
    dp.type_desc,
    perm.permission_name,
    perm.state_desc,
    OBJECT_SCHEMA_NAME(perm.major_id) AS schema_name
FROM sys.database_principals dp
JOIN sys.database_permissions perm 
    ON dp.principal_id = perm.grantee_principal_id
WHERE dp.name = 'onlyque';
GO

关键改进点

1. 密码策略 — 拒绝弱口令

CHECK_POLICY = ON 强制使用 Windows 密码复杂度策略（大写 + 小写 + 数字 + 特殊字符，最少 8 位）。生产环境永远不要用 123456。

2. 幂等性（Idempotent）

用 IF NOT EXISTS 包裹所有 DDL 语句，脚本可以反复执行而不会报错。这在自动化部署（CI/CD / Ansible / Terraform）中是基本要求。

3. 两种只读授权方式对比

方式 命令 适用场景
Schema 级 GRANT SELECT ON SCHEMA::dbo TO onlyque 精细控制，只给 dbo schema
数据库角色 EXEC sp_addrolemember 'db_datareader', 'onlyque' 更标准，包含所有 schema

如果用户只需要查 dbo 下的表，用 Schema 级即可；如果后续可能查其他 schema，用 db_datareader 角色更省事。

4. 纵深防御（Defense in Depth）

DENY INSERT, UPDATE, DELETE 是显式拒绝 — 即使未来有人通过角色继承不小心给了写权限，DENY 优先级高于 GRANT，形成安全兜底。

延伸思考：生产环境还需要什么？

  • Connection String 规范： 应用层连接串应设置 ApplicationIntent=ReadOnly，配合 AlwaysOn Readable Secondary 实现读写分离
  • 审计（Audit）： 对 onlyque 用户的查询开启 SQL Server Audit 或 Extended Events，记录慢查询和敏感表访问
  • 定期轮换密码： 通过自动化脚本（PowerShell + ALTER LOGIN）每 90 天轮换一次

Professional English Terms

Term Meaning
Read-Only Query User 只读查询用户
Defense in Depth 纵深防御（安全架构原则）
Idempotent 幂等性（脚本可重复执行而不产生副作用）
Schema-Level Permission Schema 级权限控制
ApplicationIntent 连接串中的应用程序意图，用于读写分离路由