主页 / dba / 问题 / 111095
Accepted
BobNoobGuy
Asked: 2015-08-15 09:54:35 +0800 CST

为什么标量值函数需要执行权限而不是选择？

  • 772

我想知道为什么对于标量值函数，我必须授予用户执行而不仅仅是选择？

同时，表值函数只需选择权限或db_datareader成员资格即可正常工作。

在这里更清楚的是我的例子：我需要一个对数据库具有只读权限的用户。所以我创建了一个名为的用户testUser并为其提供db_datareader会员资格。然后我创建了一个名为fn_InlineTable. 一切都很好。testUser整天运行这个 SQL

select * from dbo.fn_InlineTable

那么我需要一个标量函数，所以我创建了一个名为fn_ScalarTest. testUser无法运行此 SQL

Select dbo.fn_ScalarTest(1)

可以理解：这是因为我没有授予“testUser”执行权限fn_ScalarTest。

我的问题是：基于此链接https://stackoverflow.com/questions/6150888/insert-update-delete-with-function-in-sql-server，表示 aFUNCTION不能用于执行修改数据库状态的操作. 那么为什么不让标量函数使用相同的“SELECT”权限而不是执行权限呢？

我希望我的问题是有道理的。谢谢你。

sql-server t-sql

2 个回答

  1. Best Answer

    最可能的主要原因是表值函数返回一个结果集，就像表和视图一样。这意味着它们可以用在, , 和查询的FROM子句（包括JOINs 和APPLYs 等）中。但是，您不能在任何这些上下文中使用标量 UDF。SELECTUPDATEDELETE

    其次，您还可以EXECUTE使用标量 UDF。当您为输入参数指定默认值时，此语法非常方便。以下面的 UDF 为例：

    CREATE FUNCTION dbo.OptionalParameterTest (@Param1 INT = 1, @Param2 INT = 2)
RETURNS INT
AS
BEGIN
    RETURN @Param1 + @Param2;
END;

    如果您想将任何输入参数视为“可选”，DEFAULT则在像函数一样调用它时仍然需要传入关键字，因为签名是固定的：

    DECLARE @Bob1 INT;

SET @Bob1 = dbo.OptionalParameterTest(100, DEFAULT);

SELECT @Bob1;
-- Returns: 102

    另一方面，如果您EXECUTE使用函数，那么您可以将任何具有默认值的参数视为真正的可选参数，就像使用存储过程一样。您可以在不指定参数名称的情况下传入前n 个参数：

    DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

    您甚至可以通过指定参数名称来跳过第一个参数，就像使用存储过程一样：

    DECLARE @Bob3 INT;

EXEC @Bob3 = dbo.OptionalParameterTest @Param2 = 50;

SELECT @Bob3;
-- Returns: 51

    更新

    为什么要使用EXEC语法来调用标量 UDF，就像存储过程一样？有时，有一些 UDF 非常适合作为 UDF，因为它们可以添加到查询中并对返回的行集进行操作，而如果代码位于存储过程中，则需要将其放入游标中以便遍历一组行。但是有时您想在单个值上调用该函数，可能来自另一个 UDF。为单个值调用 UDF 可以通过以下方式完成：

    SELECT dbo.UDF('some value');

    在这种情况下，您会在结果集中获得返回值（结果集将不起作用）。或者可以按如下方式完成：

    DECLARE @Dummy INT;

SET @Dummy = dbo.UDF('some value');

    在这种情况下，您需要声明@Dummy变量；

    但是，使用EXEC语法，您可以避免这两种烦恼：

    EXEC dbo.UDF 'some value';

    此外，标量 UDF 缓存了它们的执行计划。这意味着，如果 UDF 中存在具有执行计划的查询，则可能会遇到参数嗅探问题。EXEC对于可以使用语法的场景，也可以使用选项来忽略该执行WITH RECOMPILE的计划编译值。例如：

    设置：

    GO
CREATE FUNCTION dbo.TestUDF (@Something INT)
RETURNS INT
AS 
BEGIN
   DECLARE @Ret INT;
   SELECT @Ret = COUNT(*)
   FROM   sys.indexes si
   WHERE  si.[index_id] = @Something;

   RETURN @Ret;
END;
GO

    测试：

    DECLARE @Val INT;

SET @Val = dbo.TestUDF(1);
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 -- uses compiled value of (1)
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 WITH RECOMPILE; -- uses compiled value of (0)
SELECT @Val;

EXEC @Val = dbo.TestUDF 3 -- uses compiled value of (1)
SELECT @Val;
    • 16

  2. 我认为权限的差异是因为您实际上可以像存储过程一样使用 EXEC 调用标量值用户定义函数（直到我深入研究 SQL Server 2000 联机丛书，他们在其中引入了用户定义函数，我才意识到这一点） ，但您实际上不能从它们中选择作为表源。例如：

    DECLARE @date datetime
EXEC @date = dbo.first_day_of_month '8/14/2015'
SELECT @date

    在这种情况下，dbo.first_day_of_month 是用户定义的函数。我不知道你为什么会那样调用函数，但我推测他们需要 EXECUTE 权限而不是 SELECT 来保持一致性。如今，它可能只是作为兼容性包袱。

    • 6

相关问题