Simulado para o Exame 70-433 – MCTS: Microsoft SQL Server 2008 – Database Development – Parte 03

Oi Pessoal,

A última parte do simulado finalizou o tópico "Implementing Tables and Views". As próximas lições irão focar o tópico "Implementing Programming Objects" responsável por 16% das questões da prova.

Questão 022
Qual dos benefícios abaixo não pode ser obtido por uma stored procedure ?

A. Redução do tráfego de rede
B. Uso de outra linguagem que não o T-SQL
C. Melhorias na segurança do banco de dados
D. Facilidade de manutenção de código
E. Chamadas diretas a partir de um instruções SELECT locais
F. Melhorias de desempenho

Questão 023
A empresa ARB Importados possui uma aplicação de e-commerce em .NET. A aplicação grava pedidos de compras de equipamentos importados. Cada pedido possui um ou mais produtos a serem adquiridos conforme o modelo abaixo:

Quando um pedido é realizado, a aplicação grava um registro de pedido e em seguida, através de um loop, grava os produtos inclusos naquele pedido (no máximo cinco produtos por pedidos). Tanto a gravação do pedido quanto a gravação dos itens do pedido é feita através de stored procedures (uma para cada tabela). Você é um dos programadores da aplicação e lhe foi solicitado que apresentassem uma solução para que em uma única stored procedure fosse feita a inserção do pedido e de seus respectivos itens. Tanto o pedido quanto os itens seriam parâmetros de entrada dessa stored procedure. Qual seria a implementação mais adequada ?

A. Criação de uma stored procedure com todos os atributos de pedido e de cinco itens de pedido
B. Concatenação de todos os parâmetros em uma string delimitada por ponto e vírgula. Dentro da stored procedure haveria a separação dos parâmetros e inserção dos registros nas respectivas tabelas
C. Utilização de variáveis do tipo TABLE
D. Utilização de estruturas do tipo Table Value Parameter

Questão 024
Qual é a diferença entre parâmetros de saída (output parameters) e valores de retorno (return value) em stored procedures ?

A. O valor de retorno permite apenas o tipo INT enquanto parâmetros de saída podem trabalhar com outros tipos de dados
B. Só pode haver um único parâmetro de saída enquanto podem haver vários valores de retorno
C. Não há diferença entre eles. Ambos são sinônimos para um retorno de uma stored procedure
D. O parâmetro de saída será sempre um resultset enquanto que o valor de retorno é um tipo simples

Questão 025
A gerência de segurança da informação da empresa onde você trabalha como arquiteto está revendo as políticas de acesso a objetos de banco de dados. Embora o acesso das aplicações ao banco de dados seja apenas via stored procedures, há muitas permissões diretas em tabelas. Como parte de um esforço para melhoria da segurança de banco de dados, houve uma determinação de retirar todas as permissões diretas nas tabelas sempre que possível para evitar acesso a dados de forma não autorizada. Após retirar as permissões, uma determinada stored procedure apresentou o seguinte erro:

EXEC UspRecuperaRegistrosAleatorios @Quantidade = 10, @Tabela = ‘ModeloProposta’

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘ModeloProposta’, database ‘BDContas’, schema ‘FIN’.

A procedure UspRecuperaRegistros tem o seguinte código:

CREATE PROCEDURE UspRecuperaRegistrosAleatorios
   
@Quantidade INT,
   
@Tabela VARCHAR(50)
As
IF @Quantidade > 10
BEGIN
   
RAISERROR(‘Não é permitido escolher mais de 10 registros’,16,1)
   
RETURN
END

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @tabela)
BEGIN
   
RAISERROR(‘Tabela Inexistente’,16,1)
   
RETURN
END

DECLARE @cmdSQL VARCHAR(200)
SET @cmdSQL = ‘SELECT TOP(‘ +
    CAST(@Quantidade As VARCHAR(4)) + ‘) * FROM ‘ + @Tabela + ‘ ORDER BY NEWID()’

EXEC(@cmdSQL)

