/*--創建一個只允許特定程序使用的數據庫用戶

	創建一個用戶,這個用戶只有用我們特定的應用程序登錄
	才具有訪問數據庫的權限,用其他工具登錄沒有任何權限

	在下面的示例中，演示了如何控制登錄l_test
	使其登錄后只允許訪問pubs數據庫的titles表
	而對jobs表的訪問權限，只允許在某些許可的應用程序中訪問。

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

--創建測試環境
USE pubs

--創建一個登錄 l_test, 密碼 pwd, 默認數據庫 pubs
EXEC  sp_addlogin  'l_test','pwd','pubs' 

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

--授予安全帳戶 u_test 對 titles 表的 SELECT 權限
GRANT SELECT ON titles TO u_test

--創建一個應用程序角色 r_p_test, 密碼 abc
EXEC sp_addapprole 'r_p_test','abc'

--授予角色 r_p_test 對 jobs 表的 SELECT 權限
GRANT SELECT  ON jobs TO r_p_test
GO


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

--下面我們再來說在程序中的處理,因為用戶在程序中登錄后,需要對jobs表有訪問權限的
--我們只需要在用戶登錄后,執行一句
	EXEC sp_setapprole 'r_p_test',{Encrypt N'abc'},'ODBC'
--這樣,我們登錄的用戶就轉變為 r_p_test 角色的權限,而它自身的權限丟失
--只要這個用戶不退出應用程序,他的權限就會保持
--如果用戶退出了當前應用程序,則他的權限自動收回
--同時,這個用戶即使沒有退出應用程序,他也是只在我們的應用程序中有權限,在其他地方登錄,也不會有權限
--因為這個密碼我們是不用給用戶的,所以,用戶沒有這個角色的密碼,也就限制了他只能在程序中使用我們的數據

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

（所影響的行數為 1 行）

服務器: 消息 229，級別 14，狀態 5，行 2
拒絕了對對象 'jobs'（數據庫 'pubs'，所有者 'dbo'）的 SELECT 權限。
--*/
GO

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

--激活應用程序角色 r_p_test 后，登錄失去表 titles 的訪問權，獲取表 jobs 的訪問權
SELECT titles_count=COUNT(*) FROM titles
SELECT jobs_count=COUNT(*) FROM jobs
/*--結果
服務器: 消息 229，級別 14，狀態 5，行 2
拒絕了對對象 'titles'（數據庫 'pubs'，所有者 'dbo'）的 SELECT 權限。
jobs_count  
----------- 
14

（所影響的行數為 1 行）
--*/
go

--刪除測試
EXEC sp_dropapprole 'r_p_test'
EXEC sp_revokedbaccess 'u_test'
EXEC sp_droplogin 'l_test'