SQL Server 权限管理

最新推荐文章于 2024-04-08 10:36:03 发布
最新推荐文章于 2024-04-08 10:36:03 发布
阅读量372 收藏 1
点赞数
文章标签： 数据库 测试
原文链接：http://www.cnblogs.com/cjm123/p/8434622.html
版权

SQL Server 权限管理

2014-11-10 08:37 by pursuer.chen, 24214 阅读, 40 评论, 收藏, 编辑

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/权限控制/管理/分配/登入名/数据库用户/角色

概述

      对数据库系统而言，保证数据的安全性永远都是最重要的问题之一。一个好的数据库环境，必须明确每个用户的职责，并分配其对应的权限。同时出现问题了也可以找到根源。

你是否会有这样的需求：

  1. 给某个用户查询所有数据库的权限
  2. 给某个用户只有备份数据库的权限
  3. 给一个用户只有指定数据库的权限
  4. 给一个用户只有某个表的权限
  5. 给一个用户只有查看某些对象（例如：视图）的权限
  6. 给一个用户只有执行一些存储过程的权限

 

目录

元素

文章可能会有些枯燥，还望耐心，相信应该有你想要的。

登入名

只有拥有了登入名才能访问实例（sql server）.

角色

角色是一类权限的组合。

  • 数据库角色的拥有者可以是用户也可以是数据库角色本身，管理员可以创建数据库角色，也可以勉强将数据库角色理解为一组相同权限的用户，为什么这么说呢，因为数据库角色和数据库用户不允许存在同名。 

注意：不要将用户创建的数据库角色添加到固定的服务器数据库角色当中去，否则将导致固定的数据库角色的权限升级。

  • 服务器角色的拥有者只有登入名，服务器角色是固定的，用户无法创建服务器角色。

注意：一般不建议给用户直接分配服务器角色，因为服务器角色是全局的，也就是说你拥有了服务器级别的权限，一般建议给用户分配数据库，然后给对应的数据库分配数据库角色权限。

用户

      用户是数据库级的概念，数据库用户必须绑定具体的登入名，你也可以在新建登入名的时候绑定此登入名拥有的数据库，当你绑定登入名数据库后，数据库默认就创建了此登入名同名的数据库用户，登入名与数据库用户之间就存在关联关系，数据库用户是架构和数据库角色的拥有者，即你可以将某个架构分配给用户那么该用户就拥有了该架构所包含的对象，你也可以将某个数据库角色分配给用户，此用户就拥有该数据库角色的权限。

架构

      架构是对象的拥有者，架构本身无权限，架构包含数据库对象：如表、视图、存储过程和函数等，平时最常见的默认架构dbo.,如果没指定架构默认创建数据库对象都是以dbo.开头，架构的拥有者是数据库用户、数据库角色、应用程序角色。用户创建的架构和角色只能作用于当前库。

 

 理解了这些概念之后接下来就可以实践了,接下来我们测试的都是服务器角色选择public,只测试对数据库权限的控制。

权限分配

新建登入名

新建一个登入名person,只给登入名服务器角色分配public权限，不分配数据库

接下来用person登入实例，person用户无法访问任何数据库，由于我们未给用户分配任何数据库。

给用户分配数据库查看权限

只允许用户查看AdventureWorks2008R2数据库

 此时用户可以查询所有对象，但无法修改对象。

给用户查询某个对象的权限

      如果觉得给用户查看权限太大了，将da_datareader数据库角色权限回收，你会发现用户可以访问数据库，但是看不到任何对象。

   

 只给用户查看Person.Address表

复制代码 
USE AdventureWorks2008R2;
GRANT SELECT ON OBJECT::Person.Address TO person;
--或者使用
USE AdventureWorks2008R2;
GRANT SELECT ON Person.Address TO RosaQdM;
GO
复制代码

扩展功能

--以下都是赋予用户对表的dml权限
复制代码 
---授予用户person对表Person.Address的修改权限
USE AdventureWorks2008R2;
GRANT UPDATE ON Person.Address TO person;
GO

---授予用户person对表Person.Address的插入权限
USE AdventureWorks2008R2;
GRANT INSERT ON Person.Address TO person;
GO

---授予用户person对表Person.Address的删除权限
USE AdventureWorks2008R2;
GRANT DELETE ON Person.Address TO person;

 --授予用户存储过程dbo.prc_errorlog的执行权限
