數(shù)據(jù)庫原理與應(yīng)用：第16章 SQL Server的安全管理

數(shù)據(jù)庫原理與應(yīng)用第16章SQLServer的安全管理

數(shù)據(jù)的安全性管理是數(shù)據(jù)庫管理系統(tǒng)應(yīng)實現(xiàn)的重要功能之一。SQLServer數(shù)據(jù)庫采用了如下的安全管理機(jī)制：對用戶登錄進(jìn)行身份認(rèn)證。當(dāng)用戶登錄到數(shù)據(jù)庫系統(tǒng)時，系統(tǒng)對該用戶的帳號和口令進(jìn)行認(rèn)證，包括確認(rèn)用戶賬號是否有效以及能否訪問數(shù)據(jù)庫系統(tǒng)。對用戶進(jìn)行的操作進(jìn)行權(quán)限控制。當(dāng)用戶登錄到數(shù)據(jù)庫后，只能對數(shù)據(jù)庫中的數(shù)據(jù)在允許的權(quán)限內(nèi)進(jìn)行操作。

一個用戶要對某一數(shù)據(jù)庫進(jìn)行操作，必須滿足以下3個條件：登錄SQLServer服務(wù)器時必須通過身份驗證必須是該數(shù)據(jù)庫的用戶，或者是某一數(shù)據(jù)庫角色的成員必須有執(zhí)行該操作的權(quán)限16.1SQLServer2000的身份認(rèn)證模式

1.WindowsNT認(rèn)證模式用戶登錄WindowsNT時進(jìn)行身份認(rèn)證，登錄SQLServer時不再進(jìn)行身份驗證。對于WindowsNT認(rèn)證模式登錄的幾點重要說明：

(1)必須將NT網(wǎng)絡(luò)賬號加入到SQLServer中，才能采用NT網(wǎng)絡(luò)賬號登錄SQLServer。

(2)如果使用NT網(wǎng)絡(luò)賬號登錄到另一個網(wǎng)絡(luò)的SQLServer，必須在NT網(wǎng)絡(luò)中設(shè)置彼此的托管權(quán)限。2.SQLServer認(rèn)證模式在SQLServer認(rèn)證模式下，SQLServer服務(wù)器要對登錄的用戶進(jìn)行身份驗證對于Windows9x系列的操作系統(tǒng)只能使用SQLServer認(rèn)證模式，而當(dāng)SQLServer在WindowsNT或Windows2000上運行時，系統(tǒng)管理員設(shè)定登錄認(rèn)證模式的類型可為WindowsNT認(rèn)證模式和混合模式。16.2.1WindowsNT認(rèn)證模式登錄賬號的建立與刪除1.WindowsNT認(rèn)證模式登錄賬號的建立1)通過企業(yè)管理器建立WindowsNT認(rèn)證模式的登錄賬號

Windows2000本地計算機(jī)管理界面

Windows2000本地計算機(jī)創(chuàng)建新用戶的界面16.2.1WindowsNT認(rèn)證模式登錄賬號的建立與刪除

在企業(yè)管理器中選擇“登錄”圖標(biāo)右擊SQLServer信任連接設(shè)置窗口16.2.1WindowsNT認(rèn)證模式登錄賬號的建立與刪除2)通過調(diào)用系統(tǒng)存儲過程建立WindowsNT認(rèn)證模式的登錄賬號創(chuàng)建WindowsNT、Windows2000的用戶或組后，使用系統(tǒng)存儲過程sp_grantlogin也可將一個WindowsNT或Windows2000的用戶或組的登錄賬號添加到SQLServer中，以便通過Windows身份驗證連接到SQLServer。語法格式：

sp_grantlogin[@loginame=]'login'

參數(shù)含義：

@loginame=：原樣輸入的常量字符串注意：(1)不能在用戶定義的事務(wù)內(nèi)執(zhí)行sp_grantlogin；(2)僅sysadmin

或securityadmin

固定服務(wù)器角色的成員可以執(zhí)行sp_grantlogin。EXECsp_grantlogin‘WYATT\wy’16.2.1WindowsNT認(rèn)證模式登錄賬號的建立與刪除

2.WindowsNT認(rèn)證模式登錄賬號的刪除

步驟如下：

(1)以系統(tǒng)管理員身份進(jìn)入企業(yè)管理器，并展開目錄樹；

