第9 SQL的安全管理

9.1 SQL的安全认证模式

用户使用SQL,需要两个安全性阶段:

1>身份验证阶段:决定该用户可否连接到SQL服务器上。
2>权限认证阶段:决定该用户有对哪些数据的哪些操作的权限。


9.2身份验证

  SQL与WINDOWS是结合在一起的,它们会产生两种验证模式:

1.Windows身份验证模式

  它使用windows操作系统的安全机制来验证用户身份,只要用户能通过windows用户身份验证,即可连

接到SQL Server 2003服务器上。(只适于win os)

2.混合身份验证模式(windows和SQL身份验证)

  使用哪个模式取决于最初的通信时使用的网络库。
1)如果是TCP/IP Sockets登录验证:使用SQL SERVER 验证模式。
2)如果用户使用命名管道:则登录时使用WINDOWS验证模式。


9.3 权限认证

  若没有通过数据库的权限认证,即使用连接到SQL Server服务器上,也无法使用数据库。

9.4 创建SQL SERVER登录用户 (登陆SQL用的帐户)

1.使用系统存储过程创建登录帐户

语法格式:
sp_addlogin[@loginame]='login'[,[@passwd=]'password'][,[@defdb=] 'database']
[,[@deflanguage=]'language'][,[@sid=]sid][,[@encryptopt=]'endcryption_option']


其中:
[@loginame]='login'  : 登录帐号名称
[@passwd=]'password' : 登录密码
[@defdb=] 'database' : 登录的默认数据库名.
[@deflanguage=]'language' : 使用的语言.
[@sid=]sid  : 为安全标识号.
@encryptopt=]'endcryption_option' : 为指定当密码存储在系统表中时,密码是否需要加密.

说明:使用的语言如果指定为NULL,则表示使用系统默认语言。


例1 建立登录账号my_login,密码123456,默认数据库是book

EXEC sp_addlogin 'my_login','123456','book'

说明:“登录帐号”指的是用于与SQL连接的帐号,但没有操作数据库的权限。这里默认数据库是book,

登陆时可以会出现“无法打开默认数据库”的情况,只需要把默认数据库改为master即可。

9.5 创建数据库的用户(操作数据库用的帐户)

由上得知,SQL有两种帐号:

1>登录服务器的登录帐号。  (该帐号用来登陆,连接SQL)
2>使用数据库的用户帐号。  (该帐号用来访问数据库)

  用户帐号的用户名要在特定的数据库内创建,并关联一个已存在的“登陆帐号”,这样该登陆帐号就

可以对已关联的数据库操作。用户定义的信息存放在服务器的每个数据库的SYSUERS表中。

1.使用存储过程创数据库用户

1>使用sp_grantdbaccess
语法格式:
sp_grantdbaccess [@logginame=]'login'[,[@name_in_db=]'name_in_db']

其中:
[@logginame=]'login':表示当前数据库中新帐户的登录名称。
[@name_in_db=]'name_in_db']:表示数据库中帐户的名称。

2>使用sp_adduser
语法格式:
sp_adduser[@logginame=]'login'[,[@name_in_db=]'user'][,[@grpname=]'group']

其中:
[@logginame=]'login':表示登录名称。
[@name_in_db=]'user':表示用户帐号。
[@grpname=]'group':表示组或所属的数据库角色,新用户自动地成为成员。

例3 在book数据库中,添加一个名为b_user1的用户帐号

use book
go
EXEC sp_addlogin 'b_login','123456','book'

#添加一名b_login的登陆帐户,密码为123456,默认数据库为book.#

go
EXEC sp_adduser 'b_login','b_user1',db_owner

#在book上(因为我们最开始有use book来开打这个数据库)建立用户帐号b_user1, 并与b_login登陆帐号

关联(一个用户帐号必须要关联一个或多个未被其它用户帐号关联的“登陆帐号”),使该登陆帐号可使

用book数据库。其中b_login登陆帐号可以已建好的任何登陆帐号。db_owner是属于数据库的角色类型#

 说明:这里的sp_addlogin只是添加一个登帐帐号,只能登录到SQL服务器上,并不对book数据库具有存

取权限,所以,还要利用sp_adduser将登录帐号加入到每时定的数据库中才行。


9.6安全管理帐户
1.查看服务器的登录帐号
  用SQL操作界面查看.

2.修改登录属性

1>使用sp_password改变登录帐号的密码

语法格式:
EXEC sp_password 'old_password','new_password','login'

其中:
'login':表示登录帐号名称.

例4 将“登陆帐号”b_login的密码由 zhouqi改为zhouqiqi

use book
go
EXEC sp_password 'zhouqi','ahouqiqi','b_login'

2>使用sp_addsrvrolemember将登陆账号加入服务器角色

语法格式:
EXEC sp_addsrvrolemember 'login','role'

其中:
'login'表示:添加到服务器角色的登陆名称。
'role'表示:服务器角色名称

(sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin)

例5 将登陆帐号b_login加入到dbcreator服务器角色中

use book
go
EXEC sp_addsrvrolemember 'b_login','dbcreator'