GRANT EXECUTE ON dbo.prc_errorlog TO person
复制代码
复制代码

标量函数权限：EXECUTE、REFERENCES。

表值函数权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

存储过程权限：EXECUTE。

表权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

视图权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

复制代码

授予用户架构的权限

新建数据库角色db_persons

新增架构

数据库-安全性-架构

架构包含数据库对象

创建架构persons表

---创建架构persons的表
CREATE TABLE Persons.sutdent
(id int not null)

你会发现用户同时有了Persons.sutdent表的查看权限，因为用户是数据库角色db_person的所有者，而db_person又是架构persons的所有者。

创建一些persons架构的视图，存储过程

复制代码 
---创建视图
USE AdventureWorks2008R2
GO
CREATE VIEW Persons.vwsutdent
AS
SELECT * FROM Persons.sutdent

GO
USE AdventureWorks2008R2
GO
---创建存储过程
CREATE PROCEDURE Persons.SP_sutdent
(@OPTION NVARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON
    IF @OPTION='Select'
    BEGIN
    SELECT * FROM Persons.sutdent
    END
END
复制代码

 

详细的GRANT功能可以查询2008r2连接丛书:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm

查询权限

 

复制代码 
  ---登入名表
  select * from master.sys.syslogins 
  ---登入名与服务器角色关联表
  select * from sys.server_role_members
  ---服务器角色表
  select * from sys.server_principals
  ----查询登入名拥有的服务器角色
  select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
  from sys.server_role_members m  inner join sys.server_principals g on  g.principal_id = m.role_principal_id 
  inner join sys.server_principals u on u.principal_id = m.member_principal_id

  ---数据库用户表
  select * from sysusers
  ---数据库用户表角色关联表
  select * from sysmembers 
  ---数据库角色表
  select * from sys.database_principals
  ----查询数据库用户拥有的角色
  select ta.name as username,tc.name as databaserole  from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid
  inner join  sys.database_principals tc on tb.groupuid=tc.principal_id
复制代码

 

查询登入名与数据库用户之间的关系

复制代码 
--查询当前数据库用户关联的登入名
  use AdventureWorks2008R2
  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 
  
  /*如果将当前数据库还原到另一台服务器实例上,刚好那台服务器上也存在person登入用户，你会发现二者的sid不一样，
  由于sid不一样,所以登入用户不具有当前数据库的访问权限,我们要想办法将二者关联起来。
  */
  ---关联登入名与数据库用户（将数据库用户的sid刷成登入名的sid）
    use AdventureWorks2008R2
    EXEC sp_change_users_login 'Update_One', 'person', 'person'
    Go
复制代码

查询数据库用户被授予的权限

exec sp_helprotect @username = 'person'

 查询person数据库用户权限会发现，数据库用户拥有的权限都是前面使用GRANT赋予的权限，而后面给用户分配的架构对象不在这个里面显示，上面显示的只是被授予的权限，而架构是数据库用户所拥有的权限。

 

回收权限

 如果安全对象是数据库，对应 BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。

如果安全对象是标量函数，对应 EXECUTE 和 REFERENCES。

如果安全对象是表值函数，对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

如果安全对象是存储过程，表示 EXECUTE。

如果安全对象是表，对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

如果安全对象是视图， 对应 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

 

回收dbo.prc_errorlog存储过程的执行权限

USE AdventureWorks2008R2;
REVOKE EXECUTE ON dbo.prc_errorlog    FROM person;

回收Person.Address表的查询，修改，删除权限

复制代码 
--回收修改
USE AdventureWorks2008R2;
REVOKE update ON   Person.Address FROM person;

USE AdventureWorks2008R2;
REVOKE alter ON   Person.Address FROM person;

--回收删除
USE AdventureWorks2008R2;
REVOKE delete ON   Person.Address FROM person;

--回收查询
USE AdventureWorks2008R2;
REVOKE select ON   Person.Address FROM person;
复制代码

 

 最后剩下owner为‘.’的是数据库级的权限

最后回收数据库的权限

复制代码 
USE AdventureWorks2008R2;
REVOKE CREATE TABLE FROM person;
GO

CONNECT权限是用户访问数据库的权限，将此权限回收后用户将无法访问数据库
--USE AdventureWorks2008R2;
--REVOKE CONNECT FROM person;
--GO
复制代码

再执行exec sp_helprotect @username = 'person'，就剩下action=connect的数据库访问权限

将权限回收后，数据库用户还剩下架构Persons的权限，如果还需要将该权限回收，只需要用户取消关联对应的db_person数据库角色权限。

 详细的revoke权限回收请参考2008r2联机丛书：

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/9d31d3e7-0883-45cd-bf0e-f0361bbb0956.htm

补充

针对生产数据库服务器创建一个应用程序访问的用户最常见的是授予用户某个数据库：“查询”、“删除”、“修改”、“插入”、“执行”的权限，用SQL语句实现如下（用户：person,数据库：news）：
复制代码 
USE [master]
GO
---创建登入名
CREATE LOGIN [person] WITH PASSWORD=N'person', DEFAULT_DATABASE=[news], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [news]
GO
---在指定的数据库下创建和登入名相关联的数据库用户
CREATE USER [person] FOR LOGIN [person]
GO
USE [news]
GO
---在指定的数据库下授予用户SELECT,DELETE,UPDATE,INSERT,EXECUTE权限。
GRANT SELECT,DELETE,UPDATE,INSERT,EXECUTE TO person;
复制代码

 

注意：创建登入名在master数据库下，创建数据库用户和授予数据库权限都是在具体的数据库下操作。

 

总结

      所以如果你想对某个用户某个数据库的权限进行细分，你可以通过GRANT来授予具体的对象给用户（当然你也可以revoke回收权限），也可以通过添加某个架构的权限给用户那么用户就拥有该类架构的权限。 

用户拥有什么权限取决于角色，而拥有哪些对象取决于拥有包含这些对象的架构，架构的拥有者可以是数据库用户也可以是数据库角色也可以是应用程序角色，明白了这个道理你对权限的管理也就很清晰了。

 虽然心有余但是还是无法将整个知识点给讲透，写文章之前虽然把整个框架给整理了，但是在写的过程中发现要写的内容太多了，比如GRANT权限里面就涉及了表、数据库、应用程序角色、函数、证书、角色、架构、存储过程、同义词还有很多；同时表有可以精确到给具体的某个字段的权限，所以太多了，接下来的REVOKE也同样是这么多。本文可以起到一个引领的作用，让你了解有这些功能，了解权限的功能细分；如果有兴趣的朋友可以更深入的去钻研，这篇文章写下来还是挺累的,写完这篇文章看一下时间已经是凌晨二点钟，主要是思维不想被中断所以一口气给写完了，希望能给大家有所帮助。

 

 

备注：

    作者：pursuer.chen

    博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》

 

 

 

 

SqlServer的权限是相当良好的。但在应用系统进程也要有自己的的权限管理，数据库的是数据库的用户，应用系统是应用系统的用户。
有9种AC元素：Account、Organization、Role、Group、Function、Menu、AppSystem、ResourceType、Privilege。 
Privilege的定义是这9种AC元素的任意两两组合，两元中的一元是“主体”，一元是“客体”，主体负责感知客体。区分主客体就是为这两两组合定义了方向。一共是(9 * 8)/(2*1) = 36 + 9 = 45种结果。 
在功能级权限的这45种组合中只有一种组合最关键：（Account,Function），主体是Account，客体是Function。其余44种组合的目的最终都是为了得出这个主体为Account客体为Function的组合。 
每一个组合实例称作一个Privilege，Privilege也是9种AC元素的一员，有一种组合比较特殊，它是（Privilege，Privilege），这种组合目的是组成Privilege的继承链条，类似面向对象中的继承。另外上面的36+9中的9种是(Account,Account)、(Organization,Organization)、(Role,Role)、(Group,Group)、(Function,Function)、(Menu,Menu)、(AppSystem,AppSystem)、(ResourceType,ResourceType)、(Privilege,Privilege)。 
功能级的权限是常驻内存的。 
进入数据权限的时候又多了一个元素，第10种元素是Entity或者叫资源记录，数据级权限是10种AC元素的组合。数据级权限和资源记录存储在相同的物理位置，随同对资源记录的访问一起加载进内存，用完后随时丢弃不会常驻内存。 
按照rbac标准上的说法是能够完成任何功能级的权限控制的，那么多ac元素最终都是为了得到(account,function)组合，系统运行到一个受控的区域时什么都不管只认(account,function)组合，只问当前用户是否可以执行当前function。 

要鉴权只需要知道当前的account和当前正在试图访问的function分别是什么，其中account可以从UserSession中直接读取，而如何识别function相对曲折，对于asp.net mvc来说可以基于这样的约定：ControllerName=ResourceTypeCode，ActionName=FunctionCode，知道了ResourceType.Code和Function.Code就可以直接到FunctionSet中索引到function了。 

为了开始构建后100层的数据级权限，anycmd需要尽快为前100层的功能级权限打个好节。

 

 

 

dbo 和Person都是架构名，默认的架构都是以dbo 开头的 一般我们在调用数据库
数据库名.构架名.表名,这种方式在不同的数据库。
当在同一个数据库中时就可以省略数据库名，只需要构架名.表名，这是在表中只有相同的架构的情况下，只需要直接用表名就可以了select * from 表 ，如果存在架构有多种的话就需要在调用中用 构架名.表名 select * from 架构名.表。
他们起到识别功能，比方说表名相同都叫 a,但是一个是dbo 架构的，一个是 Person，在调用过程中是不一样的，select * from dbo.表
select * from person.表  不写架构名则默认为dbo。

转载于:https://www.cnblogs.com/cjm123/p/8434622.html

anmei1912
关注
SQL server 数据库管理
wx号:wenhs5479的博客
09-23 393
通过命令方式查看数据库 1,先按Ctrl+n新建查询窗口 2,输入如下T-SQL语句,然后执行,可查看到数据库student的相关信息 sp_helpdb student 3,使用系统储存过程,SP_DATABASES显示服务器中所有可以使用的数据库信息,命令:sp_databases 4,使用系统储存过程,SP_HELPFILE查看数据库中文件的信息.语法如下 SP_HELPFILE [文...
SQL Server 数据库之角色、管理权限
程序员小白的博客
03-24 9834
角色、管理权限1. 角色1.1 概述1.2 预定义角色1.3 角色的操作2. 管理权限 1. 角色 1.1 概述 一个数据库可能会有许多个用户，若单独给每个用户设置管理权限，会浪费很多时间；角色就是集中管理权限的一种机制，它能将不同权限的用户分类组合，便于数据库管理员管理； 1.2 预定义角色 SQL Server 中有两类预定义的角色，系统管理预定义角色和数据库预定义角色，这两类预定义角色将系统管理和数据库管理的权限做了分解，方便数据库管理员分配使用； 当中，较为特殊的是 public 角色，它的权限是所
SQL server服务器管理
weixin_34174422的博客
09-04 161
创建服务器组 在一个网络系统中，可能有多个SQL Server服务器，可以对这些SQL Server服务器进行分组管理。分组的原则往往是依据组织结构原则，如将公司内一个部门的几个SQL Server服务器分为一组。SQL Server分组管理由SQL Server管理平台来进行。 服务器注册与连接 ?在SQL Server管理平台中注册服务器可以存储服务器连接信息，以供...
SQL SERVER-权限管理
dxm809的博客
02-03 709
新建一个用户（在主机名下面的安全性，登录名里面新建） 设置服务器角色，public是最低权限 设置映射的数据库，和角色   此时登陆，新用户名，验证角色设置正常   回到sa账户，分配更新权限给新建账户 grant update on userinfo to zhao   在指定数据库下面，新建架构 利用新建架构创建表 create table itcast.de...
SQL Server权限管理图文教程.rar
12-16
在SQL Server中，权限管理是数据库安全的重要组成部分，它确保只有授权的用户或应用程序能够访问、修改或控制数据。本教程将深入探讨SQL Server的权限系统，帮助你理解和实施有效的数据库安全策略。 首先，SQL ...
SQL Server权限管理
04-24
对于一个数据库管理员来说，安全性就意味着必须保证那些具有特殊数据访问权限的用户能够登录到SQL Server，并且能够访问数据以及对数据库对象实施各种权限范围内的操作；同时，他还要防止所有的非授权用户的非法操作...
SQL Server权限管理：确保数据安全与访问控制
SQL Server权限管理是数据库管理员的重要职责，它确保只有授权的用户能访问特定的数据和执行特定的操作，同时防范未经授权的用户造成潜在威胁。SQL Server的安全管理模式基于两个关键机制：安全身份验证和访问许可。...
《SQL Server》之 数据库管理
架构设计
08-21 151
一、创建数据库 database_name：数据库的名称，最长为128个字符 ON：指明数据库文件盒文件组的明确定义额 PRIMARY：指定主数据库文件或主文件组。主文件组的第一个文件被认为是主数据库文件，其中包含了数据库的逻辑起点和数据库的系统表。如果没有PRIMARY项， 则在VREATE DATABASE命令中列出的第一个文件将被默认为主文件 filespec：文件说明...
SqlServer管理和查询数据
04-09
此资源为使用SQLServer 管理和查询数据的操作指导，欢迎各位C友下载学习。
Sql server 数据库权限管理 - 第5章 安全与权限
lijwen的博客
04-22 815
http://note.youdao.com/noteshare?id=29b3594ee56258d386ae61ff2abfdea4&sub=9E11C9100D3148D5A2F0F12AF97CC188 待完善： 20180719 16:00 前完成。
SQL Server的管理
好记性不如烂笔头
05-15 227
这里在对SQL server 2008R2来做一些功能性和结构性的解释，技多不压身，多学点总是好的，对吧。好，接下来进入正题 SQL Server的数据存储结构 • 文件类型 说明：一个数据库至少应该包含一个数据文件和一个事务日志文件，接下来介绍介绍数据库一共具有几种文件类型           ✓ 主数据库文件：主数据文件包含数据库的启动信息，指向数据库中的其他文件。每个数据库有且只
SQL Server数据库管理
Snower_2022的博客
11-07 2123
数据库登录和数据库的三种备份使用，完整备份在差异备份和事务日志备份中都会用到。
【SQL Sever】3. 用户管理 / 权限管理
最新发布
哈茶的博客
04-08 7807
该篇文章包含对SQL Sever中用户/登录名/角色的理解，赋予用户权限等内容，快来看看吧！
一学就会的Sql Server数据库权限管理
Friendsofthewind的博客
12-30 6955
场景：设想您是微软公司总裁，公司新添加一台服务器，保存重要的Sql Server数据库，既不想服务器隐私被泄露，又想让公司工作人员正常维护，给您做数据备份，或者优化配置等等，具体怎么实现呢？请看课程讲解！ 一.根据需求分析要做的配置 1.更改数据库身份验证模式为混合模式（只有这样才能给别人设账号，否则，只能本地系统登录，秘书都不能哟！） 2.以管理员的身份登录服务器，创建帅哥数据库，并在其中创建一个表empinfo 3.创建登录账户zhangsan并最终使其可以访问帅哥.empinfo表，并验证（秘书叫zh
SQL Server数据管理
Angel Devil
05-02 415
SQL Server数据管理T-SQL组成数据定义语言（DDL）用来建立数据库、数据库表和定义其列，大部分是以CREATE开头的命令数据操纵语言（DML）用来操作数据库中数据的命令，如SELECT、UPDATE、insert、DELETE 等。数据控制语言（DCL）用来控制数据库组件的存取许可、存储权限等的命令，如GRANT、REVOKE等。流程控制语言（FCL）用于设计应用 程序的语句，如IF、...
SQL Server 数据管理
m0_52681702的博客
12-17 987
本期重点： 1.使用SQL Server中的逻辑表达式和运算符 2.使用T-SQL语句向表中查询,插入、更新、删除数据 SQL(Structured Query Language)：结构化查询语言，主要用于应用程序向数据库发送增删改查的命令，以操作数据库表中的数据。 T-SQL = Transact-SQL T-SQL是SQL的加强版 对功能进行了扩充：如变量说明、流程控制、功能函数 一.SQL Server运算符 1.算术运算符 2.比较运算符 3.按位运算符 4....
sqlsever数据库管理
yuki5233的博客
08-25 972
目录 文章目录目录1. 使用SQL语句创建数据库1.1 创建只有一个数据库主文件和一个日志文件的数据库1.2 1. 使用SQL语句创建数据库 1.1 创建只有一个数据库主文件和一个日志文件的数据库 要求如下： 数据库名为 E_Market； 数据库主文件的逻辑文件名为 E_Market_data； 数据库主文件物理文件名为 /var/opt/mssql/data/E_Market_data.mdf； 数据库主文件的初始大小是5MB； 数据库主文件能增长到的最大值是100MB； 数据库主文件大小的增长率为1
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值