SQL Server: Application Role

Uma funcionalidade muito interessante foi adicionada ao SQL Server 2005 e mantida, obviamente, no SQL Server 2008. Para facilitar o gerenciamento das permissões no banco de dados utilizamos um recurso chamado ROLE. Um database ROLE é um objeto criado no banco de dados no qual é possível dar ou negar permissões de acesso dos outros objetos.

Vamos a um exemplo: você tem um perfil de usuários na empresa que só pode manipular informações referentes ao sistema de Recursos Humanos. Para facilitar o gerenciamento das permissões você cria uma ROLE chamada RL_RH e define todas as permissões de acesso a ela.

CREATE ROLE RL_RH AUTHORIZATION dbo; –CRIA A ROLE

GRANT SELECT TO RL_RH; –PERMITE SELECT EM TODOS OS OBJETOS

Imagine que um novo usuário deve ser adicionado ao banco de dados e precisa ter os mesmos acessos de todos os outros do RH. Para isso basta criar o usuário e adicioná-lo ao papel já criado. Por exemplo:

CREATE LOGIN US1 WITH PASSWORD=’p@ssw0rd’; –CRIA O LOGIN NO SERVIDOR

CREATE USER US1 FOR LOGIN US1; –CRIA UM USUÁRIO PARA O LOGIN NO BANCO DE DADOS

SP_ADDROLEMEMBER ‘RL_RH’, ‘US1’; –ADICIONA O USUÁRIO COMO MEMBRO DA ROLE

Com isso, o usuário passa a ter todos os direitos da role!

Legal, não é? Mas agora imaginemos que temos alguns usuários mais avançados capazes de criar comandos SQL e executá-los diretamente no banco de dados. Coisa que não deveriam fazer a não ser através de um sistema. Como evitar que isso ocorra? Como saber se o usuário está se conectando através de uma ferramenta tipo o SSMS ou através de um sistema. Para isso, existe um recurso chamado de Application Role!

O funcionamento é semelhante, cria-se um APPLICATION ROLE e se define todas as permissões a ele. Por exemplo:

CREATE APPLICATION ROLE RL_APP_RH WITH PASSWORD = ‘pa$$w0rd’; –CRIA UM APPLICATION ROLE COM SENHA

GRANT SELECT TO RL_APP_RH; –PERMITE SELECT EM TODOS OS OBJETOS

Para os usuários:

CREATE LOGIN US2 WITH PASSWORD=’xpt0′;

CREATE USER US2 FOR LOGIN US2;

No sistema, logo após abrir a conexão com o banco de dados execute:

SP_SETAPPROLE ‘RL_APP_RH’, ‘pa$$w0rd’; –ATIVA O USO DA ROLE MEDIANTE INFORMAR O PASSWORD

Pronto a partir de agora a ROLE está ativada digamos assim. Houve uma troca no contexto de segurança e o usuário passa a ter acesso a tudo que havia sido definido para a ROLE. Se ele conectar-se diretamente no banco de dados não terá acesso a nada.

SQL Server – Rollback Transaction em Triggers

O que acontece se executar o comando ROLLBACK TRANSACTION em uma Trigger no SQL Server? A primeira coisa que passa na cabeça é: o que foi iniciado na transação será desfeito! Sim, isso está correto. Mas se pensarmos um pouco mais além: e se após este comando houver outros comandos, eles serão executados? E se estes comandos causarem o disparo de outra trigger, ela será disparada? E os outros comandos que que estão no batch após o comando que originou o disparo da trigger, serão executados?

O comportamento é seguinte:

• As alterações feitas até a execução do ROLLBACK TRANSACTION são desfeitas;
• A trigger continua a execução com os demais comandos que houverem após o ROLLBACK, mas nenhuma outra trigger será disparada;
• Os demais comandos que houverem no batch também não serão executados.

Ou seja, imaginemos o seguinte cenário: em uma trigger é implementada uma regra de negócio. Regra esta que em determinada condição precisa interromper a transação. Mas você gostaria de gravar um trace da execução dessa trigger. Ok! Após o ROLLBACK TRANSACTION é possível executar um comando INSERT para inserir o trace em outra tabela!

Se quiserem saber mais sobre o comando ROLLBACK TRANSACTION acessem o MSDN!

Abraços!