Essa stored procedure normalmente é executada contra três principais tabelas (ModeloProposta, LancamentoContabil e EstornoContabil) para obter aleatoriamente até 10 registros de qualquer uma dessas tabelas pelo usuário UsrContab. De forma a obedecer as diretrizes de segurança, qual seria medida mais indicada para que o erro não ocorra ?

A. Criar um usuário a parte com acesso às tabelas ModeloProposta, LancamentoContabil e EstornoContabil. Incluir dentro da stored procedure a cláusula EXECUTE AS referenciando esse usuário.
B. Criar uma role e conceder permissão somente de SELECT nas tabelas ModeloProposta, LancamentoContabil e EstornoContabil.
C. Criar uma role e conceder permissão somente de SELECT nas tabelas ModeloProposta, LancamentoContabil e EstornoContabil. Incluir o usuário UsrContab na role.
D. Adicionar o usuário UsrContab à role db_datareader.

Questão 026
A empresa High Solutions LTDA o contratou como consultor de banco de dados para um projeto de revisão das implementações já realizadas. Há várias stored procedures de leitura de dados apresentando problemas aleatórios de lentidão. Ocorre que com determinados parâmetros ela produzem bons tempos de resposta, mas ao trocar os valores desses parâmetros os tempos de resposta aumentam drasticamente. Você suspeita que os planos de execução dessas stored procedures não estão adequados aos valores informados. Como parte do processo de análise dessas stored procedures, você deseja produzir um plano de execução para cada chamada da stored procedure. Qual das alternativas abaixo você deverá adotar para fazer isso ?

A. Preparar um script para executar todas as procedures com a clásula WITH RECOMPILE
B. Incluir a cláusula WITH RECOMPILE no corpo da stored procedure
C. Executar o comando sp_recompile ‘Procedure’ para cada stored procedure
D. Utilizar o comando ALTER PROCEDURE para cada stored procedure envolvida
E. Utilizar o HINT (WITH RECOMPILE) para cada comando dentro da stored procedure

Questão 027
Em um projeto de ERP que você está desenvolvendo surgiu a necessidade de criar uma função que verifique a quantidade em estoque de um determinado produto. O parâmetro de entrada dessa function será o ID do produto e o retorno deve ser a quantidade em estoque (INT). Qual o tipo de function que melhor atende à essa necessidade ?

A. InLine Function
B. MultiStatement Function
C. Table Valued Function
D. System Function
E. Scalar Function

Questão 028
Um determinado banco de dados possui três esquemas chamados FIN, RH e MKT e os donos desses esquemas são respectivamente os usuários UsrFin, UsrRH e UsrMKT. O esquema FIN possui uma tabela chamada PagamentoFuncionario e o usuário UsrRH tem permissão de SELECT nessa tabela. O usuário UsrRH criou uma function através do seguinte trecho de código:

CREATE FUNCTION RH.TotalPagoFunc (@IDFunc INT)
RETURNS MONEY
AS
BEGIN
    DECLARE
@Total MONEY
    SET @Total = (SELECT SUM(Salario) FROM FIN.PagamentoFuncionario
        WHERE IDFunc = @IDFunc)
    RETURN @Total
END

O usuário UsrRH testou a função e conseguiu utilizá-la adequadamente. Posteriormente, o usuário UsrRH concedeu permissão na function RH.TotalPagoFunc para o usuário UsrMKT. Quando o usuário UsrMKT tentou executar a função uma mensagem de erro foi retornada.

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘PagamentoFuncionario’, database ‘ERP’, schema ‘FIN’.

Segundo o princípio do menor privilégio, qual alternativa é a mais segura para fazer com que o usuário UsrMKT consiga retornar os dados da function ?

A. Conceder permissão de SELECT na tabela FIN.PagamentoFuncionario para o usuário UsrMKT
B. Incluir o usuário UsrMKT na role db_datareader
C. Incluir o usuário UsrMKT na role db_owner
D. Incluir no código da function a instrução WITH EXECUTE AS ‘UsrRH’
E. Tornar o usuário UsrRH dono do esquema FIN

Questão 029
Um desenvolvedor criou uma function com o seguinte código:

