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

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

	在下面的示例中，演示了如何控制登錄l_test
	使其登錄后只允許訪問pubs數(shù)據(jù)庫(kù)的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ù)庫(kù) pubs
EXEC  sp_addlogin  'l_test','pwd','pubs' 

--為登錄 l_test 在數(shù)據(jù)庫(kù) 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ù)庫(kù) '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ù)庫(kù) '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'