yxhxj2006

sql server中利用sql語句如何創(chuàng)建角色和用戶

SQL code

--1. 創(chuàng)建示例環(huán)境。
首先使用下面的代碼創(chuàng)建一個(gè)登錄l_test，并且為登錄在數(shù)據(jù)庫pubs中創(chuàng)建關(guān)聯(lián)的用戶賬戶u_test，并且授予用戶賬戶u_test對(duì)表titles的SELECT權(quán)限，用以實(shí)現(xiàn)登錄l_test連接到SQL Server實(shí)例后，可以訪問表titles。然后創(chuàng)建了一個(gè)應(yīng)用程序角色r_p_test，授予該角色對(duì)表jobs的SELECT權(quán)限，用以實(shí)現(xiàn)激活r_p_test時(shí)，允許訪問特定的表jobs。
USE pubs

--創(chuàng)建一個(gè)登錄 l_test, 密碼 pwd, 默認(rèn)數(shù)據(jù)庫 pubs
EXEC  sp_addlogin  'l_test','pwd','pubs' 

--為登錄 l_test 在數(shù)據(jù)庫 pubs 中添加安全賬戶 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--授予安全賬戶 u_test 對(duì) titles 表的 SELECT 權(quán)限
GRANT SELECT ON titles TO u_test

--創(chuàng)建一個(gè)應(yīng)用程序角色 r_p_test, 密碼 abc
EXEC sp_addapprole 'r_p_test','abc'

--授予角色 r_p_test 對(duì) jobs 表的 SELECT 權(quán)限
GRANT SELECT  ON jobs TO r_p_test
GO

--2. 激活應(yīng)用程序角色。
/*--激活說明
    示例環(huán)境創(chuàng)建完成后，在任何地方（比如查詢分析器、應(yīng)用程序）
    使用登錄l_test連接SQL Server實(shí)例，均只能訪問表titles，或者是guest用戶
        和public角色允許訪問的對(duì)象。
    如果要在某些特定的應(yīng)用程序中，允許登錄訪問表jobs，那么，
        可以激活應(yīng)用程序角色r_p_test，激活應(yīng)用程序角色后，登錄本身的權(quán)限會(huì)消失。
    下面在查詢分析器中登錄，演示激活應(yīng)用程序角色r_p_test前后對(duì)數(shù)據(jù)訪問的區(qū)別。
--*/
--激活應(yīng)用程序角色 r_p_test 前，登錄具有表 titles 的訪問權(quán)，但無表 jobs 的訪問權(quán)
SELECT titles_count=COUNT(*) FROM titles
SELECT jobs_count=COUNT(*) FROM jobs
/*--結(jié)果:
titles_count 
------------ 
18

（所影響的行數(shù)為 1 行）

服務(wù)器: 消息 229，級(jí)別 14，狀態(tài) 5，行 2
拒絕了對(duì)對(duì)象 'jobs'（數(shù)據(jù)庫 'pubs'，所有者 'dbo'）的 SELECT 權(quán)限。
--*/
GO

--用密碼 abc 激活 r_p_test 應(yīng)用程序角色,并且在將此密碼發(fā)送到SQL Server之前對(duì)其加密
EXEC sp_setapprole 'r_p_test',{Encrypt N'abc'},'ODBC'
GO

--激活應(yīng)用程序角色 r_p_test 后，登錄失去表 titles 的訪問權(quán)，獲取表 jobs 的訪問權(quán)
SELECT titles_count=COUNT(*) FROM titles
SELECT jobs_count=COUNT(*) FROM jobs
/*--結(jié)果
服務(wù)器: 消息 229，級(jí)別 14，狀態(tài) 5，行 2
拒絕了對(duì)對(duì)象 'titles'（數(shù)據(jù)庫 'pubs'，所有者 'dbo'）的 SELECT 權(quán)限。
jobs_count  
----------- 
14

（所影響的行數(shù)為 1 行）
--*/

SQL code

exec sp_dropsrvrolemember N'aa', sysadmin
go
exec sp_addsrvrolemember N'aa', securityadmin
go

--作好SQL的安全管理
--作者:鄒建

首先,做好用戶安全:  
 
--簡(jiǎn)單的,只允許sql的用戶訪問sql(防止利用administrator組用戶訪問)  
 
1.企業(yè)管理器--右鍵SQL實(shí)例--屬性--安全性--身份驗(yàn)證--選擇"sql  server和windows"--確定  
 
2.企業(yè)管理器--安全性--登陸--右鍵sa--設(shè)置密碼--其他用戶也設(shè)置密碼  
 
