SQLServer2005

數據庫管理系統第十四講SQLServer權限管理引入

數據的安全性是指保護數據以防止因不合法的使用而造成數據的泄密和破壞，這就要采取一定的安全措施.。在數據庫系統中,用檢查口令等手段來檢查用戶身份,合法的用戶才能進入數據庫系統，當用戶對數據庫操作時,系統自動檢查用戶是否有權限執行這些操作。學習目標SQLServer的驗證模式登錄帳號的設置用戶和角色的創建權限的設置學習完本課程，您應該能夠：知識重、難點分析重點：

SQLServer的驗證模式用戶和角色的創建難點：

登錄帳號的設置用戶和角色的權限設置任務一SQLServer數據庫的安全性層次1、客戶機操作系統的安全性。2、SQLServer服務器登錄的安全性。3、數據庫的使用安全性。4、數據庫對象的使用安全性。用戶客戶機SQLServer服務器數據庫操作系統的安全性SQLServer登錄安全性數據庫訪問權限數據庫對象許可權表視圖任務一SQLServer數據庫的安全性層次任務一SQLServer數據庫的安全性層次WindowsSQLServerdatabasetableWindows賬號Windows賬號SQL賬號Pdb1db2dbnPPPSQLLoginsSQLRolesDbuserdbrolesDbuserdbrolesDbuserdbrolesPPPPPP任務一SQLServer數據庫的安全性層次任務二SQLServer的認證模式Windows認證模式SQLServer數據庫系統通常運行在Windows服務器平臺上，Windows認證模式正是利用了這一用戶安全性和帳號管理的機制，允許SQLServer也可以使用NT的用戶名和口令。在這種模式下，用戶只需要通過Windows認證，就可以連接到SQLServer，而SQLServer本身也就不需要管理一套登錄數據。混合認證模式在混合認證模式下Windows認證和SQLServer認證模式都是可用的。NT的用戶既可以使用NT認證，也可以使用SQLServer認證。在SQLServer認證模式下，用戶在連接SQLServer時必須提供登錄名和登錄密碼。這些登錄信息存儲在系統表syslogins中，與NT的登錄賬號無關，SQLServer自己執行認證。Windows認證模式的優點更先進的安全策略一組只需建一個用戶更快捷的訪問混合認證模式的優點非windows用戶及Internet客戶可以連接到數據庫任務二SQLServer的認證模式利用企業管理器進行認證模式設置：1.打開企業管理器，用右鍵單擊要設置認證模式的服務器，從快捷菜單中選擇“屬性（properties）”選項，則出現SQLServer屬性對話框。2.在SQLServer屬性對話框中選擇安全性選項。任務二SQLServer的認證模式2.打開企業管理器，用右鍵單擊要設置認證模式的服務器，從快捷菜單中選擇“編輯SQLServer注冊屬性”選項，則出現“已注冊SQLServer屬性”對話框。任務二SQLServer的認證模式任務三利用企業管理器管理登錄帳號其具體執行步驟如下：（１）打開企業管理器，單擊需要登錄的服務器左邊的“+”號，然后展開安全性文件夾。（２）用右鍵單擊登錄圖標，從快捷菜單中選擇新建登錄選項，則出現SQLServer登錄屬性—新建登錄對話框，如圖6-2所示。（3）在名稱編輯框中輸入登錄名，在身份驗證選項欄中選擇新建的用戶帳號是WindowsNT認證模式，還是SQLServer認證模式。任務三利用企業管理器管理登錄帳號（４）選擇服務器角色頁框，如圖6-3所示。在服務器角色列表框中，列出了系統的固定服務器角色。（５）選擇數據庫訪問頁框，如圖6-4所示。上面的列表框列出了該帳號可以訪問的數據庫，單擊數據庫左邊的復選框，表示該用戶可以訪問相應的數據庫以及該帳號在數據庫中的用戶名。（６）設置完成后，單擊“確定”按鈕即可完成登錄帳號的創建。

任務三利用企業管理器管理登錄帳號任務三利用企業管理器管理登錄帳號任務三利用企業管理器管理登錄帳號1、創建SQLServer登錄帳戶