CREATE FUNCTION dbo.ListaVendas(@IDEmpregado INT)
RETURNS TABLE
AS RETURN
(SELECT * FROM Pedidos WHERE IDEmpregado = @IDEmpregado)

Como parte de uma série de testes unitários, você tentou executar um comando que referencia a function com o usuário UsrApp

SELECT * FROM dbo.ListaVendas(1)

Após a execução do comando, uma mensagem de erro é retornada:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘ListaVendas’, database ‘Pedidos’, schema ‘dbo’.

Qual a instrução que deve ser executada para que você consiga efetuar o teste corretamente ?

A. REVOKE SELECT ON dbo.Pedidos TO UsrApp
B. GRANT SELECT ON dbo.Pedidos TO UsrApp
C. GRANT EXECUTE ON dbo.Pedidos TO UsrApp
D. GRANT SELECT ON dbo.ListaVendas TO UsrApp
E. GRANT EXECUTE ON dbo.ListaVendas TO UsrApp

Respostas

Questão 022
Qual dos benefícios abaixo não pode ser obtido por uma stored procedure ?

A. Redução do tráfego de rede
B. Uso de outra linguagem que não o T-SQL
C. Melhorias na segurança do banco de dados
D. Facilidade de manutenção de código
E. Chamadas diretas a partir de um instruções SELECT locais
F. Melhorias de desempenho

Resposta Correta: E

Embora as stored procedures permitam várias possibilidades, não é possível chamar uma stored procedure a partir de uma instrução SELECT local. Ex: SELECT <Campos> FROM <Stored Procedure>.

Respostas Incorretas: A, B, C, D, F

A – As stored procedures reduzem o tráfego de rede uma vez que que possibilitam as aplicações chamarem apenas a stored procedure em uma única instrução ao invés de codificar várias linhas de código para obter o retorno desejado diminuindo assim a quantidade de informação submetida ao banco de dados

B – A partir do SQL Server 2005 é possível utilizar linguagens compatíveis com o Framework .NET para desenvolvimento de stored procedures como o VB.NET, C#, F#, etc.

C – É possível conceder permissão de execução de uma stored procedure sem a necessidade de conceder permissão aos objetos por ela manipulados.

D – Se uma stored procedure for chamada em vários trechos de código de uma aplicação e (ou) por várias aplicações, é possível que uma mudança possa ser implementada apenas alterando-se o código da stored procedure evitando a mudança em vários locais, atividades de deploy, etc

F – A stored procedure quando executada pela primeira vez produz um plano de execução e o reproveita para as chamadas subsequentes (mesmo que haja mudança dos valores do parâmetros) evitando que uma nova chamada tenha de produzir o plano de execução novamente. Essa reutilização do plano de execução tende a melhor o desempenho.

Questão 023
A empresa ARB Importados possui uma aplicação de e-commerce em .NET. A aplicação grava pedidos de compras de equipamentos importados. Cada pedido possui um ou mais produtos a serem adquiridos conforme o modelo abaixo:

Quando um pedido é realizado, a aplicação grava um registro de pedido e em seguida, através de um loop, grava os produtos inclusos naquele pedido (no máximo cinco produtos por pedidos). Tanto a gravação do pedido quanto a gravação dos itens do pedido é feita através de stored procedures (uma para cada tabela). Você é um dos programadores da aplicação e lhe foi solicitado que apresentassem uma solução para que em uma única stored procedure fosse feita a inserção do pedido e de seus respectivos itens. Tanto o pedido quanto os itens seriam parâmetros de entrada dessa stored procedure. Qual seria a implementação mais adequada ?

A. Criação de uma stored procedure com todos os atributos de pedido e de cinco itens de pedido
B. Concatenação de todos os parâmetros em uma string delimitada por ponto e vírgula. Dentro da stored procedure haveria a separação dos parâmetros e inserção dos registros nas respectivas tabelas
C. Utilização de variáveis do tipo TABLE
D. Utilização de estruturas do tipo Table Value Parameter

Resposta Correta: D

Explicação: A estrutura Table Value Parameter permite que tabelas inteiras sejam passadas como parâmetros. Nesse caso pode-se criar uma tabela com o pedido e uma tabela com os itens daquele pedido e repassá-las como parâmetros. Ex:

