Início / dba / Perguntas / 111095
Accepted
BobNoobGuy
Asked: 2015-08-15 09:54:35 +0800 CST

Por que funções de valor escalar precisam de permissão de execução em vez de seleção?

  • 772

Eu estou querendo saber por que, para função de valor escalar, que eu tenho que conceder ao usuário para executar em vez de apenas um select?

enquanto isso, uma função com valor de tabela funciona bem com apenas permissão ou db_datareaderassociação de seleção.

para ser mais claro aqui está o meu exemplo: Eu preciso de um usuário que tenha permissão somente leitura para o banco de dados. então eu criei um usuário chamado testUsere dei a ele uma db_datareaderassociação. então eu criei uma função com valor de tabela chamada fn_InlineTable. E tudo é ótimo. testUserexecuta esse SQL o dia todo

select * from dbo.fn_InlineTable

então eu preciso de uma função escalar, então criei uma função escalar chamada fn_ScalarTest. testUsernão pode executar este SQL

Select dbo.fn_ScalarTest(1)

Bem compreensivelmente: é porque eu não dei permissão "testUser" para executar fn_ScalarTest.

Minha pergunta é: com base neste link https://stackoverflow.com/questions/6150888/insert-update-delete-with-function-in-sql-server , que diz que FUNCTIONnão pode ser usado para executar ações que modificam o estado do banco de dados . Então, por que não permitir que uma função escalar seja usada com a mesma permissão "SELECT" em vez de permissão de execução?

Espero que minha pergunta faça sentido. Obrigada.

sql-server t-sql

2 respostas

  1. Best Answer

    Muito provavelmente, o motivo principal é que as funções com valor de tabela retornam um conjunto de resultados, assim como tabelas e exibições. Isso significa que eles podem ser usados ​​na FROMcláusula (incluindo JOINs e APPLYs, etc) de SELECT, UPDATE, e DELETEconsultas. No entanto, você não pode usar uma UDF escalar em nenhum desses contextos.

    Secundariamente, você também pode EXECUTEusar uma UDF escalar. Essa sintaxe é bastante útil quando você tem valores padrão especificados para parâmetros de entrada. Tome o seguinte UDF, por exemplo:

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

    Se você quiser tratar qualquer um dos parâmetros de entrada como "opcional", ainda precisará passar a palavra- DEFAULTchave ao chamá-la como uma função, pois a assinatura é fixa:

    DECLARE @Bob1 INT;

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

SELECT @Bob1;
-- Returns: 102

    Por outro lado, se você EXECUTEusar a função, poderá tratar quaisquer parâmetros com um valor padrão como verdadeiramente opcionais, assim como com Stored Procedures. Você pode passar os primeiros n parâmetros sem especificar nomes de parâmetros:

    DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

    Você pode até pular o primeiro parâmetro especificando nomes de parâmetros, novamente, assim como com Stored Procedures:

    DECLARE @Bob3 INT;

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

SELECT @Bob3;
-- Returns: 51

    ATUALIZAR

    Por que você pode querer usar a EXECsintaxe para chamar uma UDF escalar como um procedimento armazenado? Ocasionalmente, existem UDFs que são ótimas para se ter como UDFs, pois podem ser adicionadas a uma consulta e operar sobre o conjunto de linhas retornadas, enquanto que, se o código estivesse em um procedimento armazenado, ele precisaria ser colocado em um cursor para iterar sobre um conjunto de linhas. Mas há momentos em que você deseja chamar essa função em um único valor, possivelmente de dentro de outra UDF. Chamar uma UDF para um único valor pode ser feito da seguinte maneira:

    SELECT dbo.UDF('some value');

    nesse caso, você obtém um valor de retorno em um conjunto de resultados (um conjunto de resultados não funcionará). Ou pode ser feito da seguinte forma:

    DECLARE @Dummy INT;

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

    nesse caso você precisa declarar a @Dummyvariável;

    NO ENTANTO, com a EXECsintaxe, você pode evitar esses dois aborrecimentos:

    EXEC dbo.UDF 'some value';

    ALÉM DISSO, UDFs escalares têm seus planos de execução armazenados em cache. Isso significa que é possível encontrar problemas de detecção de parâmetros se houver consultas na UDF que tenham planos de execução. Para cenários em que é viável usar a EXECsintaxe, é possível também usar a WITH RECOMPILEopção de ignorar o valor compilado dos planos para essa execução . Por exemplo:

    CONFIGURAR:

    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

    TESTE:

    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. Eu acho que a diferença nas permissões é porque você pode realmente invocar funções definidas pelo usuário com valor escalar com EXEC, assim como procedimentos armazenados (o que eu não tinha percebido até pesquisar no SQL Server 2000 Books Online, onde eles introduziram funções definidas pelo usuário) , mas você não pode selecioná-los como uma fonte de tabela. Por exemplo:

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

    Nesse caso, dbo.first_day_of_month é uma função definida pelo usuário. Não sei por que você invocaria uma função dessa maneira, mas especularia que eles exigiam permissão EXECUTE em vez de SELECT para manter a consistência. Hoje em dia provavelmente está apenas se segurando como bagagem de compatibilidade.

    • 6

relate perguntas