第25节 登录用户角色与权限

你已经会建立和操作数据库,最后学习“谁能进来、进来后能做什么”。先分清身份,再记命令。本节有管理权限要求,账号代码是练习模板,不是要求你修改自己当前登录。

1 四个对象分属两个层次

对象作用范围可以怎么理解
登录Login服务器实例进入实例的身份
数据库用户User某个数据库这个身份在本库内叫什么、能访问什么
服务器角色Server Role实例一组服务器级权限
数据库角色Database Role本库一组数据库级权限

一条常见路径是:身份验证通过→以Login连接实例→映射到本库User→根据用户和角色权限访问表。同一Login可以在多个数据库各有自己的User;删除User不会自动删除Login。

2 Windows验证与混合模式

Windows身份验证使用操作系统身份;混合模式同时支持Windows与SQL Server账号密码登录。SSMS服务器右键→属性→安全性查看身份验证模式。

-- 只查看,不改变模式:1为仅Windows,0为混合模式
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS [仅Windows验证];

修改模式通常需重启数据库引擎服务;共享机房先按老师安排,不为了练习随意重启。创建SQL登录也不代表一定能连入,还要看模式、是否启用、密码和连接权限。

3 创建Login,再映射User

以下是需要管理权限的模板;先确认名称没占用,并把示例密码替换为你自己的合规密码。无需把真实密码保存到公开笔记。

USE master;
GO
-- 服务器级创建SQL登录;此处密码只是占位示例
CREATE LOGIN BookLearnerLogin
WITH PASSWORD='Replace_With_Your_Strong_Password!2026';
GO
USE LearningBookstore2012;
GO
-- 在学习库里为这个登录建立用户,允许它获得本库权限
CREATE USER BookLearnerUser FOR LOGIN BookLearnerLogin;
ALTER USER BookLearnerUser WITH DEFAULT_SCHEMA=dbo;

默认架构dbo只是未写架构名时的命名解析设置,不是把用户变成数据库所有者。Windows账号的登录创建模板为 CREATE LOGIN [计算机或域\账号] FROM WINDOWS;,必须换成实际已有身份。

用户改名模板:ALTER USER BookLearnerUser WITH NAME=BookReaderUser;;重新映射到另一个登录用 ALTER USER 用户 WITH LOGIN=登录;。后续模板仍用原名,所以现在不实际改名。

4 授予、明确拒绝、撤销

-- 管理模板:TO后是接受权限的用户;SELECT只允许读取这张表
GRANT SELECT ON OBJECT::dbo.Book TO BookLearnerUser;
 
-- 明确拒绝删除;不是撤销SELECT,也不是删除用户
DENY DELETE ON OBJECT::dbo.Book TO BookLearnerUser;
 
-- 撤销刚才的显式拒绝记录;不代表自动授予DELETE
REVOKE DELETE ON OBJECT::dbo.Book FROM BookLearnerUser;

REVOKE移除显式GRANT或DENY记录,用户仍可能通过角色有权限。通常DENY会覆盖其他路径的GRANT,但sysadmin、对象所有者和列级等特例不能用“DENY任何时候最大”概括。

5 把权限交给角色集中管理

-- 管理模板:先建立本库角色,再给角色读取Book的权限
CREATE ROLE BookReadRole;
GRANT SELECT ON OBJECT::dbo.Book TO BookReadRole;
ALTER ROLE BookReadRole ADD MEMBER BookLearnerUser;
 
-- 学会移出成员:只解除这一条成员关系,不删除用户
ALTER ROLE BookReadRole DROP MEMBER BookLearnerUser;
REVOKE SELECT ON OBJECT::dbo.Book FROM BookReadRole;
DROP ROLE BookReadRole;

SQL Server 2012支持ALTER ROLE ADD/DROP MEMBER;旧教材的sp_addrolemember也能见到,但优先认识现代写法。

固定数据库角色:db_datareader读所有用户表/视图,db_datawriter可增删改,db_owner权限很大。固定服务器角色sysadmin拥有很高的实例控制能力,不能为“只读Book”随手授予它。

常见固定服务器角色大致职责
sysadmin完整的实例管理能力,权限极高
serveradmin服务器范围的配置等管理
securityadmin登录及服务器权限等安全管理,权限敏感
dbcreator创建、改变、删除、还原数据库等
public每个登录都属于它,提供基本公共权限,不应随意增加广泛授权

服务器角色成员语法是 ALTER SERVER ROLE 服务器角色 ADD MEMBER 登录;与DROP MEMBER。登录加入服务器角色,不是数据库用户加入数据库角色,这两个层次不要交叉写。

6 禁用、拒绝连接和删除登录

USE master;
GO
-- 管理模板:禁止这个登录今后建立新连接,定义仍存在
ALTER LOGIN BookLearnerLogin DISABLE;
ALTER LOGIN BookLearnerLogin ENABLE;
 
-- 显式拒绝连接实例,再撤销这条拒绝
DENY CONNECT SQL TO BookLearnerLogin;
REVOKE CONNECT SQL FROM BookLearnerLogin;

禁用或拒绝新连接一般不等于立即踢掉已存在会话。改密码模板为 ALTER LOGIN 登录 WITH PASSWORD='新密码';;需要相应权限和密码策略,不要对当前账号随意试。

7 查看对象并清理自己的练习账号

-- 这两句只读查询可执行;显示范围取决于你拥有的元数据可见权限
SELECT name,type_desc,is_disabled FROM sys.server_principals
WHERE type IN('S','U','G');
SELECT name,type_desc FROM sys.database_principals
WHERE type IN('S','U','G','R');

清理模板,只对本节自己新建的测试账号使用:

USE LearningBookstore2012;
GO
-- 先清理本库用户的显式授权;角色成员关系在前面已经移除
REVOKE SELECT ON OBJECT::dbo.Book FROM BookLearnerUser;
DROP USER BookLearnerUser;
GO
USE master;
GO
DROP LOGIN BookLearnerLogin;

如果用户拥有架构等对象,先按要求转移所有权再删除。没有完成创建就不运行清理代码。

本节练习

能连接实例却打不开某库,优先检查哪层?只读一张表应该授什么?REVOKE和DENY区别是什么?


上一节:24 触发器与自动检查 | 下一节:26 数据导入导出与核对 | 课程目录