210.应用程序角色使用

--1. 创建示例环境。
首先使用下面的代码创建一个登录l_test&＃xff0c;并且为登录在数据库pubs中创建关联的用户账户u_test&＃xff0c;并且授予用户账户u_test对表titles的SELECT权限&＃xff0c;用以实现登录l_test连接到SQL Server实例后&＃xff0c;可以访问表titles。然后创建了一个应用程序角色r_p_test&＃xff0c;授予该角色对表jobs的SELECT权限&＃xff0c;用以实现激活r_p_test时&＃xff0c;允许访问特定的表jobs。
USE pubs--创建一个登录 l_test, 密码 pwd, 默认数据库 pubs
EXEC sp_addlogin &＃39;l_test&＃39;,&＃39;pwd&＃39;,&＃39;pubs&＃39; --为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess &＃39;l_test&＃39;,&＃39;u_test&＃39;--授予安全账户 u_test 对 titles 表的 SELECT 权限
GRANT SELECT ON titles TO u_test--创建一个应用程序角色 r_p_test, 密码 abc
EXEC sp_addapprole &＃39;r_p_test&＃39;,&＃39;abc&＃39;--授予角色 r_p_test 对 jobs 表的 SELECT 权限
GRANT SELECT ON jobs TO r_p_test
GO--2. 激活应用程序角色。
/*--激活说明示例环境创建完成后&＃xff0c;在任何地方&＃xff08;比如查询分析器、应用程序&＃xff09;使用登录l_test连接SQL Server实例&＃xff0c;均只能访问表titles&＃xff0c;或者是guest用户和public角色允许访问的对象。如果要在某些特定的应用程序中&＃xff0c;允许登录访问表jobs&＃xff0c;那么&＃xff0c;可以激活应用程序角色r_p_test&＃xff0c;激活应用程序角色后&＃xff0c;登录本身的权限会消失。下面在查询分析器中登录&＃xff0c;演示激活应用程序角色r_p_test前后对数据访问的区别。
--*/
--激活应用程序角色 r_p_test 前&＃xff0c;登录具有表 titles 的访问权&＃xff0c;但无表 jobs 的访问权
SELECT titles_count&＃61;COUNT(*) FROM titles
SELECT jobs_count&＃61;COUNT(*) FROM jobs
/*--结果:
titles_count
------------
18&＃xff08;所影响的行数为 1 行&＃xff09;服务器: 消息 229&＃xff0c;级别 14&＃xff0c;状态 5&＃xff0c;行 2
拒绝了对对象 &＃39;jobs&＃39;&＃xff08;数据库 &＃39;pubs&＃39;&＃xff0c;所有者 &＃39;dbo&＃39;&＃xff09;的 SELECT 权限。
--*/
GO--用密码 abc 激活 r_p_test 应用程序角色,并且在将此密码发送到SQL Server之前对其加密
EXEC sp_setapprole &＃39;r_p_test&＃39;,{Encrypt N&＃39;abc&＃39;},&＃39;ODBC&＃39;
GO--激活应用程序角色 r_p_test 后&＃xff0c;登录失去表 titles 的访问权&＃xff0c;获取表 jobs 的访问权
SELECT titles_count&＃61;COUNT(*) FROM titles
SELECT jobs_count&＃61;COUNT(*) FROM jobs
/*--结果
服务器: 消息 229&＃xff0c;级别 14&＃xff0c;状态 5&＃xff0c;行 2
拒绝了对对象 &＃39;titles&＃39;&＃xff08;数据库 &＃39;pubs&＃39;&＃xff0c;所有者 &＃39;dbo&＃39;&＃xff09;的 SELECT 权限。
jobs_count
-----------
14&＃xff08;所影响的行数为 1 行&＃xff09;
--*/