(2)在目錄樹的“登錄”節(jié)點下，選中待刪除的“賬號”節(jié)點，2)通過調(diào)用系統(tǒng)存儲過程刪除WindowsNT認(rèn)證模式的登錄賬號通過執(zhí)行系統(tǒng)存儲過程sp_revokelogin可刪除WindowsNT用戶或組登錄SQLServer的賬號。1)通過企業(yè)管理器刪除WindowsNT認(rèn)證模式的登錄賬號16.2.2混合認(rèn)證模式下SQLServer登錄賬號的建立與刪除

(1)在企業(yè)管理器中，選擇要登錄的SQLServer服務(wù)器圖標(biāo)右擊，出現(xiàn)一快捷菜單，選擇菜單項“屬性”，出現(xiàn)如圖的SQLServer服務(wù)器屬性配置窗口。

(2)選擇“安全性”選項卡，如圖所示，選擇身份驗證方式為“SQLServer與Windows”，選擇“確定”按鈕。SQLServer服務(wù)器屬性配置窗口

SQLServer身份認(rèn)證方式配置窗口

16.2.2混合認(rèn)證模式下SQLServer登錄賬號的建立與刪除1.通過企業(yè)管理器創(chuàng)建SQLServer登錄賬號

(1)在企業(yè)管理器中選擇“登錄”圖標(biāo)右擊，出現(xiàn)快捷菜單，選擇“新建登錄”菜單項，進(jìn)入如下圖的界；

(2)輸入賬號名、密碼，選擇“SQLServer身份驗證”方式，點擊“確定”按鈕。SQLServer登錄賬號新建窗口

16.2.2混合認(rèn)證模式下SQLServer登錄賬號的建立與刪除2.利用系統(tǒng)存儲過程創(chuàng)建SQLServer登錄賬號語法格式：sp_addlogin[@loginame=]'login'

[,[@passwd=]'password']

[,[@defdb=]'database']

[,[@deflanguage=]'language']

[,[@sid=]sid]

[,[@encryptopt=]'encryption_option']16.2.2混合認(rèn)證模式下SQLServer登錄賬號的建立與刪除3.SQLServer登錄賬號的刪除

利用sp_droplogin系統(tǒng)存儲過程可刪除SQLServer登錄賬號。語法格式：

sp_droplogin[@loginame=]'login'

參數(shù)含義：

'login'：將被刪除的登錄賬號名。返回值：0（成功）或1（失?。Ｕf明：

(1)若要刪除一個數(shù)據(jù)庫現(xiàn)有用戶的登錄賬號，必須首先使用sp_dropuser

刪除該用戶。

(2)不能刪除系統(tǒng)管理員(sa)的登錄賬號。

(3)不能在用戶定義的事務(wù)內(nèi)執(zhí)行sp_droplogin。

(4)只有sysadmin

和securityadmin

固定服務(wù)器角色的成員才能執(zhí)行sp_droplogin。16.3服務(wù)器角色與數(shù)據(jù)庫角色在SQLServer中，通過角色可將用戶分為不同的類，對相同類用戶（相同角色的成員）進(jìn)行統(tǒng)一管理，賦予相同的操作權(quán)限，SQLServer給用戶提供了預(yù)定義的服務(wù)器角色（固定服務(wù)器角色）和數(shù)據(jù)庫角色（固定數(shù)據(jù)庫角色），固定服務(wù)器角色和固定數(shù)據(jù)庫角色都是SQLServer內(nèi)置的，不能進(jìn)行添加、修改和刪除。用戶可根據(jù)需要，創(chuàng)建自已的數(shù)據(jù)庫角色。16.3.1固定服務(wù)器角色

服務(wù)器角色獨立于各個數(shù)據(jù)庫，如果在SQLServer中創(chuàng)建一個登錄賬號后，要賦予該登錄者具有管理服務(wù)器的權(quán)限，此時可設(shè)置該登錄賬號為服務(wù)器角色的成員。SQLServer提共了如下固定服務(wù)器角色：Sysadmin:系統(tǒng)管理員，可對SQLServer服務(wù)器進(jìn)行所有的管理工作，為最高管理角色。Securityadmin:安全管理員，可以管理登錄和CREATEDATABASE權(quán)限，還可以讀取錯誤碼日志和更改密碼。Serveradmin:服務(wù)器管理員，具有設(shè)置及關(guān)閉服務(wù)器的權(quán)限。Setupadmin:設(shè)置管理員，添加和刪除鏈接服務(wù)器，并執(zhí)行某些系統(tǒng)存儲過程。Processadmin:進(jìn)程管理員，可以管理運行在SQLServer中的進(jìn)程。Diskadmin:可以管理磁盤文件。Dbcreator:數(shù)據(jù)庫創(chuàng)建者，可以創(chuàng)建、更改和刪除數(shù)據(jù)庫。Bulkadmin:可執(zhí)行bulkinsert語句。16.3.1固定服務(wù)器角色

