210.应用程序角色使用

深碍√TFBOYSˉ_ 2024-02-19 19:42 22阅读 0赞

--1. 创建示例环境。
    首先使用下面的代码创建一个登录l_test，并且为登录在数据库pubs中创建关联的用户账户u_test，并且授予用户账户u_test对表titles的SELECT权限，用以实现登录l_test连接到SQL Server实例后，可以访问表titles。然后创建了一个应用程序角色r_p_test，授予该角色对表jobs的SELECT权限，用以实现激活r_p_test时，允许访问特定的表jobs。
    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
    
    --2. 激活应用程序角色。
    /*--激活说明
    	示例环境创建完成后，在任何地方（比如查询分析器、应用程序）
    	使用登录l_test连接SQL Server实例，均只能访问表titles，或者是guest用户
    		和public角色允许访问的对象。
    	如果要在某些特定的应用程序中，允许登录访问表jobs，那么，
    		可以激活应用程序角色r_p_test，激活应用程序角色后，登录本身的权限会消失。
    	下面在查询分析器中登录，演示激活应用程序角色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 行）
    --*/