3>改变登陆帐号和用户帐号之间的关系。

语法格式:
sp_change_users_login 'action','user','login'

其中:
action:说明过程要执行的操作。如:Update_one(将当前数据库中指定的用户接到已存在的登录帐号)

例6 将book数据库中的用户zhouqi与现有登录之间的连接变更连接到新的登录book_newlogin帐号上。

use book
go
EXEC sp_addlogin 'book_newlogin'  /*添加新的登当帐户*/
go                                /*将现有连接变更为book_newlogin*/
EXEC sp_change_users_login 'Update_One','zhouqi','book_newlogin'


9.6.3查看数据库的用户

1.使用sp_helpuser

例7 列出目前book数据库中所有的数据库用户帐户

use book
go
EXEC sp_helpuser


9.7删除登录帐号和用户帐号

1.删除登录帐号

语法格式:sp_droplogin 'login'

例8 删除b_login登录帐号

EXEC sp_droplogin 'b_login'

2.删除用户帐号

语法格式:sp_revokedbaccess 'name'

例9 删除book数据库中b_user1用户

use book
go
EXEC sp_revokedbaccess 'b_user1'


9.8 管理数据库用户和角色

1.服务器角色(属全局层,不可创建)

  它是负责管理维护SQL的组.

1>sysadmin(system administrator):可以在SQL中执行任何活动。
2>serveradmin(server administrator):可以设置服务器范围的配置选项及关闭服务器。
3>setuupadmin(Setup Administrator):管理连接服务器和启动过程。
4>securityadmin(Security Administrator):管理登录和创建数据库的权限与读取错误日志和更改密码
5>processadmin(Process Administrator):管理在SQL中运行的进程。
6>dbcreator(Database Creators):创建、更改和删除数据库。
7>diskadmin(Disk Administraotrs):管理磁盘文件。
8>bulkadministrator(Bulk Administrator):执行BULK INSERT(大容量插入)语句。


2.数据库角色(类似于WIN帐号管理中的"组",并在某数据库内建,属局部层)
 
  角色可以将用户集中到一个单元,然后,对该单元应用权限。
  数据库角色(Database Roles):即用户帐号的分组。SQL中,数据库角色有两种:

1>标准角色
  它是由数据库成员所组成的组,此成员可以是用户或者其他的数据库角色。在创建一个数据库时,系

统默认创建10个固定的标准角色。

Db_owner:在数据库中有全部权限。
Bb_accessadmin:可添加和删除用户ID
Db_securityadmin:可管理全部权限、对象所有权限,拥有角色和角色成员资格
Bb_ddladmin:可发出除crant,revke,deny之外的所有数据定义语句
Bb_backupoperator:可发出DBCC,CHECKPOINT和BACHUP语句
Bb_datareader:可选择数据库内任何用户表中的所有数据
Bb_datawriter:可更改数据库内任何表中的所有数据
Db_denydatareader:不能选择数据库内任何用户表中的任数据库
Bb_denydatawriter:不能更改数据库内任何用户表中的任何数据。
Public:最基本的数据库角色。

注:当数据库添加新用户帐号时,SQL会自动将新的用户加入pulic数据库角色中。


2>应用程序角色

  它用来控制应用程序存取数据的,本身并不包括任何成员。

3>创建新角色

1)使用sp_addrole在当前数据库中创建新角色,语法格式:
 sp_addrole 'role','owner'

其中:
role:表示新角色的名称
owner:表示新建角色的拥有者

例10 在book中创建一个名为myrole的数据库角色

use book
go
EXEC sp_addrole 'myrole','dbo'

2>使用sp_addapprole创建应用程序角色,语法格式:

sp_addapprole 'role','password'

其中:
role表示:新角色的名称;
password表示:激活角色所需的密码,以加密形式存储.

例11 利用sp_addapprole在当前数据库中建立一个应用程序解色approle

use book
go
EXEC sp_addapprole 'approle','123456'

4>查看角色的属性

  使用SQL界面操作

5>删除角色

1)使用sp_droprole从当前数据库删除指定的"数据库角色",语法格式:
 sp_droprole 'role'

例12 删除book数据库中的myrole数据库角色。

use book
go
EXEC sp_droprole 'myrole'

2)删除“应用程序角色“
use book
go
EXEC sp_dropapprole 'approle'

6>用户和角色的权限问题

  用户和角色权限关系与继承关系,与WIN帐户的用户与组的关系的原理一模一样。


9.9 设置数据库用户帐号的权限

  用户登陆到SQL服务器后,角色和用户的权限被决定 对数据库所有能执行的操作权限。

1.SQL中有三类权限:

1>对象权限

  它决定用户操作的数据库对象(表,视图,列,存储过程等对象)

2>语句权限

BACKUP DATABASE:备份数据库
BACKUP LOG:备份数据库日志
CREATE DATABASE:创建数据库
CREATE DEFAULT:在数据库中创建默认对象
CREATE FUNCTION:创建函数
CREATE PROCEDURE:在数据库中创建存储过程
CREATE RULE:在数据库中创建规则
CREATE TABLE:在数据库中创建建
CREATE VIEW:在数据库中创建视图