CREATE TYPE Pedidos As TABLE (<Definições de estrutura>)
CREATE TYPE Itens As TABLE (<Definições de estrutura>)

CREATE PROCEDURE uspInserirPedidoCompleto (
  @Pedidos As Pedidos READONLY,
  @Itens As Itens READONLY)
As
     INSERT INTO Pedidos
     SELECT * FROM @Pedidos

     INSERT INTO Itens
     SELECT * FROM @Itens

Respostas Incorretas: A, B, C

A – Criar uma stored procedure com todos os atributos possíveis é uma solução muito trabalhosa. Os pedidos que tivessem dois itens teriam os demais atributos dos outros itens nulos. Outro problema seria validar os atributos realmente obrigatórios, pois, como a quantidade de itens é variável todos os parâmetros deveriam ser opcionais.

B – A concatenação de todos os parâmetros em uma string possui os mesmos problemas da stored procedure com os atributos de pedidos e de itens de pedido. Haverá preocupações adicionais em validar se os valores passados são do tipo correto e a montagem da lógica para recuperar todos os valores a partir da string.

C – Variáveis do tipo TABLE não podem ser utilizadas como parâmetros de entrada de uma stored procedure. Essa necessidade levou a criação de estruturas do tipo Table Value Parameter

Referências:
Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1066.entry

Algumas implementações multivaloradas com XML e Table Value Parameter – Parte II
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1067.entry

Questão 024
Qual é a diferença entre parâmetros de saída (output parameters) e valores de retorno (return value) em stored procedures ?

A. O valor de retorno permite apenas o tipo INT enquanto parâmetros de saída podem trabalhar com outros tipos de dados
B. Só pode haver um único parâmetro de saída enquanto podem haver vários valores de retorno
C. Não há diferença entre eles. Ambos são sinônimos para um retorno de uma stored procedure
D. O parâmetro de saída será sempre um resultset enquanto que o valor de retorno é um tipo simples

Resposta Correta: A

Explicação: Os valor de retorno retorna um tipo de dados inteiro que tem como valor padrão 0 caso não seja informado. O valor de retorno é definido através do comando RETURN que cessa a execução do código da stored procedure e retorna o valor especificado. O parâmetro de saída é um parâmetro presente no corpo da stored procedure assim como os parâmetros de entrada, mas é acompanhado da palavra OUTPUT. Como os parâmetros das stored procedures utilizam variáveis, o parâmetro de saída pode ser de outros tipos que não o INT.

Respostas Incorretas: B, C, D

B – Só pode haver um único valor de retorno, uma vez que qualquer comando RETURN irá cessar a execução da stored procedure. Caso a stored procedure não possua instrução de RETURN, automaticamente o valor de retorno será igual a zero. Já os parâmetros de uma stored procedure podem chegar 2100 e isso inclui os parâmetros de saída.

C – Embora o valor de retorno e os parâmetros de saída possam representar um retorno, eles não são sinônimos e possuem restrições diferentes. Normalmente o valor de retorno é utilizado para sinalizar um status de execução (ex: 0 – erro, 1 – sucesso) e os parâmetros de saída são utilizados para repassar algum valor como o último ID inserido, um valor criptografado, etc

D – O parâmetro de saída é representado através de variáveis e isso inclui tipos simples e não resultsets

Questão 025
A gerência de segurança da informação da empresa onde você trabalha como arquiteto está revendo as políticas de acesso a objetos de banco de dados. Embora o acesso das aplicações ao banco de dados seja apenas via stored procedures, há muitas permissões diretas em tabelas. Como parte de um esforço para melhoria da segurança de banco de dados, houve uma determinação de retirar todas as permissões diretas nas tabelas sempre que possível para evitar acesso a dados de forma não autorizada. Após retirar as permissões, uma determinada stored procedure apresentou o seguinte erro:

EXEC UspRecuperaRegistrosAleatorios @Quantidade = 10, @Tabela = ‘ModeloProposta’

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘ModeloProposta’, database ‘BDContas’, schema ‘FIN’.

A procedure UspRecuperaRegistros tem o seguinte código:

