terça-feira, 10 de dezembro de 2013

Adicionar Error Description no resultado de erro do SSIS

 

Screenshot (60)O Error Output de um componente dentro de um data flow nos fornece informações sobre o erro, tal como o código do erro, nos permitindo direcionar essas informações para gravação em outro local.

Porém o error output não possui por padrão a descrição do erro. A falta da descrição é um problema, podemos resolver isso utilizando um script component.

Devemos inserir o script component no data flow, ele atuará como uma transformação, receberá o error output do componente anterior e passará o resultado transformado para o componente seguinte. A transformação que será utilizada é a inclusão da descrição do erro.

Screenshot (61)É necessário ainda adicionar um novo campo no output do script component, podemos adicionar como ErrorDescription.

O código do script transformation fica da seguinte forma:

public class ScriptMain:
    UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

  Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    }
}

domingo, 8 de dezembro de 2013

Acesso entre Bancos de Dados–Parte 4

 

sql-server-logoNesta 4a e última parte do artigo, vamos mostrar mais uma forma de resolver o problema do acesso entre bancos de dados, desta vez utilizando certificados e assinaturas de stored procedures.

Opção 4: Assinatura de stored procedures

Escrevi um artigo anterior explicando sobre a utilidade do conceito de assinatura de stored procedures. Após assinar uma stored procedure com um certificado e criar um usuário baseado no certificado, a procedure terá permissão de realizar o que o usuário tiver permissão de realizar.

No artigo anterior citei o SOD como uma das utilidades. Respondendo a pergunta que deixei no ar, demonstrarei o acesso entre bancos como mais uma utilidade da assinatura de procedures.

Primeiramente, vamos montar um banco adicional em nosso cenário (já explicado na parte 2) para essa demonstração:

CREATE DATABASE [testecdoc4]
 ON  PRIMARY 
( NAME = N'testecdoc4', FILENAME = N'testecdoc4.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'testecdoc4_log', FILENAME = N'testecdoc4_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

use testecdoc4
CREATE PROC dbo.QueryATable
AS
BEGIN
  SELECT TableID FROM testecdoc.dbo.ATable;
END;

alter authorization on database::testecdoc4 to fulano

CREATE USER [ciclano] FOR LOGIN [ciclano] WITH DEFAULT_SCHEMA=[dbo]
GO

Grant execute on QueryATable to ciclano

Criado o cenário, precisamos configurar o certificado com os seguintes passos:

  • Criar o certificado
  • Criar um usuário baseado no certificado
  • Atribuir permissão de leitura na tabela

Veja como fica a execução desses passos:

use testecdoc

CREATE CERTIFICATE certificado
   ENCRYPTION BY PASSWORD = 'Abc,xyz'
   WITH SUBJECT = 'Cross-db test'

CREATE USER [usrCertificado] FOR CERTIFICATE [certificado]
GO

GRANT SELECT ON ATABLE TO usrCertificado

Em seguida precisamos exportar o certificado para disco e cria-lo no outro banco de dados, no testecdoc4. Desta forma cada base de dados tem seu próprio objeto certificado, mas ambos são iguais, um gerado da exportação do outro. Veja como fica:

use testecdoc
BACKUP CERTIFICATE certificado TO FILE = 'C:\estudos\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\estudos\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Abc,xyz',
                  DECRYPTION BY PASSWORD = 'Abc,xyz')
go

use testecdoc4 

CREATE CERTIFICATE certificado FROM FILE = 'C:\estudos\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\estudos\crossdbcert.pvk',
                  DECRYPTION BY PASSWORD = 'Abc,xyz',
                  ENCRYPTION BY PASSWORD = 'Abc,xyz')

Por fim, basta assinarmos a stored procedure do banco testecdoc4 e realizarmos um teste:

use testecdoc4
ADD SIGNATURE TO queryatable BY CERTIFICATE certificado
    WITH PASSWORD = 'Abc,xyz'

go

execute as login='ciclano'
EXEC dbo.QueryATable;

Importante: A vantagem de uso dos certificados em comparação com o Execute AS é que com o uso do Execute As a mudança do contexto de execução dificulta realizações de auditorias, pois todas as execuções aparentam ser feitas pelo mesmo usuário. Isso não ocorre com a técnica de assinaturas.