3>隐含权限

  它控制那些只能预定义系统角色的成员或数据库对象所有者执行的活动。


2.设置权限

1>授予权限

1)授予语句权限的语法格式:
GRANT{ALL | statement[,...n]}
TO security_account[,...n]

2)授予对象权限的语法格式:

GRANT
{ALL[PRIVILEGES] | permission [,...n]}
{
[(column[,...n])] ON {table | view }
| ON {table | view } [(column [,...n])]
| ON {stored_procedure | extended_procedure}
| ON {user_defined_function}
}
TO security_account[,...n]
[WITH GRANT OPTION]
[AS | group | role)]

说明:WITH GRANT OPTION决定该权限可否被传播。

例13 给已存在的用户book_user1和book_user2授予创建数据库与表的权限。

use master
go
CRANT CREATE DATABASE,CREATE TABLE
TO book_user1,book_user2

例14 给用户book_user1,book_user2授予对book1表的所有权限。
说明:先给public授予select权(任何用户被创建默认就自动加入到public角色,故都有public所有的权

限),然后特定的权限授予book_user1,book_user2.

use book
go
CRANT SELECT ON book1 to public
go
grant insert,update,delete on book1 to book_user1,book_user2

2>拒绝权限(与授予权限的关系原理,如同WIN中帐户中的一样)

1)拒绝语句权限的语法:
DENY {ALL | statement [,...n]} TO security_account[,...n]

2)拒绝对象权限的语法:
DENY
{ALL [PRIVILEGES] | permission [,...n] }
{
[(column[,...n])] ON {table | view }
| ON {table | view } [(column [,...n])]
| ON {stored_procedure | extended_procedure}
| ON {user_defined_function}
}
TO security_account[,...n]
[cascade]


例15 拒绝给用户book_user1和book_user2授予多个语句权限.

use master
go
DENY CREATE DATABASE,CREATE TABLE
TO book_user1,book_user2


例16 拒绝给用户book_user1和book_user2授予对book1表的所有权限.
说明:先给public角色删除select权限,然后,拒绝给用户book_user1和book_user2授予特定权限.

use book
go
revoke select on book1 to public
go
deny insert,update,delete on book1 to book_user1,book_user2
go

3>撤销权限

  撤销以前给当前数据库内的用户授予或拒绝的权限,通过REVOKE语句完成。

例17 撤销授邓用户帐号book_user1的CREATE TABLE权限

revoke create table from book_user1

例18 撤销授予多个用户帐户的多个权限.

revoke create table,create default
from book_user1,book_user2

  SQL有非常灵活的授权机制,数据库管理员拥有对数据库中所有对象的所有权限,并可以根据应用的需

要将不同的权限授予不同的用户。

  用户对自己建立的表和视图有全部的操作权限,并可用GRANT语句把其中某些权限授予其他用户,若被

授权的用户有“继续授权”许可,还可把获得的权限再授予其它用户。

  所授予出去的权限在必要时又可用REVOKE语句撤销。(它仅适用于当前数据库内的权限)

============补充===============
1创建登录帐户
     添加windows登录帐户需要调用sql server 内置的系统存储过程sp_grantlogin,
语法:exec sp_grantlogin ‘windows 域名\域帐户’

exec sp_grantlogin 'berlin\admin'
注释:把计算机berlin中的用户admin创建为SQL的“登陆帐户”,admin必须要存在。


 添加sql 登录帐户需要调用系统存储过程sp_addlogin,
 语法:exec sp_addlogin '帐户', '密码'

exec sp_addlogin 'test','test123'
/*添加名为test帐户作为SQL的登录帐户*/

2 创建数据库用户
  需要调用系统存储过程sp_grantdbaccess
  语法:exec sp_grantdbaccess '登录帐户','数据库用户'

use test
go
exex sp_grantdbaccess 'test','我是test'

这样就会在当前数据库中创建一与登陆用户test相关联的数据库用户'我是test',以便这个用户可以访问test数据库,但只能访问数据库,不能访问表。

3给数据库用户授权
  语法:grant 权限[on 表名] to 数据库用户

要想数据库用户'我是test'能访问表,则需要赋予它权限。

grant select,update,insert on userinfo to 我是test

把对表userinfo的'select,insert,update权限赋予了数据库用户‘我是test',而'我是test'是与登陆帐户'test'是相关联的,故用test登帐户,即可直接对userinfo作select,update,insert操作。

=====================================
小结:
一、使用sql 语句创建登录
1创建登录帐户
  添加windows登录帐户需要调用sql server 内置的系统存储过程sp_grantlogin,
语法:exec sp_grantlogin ‘windows 域名\域帐户’

 添加sql 登录帐户需要调用系统存储过程sp_addlogin,
 语法:exec sp_addlogin '帐户', '密码'

2 创建数据库用户
  需要调用系统存储过程sp_grantdbaccess
  语法:exec sp_grantdbaccess '登录帐户','数据库用户'

3给数据库用户授权
  语法:grant 权限[on 表名] to 数据库用户