格式：sp_addlogin[@loginname]‘login’[,[@passwd=]‘password’][,[@defdb=]‘database’][,[@language=]‘language’][,[@sid=]‘sid’][,[@encryption=]‘encryption_option’]例如：execsp_addlogin'dbloginuser1','123'

execsp_addlogin'dbloginuser1','123',‘學生信息庫’任務四利用T-SQL管理登錄帳號2、修改SQLServer登錄帳戶

例如：修改登錄帳戶dbloginuser1的默認數據庫。

execsp_defaultdb‘dbloginuser1’,‘學生管理數據庫’

修改dbloginuser1的密碼。

execsp_password‘password’,’newpassword’,‘dbloginuser1’

任務四利用T-SQL管理登錄帳號4、刪除SQLServer登錄帳戶

刪除登錄帳戶dbloginuser1。

execsp_droplogindbloginuser1任務四利用T-SQL管理登錄帳號Windows集成登錄帳號1、使用系統存儲過程使WindowsNT的用戶或工作組成為SQLServer的登錄帳戶。格式：sp_grantlogin[@loginane=]’login’例如：將WindowsNT服務器上[servername]域的工作組[sqlgroup]映射為SQLServer登錄帳戶。

execsp_grantlogin[servername\sqlgroup]任務四利用T-SQL管理登錄帳號Windows集成登錄帳號2、撤消WindowsNT服務器上[servername]域的工作組[sqlgroup]內用戶與SQLServer登錄帳戶的映射。execsp_revokelogin[servername\sqlgroup]任務四利用T-SQL管理登錄帳號Windows集成登錄帳號3、拒絕WindowsNT服務器上[servername]域的工作組[sqlgroup]內用戶登錄SQLServer服務器。execsp_denylogin[servername\sqlgroup]查看登錄帳號信息。execsp_helplogin任務四利用T-SQL管理登錄帳號任務五SQLServer數據庫安全性在數據庫中，一個用戶或工作組取得合法的登錄帳號，只表明該帳號通過了WindowsNT認證或者SQLServer認證，但不能表明其可以對數據庫對象進行操作，只有當它同時擁有了用戶帳號后，才能夠訪問數據庫。數據庫的訪問權限是通過映射數據庫的用戶與登錄帳號之間的關系實現的。每個登錄賬號在一個數據庫中只能有一個用戶賬號,但是每個登錄賬號可以在不同的數據庫中各有一個用戶賬號。如果在新建登錄賬號的過程中，指定對某個數據庫具有存取權限，則在該數據庫中將自動創建一個與該登錄賬號同名的用戶賬號。任務五使用企業管理器管理數據庫用戶1、添加數據庫用戶：

