Como limitar as permissões do usuário sql para somente leitura enquanto ainda permite a execução de procedimentos armazenados?

Question

Aqui está meu objetivo: criar um login e um usuário do banco de dados associado com permissões para selecionar (somente leitura) e executar procs armazenados que não atualizam.

Um pouco de história é que este é o SQL 2005, e é um banco de dados com uma década + história e camadas de coisas desagradáveis. Centenas de procs armazenados, todos no esquema dbo. Eu sei ...

Eu tenho meu login e usuário criado e recebo os direitos db_datareader do usuário. Eu também tive que conceder EXECUTE perms para executar qualquer procs, mas meu objetivo é proibir qualquer proc que faça qualquer tipo de ação UPDATE / DELETE. Tentar definir perms individualmente nesses procs seria um pesadelo que não estou pronto para considerar.

Este cenário eu descrevo possível?

    

Answer 1

Depois que você tiver a lógica encapsulada em um procedimento armazenado, as permissões nos objetos subjacentes não serão necessárias. Uma maneira de realizar o que você está procurando é com um gatilho na mesa. Algo como:

create trigger tr_del_tbl on tbl after delete as
begin
    if user_name() = 'foobar' and exists (select 1 from deleted)
        rollback
end

Eu também usaria uma função para fazer isso em vez de um usuário específico. Dessa forma, se você receber outro usuário mais tarde que precise de tratamento semelhante, não será necessário alterar o gatilho; você acabou de adicioná-los ao papel.

    

Answer 2

Não há nenhum modelo baseado em permissões que faça isso, a menos que você esteja atribuindo direitos manualmente com base no que o procedimento armazenado faz. Sua única opção será gatilhos. Tenha em mente que só funcionará se não houver uso de EXECUTE AS, o que mudaria a forma como o usuário é relatado pela função user_name ().