SQL Server用户权限管理 #

SQL Server安全层次：
├── Windows级别
│   └── Windows用户/组
├── SQL Server级别
│   ├── 登录名（Login）
│   └── 服务器角色
└── 数据库级别
    ├── 用户（User）
    ├── 数据库角色
    └── 架构（Schema）

SQL Server权限类型：
├── 服务器权限
│   ├── CREATE DATABASE
│   ├── ALTER ANY DATABASE
│   └── SHUTDOWN
├── 数据库权限
│   ├── CREATE TABLE
│   ├── CREATE VIEW
│   └── BACKUP DATABASE
└── 对象权限
    ├── SELECT
    ├── INSERT
    ├── UPDATE
    └── DELETE

-- Windows身份验证登录名
CREATE LOGIN [DOMAIN\user] FROM WINDOWS;

-- SQL Server身份验证登录名
CREATE LOGIN user1 WITH PASSWORD = 'Strong@Password123';

-- 创建带选项的登录名
CREATE LOGIN user2 
WITH 
    PASSWORD = 'Strong@Password123',
    DEFAULT_DATABASE = mydb,
    DEFAULT_LANGUAGE = 简体中文,
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;

-- 从证书创建登录名
CREATE LOGIN login_cert FROM CERTIFICATE cert_name;

-- 修改密码
ALTER LOGIN user1 WITH PASSWORD = 'NewStrong@Password456';

-- 修改默认数据库
ALTER LOGIN user1 WITH DEFAULT_DATABASE = master;

-- 启用/禁用登录名
ALTER LOGIN user1 ENABLE;
ALTER LOGIN user1 DISABLE;

-- 解锁登录名
ALTER LOGIN user1 WITH PASSWORD = 'NewPassword' UNLOCK;

-- 修改登录名
ALTER LOGIN user1 WITH NAME = new_user1;

-- 删除登录名
DROP LOGIN user1;

-- 安全删除
IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'user1')
    DROP LOGIN user1;

-- 查看所有登录名
SELECT name, type_desc, is_disabled, default_database_name
FROM sys.sql_logins;

-- 使用系统存储过程
EXEC sp_helplogins;

-- 从登录名创建用户
CREATE USER user1 FOR LOGIN user1;

-- 创建带默认架构的用户
CREATE USER user1 FOR LOGIN user1 WITH DEFAULT_SCHEMA = dbo;

-- 创建不带登录名的用户（用于模拟）
CREATE USER app_user WITHOUT LOGIN;

-- 从Windows用户创建
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user];

-- 修改默认架构
ALTER USER user1 WITH DEFAULT_SCHEMA = sales;

-- 修改用户名
ALTER USER user1 WITH NAME = new_user1;

-- 修改登录名映射
ALTER USER user1 WITH LOGIN = new_login;

-- 删除用户
DROP USER user1;

-- 安全删除
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'user1')
    DROP USER user1;

-- 查看数据库用户
SELECT name, type_desc, default_schema_name
FROM sys.database_principals
WHERE type IN ('S', 'U');

-- 使用系统存储过程
EXEC sp_helpuser;

-- 固定服务器角色
/*
sysadmin       - 系统管理员
securityadmin  - 安全管理员
serveradmin    - 服务器管理员
setupadmin     - 设置管理员
processadmin   - 进程管理员
diskadmin      - 磁盘管理员
dbcreator      - 数据库创建者
bulkadmin      - 批量操作管理员
*/

-- 添加登录名到服务器角色
ALTER SERVER ROLE sysadmin ADD MEMBER user1;

-- 从服务器角色移除
ALTER SERVER ROLE sysadmin DROP MEMBER user1;

-- 查看服务器角色成员
EXEC sp_helpsrvrolemember;

-- 固定数据库角色
/*
db_owner        - 数据库所有者
db_accessadmin  - 访问管理员
db_securityadmin - 安全管理员
db_ddladmin     - DDL管理员
db_backupoperator - 备份操作员
db_datareader   - 数据读取者
db_datawriter   - 数据写入者
db_denydatareader - 拒绝读取
db_denydatawriter - 拒绝写入
*/

