SQL Server,第16章數(shù)據(jù)庫安全及訪問控制,16.1 SQL Server安全認證模式與設置 16.2 SQL Server登錄賬戶的管理 16.3 數(shù)據(jù)庫訪問權限的建立與刪除 16.4 角色管理 16.5 數(shù)據(jù)庫權限管理,第16章數(shù)據(jù)庫安全及訪問控制,16.1 SQL Server安全認證模式與設置,16.1.1 用戶安全認證模式 16.1.2 設置安全認證模式,16.1 SQL Server安全認證模式與設置,SQL Server作為DBMS，采用了三個層次的安全控制策略： 1、用戶首先登錄到數(shù)據(jù)庫服務器上(是服務器合法用戶) 2. 然后使服務器用戶（login）成為某個數(shù)據(jù)庫的合法用戶，從而能夠訪問數(shù)據(jù)庫。 3. 讓數(shù)據(jù)庫用戶在數(shù)據(jù)庫中具有一定的權限(數(shù)據(jù)操作權、創(chuàng)建對象權等),16.1.1 用戶安全認證模式,安全帳戶認證是用來確認登錄SQL Server的用戶的登錄帳號和密碼的正確性，由此來驗證其是否具有連接SQL Server的權限。 SQL Server 提供了兩種確認用戶的認證模式： （一）Windows NT認證模式。 （二）混合認證模式。,（一）Windows NT認證模式,SQL Server數(shù)據(jù)庫系統(tǒng)通常運行在Windows NT服務器平臺上，而NT作為網(wǎng)絡操作系統(tǒng)，本身就具備管理登錄、驗證用戶合法性的能力，因此Windows NT認證模式正是利用了這一用戶安全性和帳號管理的機制，允許SQL Server也可以使用NT的用戶名和口令。在這種模式下，用戶只需要通過Windows NT的認證，就可以連接到SQL Server，而SQL Server本身也就不需要管理一套登錄數(shù)據(jù)。,需要注意的是：登錄前必須將Windows NT賬號加入到SQL Server 中，才能采用Windows NT賬號登錄到SQL Server上。,（二）混合認證模式,混合認證模式允許用戶使用Windows NT安全性或SQL Server安全性連接到SQL Server。在這種方式下，對于可信連接用戶的連接請求，系統(tǒng)將采用Windwos NT/2003認證模式，而對于非可信連接用戶則采用SQL Server認證模式。采用SQL Server模式認證時，系統(tǒng)檢查是否已經(jīng)建立了該用戶的登錄標識以及二者的口令是否相同。通過認證后，用戶應用程序才可連接到SQL Server服務器，否則系統(tǒng)將拒絕用戶的連接請求。,SQL Server對登錄標識的認證過程可用下圖表示：,無論采用以上哪種認證模式，在用戶連接到SQL Server后，他們的操作完全相同。比較起來，兩種認證模式各有優(yōu)劣： （1）Windows認證更為安全。 （2）SQL Server認證管理較為簡單，它允許應用程序的所有用戶使用同一登錄標識。 為了便于用戶帳戶的集中管理，在Windows NT/2003平臺下，最好選用Windows認證模式。,16.1.2 設置安全認證模式,其主要過程如下： 1. 打開SSMS，用右鍵單擊要設置認證模式的服務器，從快捷菜單中選擇“屬性（properties）”選項，則出現(xiàn)SQL Server屬性對話框。 2. 在SQL Server屬性對話框中選擇安全性選項。 3. 在安全性選項欄中，身份驗證中可以選擇要設置的認證模式，同時審核級別中還可以選擇跟蹤記錄用戶登錄時的哪種信息，例如登錄成功或登錄失敗的信息等。 4. 在啟動服務帳戶中設置當啟動并運行SQL Server時默認的登錄者中哪一位用戶。,16.2 SQL Server登錄賬戶的管理,16.2.1 Windows NT登錄帳戶建立與刪除 16.2.2 SQL Server登錄賬戶建立與刪除,系統(tǒng)內(nèi)置已有的登錄帳號: SQL Server有三個默認（內(nèi)置）的用戶登錄帳號：即sa、 builtinadministrators和guest。 Sa：SQL Server驗證模式的系統(tǒng)管理員帳號； builtinadministrators： 是一個windows組帳號，表示所有windows系統(tǒng)管理員（Administrator）組中的用戶都可以登錄到SQL Server; Guest:是來賓帳號。,16.2.1 Windows NT登錄帳戶建立與刪除,1、建立其他新的Windows NT/2003賬戶 操作步驟如下： 以Administrators登錄到Windows 2003； 選擇“開始”“設置” “控制面板” “管理工具” “計算機管理”； 在計算機管理窗口，選擇“本地用戶和組”，單擊右鍵，在快捷菜單上單擊“新用戶”，進入如下圖界面； 在“新用戶”對話框中輸入新用戶名和密碼（這里用戶名是meng，密碼是1111）； 單擊【確定】按鈕，一個新的Windows NT/2003賬戶建立成功。,2.將Windows NT/2003賬戶加入到SQL Server中 方法一：使用系統(tǒng)存儲過程 在SQL Server中，授予Windows NT/2003用戶或用戶組連接SQL Server服務器的權限。其語法格式為： sp_grantlogin loginame=login 其中， login是Windows NT/2003用戶或用戶組名稱，其格式為“域用戶名稱” 。對于本地用戶或組，則域名即為本地計算機名，其格式為“計算機名用戶名稱”。 方法二：使用,例：將新建的Windows NT賬戶meng用系統(tǒng)存儲過程添加到SQL Server系統(tǒng)中。 EXEC sp_grantlogin ZUFE-MXHmeng - ZUFE-MXH是計算機名，meng是Windows 2003用戶,例：將Windows NT賬戶deng用方法添加到SQL Server系統(tǒng)中 。,3.用新建Windows 2003用戶登錄SQL Server 如果想用新建Windows 2003用戶登錄SQL Server，首先將登錄的默認數(shù)據(jù)庫選擇成用戶自己建立的數(shù)據(jù)庫（如果默認數(shù)據(jù)庫是master，新建用戶登錄權限限制不起作用）。 在建立訪問該數(shù)據(jù)庫權限的基礎上，然后可以在Windows 2003中選擇“開始”“關機”，注銷原來的賬戶，用新的賬戶（比如meng）登錄Windows 2003。登錄成功后，啟動SSMS就可以新的用戶自動進入SQL Server。,4.廢除Windows NT用戶和SQL Server的連接 系統(tǒng)存儲過程sp_grantlogin所添加的登錄標識均存儲在SQL Server的syslogins系統(tǒng)表中。以系統(tǒng)管理員身份調(diào)用系統(tǒng)存儲過程sp_revokelogin或從ssms中能夠將它們從syslogin系統(tǒng)表中刪除，這時在登錄窗口中被刪除的登錄標識也將不再存在（需要刷新界面）。 方法一：使用系統(tǒng)存儲過程 語法格式： sp_revokelogin loginame=login 參數(shù)說明： login是待刪除的SQL Server服務器登錄標識。 方法二：使用ssms,16.2.2 SQL Server登錄賬戶建立與刪除,在Windows NT/2003環(huán)境下，如果要使用SQL Server登錄標識登錄SQL Server，首先應將SQL Server的認證模式設置為混合模式。設置成混合認證模式后，可以使用系統(tǒng)存儲過程或ssms創(chuàng)建SQL Server登錄標識。,1.創(chuàng)建SQL Server登錄標識 方法一：使用系統(tǒng)存儲過程 語法格式： sp_addlogin loginame=login ,passwd=password ,defdb=database ,deflanguage=language ,sid=sid ,encryptopt=encryption_option,參數(shù)說明： Login為注冊標識或SQL Server用戶名，長度為1到128個字符，其中可以包括字母、符號和數(shù)字，但不能是空字符串，不能包含，不能與現(xiàn)有登錄標識同名； Passwd為口令，默認口令是NULL（即不需要口令），用戶可以在任何時候使用； Database指定用戶在注冊時連接到的默認數(shù)據(jù)庫，如果沒有指定默認數(shù)據(jù)庫，則默認數(shù)據(jù)庫是master； sid 是新建登錄標識的安全標識號，一般由系統(tǒng)自動建立。 encryption_option說明登錄標識口令是否需要加密存儲到系統(tǒng)表中，其數(shù)據(jù)類型為varchar(20),它有以下三種取值： NULL：默認設置，口令加密存儲； skip_encryption：要求不要加密口令。 skip_encryption_old：所提供的口令被SQL Server前期版本加密，這種取值主要用于早期版本數(shù)據(jù)庫的升級。 language說明用戶注冊到SQL Server時使用的默認語言代碼。,例：創(chuàng)建SQL Server登錄賬戶wang。 EXEC sp_addlogin loginame= wang, passwd=1234 -新建登錄標識wang EXEC sp_addlogin loginame= wang1, passwd=1234 , defdb=stu 方法二：使用ssms select * from sys.syslogins,2用新建SQL server登錄用戶登錄SQL Server 首先設置安全認證模式成混合模式，再將登錄的默認數(shù)據(jù)庫選擇成用戶自己建立的數(shù)據(jù)庫，在建立該用戶訪問該數(shù)據(jù)庫權限的基礎上，啟動ssms，先斷開原來的連接，再重新連接，然后選擇SQL server身份認證，輸入新的用戶名和密碼即可用新用戶身份訪問SQL Server。,3、刪除SQL server 登錄標識 如果管理員要禁止某個用戶連接SQL server 服務器，則可調(diào)用系統(tǒng)存儲過程sp_droplogin或使用ssms將其登錄標識從系統(tǒng)中刪除。 方法一：使用系統(tǒng)存儲過程 語法格式： sp_droplogin loginame = login 參數(shù)說明： login為存儲在syslogin系統(tǒng)表中的SQL server登錄標識。刪除標識也就是刪除該用戶在syslogin表中的對應記錄。 方法二：使用ssms 刪除步驟類似上述Windows賬戶刪除。,16.3 數(shù)據(jù)庫訪問權限的建立與刪除 在數(shù)據(jù)庫中，一個用戶或工作組取得合法的登錄帳號，只表明該帳號通過了Windows NT認證或者SQL Server認證，但不能表明其可以對數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)庫對象進行某種或者某些操作，只有當他同時擁有了數(shù)據(jù)庫訪問權限后，才能夠訪問數(shù)據(jù)庫。,16.3.1 建立用戶訪問數(shù)據(jù)庫的權限 16.3.2 刪除用戶訪問數(shù)據(jù)庫的權限,16.3.1建立用戶訪問數(shù)據(jù)庫的權限 方法一：使用系統(tǒng)存儲過程 語法格式： sp_grantdbaccess loginame=login ,name_in_db=name_in_db 將登錄賬號用戶或組添加到當前數(shù)據(jù)庫，使該用戶能夠具有在當前數(shù)據(jù)庫中執(zhí)行活動的權限。 參數(shù)說明： login是登錄標識名稱或Windows NT/2003用戶或用戶組名稱。 name_in_db是在數(shù)據(jù)庫中為login參數(shù)指定登錄標識所創(chuàng)建的用戶名稱，它可以與登錄名稱不同，也可以相同。省略該參數(shù)時，所創(chuàng)建的數(shù)據(jù)庫用戶名稱與login相同。,例：將上述建立的Windows 用戶“ZUFE-MXHmeng” 添加到“教學管理”數(shù)據(jù)庫，并取名MANAGER。 USE 教學管理 GO EXEC sp_grantdbaccess ZUFE-MXHmeng, MANAGER GO 注意： sa不能添加到數(shù)據(jù)庫中； 只有sysadmin固定服務器角色、db_accessadmin和db_owner固定數(shù)據(jù)庫角色成員才能執(zhí)行sp_grantdbaccess； 不能從用戶定義的事務中執(zhí)行sp_grantdbaccess。 方法二：使用ssms,16.3.2 刪除用戶訪問數(shù)據(jù)庫的權限 方法一：使用系統(tǒng)存儲過程 語法格式： sp_revokedbaccess name_in_db=name_in_db 參數(shù)說明： name_in_db是在數(shù)據(jù)庫中指定登錄標識所創(chuàng)建的用戶名稱。 例16-11刪除windows賬戶“ZUFE-MXHmeng” 名為“MANAGER”訪問“教學管理”數(shù)據(jù)庫的訪問權限。 USE 教學管理 GO EXEC sp_revokedbaccess MANAGER GO 方法二：使用ssms。,16.4 角色管理,角色是一個強大的工具，可以將用戶集中到一個單元中，然后對該單元應用權限?？梢越⒁粋€角色來代表單位中一類工作人員所執(zhí)行的工作，然后給這個角色授予適當?shù)臋嘞?。當工作人員開始工作時，只須將他們添加為該角色成員，當他們離開工作時，將他們從該角色中刪除。而不必在每個人接受或離開工作時，反復授予、拒絕和廢除其權限。權限在用戶成為角色成員時自動生效。 管理員和數(shù)據(jù)庫擁有者在設置訪問權限時，應首先建立角色，并將訪問權限集中授予角色，之后將需要擁有這一權限的用戶加入到角色中，這些用戶即繼承角色的訪問權限。需要撤消用戶的訪問權限時，將用戶從角色中刪除即可。,16.4.1 固定服務器角色,1、查看固定服務器角色,2、添加固定服務器角色成員,3、刪除固定服務器角色成員,4、用T-SQL進行固定服務器角色成員管理,在SQL Server中使用以下兩個存儲過程來添加或刪除固定服務器角色成員： sp_addsrvrolemember loginame=login,rolename=role sp_dropsrvrolemember loginame=login,rolename=role 其中，login為登錄標識名稱，可以為SQL Server登錄標識或Windows NT用戶帳戶。 role為固定服務器角色名稱。,例如： sp_addsrvrolemember ZUFE-MXH meng,sysadmin go sp_dropsrvrolemember ZUFE-MXH meng,sysadmin go,16.4.2 固定數(shù)據(jù)庫角色,1、查看固定數(shù)據(jù)庫角色 2、添加固定數(shù)據(jù)庫角色成員 3、刪除固定數(shù)據(jù)庫角色成員,4、用T-SQL進行數(shù)據(jù)庫角色成員管理,在SQL Server中使用以下兩個存儲過程來添加或刪除固定數(shù)據(jù)庫角色成員和用戶自定義數(shù)據(jù)庫角色： sp_addrolemember rolename=role，membername=security_account sp_droprolemember rolename=role，membername=security_account 其中，role為固定服務器角色名稱或當前數(shù)據(jù)庫中自定義角色名稱。 security_account為待添加或刪除的SQL Server登錄標識、其他角色或Windows NT/2003用戶帳戶。當添加時，如果當前數(shù)據(jù)庫中沒有建立Windows NT/2003用戶帳戶安全帳戶，數(shù)據(jù)庫用戶帳戶被自動建立。刪除時，security_account必須為當前數(shù)據(jù)庫的一個有效用戶帳戶。 sp_addrolemember Db_owner,wang,5應用程序角色建立與刪除,前面介紹的標準角色對訪問控制實現(xiàn)在數(shù)據(jù)庫一級，它決定用戶能夠訪問的數(shù)據(jù)庫及其對象。下面介紹另一種訪問控制方法應用程序角色。它不同于標準角色，它不是根據(jù)用戶，而是根據(jù)用戶所運行的應用程序決定當前連接能否訪問數(shù)據(jù)庫對象。 使用應用程序角色的直接原因有以下兩個方面： 第一、限制訪問數(shù)據(jù)庫所使用的應用程序，提高系統(tǒng)安全性。例如，在一個財務系統(tǒng)中，用戶添加到數(shù)據(jù)庫中的數(shù)據(jù)不希望任何非法用戶通過其他任何途徑進行訪問，而只允許通過財務系統(tǒng)自身檢驗后的合法用戶訪問，這時使用應用程序角色就能達到這一目的。 第二、提高SQL Server服務器的運行性能。因為應用程序角色只允許指定的應用程序運行，這能避免用戶在SQL Server服務器上運行其他程序，從而提高數(shù)據(jù)庫系統(tǒng)的運行性能。,（1）建立應用程序角色 SQL Server中，只有固定服務器角色sysadmin成員、固定數(shù)據(jù)庫角色db_owner和db_securityadmin成員才能運行以下存儲過程創(chuàng)建或刪除應用程序角色： sp_addapprole rolename=role,password=password sp_dropapprole rolename=role 例如、在教學管理數(shù)據(jù)庫中建立應用程序角色查詢，其口令為123。 USE 教學管理 GO EXEC sp_addapprole 查詢,123 GO,（2）激活和使用應用程序角色 建立應用角色后，SQL Server數(shù)據(jù)庫應用程序可以調(diào)用系統(tǒng)存儲過程sp_setapprole激活角色。其語法格式為： sp_setapprole rolename=role,password= Encrypt N password|password,encrypt=encrypt_style 其中，Encrypt N選項要求應用程序在向SQL Server傳遞應用程序角色口令之前，將其加密。 encrypt_style說明加密方式，它有以下兩種取值： none:用明文方式傳遞，這時默認方式； odbc:使用ODBC規(guī)范定義的Encrypt加密函數(shù)對角色口令進行加密,例如，在一個客戶端應用程序中可以執(zhí)行以下語句激活前面創(chuàng)建的應用程序角色查詢。 USE 教學管理 GO EXEC sp_setapprole 查詢,123,odbc GO,應用程序角色與標準角色相比，二者之間存在以下差別： （1）應用程序角色不包含任何成員，而標準角色則擁有自己的成員。 （2）默認時，應用程序角色是無效的，只有當數(shù)據(jù)庫應用程序執(zhí)行系統(tǒng)存儲過程sp_approle，并為應用程序角色提供正確的口令后才激活應用程序角色。而標準角色一直保持有效。 （3）應用程序角色激活后，應用程序角色所擁有的訪問權限才起作用。這時，它屏蔽掉標準角色中的訪問權限。也就是說，應用程序角色激活后，無論連接用戶是否擁有對數(shù)據(jù)庫的訪問權限，SQL Server只根據(jù)應用程序角色的訪問權限判斷應用程序能否操作指定的數(shù)據(jù)庫。 （4）在運行應用程序所建立的連接斷開后，應用程序角色即隨之失去作用。,16.5 數(shù)據(jù)庫權限管理,當用戶成為數(shù)據(jù)庫中的合法用戶之后，他除了具有一些系統(tǒng)表的查詢權之外，并不對數(shù)據(jù)庫中的對象具有任何操作權，因此，下一步就需要為數(shù)據(jù)庫中的用戶授予數(shù)據(jù)庫對象的操作權。,16.5.1 權限種類,1、對象權限,2、語句權限 語句權限決定用戶能否操作數(shù)據(jù)庫和數(shù)據(jù)對象，如表、視圖、存儲過程、默認和規(guī)則等。語句權限決定用戶能否執(zhí)行以下語句： . CREATE DATABASE：創(chuàng)建數(shù)據(jù)庫。 . CREATE DEFAULT：在數(shù)據(jù)庫中建立默認值。 . CREATE PROCEDURE：在數(shù)據(jù)庫中創(chuàng)建存儲過程。 . CREATE FUNCTION：在數(shù)據(jù)庫中創(chuàng)建用戶自定義函數(shù)。 . CREATE RULE：在數(shù)據(jù)庫中創(chuàng)建規(guī)則。 . CREATE TABLE：在數(shù)據(jù)庫中創(chuàng)建表。 . CREATE VIEW：在數(shù)據(jù)庫中創(chuàng)建視圖。 . BACKUP DATABASE：備份數(shù)據(jù)庫。 . BACKUP LOG：備份數(shù)據(jù)庫日志。 在SQL Server中，每個數(shù)據(jù)庫都有各自獨立的權限保護，所以對于不同的數(shù)據(jù)庫要分別向用戶授予語句權限。（CREATE DATABASE 除外）。,3、隱含權限 是指系統(tǒng)安裝以后有些用戶和角色不必授權就有的許可。 SQL Server預定義的固定服務器角色、固定數(shù)據(jù)庫角色和數(shù)據(jù)庫對象所有者均具有隱含權限。,16.5.2 權限管理,1、使用ssms管理權限,2、使用T-SQL管理權限,T-SQL中的權限管理語句有以下三種： . GRANT：允許權限。 . DENY：禁止權限。 . REVOKE：取消允許權限或禁止權限設置。 由于SQL Server預定義角色的隱含權限是固定的，所以不能使用以上語句重新設置，權限管理語句只能設置用戶、角色等權限。,（1）語句權限管理,語句授權的命令格式是： GRANTALL|statement_listTOPUBLIC|name_list DENY ALL|statement_listTOPUBLIC|name_list REVOKE ALL|statement_listFROM PUBLIC|name_list ALL即全部語句，只有系統(tǒng)管理員可以使用此選項，因為只有系統(tǒng)管理員可以授予或收回CREATE DATABASE的權限； statement_list給出授權的語句列表，這些語句可以是CREATE DATABASE （如果執(zhí)行這個語句的用戶是系統(tǒng)管理員）、CREATE DEFAULT、CREATE PROCEDURE、CREATE FUNCTION、CREATE RULE、CREATE TABLE、CREATE VIEW、BACKUP DATABASE和DUMP TRANSACTION等； PUBLIC說明這些語句的執(zhí)行權限將授予所有的用戶； name_list是數(shù)據(jù)庫用戶名或組名或角色名，說明這些語句的執(zhí)行權限授予哪些用戶或組。,例1系統(tǒng)管理員授予注冊名為meng的用戶CREATE DATABASE的權限。 USE master GO GRANT CREATE DATABASE TO meng GO 例2授予用戶STU_MANAGER（ZUFE-MXHmeng登錄標識的名稱）具有創(chuàng)建表的權限。 USE 教學管理 GO GRANT CREATE TABLE TO MANAGER GO,2、 對象權限管理 數(shù)據(jù)庫對象的授權命令格式是： GRANTALL|Permission_list ON table_name(colume_list)|view_name(column_list)| stored_procedure_name|extended_stored_procedure_name| user_defined_function TO PUBLIC|name_list WITH GRANT OPTION as group|role 其中： ALL說明將指定對象的所有操作權限都授予指定的用戶，只有sysadmin、db_owner角色成員和數(shù)據(jù)庫對象所有者才可以使用all選項；permission_list是權限列表。 .AS group|role:用戶可以從多個角色或組中繼承權限，在GRANT和REVOKE語句中使用AS選項說明他們要管理的用戶從哪個角色或組繼承權限。,DENY ALL|Permission_list ON table_name(colume_list)|view_name(column_list)| stored_procedure_name|extended_stored_procedure_name| user_defined_function TO PUBLIC|name_list CASCADE,數(shù)據(jù)庫對象的拒絕權限的命令格式是：,DENY 語句中的CASCADE選項有以下兩