(1)以系統(tǒng)管理員身份登錄到SQLServer服務(wù)器，在登錄圖標(biāo)對應(yīng)的列表項中，選擇登錄賬號“DLGC-YPOOLOLRW4\wy”的項目雙擊；

(2)選擇“服務(wù)器角色”選項卡，選項卡中列出了SQLServer所有的固定服務(wù)器角色，將“Systemadministrators”服務(wù)器角色前的復(fù)選框選中。1.通過企業(yè)管理器添加服務(wù)器角色成員SQLServer服務(wù)器角色設(shè)置窗口16.3.1固定服務(wù)器角色

2.利用系統(tǒng)存儲過程添加固定服務(wù)器角色成員

利用系統(tǒng)存儲過程sp_addsrvrolemember可將一登錄賬號添加到某一固定服務(wù)器角色中，使其成為固定服務(wù)器角色的成員。說明：

(1)將登錄賬號添加到固定服務(wù)器角色時，該登錄就會得到與此固定服務(wù)器角色相關(guān)的權(quán)限。

(2)不能更改sa角色成員資格。

(3)不能在用戶定義的事務(wù)內(nèi)執(zhí)行sp_addsrvrolemember

存儲過程。

(4)

sysadmin

固定服務(wù)器的成員可以將登錄賬號添加到任何固定服務(wù)器角色，其他固定服務(wù)器角色的成員可以執(zhí)行sp_addsrvrolemember

將登錄賬號添加到同一個固定服務(wù)器角色。16.3.1固定服務(wù)器角色

3.利用系統(tǒng)存儲過程刪除固定服務(wù)器角色成員

利用sp_dropsrvrolemember系統(tǒng)存儲過程可從固定服務(wù)器角色中刪除SQLServer登錄賬號或WindowsNT用戶或組。說明：

(1)不能刪除sa

登錄賬號。

(2)不能在用戶定義的事務(wù)內(nèi)執(zhí)行sp_dropsrvrolemember。

(3)sysadmin固定服務(wù)器角色的成員執(zhí)行sp_dropsrvrolemember，可刪除任意固定服務(wù)器角色中的登錄賬號，其他固定服務(wù)器角色的成員只可以刪除相同固定服務(wù)器角色中的其他成員。16.3.2固定數(shù)據(jù)庫角色

1.固定數(shù)據(jù)庫角色(1)db_owner：數(shù)據(jù)庫所有者，可執(zhí)行數(shù)據(jù)庫的所有管理操作。(2)db_accessadmin：數(shù)據(jù)庫訪問權(quán)限管理者，具有添加、刪除數(shù)據(jù)庫使用者、數(shù)據(jù)庫角色和組的權(quán)限。(3)db_securityadmin：數(shù)據(jù)庫安全管理員，可管理數(shù)據(jù)庫中的權(quán)限，如設(shè)置數(shù)據(jù)庫表的插入、刪除、修改和查詢等存取權(quán)限。(4)db_ddladmin：數(shù)據(jù)庫DDL管理員，可增加、修改或刪除數(shù)據(jù)庫中的對象。(5)db_backupoperator：數(shù)據(jù)庫備份操作員，具有執(zhí)行數(shù)據(jù)庫備份的權(quán)限。(6)db_datareader：數(shù)據(jù)庫數(shù)據(jù)讀取者。(7)db_datawriter：數(shù)據(jù)庫數(shù)據(jù)寫入者，具有對表進(jìn)行插入、刪除和修改的權(quán)限。(16)db_denydatareader：數(shù)據(jù)庫拒絕數(shù)據(jù)讀取者，不能讀取數(shù)據(jù)庫中任何表的內(nèi)容(9)db_denydatawriter：數(shù)據(jù)庫拒絕數(shù)據(jù)寫入者，不能對任何表進(jìn)行插入、刪除和修改操作。(10)public：是一個特殊的數(shù)據(jù)庫角色，每個數(shù)據(jù)庫用戶都是public角色的成員，因此，不能將用戶、組或角色指派為public角色的成員，也不能刪除public角色的成員。16.3.2固定數(shù)據(jù)庫角色2.數(shù)據(jù)庫用戶的操作權(quán)限(1)在當(dāng)前數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫對象及進(jìn)行數(shù)據(jù)庫備份的權(quán)限，主要有：創(chuàng)建表、視圖、存儲過程、規(guī)則、缺省值對象、函數(shù)的權(quán)限及備份數(shù)據(jù)庫、日志文件的權(quán)限。(2)用戶對數(shù)據(jù)庫表的操作權(quán)限及執(zhí)行存儲過程的權(quán)限，主要有：SELECT：對表或視圖執(zhí)行SELECT語句的權(quán)限；INSERT：對表或視圖執(zhí)行INSERT語句的權(quán)限；UPDATE：對表或視圖執(zhí)行UPDATE語句的權(quán)限；DELETE：對表或視圖執(zhí)行DELETE

