SqlServer2008基础知识：安全与权限

分享自 儒雅的男人blog http://www.cnblogs.com/yushaoye201314/archive/2013/04/19/3031203.html

好文，转载收藏

这两天在调用Microsoft.SqlServer.SMO.dll组件 《add login,create,backup,restore database to Sql Server using SMO》，接触到一些数据库的名词定义：登录名,用户,角色,架构,权限，部分新人对几个名词的概念不是很清晰 所以参考部分文档，摘录如下(权当温故⑧^_^)：

SqlServer2008的安全机制分为四个级别：

1.操作系统的安全性

　　创建操作系统用户账号(win7)：

　　

2.数据库服务器的登录安全性【下面两种验证模式下登录所使用的用户名，即为：登录名定义。】

　　数据库服务器登录模式分为：

　　①Windows身份验证模式(如下图)

　　

　　用户名即是：当前Windows操作系统中的用户账户，我这里是用的管理员账号(计算机名)，如果系统账户有登录密码 可以相应的输入

　　登录成功后，设置SqlServer 验证模式如图：

　　

　　

　　②混合身份验证模式(Windows身份验证模式&SqlServer身份验证模式[前者安全性比后者高])，如下图：

　　　

　　在安装SQLServer2008时 会默认生成一个登录名：sa,其隶属于服务器角色：sysadmin。

　　而新创建的登录名，则会自动关联服务器角色：public,除了默认的数据库：master，其他的数据都将没有访问权限，如果想要访问其他数据库：

　　1.则需要在登录名-新建-用户映射里面勾选相应的数据库：则相应的数据库会自动创建一个同名(登录名)数据库用户账号

　　　　(注①：master数据库不勾选也是默认访问的 数据库用户里是没有同名账号。)

　　　　(注②：当登录名要删除之前 要先手动删除由登录名映射数据库而生成的用户)

　　2.或者在登录名-新建-服务器角色 中直接勾选sysadmin这样就可以访问所有的数据库(或在服务器角色sysadmin-右击属性-添加-登录名)了：修改服务器角色为　　　　sysadmin 不会在数据库用户里创建同名账号。

　　以下是数据库服务器的角色列表以及其对应所拥有的操作权限,如下图：

　　

3.数据库的使用安全性

　　登录名 只能登录进SqlServer服务器，并不能让访问服务器中的数据库，而使用登录名登录进数据库服务器后想要访问特定的数据库 还必须具有数据库用户名，用户名在特定的数据库内创建，并必须关联一个登录名，通过授权给该用户指定可以访问的数据库对象的权限

　　数据库用户可以新建或者由登录名映射数据库而生成。注：无论是新建的数据库用户 还是由登录名映射数据库生成的数据库用户 都要遵循 数据库用户1:1登录名的规则

　   创建数据库用户时 需要关联登录名；创建数据库角色是 需要关联使用者 一般选择dbo; dbo(DataBase Owner)是数据库的所有者，拥有数据库中的所有对象，每个数据库都有dbo,sysadmin服务器角色的成员自动映射成dbo，无法删除dbo，且此用户始终出现在每个数据库中。通常 登录名sa映射为数据库用户dbo.

　　数据库用户的生成需要关联数据库角色：数据库角色有三种：固定数据库角色,用户定义数据库角色,应用程序角色，如下图：

　　

4.数据库对象的使用安全性

　　我们一般将表,视图,存储过程,函数 统称为数据库对象，而数据库对象包含在架构中，而给角色划分权限也就是开放架构中部分或者全部的对象做相应的执行权限操作,

　　数据库对象的常用权限 如下图:

　　

　　权限操作分为三种：授予,拒绝,撤销，语法及代码如下

 1 --授予权限操作
 2 --语法：Grant {ALL|statement[,,,,n]} TO security_account[,,,n]
 3 --授予角色"students_mag"对"studengs"数据库中"stu"表 delete insert update权限
 4 use students
 5 go
 6 grant delete,inster,update
 7 on stu
 8 to students_msg
 9 go
