Gerenciamento de usuário no Firebird 2.5

No projeto de desenvolvimento de software do estágio supervisionado - que é o trabalho de conclusão de curso na UNIPAR - Cianorte, o gerenciamento de usuário para controle de acesso e suas respectivas permissões são componentes/funcionalidades obrigatórias.

Durante as monitorias do curso que realizo ou em conversas informais com vários colegas de turma, percebi que muitos tem algumas dúvidas ou simplesmente não sabem por onde começar.

Então vou disponibilizar o que já fiz e fazer alguns comentários sobre o formato adotado. Vale destacar que devido à minha afinidade com o banco de dados utilizado (Firebird) fui impulsionado a adotar a seguinte estratégia: Usuário da Aplicação / Sistema = Usuário do Banco de Dados, mas pode talvez não seja a melhor forma (para mim, neste momento foi).

Ou seja, quando um usuário novo registra-se no sistema que estou desenvolvendo, eu também incluo-o no Security2.fdb (a partir da versão 2.0) que é o banco de dados do servidor Firebird onde são armazenados os usuários. O mesmo conceito vale para alteração e exclusão.

Existe muita informação à respeito deste tópico já disponível na internet, eu recomendo este link http://www.firebirdsql.org/manual/pt_br/fbutils-gsec-pt_br.html, pois embora esteja explicando o uso da ferramenta GSec (via linha de comando) que acompanha a instalação do Firebird, fornece uma boa visão de como funciona o gerenciamento de usuário pelo SGBDR.

Outro motivo que levou-me a fazer assim foi a simplicidade oferecida pela versão 2.5 com a adição dos comando DDL (Data Definition Language) CREATE/ALTER/DROP USER, veja todos os detalhes no Release Notes da versão 2.5 (versão em html).

Um ponto polêmico na utilização do Firebird é em relação a segurança de acesso ao arquivo do banco de dados (.fdb), é evidente e já está previsto melhoras neste quesito (disponível no Firebird Roadmap), mas por enquanto no portal Firebase você encontrará respostas para as seguintes perguntas:

Existe alguma maneira de impedir que o SYSDBA tenha acesso ao meu banco de dados?

Como posso proteger meu banco de dados para que outras pessoas não tenham acesso ao meu arquivo GDB/FDB?

Bem, vamos aos comandos SQLs que utilizei:
Abaixo, minha tabela de usuário. Perceba que foram utilizados domínios na sua definição e que a implementação dos mesmos encontra-se comentada logo a frente do campo da tabela.

CREATE TABLE TB$USUARIO (

ID DOM$ID_SMALL NOT NULL /* DOM$ID_SMALL = SMALLINT NOT NULL */,

NOME DOM$NOME_USUARIO NOT NULL /* DOM$NOME_USUARIO = VARCHAR(32) */,

NOME_COMPLETO DOM$NOME_COMPLETO /* DOM$NOME_COMPLETO = VARCHAR(60) NOT NULL */,

SENHA DOM$SENHA /* DOM$SENHA = VARCHAR(10) NOT NULL */,

FUNCAO DOM$FLAG_CHAR NOT NULL /* DOM$FLAG_CHAR = CHAR(1) NOT NULL */,

ATUALIZACAO DOM$ATUALIZACAO NOT NULL /* DOM$ATUALIZACAO = TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL */,

CONSTRAINT PK_USUARIO PRIMARY KEY (ID)

);

Stored Procedure que centraliza a execução dos comandos DDL, sendo chamada por um trigger da tabela acima. Estou utilizando novos recursos do Execute Statement.

CREATE OR ALTER PROCEDURE SP$USER_MANAGER (

I$CMD_DDL_USER VARCHAR(255),

I$USER_ADMIN TYPE OF COLUMN TB$USUARIO.NOME = 'ADM',

I$PASSWORD TYPE OF COLUMN TB$USUARIO.SENHA = 'senha',

I$ROLE_ADMIN VARCHAR(30) = 'RDB$ADMIN'

)

AS

BEGIN

EXECUTE STATEMENT I$CMD_DDL_USER

WITH COMMON TRANSACTION

AS USER :I$USER_ADMIN PASSWORD :I$PASSWORD ROLE :I$ROLE_ADMIN;

END

É necessário que o usuário passado como parâmetro de entrada para a procedure acima seja um "super usuário" com direitos equiparados ao SYSDBA, portanto estou utilizando um usuário que é membro da role Rdb$admin - que também é um novo recurso da versão 2.5.

CREATE USER ADM

PASSWORD 'senha'

FIRSTNAME 'Administrador'

MIDDLENAME 'do Sistema'

LASTNAME 'Unipar'

GRANT ADMIN ROLE

Além disso, é necessário atribuir (em cada base de dados) a role rdb$admin ao usuário, assim:

GRANT RDB$ADMIN TO ADM

O Trigger abaixo é responsável por inserir, alterar e excluir o usuário no banco de dados Security2.fdb, condicional ao evento gerado na tabela TB$USUARIO.

CREATE OR ALTER TRIGGER TRI$USER_MANAGER FOR TB$USUARIO

ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0

AS

BEGIN

IF (INSERTING) THEN