任務五使用企業管理器管理數據庫用戶2、刪除數據庫用戶：任務五使用企業管理器管理數據庫用戶任務五使用T-SQL管理數據庫用戶1、添加數據庫用戶格式：sp_grantdbaccess[@loginame=]'賬戶名'[,[@name_in_db=]'數據庫用戶名’例如：在當前數據庫中為WindowsNT賬戶servername\sqlgroup

添加用戶，并取名為sqllogingroup。use學生管理數據庫goexecsp_grantdbaccess[servername\sqlgroup],'sqllogingroup’goexecsp_grantdbaccess‘dbloginuser1’,’dbaccessuser1’任務五使用T-SQL管理數據庫用戶2、查看數據庫用戶use學生管理數據庫goexecsp_helpuser任務五使用T-SQL管理數據庫用戶3、刪除數據庫用戶格式：sp_revokedbaccess[@name_in_db=]'數據庫用戶名’例如：斷開SQLServer登錄帳戶與數據庫用戶之間的映射。User學生管理數據庫goexecsp_revokedbaccess‘sqllogingroup’goexecsp_revokedbaccess’dbaccessuser1’特殊數據庫用戶SQLServer2000的數據庫級別上也存在著2個特殊的數據庫用戶，分別是dbo和guest。1、Dbo是數據庫對象所有者，在安裝SQLServer2000時被設置到model數據庫中的，不能被刪除，所以dbo在每個數據庫中都存在，具有數據庫的最高權力，可以在數據庫范圍內執行一切操作。Dbo用戶對應于創建該數據庫的登錄用戶，所以所有系統數據庫的dbo都對應于sa賬戶。2、Guest允許沒有賬戶的用戶登錄訪問數據庫。可以將權限應用到guest用戶，就如同它是任何其他用戶賬戶一樣。可以在除master和tempdb外（在這兩個數據庫中它必須始終存在）的所有數據庫中添加或刪除guest用戶。默認情況下，新建的數據庫中沒有guest用戶賬戶。【例】將guest用戶賬戶添加到名為Accounts的數據庫中，意味著所有登錄到SQLServer服務器的用戶都可以訪問該數據庫，即使它還沒有成為本數據庫的用戶。useaccountsgoexecsp_grantdbaccessguest特殊數據庫用戶任務六角色管理角色是SQLServer版本引進的新概念，它代替了以前版本中組的概念。利用角色，SQLServer管理者可以將某些用戶設置為某一角色，這樣只對角色進行權限設置便可以實現對所有用戶權限的設置，大大減少管理員的工作量。SQLServer提供了用戶通常管理工作的固定服務器角色和固定數據庫角色。1、固定服務器角色

固定服務器角色是指根據SQLServer的管理任務，以及這些任務相對的重要性等級來把具有SQLServer管理職能的用戶劃分為不同的用戶組，每一組所具有的管理SQLServer的權限都是SQLServer內置的，即不能對其進行添加、修改和刪除，只能向其中加入用戶或者其他角色。1、固定服務器角色

1、使用企業管理器管理服務器角色（1）展開指定的服務器節點。（2）展開服務器節點下的安全性子節點。（3）選擇[服務器角色]節點，則右邊窗口將列出所有的固定服務器角色。（4）選中指定的固定服務器角色，右擊[屬性]，單擊[添加]按鈕。1、固定服務器角色

2、使用T-SQL管理固定服務器角色1）查看固定服務器角色：Execsp_helpsrvrole

2）查看固定服務器角色權限：UsemastergoExecsp_srvrolepermissionsysadmin1、固定服務器角色

2、使用T-SQL管理服務器角色3）將固定服務器角色分配給某指定的登錄帳戶：execsp_addsrvrolemember[servername\user1],’sysadmin’goexecsp_addsrvrolemember‘dbloginuser1’,’sysadmin’1、固定服務器角色

2、使用T-SQL管理服務器角色4）收回分配給某指定的登錄帳戶的固定服務器角色：execsp_dropsrvrolemember[servername\user1],’sysadmin’Goexecsp_dropsrvrolemember‘dbloginuser1’,’sysadmin’1、固定服務器角色

2、使用T-SQL管理服務器角色5）查看固定服務器角色成員：execsp_helpsrvrolemember’sysadmin’七種常用的固定服務器角色

系統管理員：擁有SQLServer所有的權限許可。服務器管理員：管理SQLServer服務器端的設置。磁盤管理員：管理磁盤文件。進程管理員：管理SQLServer系統進程。安全管理員：管理和審核SQLServer系統登錄。安裝管理員：增加、刪除連接服務器，建立數據庫復制以及管理擴展存儲過程。數據庫創建者：創建數據庫，并對數據庫進行修改。數據庫角色數據庫角色是為某一用戶或某一組用戶授予不同級別的管理或訪問數據庫以及數據庫對象的權限，這些權限是數據庫專有的，并且還可以使一個用戶具有屬于同一數據庫的多個角色。SQLServer提供了兩種類型的數據庫角色：即固定的數據庫角色和用戶自定義的數據庫角色。

數據庫角色1）查看固定數據庫角色：use學生管理數據庫goexecsp_helpdbfixedrole數據庫角色2）將某固定數據庫角色分配給某指定的訪問帳戶：use學生管理數據庫goexecsp_addrolememberdb_owner,dbaccessuser1數據庫角色3）收回分配給某指定的訪問帳戶的指定數據庫角色：use學生管理數據庫goexecsp_droprolememberdb_owner,dbaccessuser11、固定的數據庫角色：Public:維護全部默認許可。數據庫中的每個用戶都屬于public數據庫角色。如果想讓數據庫中的每個用戶都能有某個特定的權限，則將該權限指派給public角色。如果沒有給用戶專門授予對某個對象的權限，他們就使用指派給public角色的權限。Db_owner：數據庫的所有者，可以對所擁有的數據庫執行任何操作。Db_accessadmin：可以增加或刪除數據庫用戶、工作組和角色。Db_addadmin：可以增加、刪除和修改數據庫中的任何對象。Db_securityadmin：執行語句許可和對象許可。數據庫角色Db_backupoperator：可以備份和恢復數據庫。Db_datareader：能且僅能對數據庫中的任何表執行select操作，從而讀取所有表的信息。Db_datawriter：能夠增加、修改和刪除表中的數據，但不能進行select操作。Db_denydatareader：不能讀取數據庫中任何表中的數據。Db_denydatawriter：不能對數據庫中的任何表執行增加、修改和刪除數據操作。數據庫角色2、用戶自定義數據庫角色創建用戶定義的數據庫角色就是創建一組用戶，這些用戶具有相同的一組許可。如果一組用戶需要執行在SQLServer中指定的一組操作并且不存在對應的WindowsNT組，或者沒有管理WindowsNT用戶帳號的許可，就可以在數據庫中建立一個用戶自定義的數據庫角色。用戶自定義的數據庫角色有兩種類型：即標準角色和應用程序角色。數據庫角色標準角色通過對用戶權限等級的認定而將用戶劃分為不用的用戶組，使用戶總是相對于一個或多個角色，從而實現管理的安全性。應用程序角色是一種特殊的角色。當讓某些用戶只能通過特定的應用程序間接地存取數據庫中的數據而不是直接地存取數據庫數據時，就應該考慮使用應用程序角色。當某一用戶使用了應用程序角色時，他便放棄了已被賦予的所有數據庫專有權限，他所擁有的只是應用程序角色被設置的角色。數據庫角色數據庫角色1、使用企業管理器管理用戶自定義角色（1）在企業管理器中找到要創建角色的數據庫。（2）展開數據庫。用鼠標右鍵單擊角色，在彈出的快捷菜單中選擇“新建數據庫角色”命令，如圖8-11所示。（3）在打開的新建數據庫角色對話框中，在名稱框中輸入數據庫角色的名稱，選中“標準角色”單選按鈕，如圖8-12所示。（4）在圖8-12中單擊“添加“按鈕，可以為角色添加用戶，也可省略創建一個暫無成員的角色。圖8-11創建數據庫角色圖8-12創建新數據庫角色2、使用T-SQL管理用戶自定義數據庫角色格式：Sp_addrole[@rolename=]’role’[,[@ownername=]’owner’]例如：use學生管理數據庫goexecsp_addrole‘dbaccessrole1’goexecsp_helprole

goexecsp_droprole‘dbaccessrole1’數據庫角色數據庫角色成員1、使用企業管理器增刪用戶自定義數據庫角色成員（1）在企業管理器中找到要添加角色的數據庫。（2）展開該數據庫。（3）用鼠標單擊角色，右邊的窗口顯示了數據庫所有的角色，雙擊要修改的成員角色，打開如圖8-13所示的對話框。（4）在圖8-13中單擊“添加”按鈕可以從當前的數據庫用戶的角色中選擇角色成員，如圖8-14所示。（5）在圖8-14中的成員列表中選擇一個成員，單擊“確定”按鈕就可以為該角色添加用戶。（6）在圖8-13中，選中用戶，單擊“刪除”按鈕，則可刪除該角色的成員。圖8-13數據庫角色屬性圖8-14為數據庫添加角色成員數據庫角色成員2、使用T-SQL增刪用戶自定義數據庫角色成員系統存儲過程sp_addsrvrolemember可以將某一登錄帳號加入到服務器角色中，使其成為該服務器角色的成員。格式：sp_addsrvrolememberaccess，role【例】將SQLServer的用戶dbaccessuser1添加到當前數據庫中的dbaccessrole1角色中。use學生管理數據庫goexecsp_addrolemember‘dbaccessuser1',‘dbaccessrole1'數據庫角色成員2、使用T-SQL查看用戶自定義數據庫角色成員use學生管理數據庫goexecsp_helprolemember‘dbaccessrole1'數據庫角色成員許可管理SQLServer提供了權限（permision）作為訪問權限設置的最后一道關卡。數據庫中每個對象都擁有自己的權限設置，以決定用戶最終是否可以訪問這些數據對象，以及可以對這些數據對象進行的操作。權限包括授予或廢除執行以下活動的用戶權限：（1）處理數據和執行過程（稱為對象權限）。（2）創建數據庫或數據庫中的項目（稱為語句權限）。（3）利用授予預定義角色的權限（稱為暗示性權限）。1、對象許可處理數據或執行過程時的需要稱為對象權限的權限類別。對象權限包括：SELECT、INSERT、UPDATE

和DELETE語句權限，它們可以應用到整個表或視圖中。SELECT和UPDATE語句權限，它們可以有選擇性地應用到表或視圖中的單個列上。SELECT權限，它們可以應用到用戶定義函數。INSERT和DELETE語句權限，它們會影響整行，因此只可以應用到表或視圖中，而不能應用到單個列上。EXECUTE語句權限，它們可以影響存儲過程和函數。許可管理2、語句許可表示對數據庫的操作許可，也就是說，創建數據庫或創建數據庫中的其它內容所需要的許可類型稱為語句許可：Createdatabase：創建數據庫Createtable：創建表Createview：創建視圖Createrule：創建規則Createdefault：創建缺省Createprocedure：創建存儲過程Createindex：創建索引Backupdatabase：備份數據庫Backuplog：備份事務日志許可管理3、預定義許可（暗示性權限）是指系統安裝以后有些用戶和角色不必授權就有的許可。暗示性權限控制那些只能由預定義系統角色的成員或數據庫對象所有者執行的活動。例如，sysadmin固定服務器角色成員自動繼承在SQLServer安裝中進行操作或查看的全部權限。數據庫對象所有者還有暗示性權限，可以對所擁有的對象執行一切活動。例如，擁有表的用戶可以查看、添加或刪除數據，更改表定義或控制允許其他用戶對表進行操作的權限。許可管理1、使用SQLServer企業管理器管理許可通過企業管理器授予權限而允許訪問的步驟是：（1）找到對象所屬的數據庫。（2）展開對象所屬的數據庫。（3）根據對象類型，單擊下列對象之一：

表視圖存儲過程（4）在“詳細信息”窗口中，右擊授予權限所在的對象，指向“所有任務”，然后單擊“管理權限”。（5）單擊“列出全部用戶/用戶定義的數據庫角色/public”，然后選擇授予每位用戶的權限。許可管理2、使用T-SQL管理許可Transaction_SQL語句使用grant、revoke和deny三種命令來實現管理權限。許可管理（１）對語句的許可Grant{all|statement[,…n]}tosecurity_account[,…n][WITHGRANTOPTION]許可管理（２）對對象的許可Grant

{{all[priviledges]|permission[,…n]}[（column[,…n]）]on{table|view}|on{table|view}[（column[,…n]）]|on{stored_procedure|extended_procedure}}tosecurity_account[,…n][WITHGRANTOPTION][as{group|role}]許可管理ALL表示授予所有可用的權限。對于語句權限，只有sysadmin角色成員可以使用ALL。對于對象權限，sysadmin和db_owner角色成員和數據庫對象所有者都可以使用ALL。WITHGRANTOPTION表示可以將指定的對象權限授予其他用戶。【例】為角色dbaccessrole1中成員授予在當前數據庫中創建表權限。use學生管理數據庫gograntcreatetabletodbaccessrole1goexecsp_helprotectnull,dbaccessrole1gograntselect,inserton學生表todbaccessrole1許可管理（１）否定語句的許可Deny{all|statement[,…n]}tosecurity_account[,…n]許可管理（２）否定對象的許可Deny

{{all[priviledges]|permission[,…n]}[（column[,…n]）]on{table|view}|on{table|view}[（column[,…n]）]|on{stored_procedure|extended_procedure}}tosecurity_account[,…n]許可管理【例】拒絕角色dbaccessrole1中成員在當前數據庫中創建表權限，除非給他們顯式授予權限。use學生管理數據庫godenycreatetabletodbaccessrole1goexecsp_helprotectnull,dbaccessrole1godenyselect,inserton學生表todbaccessrole1許可管理（１）收回語句的許可Revoke{all|statement[,…n]}fromsecurity_account[,…n]許可管理