CREATE PROCEDURE UspRecuperaRegistrosAleatorios
   
@Quantidade INT,
   
@Tabela VARCHAR(50)
As
IF @Quantidade > 10
BEGIN
   
RAISERROR(‘Não é permitido escolher mais de 10 registros’,16,1)
   
RETURN
END

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @tabela)
BEGIN
   
RAISERROR(‘Tabela Inexistente’,16,1)
   
RETURN
END

DECLARE @cmdSQL VARCHAR(200)
SET @cmdSQL = ‘SELECT TOP(‘ +
    CAST(@Quantidade As VARCHAR(4)) + ‘) * FROM ‘ + @Tabela + ‘ ORDER BY NEWID()’

EXEC(@cmdSQL)

Essa stored procedure normalmente é executada contra três principais tabelas (ModeloProposta, LancamentoContabil e EstornoContabil) para obter aleatoriamente até 10 registros de qualquer uma dessas tabelas pelo usuário UsrContab. De forma a obedecer as diretrizes de segurança, qual seria medida mais indicada para que o erro não ocorra ?

A. Criar um usuário a parte com acesso às tabelas ModeloProposta, LancamentoContabil e EstornoContabil. Incluir dentro da stored procedure a cláusula EXECUTE AS referenciando esse usuário.
B. Criar uma role e conceder permissão somente de SELECT nas tabelas ModeloProposta, LancamentoContabil e EstornoContabil.
C. Criar uma role e conceder permissão somente de SELECT nas tabelas ModeloProposta, LancamentoContabil e EstornoContabil. Incluir o usuário UsrContab na role.
D. Adicionar o usuário UsrContab à role db_datareader.

Resposta Correta: A

Explicação: A criação de instruções SQL dinâmicas faz com que o contexto de segurança seja revalidado e por isso, mesmo, que o usuário UsrContab tenha permissão na stored procedure, o fato de haver uma instrução SQL dinâmica fará com que esse usuário tenha a necessidade de ter permissões nos objetos utilizados, nesse caso as tabelas ModeloProposta, LancamentoContabil e EstornoContabil. A utilização da cláusula EXECUTE AS fará com que durante a execução da stored procedure um outro usuário seja utilizado, ou seja, o contexto de segurança da stored procedure será alterado apenas durante a  execução da stored procedure. Se esse outro usuário possuir permissão nas tabelas, a stored procedure ocorrerá normalmente sem a necessidade de concessão aos objetos. Após a execução da stored procedure o contexto de segurança voltará a ser do usuário UsrContab evitando assim que ele acesse as tabelas diretamente. Um código possível para essa implementação seria:

— Criação do Usuário
CREATE USER UsrRecuperaRegistros WITHOUT Login

— Concessão das permissões para o Usuário
GRANT SELECT ON ModeloProposta TO UsrRecuperaRegistros
GRANT SELECT ON LancamentoContabil TO UsrRecuperaRegistros
GRANT SELECT ON EstornoContabil TO UsrRecuperaRegistros

— Criação da Stored Procedure
CREATE PROCEDURE UspRecuperaRegistrosAleatorios
    @Quantidade INT,
    @Tabela VARCHAR(50)
WITH EXECUTE AS ‘UsrRecuperaRegistros’
As
IF @Quantidade > 10
BEGIN
    RAISERROR(‘Não é permitido escolher mais de 10 registros’,16,1)
    RETURN
END

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = @tabela)
BEGIN
    RAISERROR(‘Tabela Inexistente’,16,1)
    RETURN
END

DECLARE @cmdSQL VARCHAR(200)
SET @cmdSQL = ‘SELECT TOP(‘ +
    CAST(@Quantidade As VARCHAR(4)) +
    ‘) * FROM ‘ + @Tabela + ‘ ORDER BY NEWID()’

EXEC(@cmdSQL)

Respostas Incorretas: B, C, D

B – A criação de uma role sozinha não irá impedir que o erro ocorra uma vez que não há nenhuma associação entre a role e o contexto de segurança que executa a stored procedure