quarta-feira, 4 de dezembro de 2013

Mensagem de Erro: SQL Error 'INITCOLVS' is not a recognized built-in function name

 

sql-server-logoEssa mensagem, que encontrei nos últimos dias, pode ser causada por triggers de replicação criados em versões antigas do SQL Server. Eliminando o trigger com problemas e atualizando a replicação o problema desaparece.

Podemos identificar facilmente o trigger com problema utilizando a seguinte query:

select name,text from syscomments c, sys.objects a
where c.id=a.object_id and text LIKE '%INITCOLVS%'

terça-feira, 3 de dezembro de 2013

Acesso entre Bancos de Dados–Parte 3

 

sql-server-logoContinuando a série sobre Acesso entre Bancos de dados (veja a parte 1 e parte 2), vamos ver como substituir o CDOCcom mais uma opção de solução deste problema.

Utilizaremos o mesmo cenário apresentado na Parte 2

Opção 3: Execute As/Trustworthy

 

O Execute As nos permite alterar o contexto de execução de instruções. Aplicado em stored procedures, permite trazer o resultado oferecido pelo CDOC até mesmo em cenários em que o CDOC não funcionaria.

Observe que a procedure criada no banco testecdoc3 utiliza o Execute as Owner, fazendo com que o contexto da execução seja sempre o do proprietário da procedure. Com isso geramos o mesmo efeito que o CDOC, usuários com permissão de execução da procedure não precisarão de permissões de acesso na tabela.

Para funcionar entre bases de dados diferentes, porém, é necessário que exista uma confiança entre as duas bases de dados. O atributo trustworthy determina a existência da confiabilidade entre bancos de dados.

Para determinar se o trustworthy encontra-se habilitado pode-se utilizar a seguinte query:

SELECT [name] AS [Database], is_trustworthy_on
FROM [sys].databases 
ORDER BY [name];

SNAG-0100_thumb

Com o trustworthy desabilitado, vamos realizar um primeiro teste com o usuário ciclano entre o testecdoc3, que pertence ao fulano, e o testecdoc, que pertence ao beltrano. Veja como fica o primeiro teste:

use testecdoc3
execute as login='ciclano'

EXEC dbo.QueryATable;

SNAG-0101_thumb[1]


Sem o trustworthy ativado a execução falha. Nosso próximo passo é ativar o trustworthy nas bases de dados, com as seguintes instruções:

alter database testecdoc set trustworthy on
alter database testecdoc3 set trustworthy on

O trustworthy não é a única configuração necessária para essa solução. O usuário utilizado no Execute As, neste caso o Fulano, precisa receber a permissão de Authenticate no banco que será acessado.

A atribuição da permissão Authenticate fica da seguinte forma:

use testecdoc
grant authenticate to fulano

Para identificarmos quais usuários possuem a permissão de authenticate podemos utilizar a seguinte query:

select b.name,a.permission_name from sys.database_permissions a,sys.database_principals b
where a.grantee_principal_id=b.principal_id
and permission_name='AUTHENTICATE'

Com os dois atributos definidos, trustworthy e a permissão de authenticate, a execução tentada anteriormente passa a funcionar. É importante observar o seguinte:

  • Nesse cenário o usuário ciclano não precisa estar mapeado para o banco testecdoc. Nosso cenário contém esse mapeamento, que pode ser deletado e a solução continuará a funcionar.
  • Esse cenário não seria atendido nem mesmo pelo CDOC, pois os owners dos objetos são diferentes, Fulano e Beltrano. Com CDOC a permissão do Ciclano teria que ser checada na tabela
  • Esse cenário não possui as falhas de segurança do CDOC.

segunda-feira, 2 de dezembro de 2013

Acesso entre Bancos de Dados–Parte 2

 

sql-server-logoContinuando a série sobre controle de acesso entre bases de dados (veja a parte 1), vamos estabelecer um cenário para os exemplos que apresentaremos em toda a série.

Cenário

