• Diminuir as possibilidades execução de comandos não autorizados nos servidores.
• Melhorar o controle das permissões de acesso ao servidor.
• Limitar acessos desnecessários ao sistema de arquivo.

• Evitar alteração nos esquemas de objetos.
• Auditar eventos relacionados a DDLs
• Disparar alertas quando detectado comandos DDLs
• Implementar eventos subsequentes a DDLs

table logins_computadores

codlogin int identity,

varchar(30),

varchar(30))

into logins_computadores values(‘Emanuel’,‘D001SQL01’)

into logins_computadores values(‘Emanuel’,‘D001SQL02’)

into logins_computadores values(‘Emanuel’,‘D001SQL03’)

into logins_computadores values(‘BUILTINAdministradores’,‘ALL’)

TRIGGER [Tr_DDL_Seguranca]

ALL SERVER

LOGON

@ori_login varchar(40)

@computador varchar(40)

@ori_login= ORIGINAL_LOGIN()

@computador = host_name()

ORIGINAL_LOGIN() <> ‘sa’

  IF not exists (select 1 from logins_computadores where NomeLogin = @ori_login and nomecomputador=‘ALL’)

    IF not exists (select 1 from logins_computadores where nomeLogin = @ori_login and nomecomputador=@computador)

ROLLBACK;

TRIGGER [Tr_DDL_Seguranca] ON ALL SERVER

ALL SERVER

LOGON

@ori_login varchar(40)

@ori_login= ORIGINAL_LOGIN()

HOST_NAME() <> @@servername

  IF not exists (select 1 from logins_computadores where NomeLogin = @ori_login and nomecomputador=‘ALL’)

    IF not exists (select 1 from logins_computadores where nomeLogin = @ori_login and nomecomputador=HOST_NAME())

ROLLBACK;

Olá pessoal, hoje vou postar um procedimento relacionado com rotinas de manipulação de constraints.

Faz parte do dia-a-dia do DBA a realização de rotinas como: expurgos de dados, cargas de dados, manutenções em tabelas, no entanto, muitas vezes essas rotinas não são tão fáceis de serem realizadas, isso porque, para garantir a integridade dos dados, o SQL Server disponibiliza um recurso chamado de "CONSTRAINT", esse recurso é uma forma de validar os dados nas manipulações das tabelas, de forma a evitar que sejam inseridos ou removidos dados que poderão causar inconsistências na base de dados.

Os tipos de constraints existentes no SQL Server são:

PRIMARY KEY – Garante a unicidade do registro.

FOREIGN KEY – Garante que um registro só possa ser adicionado a uma tabela, caso exista outro com o mesmo código na mesma ou em outra tabela, é o que chamamos de "integridade referencial", um exemplo prático seria uma tabela de vendas só permitir a venda de produtos caso o produto exista na tabela de "produtos".  

CHECK – Permite realizar a validação de um tipo de dado com uma lógica associada, exemplo, só permitir a entrada de dados o campo valor Unitário do produto, caso não ultrapasse x% do permitido. Outro exemplo seria, a utilização de "expressões regulares" para uma validação mais refinada, ex: só permitir a entrada no campo e-mail caso o mesmo siga uma formatação lógica (fulano@dominio.com.br).

DEFAULT  – Permite atribuir um valor padrão para um atributo, caso um valor não seja declarado no momento do insert.

O grande vilão de várias rotinas de manipulação de dados é a FOREIGN KEY, visto que ela é a responsável pela garantia da integridade referêncial entre os relacionamentos de diversas tabelas e, na maioria das vezes, precisamos desativá-las ou removê-las.

Apesar de existirem diversas formas de contornar a situação, como: gerar um script pelo wizard do EM (Enterprise Manager), SSMS (SQL Server Management Studio), irei demonstrar agora uma rotina criada por mim para realizar essa tarefa.

O script é apenas uma automação do wizard, mas, de forma bem prática realiza a geração dos scripts de drop e create das FOREIGN KEYS.

Segue link do script:

http://cid-e634caff4aff243d.skydrive.live.com/self.aspx/Public/SCRIPTS/GERA%7C_SCRIPT%7C_CONSTRAINTS.sql

Segue abaixo uma demonstração de utilização da rotina.

USE teste
go
/*#####    Criação da tabela de exemplo    #####*/

create table tb_produtos
(cod int not null,
codaux int not null,
descricao varchar(30))
go

/*#####    Criação da constraint PRIMARY KEY    #####*/
alter table tb_produtos add constraint pk_TBprod PRIMARY KEY (cod,codaux)

/*#####    Criação da tabela de detalhes de vendas para simular o relacionamento usando a FOREIGN KEY  #####*/
create table tb_Detalhes_vendas
(cod int not null,
codaux int not null,
qtd int)
go

/*#####    Criação da constraint FOREIGN KEY    #####*/
alter table tb_produtos add constraint FK_Detalhe_vendas_TBprod FOREIGN KEY (cod,codaux) REFERENCES tb_produtos(cod,codaux)
GO

/*#####    Exemplo de geração dos scripts  #####*/

exec pr_script_referencias @tabela=’ALL’, @tipo=3,@nocheck=1

/*

#####   A EXECUÇÃO DA PROCEDURE RESULTOU NA SEGUINTE SAÍDA #####
 
ALTER TABLE detalhe_produto With nocheck  ADD CONSTRAINT fk_prod FOREIGN KEY (cod,cod2) REFERENCES produtos2(cod,cod2)
ALTER TABLE tb_produtos With nocheck  ADD CONSTRAINT FK_Detalhe_vendas_TBprod FOREIGN KEY (cod,codaux) REFERENCES tb_produtos(cod,codaux)
ALTER TABLE detalhe_produto DROP CONSTRAINT fk_prod
ALTER TABLE tb_produtos DROP CONSTRAINT FK_Detalhe_vendas_TBprod

OBSERVAÇÕES:

* os parâmetros podem ser omitidos fazendo com que sejam gerados todos scripts(drop e create)
* caso o parâmetro @tabela seja usado será gerado somente o script dessa tabela.
* O parâmetro @tipo define se vc deseja script de criação/drop ou os dois tipos.
 @tipo=1 – create, @tipo=2 – drop, @tipo=3 – ambos.

* O parâmetro @nocheck define se você deseja criar os scripts usando o "With Nocheck" para evitar a checagem da integridade no momento de criação da constraint.

*/

Bom, pessoal, é isso aí! O exemplo está bastante simples, iniciou com uns conceitos básicos sobre constraints, pois, o objetivo foi de nivelar um pouco o conhecimento para os iniciantes, antes de apresentar a solução criada.

Dúvidas e sugestões, estou à disposição.

Desde já, desejo a TODOS BOAS FESTAS!

Emanuel Peixoto.