EXECUTE PROCEDURE SP$USER_MANAGER('CREATE USER ' || NEW.NOME ||

' PASSWORD ''' || NEW.SENHA || ''' ', 'ADM', 'senha');

ELSE

IF (UPDATING) THEN

EXECUTE PROCEDURE SP$USER_MANAGER('ALTER USER ' || OLD.NOME ||

' PASSWORD ''' || NEW.SENHA || ''' ', 'ADM', 'senha');

ELSE

EXECUTE PROCEDURE SP$USER_MANAGER('DROP USER ' || OLD.NOME,

'ADM', 'senha');

END

É isso ai pessoal! O Firebird está cada vez melhor!!

Até a próxima...

Concurso de Stored Procedures Firebird

O portal Firebase está divulgando este concurso de stored procedures anunciado pela campanha MindTheBird.

Visite-o e se informe, quem sabe você pode ser tornar um dos três vencedores...

Banco de Dados - Firebird

Olá pessoal,
por mais que pareça fora do escopo ao que o blog se propõe a abordar (que é programação), vamos tratar nest post sobre Banco de Dados, mais especificamente o Sistema Gerenciador de Banco de Dados Relacional (SGBDR) Firebird.

Este banco de dados open-source é descendente do Interbase 6 - devido a Borland abrir o código fonte do mesmo e em versões posteriores voltar a distribuí-lo sob licença comercial, instigando à comunidade a desenvolver, a partir dos fontes do Interbase, uma nova versão aberta à comunidade.

Aprofunde seus conhecimentos visitando o site oficial ou o portal Firebase onde você poderá acessar vários artigos e dicas interessantes.

Se você não foi convencido a clicar nos links acima, acredito que após visualizar esta apresentação irá se convencer!

A versão 2.5 do Firebird, está no segundo Release Candidate, confira!



Tenho orgulho de dizer que comecei a programar na PL/SQL do Firebird (lembro-me como se fosse hoje), foi um trigger... meu primeiro contato com programação!!

Dá para fazer muitas coisas legais no banco de dados, ainda mais nas versões superiores à 2.0.

Segue alguns exemplos:
/**
* A procedure abaixo foi utilizada para gerar uma chave
* de uma informação passada como parâmetro de entrada.
* As funções embutidas (SDF) ASCII_CHAR, ASCII_VAL,
* BIN_XOR foram implementadas na versão 2.1, o
* CHAR_LENGHT já existia na versão 2.0.
*/

CREATE OR ALTER PROCEDURE SP$GERAR_HASH_CHAVE (
INPUT_HASH VARCHAR(255))
RETURNS (
OUTPUT_HASH CHAR(1))
AS
DECLARE VARIABLE POS_CHAVE SMALLINT = 3;
DECLARE VARIABLE TAMANHO_CHAVE SMALLINT;
BEGIN
TAMANHO_CHAVE = CHAR_LENGTH(INPUT_HASH);
IF (TAMANHO_CHAVE > 1) THEN
BEGIN
OUTPUT_HASH =
ASCII_CHAR(BIN_XOR(ASCII_VAL(SUBSTRING(INPUT_HASH FROM 1 FOR 1)),
ASCII_VAL(SUBSTRING(INPUT_HASH FROM 2 FOR 1))));
WHILE (POS_CHAVE <= TAMANHO_CHAVE) DO
BEGIN
OUTPUT_HASH = ASCII_CHAR(BIN_XOR(ASCII_VAL(OUTPUT_HASH),
ASCII_VAL(SUBSTRING(INPUT_HASH
FROM POS_CHAVE FOR 1))));
POS_CHAVE = POS_CHAVE + 1;
END
END
ELSE
BEGIN
OUTPUT_HASH = SUBSTRING(INPUT_HASH FROM 1 FOR 1);
END
SUSPEND;
END

Exemplo de uso/chamada da procedure acima:
/**
* Faço uma instrução select, informando o parâmetro
* de retorno e o nome da procedure na cláusula from,
* seguida do parâmetro de entrada.
*/
SELECT OUTPUT_HASH FROM SP$GERAR_HASH_CHAVE('Firebird')

Retorna: "%"

O trigger abaixo pode ser criado na versão 2.5, quando foi implementado database trigger no Firebird, consulte o Release Notes para ficar por dentro de todas as novidades (e saber como implementá-las!).

/**
* Trigger utilizada para validar a conexão de um
* usuário ao banco de dados, sendo disparada ao
* conectar (active on connect) assegurando
* que aquele usuário da conexão está cadastrado na
* tabela de usuário daquele banco de dados e não é
* o SYSDBA, caso onde o usuário da aplicação que
* utiliza o banco de dados é o mesmo do Security2.fdb
* - banco de dados que armazena os usuário do
* servidor Firebird.
*/
CREATE OR ALTER TRIGGER TRI$VALIDA_CONEXAO
ACTIVE ON CONNECT POSITION 0
AS
BEGIN
IF (NOT EXISTS(SELECT
1
FROM TB$USUARIO
WHERE TB$USUARIO.NOME = CURRENT_USER)
OR (CURRENT_USER = 'SYSDBA')) THEN
BEGIN
EXCEPTION E$LOGIN_INVALIDO;
END
END

É isso pessoal, como última dica fica a indicação do IBExpert para gerenciamento/manutenção do banco de dados Firebird - é excelente!