Primeiramente vamos estabelecer um cenário para os testes que realizaremos. Nosso cenário conterá os seguintes elementos:

  • Três bancos de dados, testecdoc, testecdoc2 e testecdoc3
  • Três logins, Fulano, Beltrano e Ciclano
  • Uma tabela no banco testecdoc
  • Duas procedures, uma no banco testecdoc2 e outra no banco testecdoc3

Criação das bases de dados:

/****** Object:  Database [testecdoc2]    Script Date: 01/12/2013 03:03:03 ******/
CREATE DATABASE [testecdoc]
 ON  PRIMARY 
( NAME = N'testecdoc', FILENAME = N'testecdoc.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'testecdoc_log', FILENAME = N'testecdoc_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO


CREATE DATABASE [testecdoc2]
 ON  PRIMARY 
( NAME = N'testecdoc2', FILENAME = N'testecdoc2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'testecdoc2_log', FILENAME = N'testecdoc2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

CREATE DATABASE [testecdoc3]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testecdoc3', FILENAME = N'testecdoc3.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'testecdoc3_log', FILENAME = N'testecdoc3_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Criação dos Logins:

CREATE LOGIN [fulano] WITH PASSWORD=N'teste'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [fulano]
GO


CREATE LOGIN [Beltrano] WITH PASSWORD=N'Šteste'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [Beltrano]
GO

CREATE LOGIN [ciclano] WITH PASSWORD=N'teste'
GO

Definição dos owners da base de dados:

alter authorization on database::testecdoc to beltrano

alter authorization on database::testecdoc2 to beltrano

alter authorization on database::testecdoc3 to fulano

go

Definição de acessos em cada base de dados:

USE [testecdoc3]
GO

CREATE USER [ciclano] FOR LOGIN [ciclano] WITH DEFAULT_SCHEMA=[dbo]
GO


USE [testecdoc2]
GO

CREATE USER [ciclano] FOR LOGIN [ciclano] WITH DEFAULT_SCHEMA=[dbo]
grant execute on queryatable to ciclano
GO


USE [testecdoc]
GO

CREATE USER [ciclano] FOR LOGIN [ciclano] WITH DEFAULT_SCHEMA=[dbo]
GO


CREATE USER [fulano] FOR LOGIN [fulano] WITH DEFAULT_SCHEMA=[dbo]

grant select on atable to fulano
GO

Criação da tabela:

use testecdoc
CREATE TABLE dbo.ATable (TableID INT);

Criação das procedures:

use testecdoc2 
CREATE PROC dbo.QueryATable
AS
BEGIN
  SELECT TableID FROM testecdoc.dbo.ATable;
END;
GO 

use testecdoc3
CREATE PROC dbo.QueryATable
Execute as owner
AS
BEGIN
  SELECT TableID FROM testecdoc.dbo.ATable;
END;
GO 

Opção 2: Utilização de CDOC

CDOC é a sigla de Cross Database Ownership Chain, ou seja, o mesmo controle de cadeia de posse aplicado para o trabalho entre bancos diferentes.

O CDOC vem desabilitado por default e a recomendação é mante-lo assim por questão de segurança. Vamos analisar como ele funciona e os problemas existentes.

Em nosso cenário os bancos testecdoc e testecdoc2 possuem o mesmo owner, beltrano. Com isso, se o usuário ciclano executar a procedure, seguindo a regra de ownership a permissão deveria ser checada apenas na execução da procedure e não na tabela.

Porém, com o CDOC desligado por default o resultado será um erro.

Veja o seguinte script para realizar a execução:

execute as login='ciclano'
EXEC dbo.QueryATable;

SNAG-0098_thumb[2]

O resultado acima demonstra que as regras de cadeia de posse não são seguidas entre diferentes bancos de dados: Está sendo exigida a permissão de SELECT diretamente na tabela.

No exemplo acima utilizamos o Execute As para simular a execução como login ciclano. Após a simulação o contexto de execução deve ser retornado ao padrão com a seguinte instrução:

revert

O CDOC pode ser habilitado a nível de banco de dados, podemos consultar se ele encontra-se ou não habilitado com a seguinte query:

SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases 
ORDER BY [name];

SNAG-0099_thumb[1]

 

Habilitando o CDOC nos dois bancos envolvidos neste primeiro exemplo, testecdoc e testecdoc2, a execução da procedure com o usuário ciclano passa a funcionar. Veja as instruções para habilitar o CDOC:

alter database testecdoc2 set db_chaining on
alter database testecdoc set db_chaining on

Existe também uma opção de configuração do CDOC a nível de servidor, habilitando o CDOC para todos os bancos de dados. Podemos utilizar a procedure sp_configure para alterar essa configuração.

Para checar se o CDOC está habilitado a nível de servidor podemos utilizar a seguinte query:

SELECT [name], value  
FROM [sys].configurations 
WHERE [name] = 'cross db ownership chaining'

Risco de Segurança

O CDOC entre bancos de dados se baseia no nome o usuário. Isso permite que qualquer dbowner possa burlar a segurança;

Supondo que os bancos tenham diferentes owners, tal como Joao e Jose. Se no banco do Joao existe um usuário chamado Pedro que possui uma tabela, o Jose pode criar um usuário Pedro em seu banco, criar uma procedure que faça acesso a tabela e com isso ganhar acesso indevido as informações, burlando  a segurança. Por causa disso o CDOC não é recomendável, as falhas de segurança são consideráveis.

  

domingo, 1 de dezembro de 2013

Acesso entre Bancos de Dados–Parte 1

 

sql-server-logoUm ponto interessante no trabalho com a segurança no SQL Server são as diferentes formas de gerarmos um acesso entre bancos de dados.

Pode parecer trivial quando trabalhamos com todos os bancos pertencendo ao mesmo owner com permissão de sysadmin, mas na verdade o procedimento se torna bem mais complexo em um cenário que exija maior segurança.

Vou escrever um artigo em 4 partes demonstrando as diferentes alternativas e problemas de segurança existentes no controle de acesso entre diferentes bancos de dados.

Vamos começar entendendo a forma mais simples e porque ela não é suficiente.

Opção 1: Atribuir permissão

SQLServer_OwnershipChainsA forma mais trivial, em termos de segurança é atribuir permissão aos usuários que irão executar o acesso, em todos os objetos acessados.

Porém isso significa abdicar de recursos de segurança que normalmente temos a disposição quando o trabalho é feito com um único banco de dados. O principal recurso em questão é a cadeia de posse de objetos.

O SQL Server nos permite criar views e procedures que façam acesso as tabelas. Os usuários que farão acesso as views e procedures precisarão apenas de permissão nestas e não nas tabelas, evitando atribuir permissões diretas nas tabelas, o que é visto como uma vantagem na organização da segurança.

Para chegar a este resultado temos o conceito de cadeia de posse. A regra é simples: em uma cadeia de objetos, a permissão do usuário será checada cada vez que a posso dos objetos mudar.

Por exemplo: Temos uma procedure A que chama a View B que utiliza a tabela C. Pertencendo todos os objetos ao mesmo usuário, apenas precisarão ser dadas permissões de execução na procedure A, os demais objetos não serão checados.

Por outro lado, se os objetos pertencerem a diferentes usuários, a cada sequencia de objetos em que o owner mudar a permissão de quem está fazendo o acesso será checada novamente, exigindo que as permissões sejam atribuidas até mesmo no nível da tabela.

No acesso entre bases de dados  a sequencia de cadeia de posse não funciona de forma padrão. O acesso sempre funcionará se o usuário que está fazendo o acesso tiver todas as permissões necessárias nos objetos. A questão é como reproduzir o comportamento da cadeia de posse, não tendo a necessidade de atribuir permissões nas tabelas. Vamos ver algumas opções para isso.

terça-feira, 26 de novembro de 2013

Demonstração de Backup (Palestra na UGB)

 

ugbfoto3Dia 14/11 estive na UGB, Universidade Geraldo de Biase, em Volta Redonda, fazendo uma palestra sobre profissões no mercado de trabalho de banco de dados, foi muito interessante.

Uma das demonstrações mais interessantes foi a demonstração sobre backup, na qual demonstrei diferentes tipos de backup inclusive com restore utilizando stopatMark, além de demonstrar a deleção do arquivo de log e do arquivo de dados e recuperar o banco com perda zero de dados, mesmo depois de um arquivo deletado e sem backup.

ugbfoto1Uma questão muito interessante é sobre a síntaxe do stopatmark. Quando se cria uma transação com marca a transação ganha o nome e utilizamos uma descrição da transação após a palavra chave ‘Mark’. Na hora do restore, porém, até mesmo no site da microsoft encontrei exemplos fazendo o restore errado: O stopatmark precisa ser feito com o nome da transação e não com a descrição que encontra-se após o Mark.

Quando se erra a sintaxe, não ocorre um aviso claro do erro. O restore simplesmente não encontra a marca no log, consequentemente restaura o log inteiro e não obedece a instrução with recovery.

Observei na internet muitas simulações do stopatmark utilizando a sintaxe errada. Por erro na montagem das simulações, elas fazem uso da sintaxe errada e não se percebe isso ao rodar a simulação.

ugbfoto2No script da demonstração, logo abaixo, entre outros passos em relação a backup vocês podem encontrar uma simulação correta do stopatmark, feita de forma a se observar claramente o resultado.

O PPT da apresentação está disponível em http://www.slideshare.net/DennesTorres/profisses-e-atividades-do-mercado-de-banco-de?utm_source=ss&utm_medium=upload&utm_campaign=quick-view

backup database north to disk='c:\bancos\sql1\northBackup.bak'
    with init

go

use north

/* Observe clientes de diversos paises */
select * from customers

/* Atualização do pais para Brazil */

begin transaction teste with mark
update customers set country='Brazil'
commit transaction teste


/* Atualização do pais para argentina */
update customers set country='Argentina'

go

backup log north to disk='c:\bancos\sql1\northBackup.bak' with noinit

go
use master
drop database North

go
/* Restore do backup full */
restore database North from disk='c:\bancos\sql1\northbackup.bak'
with file=1, recovery

go
/* Observará clientes de vários paises, como no momento do backup full */
use north
select * from customers

go
use master
drop database North

go
/* restore do backup full */
restore database north from disk='c:\bancos\sql1\northbackup.bak'
with file=1, norecovery

/* restore do backup de log */
restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=2, recovery

go
/* Observará clientes da argentina, como ao final do backup de log */
use north
select * from customers

go
use master
drop database north

go

/* restore do backup full */
restore database north from disk='c:\bancos\sql1\northbackup.bak'
with file=1, norecovery

/* restore do log com stopatmark */
restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=2,recovery, stopatmark='teste'


go
/* Observará clientes do brasil conforme estavam logo após a transação chamada teste */
use north
select * from customers

go
use master
drop database north

go
/* restore completo */
restore database north from disk='c:\bancos\sql1\northbackup.bak'
with file=1, norecovery

restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=2, recovery

go
/* atualização do pais */
use north
update customers set country='alemanha'

/* apenas por segurança da demonstração */
checkpoint
go

/* Pare o serviço do sql server e delete o arquivo de log
   Inicie novamente o serviço do sql server e observará que o banco retornará como loading 
   As instruções abaixo irão re-criar o arquivo de log */

alter database north rebuild log on
 (name='north_log.log',filename='c:\bancos\sql1\north_log.ldf')

 alter database north set multi_user

 go
/* Observe os clientes da alemanha, como estavam antes do desastre, mesmo sem haver backup */
 use north
 select * from customers

go
/* restore completo */
restore database north from disk='c:\bancos\sql1\northbackup.bak'
with file=1, norecovery

restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=2, recovery

go
/* atualização do pais */
use north
update customers set country='alemanha'

/* apenas por segurança da demonstração */
checkpoint
 go

/* Pare o serviço do sql server 
   apague o arquivo de dados
   inicie o serviço novamente
   observará o banco retornando com o estado de loading
   A instrução seguinte cria um novo backup de log mesmo após a área de dados haver sido perdida */

 backup log north to disk='c:\bancos\sql1\northbackup.bak'
 with no_truncate

 go
/* Restore dos 3 backups */

 restore database north from disk='c:\bancos\sql1\northbackup.bak'
with file=1, norecovery

restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=2, norecovery


restore log north from disk='c:\bancos\sql1\northbackup.bak'
with file=3, recovery

go
/* Observe os clientes da alemanha, como estavam antes do desastre, mesmo sem haver backup */
 use north
 select * from customers

go