第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区别是什么?
对照答案
检查该库用户映射和访问权限;只授该表SELECT,或通过自定义角色授予;DENY明确拒绝,REVOKE移除已有授权/拒绝记录。
上一节:24 触发器与自动检查 | 下一节:26 数据导入导出与核对 | 课程目录