C – Essa medida irá resolver o erro, mas desobedecerá as diretrizes de segurança estabelecidas uma vez que será possível para o usuário UsrContab acessar as tabelas ModeloProposta, LancamentoContabil e EstornoContabil diretamente.

D – Essa medida irá resolver o erro, mas desobedecerá as diretrizes de segurança estabelecidas uma vez que será possível para o usuário UsrContab acessar as tabelas ModeloProposta, LancamentoContabil e EstornoContabil diretamente. Haverá ainda um risco adicional, pois, a role db_datareader permitirá ao usuário UsrContab o acesso irrestrito de leitura a qualquer tabela.

Questão 026
A empresa High Solutions LTDA o contratou como consultor de banco de dados para um projeto de revisão das implementações já realizadas. Há várias stored procedures de leitura de dados apresentando problemas aleatórios de lentidão. Ocorre que com determinados parâmetros ela produzem bons tempos de resposta, mas ao trocar os valores desses parâmetros os tempos de resposta aumentam drasticamente. Você suspeita que os planos de execução dessas stored procedures não estão adequados aos valores informados. Como parte do processo de análise dessas stored procedures, você deseja produzir um plano de execução para cada chamada da stored procedure. Qual das alternativas abaixo você deverá adotar para fazer isso ?

A. Preparar um script para executar todas as procedures com a clásula WITH RECOMPILE
B. Incluir a cláusula WITH RECOMPILE no corpo da stored procedure
C. Executar o comando sp_recompile ‘Procedure’ para cada stored procedure
D. Utilizar o comando ALTER PROCEDURE para cada stored procedure envolvida
E. Utilizar o HINT (WITH RECOMPILE) para cada comando dentro da stored procedure

Resposta Correta: B

Explicação: O uso da cláusula WITH RECOMPILE no corpo da stored procedure fará com que a cada execução, a stored procedure produza um novo plano de execução. Normalmente o reaproveitamento do plano de execução faz com que as stored procedures tenham desempenho superior a de consultas ADHOC, mas em determinadas situações, é possível que um único plano possa produzir bons resultados para um conjunto de valores e maus resultados para outros conjuntos de valores. Nessas situações, o uso da cláusula WITH RECOMPILE pode ser uma boa alternativa, mesmo que para cada chamada da stored procedure seja necessário produzir um plano de execução.

Respostas Incorretas: A, C, D, E

A – A execução de uma procedure com a cláusula WITH RECOMPILE fará com que aquela execução produza um novo plano de execução. Entretanto, as execuções subsequentes não irão produzir novos planos de execução senão estiverem com a cláusula WITH RECOMPILE. O script irá produzir novos planos na execução, mas as chamadas futuras das stored procedures não. É possível alterar todas as chamadas das stored procedures para incluir a cláusula WITH RECOMPILE. Ex: EXEC [Procedure] [Parâmetros] WITH RECOMPILE, mas isso representa um esforço de implementação desnecessário além de maiores custos de manutenção caso essa cláusula tenha de ser retirada.

C – O comando sp_recompile marca uma stored procedure para recompilação na próxima execução. Isso iria produzir um novo plano de execução apenas na próxima vez que a stored procedure for executada, as chamadas subsequentes não irão produzir novos planos de execução.

D – O comando ALTER PROCEDURE irá mudar a estrutura da stored procedure. Toda vez que a stored procedure é alterada (mesmo que a estrutura permaneça idêntica), ela produzirá um novo plano de execução na primeira vez que ela for chamada. Entretanto, as chamadas subsequentes irão reaproveitar o plano de execução que já foi gerado ao invés de gerar novos planos de execução.

E – O HINT WITH RECOMPILE é normalmente utilizado para consultas que possam provocar uma recompilação da stored procedure. Ao invés da stored procedure recompilar, apenas o comando será recompilado. Esse HINT é utilizado em nível de comando e não serve para recompilar stored procedures, mas apenas partes dela.

Questão 027
Em um projeto de ERP que você está desenvolvendo surgiu a necessidade de criar uma função que verifique a quantidade em estoque de um determinado produto. O parâmetro de entrada dessa function será o ID do produto e o retorno deve ser a quantidade em estoque (INT). Qual o tipo de function que melhor atende à essa necessidade ?