10 --撤销权限操作
11 --语法：revoke {all|statement[,,,n]} from security_account[,,,n]
12 --使用revoke 撤销角色"students_mag"对"studengs"数据库中"stu"表 delete insert update权限
13 use students
14 go
15 revoke delete,insert,update
16 on stu
17 from students_mag CASCADE
18 --拒绝权限操作
19 --deny {all|statement[,,,n]} to security_account[,,,n]
20 --在数据库"students"的"stu"表中执行insert操作的权限授予了public角色，然后拒绝用户guest拥有该项权限
21 use students
22 go
23 grant insert
24 on stu
25 to public
26 go
27 deny insert
28 on stu
29 to guest

　　在创建或生成数据库用户时 需要关联架构，由登录名映射数据库生成的数据库用户 默认选择了dbo,手动创建数据库用户，则选择dbo,如下图：

　　

　　针对数据库设置用户/角色权限，如下图：

　　

总结：数据库的安全和权限如下图所示：

操作过程中相关的步骤、用T-SQL语句表示如下：

1.利用系统存储过程sp_addsrvrolemember可将登录名添加到某个固定服务器角色中

　　语法：exec sp_addsrvrolemember 'login',''role'

2.利用系统存储过程sp_dropsrvrolemember可从固定服务器角色中删除登录名

　　语法：exec sp_dropsrvrolemember 'login','role'

3.利用系统存储过程sp_srvrolepermission可以浏览固定服务器角色的权限

　　语法：exec sp_srvrolepermission 'role'

4.使用系统存储过程浏览固定的数据库角色

　　语法：exec sp_helpdbfixedrole 

5.创建用户自定义数据库角色

　　语法：create role role_name [authorization owner_name]    role_name：将要创建的数据库角色名  owner_name：该角色拥有者的名字，必须是当前数据库里的用户或角色，默认为dbo

　　T-Sql:create role 'myRole'    

　　　　　　使用存储过程sp_addrolemember给新建数据库角色设置已经存在的数据库角色功能exec sp_addrolemember 'db_datareader','myRole'

　　　　　　使用存储过程sp_droprolemember撤销给新建数据库角色所设置的已存在数据库角色的功能exec sp_droprolemember 'db_datareader','myRole'

　　Proc示例：exec sp_addrole 'testRole','dbo'

5.1.删除用户自定义数据库角色

　　语法：drop role testRole

　　proc:exec sp_droprole 'testRole'

6.创建数据库服务器登录名

　　T-Sql:create LOGIN lisi with password='111111'

　　Proc :exec sp_addlogin 'lisi' [,'111111' [,'master']]

7.修改登录名 名称和密码

　　alter login lisi with name=lisi_new

　　alter login lisi_new with password='abcdef'

8.删除登录名

　　T-Sql:drop login lisi_new

　　Proc :exec sp_droplogin 'lisi_new'

8.1.禁用登录名

　　alter login lisi_new disable

8.2.启用登录名

　　alter login lisi_new enable

9.创建数据库用户

　　T-Sql语法：create user user_name {for|from} login login_name

　　示例：use Sales go  create user myUser from login lisi_new

9.1.创建带有默认架构的数据库用户

　　语法：use Sales go

　　　　　　create user myUser from login lisi_new with default_schema=HRmanager

10.使用存储过程sp_helpuser查看数据库用户

11.删除数据库用户

　　T-Sql:drop user user_name

　　Proc :exec sp_dropuser 'user_name'

12.使用存储过程sp_addrolemember给数据库用户设置固定数据库角色功能

　　语法：exec sp_addrolemember 'role','security_account'

　　示例：exec sp_addrolemember 'db_datareader','myUser'

13.使用存储过程sp_droprolemember撤销给数据库用户所设置的固定数据库角色功能

　　语法：exec sp_droprolemember 'role','security_account'

　　示例：exec sp_droprolemember 'db_datareader','myUser'

总结：关于数据库服务器级的角色管理 与 数据库级角色管理 如下图