3.刪除用戶:  
           BUILTIN\Administrators  
           <機(jī)器名>\Administrator  --這個(gè)用戶不一定有  
   這樣可以防止用windows身份登陸SQL  
 
4.設(shè)置進(jìn)入企業(yè)管理器需要輸入密碼  
在企業(yè)管理器中  
           --右鍵你的服務(wù)器實(shí)例(就是那個(gè)有綠色圖標(biāo)的)  
           --編輯SQL Server注冊(cè)屬性  
           --選擇"使用 SQL Server 身份驗(yàn)證"  
           --并勾選"總是提示輸入登錄名和密碼"  
           --確定  
 
--經(jīng)過上面的設(shè)置,你的SQL  Server基本上算是安全了.  

------------------------------------------------------------------------  

 
其次,改默認(rèn)端口,隱藏服務(wù)器,減少被攻擊的可能性  
 
SQL Server服務(wù)器  
           --開始  
           --程序  
           --Microsoft  SQL  Server  
           --服務(wù)器網(wǎng)絡(luò)實(shí)用工具  
           --啟用的協(xié)議中"TCP/IP"  
           --屬性  
           --默認(rèn)端口,輸入一個(gè)自已定義的端口,比如2433  
           --勾選隱藏服務(wù)器  
 
----------------------------------------------------------------------------  


--管好sql的用戶,防止訪問他不該訪問的數(shù)據(jù)庫(總控制,明細(xì)還可以控制他對(duì)于某個(gè)數(shù)據(jù)庫的具體對(duì)象具有的權(quán)限)  
 
--切換到你新增的用戶要控制的數(shù)據(jù)庫  
use  你的庫名  
go  
 
--新增用戶  
exec  sp_addlogin  'test'            --添加登錄  
exec  sp_grantdbaccess  N'test'            --使其成為當(dāng)前數(shù)據(jù)庫的合法用戶  
exec  sp_addrolemember  N'db_owner',  N'test'            --授予對(duì)自己數(shù)據(jù)庫的所有權(quán)限  
 
--這樣創(chuàng)建的用戶就只能訪問自己的數(shù)據(jù)庫,及數(shù)據(jù)庫中包含了guest用戶的公共表  
go  
 
--刪除測(cè)試用戶  
exec  sp_revokedbaccess  N'test'            --移除對(duì)數(shù)據(jù)庫的訪問權(quán)限  
exec  sp_droplogin  N'test'            --刪除登錄  


如果在企業(yè)管理器中創(chuàng)建的話,就用:

企業(yè)管理器--安全性--右鍵登錄--新建登錄

常規(guī)項(xiàng)
--名稱中輸入用戶名
--身份驗(yàn)證方式根據(jù)你的需要選擇(如果是使用windows身份驗(yàn)證,則要先在操作系統(tǒng)的用戶中新建用戶)
--默認(rèn)設(shè)置中,選擇你新建的用戶要訪問的數(shù)據(jù)庫名

服務(wù)器角色項(xiàng)
這個(gè)里面不要選擇任何東西

數(shù)據(jù)庫訪問項(xiàng)
勾選你創(chuàng)建的用戶需要訪問的數(shù)據(jù)庫名
數(shù)據(jù)庫角色中允許,勾選"public","db_ownew"

確定,這樣建好的用戶與上面語句建立的用戶一樣 
---------------------------------------------------------------------------  

最后一步,為具體的用戶設(shè)置具體的訪問權(quán)限,這個(gè)可以參考下面的最簡(jiǎn)示例: 
 
--添加只允許訪問指定表的用戶:  
exec  sp_addlogin  '用戶名','密碼','默認(rèn)數(shù)據(jù)庫名'  
 
--添加到數(shù)據(jù)庫  
exec  sp_grantdbaccess  '用戶名'
 
--分配整表權(quán)限  
GRANT  SELECT ,  INSERT ,  UPDATE ,  DELETE  ON table1 TO [用戶名]

--分配權(quán)限到具體的列
GRANT  SELECT ,  UPDATE  ON table1(id,AA) TO [用戶名]
 
 
-------------------------------------------------------------------  
至于具體的安全設(shè)置和理論知道,參考SQL聯(lián)機(jī)幫助
 

SQL code

/*--創(chuàng)建一個(gè)只允許特定程序使用的數(shù)據(jù)庫用戶

    創(chuàng)建一個(gè)用戶,這個(gè)用戶只有用我們特定的應(yīng)用程序登錄
    才具有訪問數(shù)據(jù)庫的權(quán)限,用其他工具登錄沒有任何權(quán)限

    在下面的示例中，演示了如何控制登錄l_test
    使其登錄后只允許訪問pubs數(shù)據(jù)庫的titles表
    而對(duì)jobs表的訪問權(quán)限，只允許在某些許可的應(yīng)用程序中訪問。

--鄒建 2004.09(引用請(qǐng)保留此信息)--*/