A. InLine Function
B. MultiStatement Function
C. Table Valued Function
D. System Function
E. Scalar Function

Resposta Correta: E

Explicação: As funções escalares (Scalar Functions) são capazes de receber um determinado valor e retonar um valor de estrutura simples (INT, VARCHAR, DATE, etc). Esse tipo de function é a adequada para receber um determinado ID de produto e retornar uma quantidade. Uma possível implementação seria:

CREATE FUNCTION dbo.RetornaEstoque(@IDProduto INT)
RETURNS INT
AS
BEGIN
    DECLARE
@QtdEstoque INT
    SET @QtdEstoque = (
        SELECT Estoque FROM Produto
        WHERE IDProduto = @IDProduto)

    RETURN @QtdEstoque
END

Respostas Incorretas: A, B, C, D

A – Uma função InLine recebe um parâmetro, mas retorna um ResultSet e não um valor. Ela poderia ser utilizada para retornar um ResultSet com a quantidade, embora não seja o tipo mais indicado.

B – Uma função MultiStatement tem o mesmo comportamento que a função Inline, embora ao contrário das funções InLine, as funções MultiStatement não estejam restritas a uma única consulta dentro do seu corpo. Embora seja possível utilizar um ResultSet com a quantidade, esse não é o tipo de function mais indicado para a necessidade estabelecida.

C – Table Value Functions representam todas as funções que retornem um ResultSet. As funções InLine e MultiStatement são Table Value Functions. Table Value Functions não retornam valores, mas sim ResultSets não sendo a implementação mais indicada.

D – Não é possível codificar uma System Function. Elas são nativas do SQL Server e não é permitido alterar as já existentes e nem criar novas.

Questão 028
Um determinado banco de dados possui três esquemas chamados FIN, RH e MKT e os donos desses esquemas são respectivamente os usuários UsrFin, UsrRH e UsrMKT. O esquema FIN possui uma tabela chamada PagamentoFuncionario e o usuário UsrRH tem permissão de SELECT nessa tabela. O usuário UsrRH criou uma function através do seguinte trecho de código:

CREATE FUNCTION RH.TotalPagoFunc (@IDFunc INT)
RETURNS MONEY
AS
BEGIN
    DECLARE
@Total MONEY
    SET @Total = (SELECT SUM(Salario) FROM FIN.PagamentoFuncionario
        WHERE IDFunc = @IDFunc)
    RETURN @Total
END

O usuário UsrRH testou a função e conseguiu utilizá-la adequadamente. Posteriormente, o usuário UsrRH concedeu permissão na function RH.TotalPagoFunc para o usuário UsrMKT. Quando o usuário UsrMKT tentou executar a função uma mensagem de erro foi retornada.

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘PagamentoFuncionario’, database ‘ERP’, schema ‘FIN’.

Segundo o princípio do menor privilégio, qual alternativa é a mais segura para fazer com que o usuário UsrMKT consiga retornar os dados da function ?

A. Conceder permissão de SELECT na tabela FIN.PagamentoFuncionario para o usuário UsrMKT
B. Incluir o usuário UsrMKT na role db_datareader
C. Incluir o usuário UsrMKT na role db_owner
D. Incluir no código da function a instrução WITH EXECUTE AS ‘UsrRH’
E. Tornar o usuário UsrRH dono do esquema FIN

Resposta Correta: D

Explicação: Como a tabela PagamentoEmpregado e a função TotalPagoFunc pertencem a esquemas diferentes e os donos (owners) desses esquemas são usuários diferentes, indiretamente o dono desses objetos não é o mesmo usuário. Se o dono dos objetos fosse o mesmo usuário, bastaria conceder a permissão de SELECT na function sem a necessidade de conceder a permissão de SELECT na tabela. Como os donos são diferentes, cada vez que a função é executada, as permissões na tabela FIN.Lancamentos são checadas. Como o usuário UsrRH possui permissão nessa tabela, ao executar a função não haverá erros de permissionamento. No caso do usuário UsrMKT, esse possui permissão apenas na function TotalPagoFunc, mas não possui permissão na tabela FIN.PagamentoFuncionario e por isso a mensagem de erro aparece. O uso da cláusula EXECUTE AS na function fará com que o contexto de segurança seja do usuário UsrRH independente de quem execute a função. Assim, quando o usuário UsrMKT executar a função, durante a execução, ele se passará pelo usuário UsrRH e conseguirá recuperar os dados. Após a execução da função, as credenciais voltam a ser do usuário UsrMKT, porém os dados foram recuperados corretamente.