語句的權(quán)限；EXECUTE：執(zhí)行存儲過程的權(quán)限。(3)用戶對數(shù)據(jù)庫中指定表字段的操作權(quán)限，主要有：SELECT：對表字段進(jìn)行查詢操作的權(quán)限；UPDATE：對表字段進(jìn)行更新操作的權(quán)限。16.3.3用戶自定義數(shù)據(jù)庫角色

1.通過企業(yè)管理器創(chuàng)建數(shù)據(jù)庫角色

(1)創(chuàng)建數(shù)據(jù)庫角色：以系統(tǒng)管理員身份登錄SQLServer，并進(jìn)入企業(yè)管理器，選中目錄樹school數(shù)據(jù)庫結(jié)點的“角色”圖標(biāo)右擊，出現(xiàn)一快捷菜單，選擇“新建數(shù)據(jù)庫角色”，進(jìn)入如圖的界面，輸入角色名，選擇確定按鈕。新建數(shù)據(jù)庫角色的屬性界面

16.3.3用戶自定義數(shù)據(jù)庫角色

(2)創(chuàng)建數(shù)據(jù)庫用戶并加入數(shù)據(jù)庫角色。即在某一數(shù)據(jù)庫中為SQLServer服務(wù)器的登錄賬號或WindowsNT的登錄賬號創(chuàng)建一數(shù)據(jù)庫用戶賬號，將數(shù)據(jù)庫用戶加入該數(shù)據(jù)庫中的某一角色，即：使數(shù)據(jù)庫用戶成為某一角色的成員。

新建數(shù)據(jù)庫用戶的屬性界面

16.3.3用戶自定義數(shù)據(jù)庫角色

(3)給數(shù)據(jù)庫角色賦予創(chuàng)建數(shù)據(jù)庫對象的權(quán)限：在企業(yè)管理器目錄樹中，選擇school數(shù)據(jù)庫結(jié)點右擊，出現(xiàn)一快捷菜單，選擇菜單項“屬性”，進(jìn)入如圖所示的界面，選擇“權(quán)限”選項卡，選中允許數(shù)據(jù)庫角色或數(shù)據(jù)庫用戶執(zhí)行的權(quán)限。

(4)

給數(shù)據(jù)庫角色賦予表操作權(quán)限：在企業(yè)管理器的目錄樹中，選擇school數(shù)據(jù)庫結(jié)點下角色圖標(biāo)的項目“ROLE”雙擊，出現(xiàn)如圖所示的界面。設(shè)置創(chuàng)建數(shù)據(jù)庫對象權(quán)限的窗口

數(shù)據(jù)庫角色的屬性窗口

16.3.3用戶自定義數(shù)據(jù)庫角色

數(shù)據(jù)庫表操作權(quán)限設(shè)置窗口

表的列操作權(quán)限設(shè)置窗口

16.3.3用戶自定義數(shù)據(jù)庫角色

2.通過SQL命令創(chuàng)建數(shù)據(jù)庫角色1)定義數(shù)據(jù)庫角色語法格式：sp_addrole[@rolename=]'role'

[,[@ownername=]'owner']說明：(1)角色名可以包括字母、符號及數(shù)字。但是不能含有反斜線(\)。(2)不能在用戶定義的事務(wù)內(nèi)使用sp_addrole。(3)只有sysadmin

固定服務(wù)器角色及db_securityadmin

和db_owner

固定數(shù)據(jù)庫角色的成員才能執(zhí)行sp_addrole。16.3.3用戶自定義數(shù)據(jù)庫角色

2)將一個登錄賬號添加為某個數(shù)據(jù)庫的用戶