--創(chuàng)建測(cè)試環(huán)境
USE pubs

--創(chuàng)建一個(gè)登錄 l_test, 密碼 pwd, 默認(rèn)數(shù)據(jù)庫 pubs
EXEC  sp_addlogin  'l_test','pwd','pubs' 

--為登錄 l_test 在數(shù)據(jù)庫 pubs 中添加安全帳戶 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--授予安全帳戶 u_test 對(duì) titles 表的 SELECT 權(quán)限
GRANT SELECT ON titles TO u_test

--創(chuàng)建一個(gè)應(yīng)用程序角色 r_p_test, 密碼 abc
EXEC sp_addapprole 'r_p_test','abc'

--授予角色 r_p_test 對(duì) jobs 表的 SELECT 權(quán)限
GRANT SELECT  ON jobs TO r_p_test
GO

--創(chuàng)建好上面的測(cè)試后,現(xiàn)在來測(cè)試如何使用應(yīng)用程序角色
--我們把用戶及密碼告訴使用者,即告訴使用者,用戶是: l_test,密碼是: pwd
--使用者可以用我們這個(gè)用戶在任何地方登錄,包含查詢分析器
--但是,用戶只能訪問 titles 表,不能訪問其他對(duì)象,如果建立用戶時(shí)不授予它任何權(quán)限,則它不訪問除guest用戶和public角色允許訪問外的任何對(duì)象
--OK,到這里,我們是把用戶控制住了

--下面我們?cè)賮碚f在程序中的處理,因?yàn)橛脩粼诔绦蛑械卿浐?需要對(duì)jobs表有訪問權(quán)限的
--我們只需要在用戶登錄后,執(zhí)行一句
    EXEC sp_setapprole 'r_p_test',{Encrypt N'abc'},'ODBC'
--這樣,我們登錄的用戶就轉(zhuǎn)變?yōu)?r_p_test 角色的權(quán)限,而它自身的權(quán)限丟失
--只要這個(gè)用戶不退出應(yīng)用程序,他的權(quán)限就會(huì)保持
--如果用戶退出了當(dāng)前應(yīng)用程序,則他的權(quán)限自動(dòng)收回
--同時(shí),這個(gè)用戶即使沒有退出應(yīng)用程序,他也是只在我們的應(yīng)用程序中有權(quán)限,在其他地方登錄,也不會(huì)有權(quán)限
--因?yàn)檫@個(gè)密碼我們是不用給用戶的,所以,用戶沒有這個(gè)角色的密碼,也就限制了他只能在程序中使用我們的數(shù)據(jù)

--激活應(yīng)用程序角色 r_p_test 前，登錄具有表 titles 的訪問權(quán)，但無表 jobs 的訪問權(quán)
SELECT titles_count=COUNT(*) FROM titles
SELECT jobs_count=COUNT(*) FROM jobs
/*--結(jié)果:
titles_count 
------------ 
18

（所影響的行數(shù)為 1 行）

服務(wù)器: 消息 229，級(jí)別 14，狀態(tài) 5，行 2
拒絕了對(duì)對(duì)象 'jobs'（數(shù)據(jù)庫 'pubs'，所有者 'dbo'）的 SELECT 權(quán)限。
--*/
GO

--用密碼 abc 激活 r_p_test 應(yīng)用程序角色,并且在將此密碼發(fā)送到SQL Server之前對(duì)其加密
EXEC sp_setapprole 'r_p_test',{Encrypt N'abc'},'ODBC'
GO

--激活應(yīng)用程序角色 r_p_test 后，登錄失去表 titles 的訪問權(quán)，獲取表 jobs 的訪問權(quán)
SELECT titles_count=COUNT(*) FROM titles
SELECT jobs_count=COUNT(*) FROM jobs
/*--結(jié)果
服務(wù)器: 消息 229，級(jí)別 14，狀態(tài) 5，行 2
拒絕了對(duì)對(duì)象 'titles'（數(shù)據(jù)庫 'pubs'，所有者 'dbo'）的 SELECT 權(quán)限。
jobs_count  
----------- 
14

（所影響的行數(shù)為 1 行）
--*/
go

--刪除測(cè)試
EXEC sp_dropapprole 'r_p_test'
EXEC sp_revokedbaccess 'u_test'
EXEC sp_droplogin 'l_test'

posted on 2012-08-08 10:47 奮斗成就男人 閱讀(3442) 評(píng)論(0)  編輯  收藏