Respostas Incorretas: A, B, C, E

A – A concessão de SELECT na tabela FIN.PagamentoFuncionario para o usuário UsrMKT permitirá que esse execute a function corretamente, mas concederá a esse a possibilidade de acessar a tabela FIN.PagamentoFuncionario diretamente incorrendo em problemas de segurança já que o usuário UsrMKT terá mais permissões do que o necessário para executar a function.

B – A inclusão do usuário UsrMKT na role db_datareader permitirá que ele acesse a tabela FIN.PagamentoFuncionario, mas concederá permissão para ler qualquer tabela. A mensagem de erro não irá mais aparecer, mas haverá um risco adicional de segurança já que o usuário UsrMKT terá mais privilégios do que o mínimo necessário para executar a função.

C – A inclusão do usuário UsrMKT na role db_owner permitirá que ele acesse a tabela FIN.PagamentoFuncionario, mas concederá permissão para efetuar qualquer operação no banco de dados. A mensagem de erro não irá mais aparecer, mas haverá um risco adicional de segurança já que o usuário UsrMKT terá mais privilégios do que o mínimo necessário para executar a função.

E – Se o usuário UsrMKT tornar-se dono do esquema FIN ele automaticamente terá acesso a tabela FIN.PagamentoFuncionario e conseguirá executar a função corretamente. Entretanto, ele terá acesso completo a todos os objetos desse esquema e portanto, mais privilégios do que o mínimo necessário para executar a function.

Questão 029
Um desenvolvedor criou uma function com o seguinte código:

CREATE FUNCTION dbo.ListaVendas(@IDEmpregado INT)
RETURNS TABLE
AS RETURN
(SELECT * FROM Pedidos WHERE IDEmpregado = @IDEmpregado)

Como parte de uma série de testes unitários, você tentou executar um comando que referencia a function com o usuário UsrApp

SELECT * FROM dbo.ListaVendas(1)

Após a execução do comando, uma mensagem de erro é retornada:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘ListaVendas’, database ‘Pedidos’, schema ‘dbo’.

Qual a instrução que deve ser executada para que você consiga efetuar o teste corretamente ?

A. REVOKE SELECT ON dbo.Pedidos TO UsrApp
B. GRANT SELECT ON dbo.Pedidos TO UsrApp
C. GRANT EXECUTE ON dbo.Pedidos TO UsrApp
D. GRANT SELECT ON dbo.ListaVendas TO UsrApp
E. GRANT EXECUTE ON dbo.ListaVendas TO UsrApp

Resposta Correta: D

Explicação: A mensagem mostra que uma permissão de SELECT é requerida sobre a função ListaVendas. O uso do comando GRANT concede a permissão de SELECT sobre a function ListaVendas e é necessário para que o teste seja concluído corretamente.

Respostas Incorretas: A, B, C, E

A – O comando REVOKE é utilizada para revogar permissões e não para concedê-las

B – Embora a função faça uma consulta sobre a tabela de Pedidos, a mensagem de erro mostra a ausência de permissão na função ListaVendas e não na tabela Pedidos.

C – Embora a função faça uma consulta sobre a tabela de Pedidos, a mensagem de erro mostra a ausência de permissão na função ListaVendas e não na tabela Pedidos. Adicionalmente a permissão correta é de SELECT e não de EXECUTE.

E – A função ListaVendas é do tipo InLine Function e retorna um ResultSet. Para que a mensagem referente ao erro de permissão não apareça, é necessário que a permissão de SELECT seja concedida e não a permissão de EXECUTE. A permissão de EXECUTE é necessária quando a function é do tipo Scalar Function o que não é o caso.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s