Permissões mínimas de login do SQL para um grupo de domínio do AD

Question

Eu criei um aplicativo da Web que usa um banco de dados do SQL Server 2008 R2 em segundo plano. O aplicativo usa a conexão SSPI de segurança integrada ao banco de dados para que os usuários precisem acessar o servidor de banco de dados.

Todos os usuários que devem usar o aplicativo são membros de um determinado grupo de domínio do AD.

Isso é o que eu fiz

1. Eu adicionei um novo Login de Segurança no nível do SQL Server e forneceu o nome do grupo de domínio do AD que é exibido como um grupo (com base no ícone)

   

2. Emseguida,abriestacaixadediálogodepropriedadesdelogindoservidoreabriasconfiguraçõesdoMapeamentodeusuáriosondeverifiqueimeubancodedadosespecíficoquedesejoqueosusuáriosdessegrupodedomínioacessem(vejaabaixonoimagem)

3. Nalistaabaixo,sãoexibidasassociaçõesàfunçãodobancodedados,nasquaispúblicoéverificado.

4. EucrieitodososobjetosnomeubancodedadoscomoumusuárioseparadodoSQLServercompermissãosecurityadmineforneceuoesquemadbocomcadaobjetocomoem:

   createtabledbo.SomeTable...createproceduredbo.SomeProcedure...

5. Todaamanipulaçãodobancodedadoséfeitasomenteatravésdeprocedimentosarmazenados.Nenhumatabelaéacessadadiretamentepeloaplicativo.

Combasenessesfatos(esquemadbocommeusobjetos,logindesegurançadogrupodedomíniodoAD,execuçãodeprocedimentoarmazenadoapenas),pergunto-mequalassociaçãodefunçãodeveserdefinidanologondoservidordegrupodedomíniodoADparausarmeubancodedados.

Osusuáriosdevemterpermissãoparaexecutarprocedimentosarmazenados.Estes,poroutrolado,fazemtodooresto.OperaçõesCRUDemtabelas,executefunçõesdefinidaspelousuárioeligueumasàsoutrastambém.

Pergunta

Aconfiguraçãodb_datareaderedb_datawriter(conformemostradoabaixo)nãoésuficiente,comoestáimplícitonateladeerroquemeususuáriosobtêm(procedimentosarmazenadossupostamentenãoexistem)?

ComodefinopermissõesdesegurançaemmeugrupodedomíniodoADparaqueelepossaverdboobjetoseexecutaressesprocedimentosarmazenados?

    

Answer 1

Solução

A aplicação de db_datareader e db_datawriter não é suficiente, pois nenhum deles recebe a execução correta do procedimento armazenado. Você deve conceder permissão de execução para cada procedimento armazenado em seu banco de dados ou ... Bastante tedioso.

Se o seu banco de dados for o SQL Server 2005 ou posterior (o meu é o SQL Server 2008 R2, então estou coberto), você pode fazer isso com muito menos esforço:

1. crie uma nova função de banco de dados chamada db_executor

   create role db_executor
   

2. concede direitos de execução de procedimento armazenado para todos os procedimentos armazenados no banco de dados

   grant execute to db_executor
   

Se você quiser conceder direitos de execução apenas a procedimentos armazenados específicos do esquema, esta última chamada deverá ser executada como (ajuste do nome do esquema - dbo - conforme necessário):

grant execute on schema::dbo to db_executor

E é isso. Então tudo o que você precisa fazer é configurar seu usuário (ou no meu caso, era um grupo) para ser membro dessa função e as coisas funcionam como esperado.