利用系統(tǒng)存儲過程sp_grantdbaccess可將一個登錄賬號添加為某個數(shù)據(jù)庫的用戶。語法格式：

sp_grantdbaccess[@loginame=]'login'

[,[@name_in_db=]'name_in_db']說明：(1)數(shù)據(jù)庫用戶名可含有字母、符號和數(shù)字。但不能包含反斜線，不能為NULL，也不能為空字符串('')。(2)sp_grantdbaccess

僅可以在當(dāng)前數(shù)據(jù)庫中添加用戶（賬戶），若要從數(shù)據(jù)庫中刪除賬戶，使用sp_revokedbaccess。(3)如果當(dāng)前數(shù)據(jù)庫中沒有g(shù)uest賬戶，而且login為guest，則可將guest添加為當(dāng)前數(shù)據(jù)庫的賬戶。(4)sa

登錄賬號不能添加到數(shù)據(jù)庫中。(5)不能從用戶定義的事務(wù)中執(zhí)行sp_grantdbaccess。(6)只有sysadmin

固定服務(wù)器角色、db_accessadmin

和db_owner

固定數(shù)據(jù)庫角色的成員才能執(zhí)行sp_grantdbaccess；(7)存儲過程sp_adduser的功能與sp_grantdbaccess的功能相同。16.3.3用戶自定義數(shù)據(jù)庫角色

3)給數(shù)據(jù)庫角色添加成員利用系統(tǒng)存儲過程sp_addrolemember可將當(dāng)前數(shù)據(jù)庫的用戶或角色添加到當(dāng)前數(shù)據(jù)庫的某個角色中，使其成為該角色的成員。語法格式：

sp_addrolemember[@rolename=]'role',

[@membername=]security_account'說明：(1)當(dāng)使用sp_addrolemember

將用戶賬號添加到角色時，新成員將繼承所有應(yīng)用到角色的權(quán)限。(2)不能將固定數(shù)據(jù)庫或固定服務(wù)器角色，或者dbo

添加到其他角色。例如，不能將db_owner

固定數(shù)據(jù)庫角色添加成為用戶定義的角色YourRole

的成員。(3)在用戶定義的事務(wù)中不能使用sp_addrolemember。(4)只有sysadmin

固定服務(wù)器角色和db_owner

固定數(shù)據(jù)庫角色中的成員可以執(zhí)行sp_addrolemember，將用戶賬號添加到固定數(shù)據(jù)庫角色。(5)db_securityadmin

固定數(shù)據(jù)庫角色的成員可以將用戶添加到任何用戶定義的角色。16.3.3用戶自定義數(shù)據(jù)庫角色

4)數(shù)據(jù)庫用戶、角色操作權(quán)限的授予、拒絕和收回

(1)數(shù)據(jù)庫用戶、角色操作權(quán)限的授予利用GRANT語句可以給數(shù)據(jù)庫用戶或數(shù)據(jù)庫角色賦予執(zhí)行T-SQL語句的權(quán)限及對數(shù)據(jù)庫對象進(jìn)行操作的權(quán)限。語法格式：GRANT{ALL|statement[,...n]}TOsecurity_account[,...n]功能：授予執(zhí)行T-SQL語句的權(quán)限。GRANT

{ALL[PRIVILEGES]|permission[,...n]}

{

[(column[,...n])]ON{table|view}

|ON{table|view}[(column[,...n])]

|ON{stored_procedure|extended_procedure}

|ON{user_defined_function}

}TOsecurity_account[,...n][WITHGRANTOPTION][AS{group|role}]16.3.3用戶自定義數(shù)據(jù)庫角色

3.數(shù)據(jù)庫用戶、角色的刪除

1)刪除數(shù)據(jù)庫用戶在企業(yè)管理器中刪除數(shù)據(jù)庫用戶很簡單，直接在目錄樹的某一數(shù)據(jù)庫結(jié)點下，選中須刪除的用戶項目，按“Del”鍵即可。語法格式：

sp_revokedbaccess[@name_in_db=]'name'說明：(1)在用戶定義事務(wù)內(nèi)部不能執(zhí)行sp_revokedbaccess。(2)只有sysadmin固定服務(wù)器角色成員及db_accessadmin

和db_owner

固定數(shù)據(jù)庫角色成員才能執(zhí)行sp_revokedbaccess。16.3.3用戶自定義數(shù)據(jù)庫角色

2)刪除數(shù)據(jù)庫角色

要刪除用戶自定義的數(shù)據(jù)庫角色，首先應(yīng)刪