sqlserver：数据库权限设计概念及如何配置一个只读账户

环境：

• window10 x64 专业版
• sqlserver 2014 x64

一、sqlserver的权限设计级别

sqlserver中的权限控制被分成服务器和数据库两个级别，一个服务器可以包含多个数据库。
服务器级别的权限可以让我们控制登录、服务器资源操作等，而数据库级别的权限可以让我们对具体的表/视图/数据等数据库内资源进行操作控制。

在服务器级别，SQL server为我们提供了：服务器角色、登录名和安全对象。
在数据库级别，SQL server为我们提供了：数据库角色、用户名、架构和安全对象。

看下图：

上图中的“安全对象”是什么东西呢？
答：安全对象就是权限要作用到的地方，也就是有权限控制的资源。比如：一个表称之为一个安全对象，因为我们可以定义它的访问权限，同理，一个角色、用户、架构、登录名都可以称之为一个安全对象。安全对象也可以按类型划分，划分后就是上图中显示的样子。

二、服务器的登录名和数据库的用户有什么关系？

无论是我们用客户端还是程序去连接数据库，都需要提供服务器级别的登录名来登录到服务器，比如下面的连接字符串：

Data Source=127.0.0.1;Initial Catalog=master;User ID=sa;Password=123456;

上面连接字符串使用的就是服务器级别的登录名sa，登录到服务器后，服务器就会将登录名sa映射到具体数据库的用户上。之后，我们在数据库内的被允许哪些操作将以数据库内的账户为准（默认创建数据库后会有dbo账户和dbo架构，sa正是被映射到了dbo账户上，而dbo账户默认的架构是dbo架构）。
那么，登录名和用户的关系究竟是怎样的呢？

• 登录名必须映射到数据库的用户上，才能够操作这个数据库
• 一个登录名在给定的数据库上只能对应一个用户，即：登录名在一个数据库上只能映射到一个用户，但一个登录名可以映射到不同的数据库中。
• 连接字符串中的User ID的值是服务器登录名。
• sqlserver默认的sa是服务器级别的登录名，每个数据库都会有默认的dbo用户。默认情况下，sa连接到具体的数据后就会被转化成dbo用户，之后在数据库里的所有操作权限都以dbo用户的权限配置为准。

三、数据库内的架构和表、视图等有什么关系？

架构是表、视图、函数等的资源容器。一个数据库内可以有不同的架构，每个架构内有各自的表、视图等资源。其实，我们在SQL sever资源管理器或sql语句中也可以看的出来：

四、数据库内的用户和架构是什么关系呢？

在数据库中，一个用户可以拥有多个架构，但只能有一个默认架构。而一个架构可以被多个用户拥有。
我们常见的dbo账户的默认架构就是dbo架构，只不过它们是重名的，所以不太留意这个。

五、数据库内的角色、用户和架构是什么关系呢？

首先，用户和角色肯定是多对多了，一个用户可以拥有多个角色，同时角色可以被多个用户拥有。
注：数据库内的所有用户都拥有public角色并且不可移除，dbo账户默认具有db_owner角色

角色和架构之间也是多对多的关系，也就是说一个角色可以拥有多个架构，同时一个架构可以被多个角色拥有。

六、数据库内的权限究竟改怎么划分呢？

上面把数据库内的用户、角色、架构以及资源(表和视图等)的关系厘清了，但是权限是怎么分配的没有提到，下面就说一下权限的分配：
首先，我们需要明白权限的定义：谁对什么资源拥有什么样的权力？(自己领会的。。。)
具体来说就是：
哪个用户或角色对架构、表、视图、角色以及用户等资源拥有什么样的权力!
有几个关键的主体：

1. 谁拥有权力： 用户或角色，分配权限就是分配到用户或角色上的
2. 拥有对谁的权力：架构、表、视图等资源对象
3. 拥有什么样的权力：具体要看资源上定义了什么权力，配置的时候是从这些定义了的权力上做选择的。

那么，sqlserver在哪里为我们提供配置的地方呢？

• 在角色的属性框中，我们可以指定这个角色对哪些安全对象(也就是资源)的操作权限：
  
• 在架构的属性框中，我们可以指定哪个用户或角色对这个架构有什么样的操作权限：
• 在具体的表、视图等资源对象（上一条说的架构也是资源对象）的属性框中，我们可以指定哪个用户或角色对这个资源有什么样的操作权限：
  

七、关于sqlserver默认的角色/用户

• 服务器默认角色
  服务器级别的权限控制上，SQL server默认有九大角色（见下图），它们都有各自定义好的权限，并且除了public之外，你不能在上面修改已定义好的权限，但是你可以新建服务器角色。。。
  服务器角色public：任何登录名都拥有这个角色并且不可撤销
  服务器sysadmin：sa账户默认拥有的角色，拥有对服务器至高的权力（理论上拥有这个角色的登录名都将拥有sa的权力，但这些权力仅限在服务器级别上的，具体的数据库内的权力要看映射到了数据库的哪个用户上）。
  关于其他服务器角色的功能介绍，看参考：https://www.cnblogs.com/rainman/p/3509626.html
  

• 服务器默认登录名
  服务器默认有10个登录名（安装时选择的功能不同，数量也会有差别），其中sa默认是不启用（安装时选择使用window身份认证）并且拥有服务器角色sysadmin和public。
  

• 数据库的默认角色：
  新建一个数据库，默认拥有10个角色，其中public角色是每个数据库用户必须拥有的，不可撤销；db_owner角色表示数据库的拥有者（默认被dbo账户拥有），对这个数据库拥有至高的权力，相当于服务器角色中的sysadmin。
  

• 数据库的默认用户：
  新建一个数据库，默认拥有四个用户，其中dbo账户拥有数据库角色db_owner，也就是说拥有了数据库的至高操作权力，同时dbo账户的默认架构是同名的dbo架构。其他几个用户除了具有public角色外，不具有其他的数据库角色。
  

• 数据库默认的架构：
  新建一个数据库，默认拥有13个架构，其中dbo架构是dbo账户的默认架构，也是我们常操作的架构，其他的架构多是系统提供给我们查询系统信息的。
  

八、权限控制实战：控制账户对数据库只读

控制用户tom对test数据库具有只读的操作权限

8.1 新建登录名tom

建立好后，我们观察数据库资源：

观察数据库用户tom的登录名和默认架构：

此时，我们用tom账户登录数据库后是没办法查询表test的，如下图：

8.2 给tom用户赋予只读数据的权限

这里我们使用简单的方法：让tom拥有db_datareader角色即可（注意切换登录身份）：

设置完成后，我们再用tom登录，并测试数据操作情况：
查询：

修改表结构：

到此，这个实验就完毕了。这个只读的账户可以分配给其他报表程序，以防止它们对原来数据库的损害；或者是在数据库读写分离的时候用来从只读数据库中读取数据以防止发生写入数据而导致的数据不一致。
最后，如果我们不需要这个账户了，可以将这个账户删掉。删除的时候要从数据库->服务器进行删除，即：先删除数据库用户，然后再删除服务器登录名。

8.3 其他

上面做的实验比较简单，实际中可能有一些复杂的案例，比如说：限制某个账户只能读取某几个表的数据等等，这个时候我们就需要结合新建数据库角色/精确控制表的权限等功能了。