-- 添加用户到数据库角色
ALTER ROLE db_datareader ADD MEMBER user1;

-- 从数据库角色移除
ALTER ROLE db_datareader DROP MEMBER user1;

-- 查看数据库角色成员
EXEC sp_helprolemember;

-- 创建自定义数据库角色
CREATE ROLE app_reader;

-- 授予权限给角色
GRANT SELECT ON SCHEMA::dbo TO app_reader;

-- 添加用户到自定义角色
ALTER ROLE app_reader ADD MEMBER user1;

-- 删除角色
DROP ROLE app_reader;

-- 创建应用程序角色
CREATE APPLICATION ROLE app_role
WITH PASSWORD = 'Strong@Password123',
     DEFAULT_SCHEMA = dbo;

-- 激活应用程序角色
EXEC sp_setapprole 'app_role', 'Strong@Password123';

-- 修改应用程序角色
ALTER APPLICATION ROLE app_role
WITH PASSWORD = 'NewStrong@Password456';

-- 删除应用程序角色
DROP APPLICATION ROLE app_role;

-- 授予对象权限
GRANT SELECT ON users TO user1;
GRANT INSERT, UPDATE ON users TO user1;
GRANT SELECT ON users TO user1 WITH GRANT OPTION;  -- 允许授权给他人

-- 授予架构权限
GRANT SELECT ON SCHEMA::dbo TO user1;

-- 授予数据库权限
GRANT CREATE TABLE TO user1;
GRANT CREATE VIEW TO user1;

-- 授予服务器权限
GRANT CREATE ANY DATABASE TO user1;

-- 撤销权限
REVOKE SELECT ON users FROM user1;
REVOKE INSERT, UPDATE ON users FROM user1;

-- 撤销GRANT OPTION
REVOKE GRANT OPTION FOR SELECT ON users FROM user1;

-- 撤销所有权限
REVOKE ALL ON users FROM user1;

-- 拒绝权限（优先级最高）
DENY SELECT ON users TO user1;
DENY INSERT, UPDATE ON users TO user1;

-- 拒绝会覆盖GRANT
GRANT SELECT ON users TO user1;
DENY SELECT ON users TO user1;  -- user1无法SELECT

权限优先级：
DENY > GRANT

权限继承：
服务器角色 → 数据库角色 → 用户权限

权限累计：
用户的最终权限是所有权限的并集（DENY除外）

-- 创建架构
CREATE SCHEMA sales AUTHORIZATION dbo;

-- 创建架构并创建对象
CREATE SCHEMA hr
    CREATE TABLE employees (id INT, name NVARCHAR(50))
    GRANT SELECT TO public;

-- 修改架构所有者
ALTER AUTHORIZATION ON SCHEMA::hr TO user1;

-- 转移对象到新架构
ALTER SCHEMA sales TRANSFER hr.employees;

-- 删除空架构
DROP SCHEMA hr;

-- 如果架构有对象，需要先转移或删除对象

-- 查看服务器权限
SELECT 
    pr.principal_id,
    pr.name,
    pe.permission_name,
    pe.state_desc
FROM sys.server_principals pr
LEFT JOIN sys.server_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pr.type = 'S';

-- 查看数据库权限
SELECT 
    USER_NAME(grantee_principal_id) AS user_name,
    permission_name,
    state_desc,
    OBJECT_NAME(major_id) AS object_name
FROM sys.database_permissions
WHERE class = 1;  -- 对象权限

-- 查看用户权限
EXEC sp_helprotect @username = 'user1';

-- 查看用户的有效权限
EXEC AS USER = 'user1';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

-- 查看服务器权限
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

安全设计原则：
├── 最小权限原则
├── 使用角色管理权限
├── 使用Windows身份验证
├── 定期审计权限
├── 使用强密码策略
├── 禁用sa账户
└── 定期更改密码

-- 创建角色并授权
CREATE ROLE report_reader;
GRANT SELECT ON SCHEMA::report TO report_reader;

-- 将用户添加到角色
ALTER ROLE report_reader ADD MEMBER user1;

-- 使用存储过程封装权限
CREATE PROCEDURE usp_get_sensitive_data
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT * FROM sensitive_table;
END