none
SQL SERVER 存储过程授权的问题 RRS feed

  • 问题

  • --步骤一:登录本地创建用户
    USE [master]
    GO
    CREATE LOGIN [locaUser] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[locaDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    CREATE LOGIN [locaAdmin] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[locaDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [locaDB]
    GO
    CREATE USER [locaUser] FOR LOGIN [locaUser]
    CREATE USER [locaAdmin] FOR LOGIN [locaAdmin]
    EXEC sp_addrolemember N'db_owner', N'locaAdmin'
    GO
    --步骤二:登录远程创建用户
    USE [master]
    GO
    CREATE LOGIN [locaUser] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[remoDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    CREATE LOGIN [locaAdmin] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[remoDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [remoDB]
    GO
    CREATE USER [locaUser] FOR LOGIN [locaUser]
    CREATE USER [locaAdmin] FOR LOGIN [locaAdmin]
    EXEC sp_addrolemember N'db_owner', N'locaAdmin'
    GO
    --步骤三:登录本地创建连接服务器
    USE [master]
    GO
    SP_ADDLINKEDSERVER @server = N'linkserver1',
       @srvproduct = N' ',
       @provider = N'SQLOLEDB', 
       @datasrc = N'192.168.1.1'
    GO
    SP_ADDLINKEDSRVLOGIN N'linkserver1', false, N'locaUser', N'locaUser', N'passWord'
    GO
    SP_ADDLINKEDSRVLOGIN N'linkserver1', false, N'locaAdmin', N'locaAdmin', N'passWord'
    GO
    --步骤四:locaAdmin登录本地创建过程访问远程表数据
    USE [locaDB]
    GO
    CREATE PROCEDURE [dbo].[SP_TEST] 
    AS
    EXECUTE AS LOGIN = 'locaAdmin'
    SELECT * FROM linkserver1.remoDB.dbo.table1
    GO
    --步骤五:locaAdmin登录本地对locaUser授权
    GRANT EXECUTE ON [dbo].[SP_TEST] TO [locaUser]
    --步骤六:locaUser登录本地执行SP_TEST
    EXEC SP_TEST --提示链接服务器 "linkserver1" 的 OLE DB 访问接口 "SQLNCLI" 不包含表 ""remoDB"."dbo"."table1""。该表不存在,或者当前用户没有访问该表的权限。

    请问是哪个步骤有问题呢?谢谢


    never

    2013年11月6日 15:14

答案

  • 我的上一个回复有误

    大致是这样的,对于存储过程的调用,影响权限的,有两个因素

    一个是调用者(执行存储过程的用户)

    另一个是存储过程所有者(Owner,如果有指定 with execute as 'xx' 选项,则是 xx 这个特定用户)

    调用者需要的权限是调用存储过程的权限,这是第一步

    第二步,对于存储过程中访问的对象,如果对象的所有者和存储过程的所有者相同,则不需要检查调用者是否具有这个对象的权限,如果对象跨数据库,则还受跨数据库所有权链配置(服务器级选项cross db ownership chaining和数据库级DB_CHAINING选项)的影响

    对于链接服务器,这里有一个问题,首次执行,是要生成执行计划的,也就意味着要获取元数据,这个元数据获取的时候,很显然是语句执行前,也就是没有 EXECUTE AS LOGIN = 'locaAdmin',  这个时候的身份是调用者,而他很显然是没有权限的。对于链接服务器的查询而言,这个元数据的获取是在每次查询前都会进行,其中的sp_columns_100_rowset,还要求SELECT权限


    • 已编辑 zjcxc.邹建 2013年11月7日 2:53 调整错误
    • 已标记为答案 jacky.joey 2013年11月7日 15:00
    2013年11月7日 2:24

全部回复

  • What's purpose of 'execute as' in the sp? You mapped user to remote login already in LS, did you?
    2013年11月6日 15:37
  • LS=Local Server?

    我想使用locaUser执行SP_TEST,但locaUser并无linkserver1.remoDB.dbo.table1表的SELECT权限,而locaAdmin拥有linkserver1.remoDB.dbo.table1表的SELECT权限,所以加入EXECUTE AS LOGIN = 'locaAdmin'这句。


    never

    2013年11月6日 16:01
  • LS = linked server. Did sp work as expected when run by locaAdmin?
    2013年11月6日 16:20
  • 我的上一个回复有误

    大致是这样的,对于存储过程的调用,影响权限的,有两个因素

    一个是调用者(执行存储过程的用户)

    另一个是存储过程所有者(Owner,如果有指定 with execute as 'xx' 选项,则是 xx 这个特定用户)

    调用者需要的权限是调用存储过程的权限,这是第一步

    第二步,对于存储过程中访问的对象,如果对象的所有者和存储过程的所有者相同,则不需要检查调用者是否具有这个对象的权限,如果对象跨数据库,则还受跨数据库所有权链配置(服务器级选项cross db ownership chaining和数据库级DB_CHAINING选项)的影响

    对于链接服务器,这里有一个问题,首次执行,是要生成执行计划的,也就意味着要获取元数据,这个元数据获取的时候,很显然是语句执行前,也就是没有 EXECUTE AS LOGIN = 'locaAdmin',  这个时候的身份是调用者,而他很显然是没有权限的。对于链接服务器的查询而言,这个元数据的获取是在每次查询前都会进行,其中的sp_columns_100_rowset,还要求SELECT权限


    • 已编辑 zjcxc.邹建 2013年11月7日 2:53 调整错误
    • 已标记为答案 jacky.joey 2013年11月7日 15:00
    2013年11月7日 2:24
  • 如果要实现需求,还是需要在链接服务器上,为调用者授予相应的权限

    或者你把链接服务器的操作放到链接服务器上的存储过程中(对于链接服务器而言,就是本地存储过程),然后授予用户 execute 权限

    2013年11月7日 2:56
  • locaAdmin能够正常执行该存储过程。

    never

    2013年11月7日 5:51
  • LocaAdmin有足够的权限,当然能够执行
    2013年11月7日 9:04
  • 谢谢您的帮助,最后的办法是在远端库中使用过程并对远端用户授权,本地数据库嵌套过程(本地过程调用远端过程)并对本地用户授权,可达到类似效果,但需将远端过程执行结果赋值给多个变量时处理起来较复杂。


    never

    2013年11月7日 15:00
  • 是一个,但不知为何给标记为答复了

    never

    2013年11月7日 15:00