Dicas do OsmarJr

Microsoft SQL Jet Avançado para Access 2000

Terceiro de uma série de três artigos, SQL Avançado amplia os conceitos apresentados nos artigos Fundamental e Intermediário, focando, agora, na sintaxe do SQL mais encontrada em um ambiente multiusuário. Se você leu os dois artigos anteriores em um esforço para se tornar um usuário com boa compreensão do SQL, este artigo vai fornecer as ferramentas finais necessárias para atingir esta meta.

Autor: Acey James Bunch

Download AcAdvSQL.exe

Conteúdo
Microsoft Jet SQL Avançado para o Access 2000
Conteúdo
Introdução
Definição de SQL Avançado
O Modelo Multiusuário
Jet vs. MSDE
Convenções de Codificação SQL
Código SQL mal formatado
Código SQL bem formatado
Linguagem de Definição de Dados Avançada
Visões (Views)
Procedimentos (Procedures)
Administrando a Segurança
Segurança em Nível de Compartilhamento
Segurança em Nível de Usuário
Contas de Usuário de de Grupo
Permissões de Objetos do Banco de Dados
Linguagem de Manipulação de Dados Avançada
Transações
Comando Com Acesso de Proprietário
Usando SQL Avançado no Access
Banco de Dados Exemplo
Usando um Procedimento
Um Último Comentário
Recursos Adicionais

Introdução

Este é o terceiro de uma série de artigos que explica o que é o Microsoft® Jet SQL e como ele pode ser usado em aplicativos Access 2000. São, ao todo, três artigos: Fundamental, Intermediário e Avançado. Os artigos mostram, progressivamente, a sintaxe e métodos para utilização do Jet SQL, demonstrando as novas características do Jet SQL que são novidade no Access 2000. Em todo este artigo, todas as referências à Linguagem Estruturada de Consultas (Structured Query Language – SQL) são a declarações (ou comandos) SQL como usados no motor de banco de dados Microsoft Jet.

Definição de SQL Avançado

Como nos dois artigos anteriores, as instruções estão divididas em duas categorias diferentes: Linguagem de Definição de Dados (Data Definition Language – DDL) e Linguagem de Manipulação de Dados (Data Manipulation Language – DML). Neste artigo veremos comandos avançados do SQL que podem ser usados para administrar objetos do banco de dados e fazer manutenção dos dados contidos por eles.

Para o DDL avançado, veremos como usar visões (views), procedimentos e segurança de banco de dados para adicionar maiores poderes e controle do banco de dados. Veremos formas muito mais genéricas de chegar aos dados contidos nas tabelas e, com relação ao gerenciamento da segurança, veremos formas de estabelecer permissões e direitos de acesso aos objetos contidos pelo banco de dados.

Para o SML avançado, examinaremos a sintaxe que pode ser utilizada para administrar os comandos SQL à medida que são executados, além da sintaxe que libera permissões especiais em determinadas consultas.

Os comandos DDL, neste artigo, são apresentados no contexto de um aplicativo multiusuário, o que não quer dizer que não possam ser utilizados em um aplicativo monousuário. Podemos ter um aplicativo que é instalado em uma única máquina mas é utilizado por mais de um usuário, necessitando, assim, de segurança de estilo multiusuário. Quando pensamos em um aplicativo multiusuário, a definição que vem à cabeça é a de um aplicativo usado por diversas pessoas ao mesmo tempo. Isto significa que um ou mais usuários poderão estar usando o mesmo aplicativo, e acessando os mesmos dados, ao mesmo tempo. Existe uma variedade de comandos SQL úteis para o desenvolvimento de aplicativos Access multiusuário, e muitos dos comandos avançados do SQL são novidades no motor de bancos de dados Jet incluído no Access 2000.

É muito importante que se note que alguns dos comandos de sintaxe avançada do SQL estão disponíveis em código apenas através do uso de ActiveX® Data Objects (ADO) e do provedor Jet OLE DB, não estando disponíveis na interface de consultas ou do Data Access Objects (DAO). Este artigo indica quando os comandos só estão disponíveis através do provedor Jet OLE DB e ADO.

O Modelo Multiusuário

Antes de usarmos alguns dos comandos avançados do SQL, seria interessante entendermos exatamente o que é um aplicativo de banco de dados multiusuário e, igualmente importante, o que não é. Quando usamos o Access e o motor de banco de dados Jet para criar um aplicativo, estamos usando uma solução conhecida como servidor de arquivo. Um cenário típico é a divisão do aplicativo Access em dois arquivos .mdb. Um dos arquivos é instalado em um servidor de arquivos da rede e contém apenas as tabelas de dados do aplicativo. O outro arquivo mdb é instalado nas estações de trabalho dos usuários e contém todos os outros objetos do banco de dados, como formulários, macros, módulos e relatórios. Quando os usuários solicitam algum tipo de ação sobre o banco de dados, todo o processamento é feito localmente e as tabelas são simplesmente vinculadas aos arquivos mdb nas estações de trabalho de cada usuário.

O modelo baseado em servidor de arquivo não deve ser confundido com o modelo baseado em cliente/servidor. Em um modelo baseado em Cliente/servidor, a maior parte do processamento de dados ocorre no servidor de banco de dados e não na estação de trabalho local. Além disso, os sistemas de bancos de dados cliente/servidor, como o Microsoft SQL Server™, fornecem maior poder e integridade de dados, como a recuperação de transações em caso de falha do sistema.

Dito isso, existem muitas novidades poderosas no Jet SQL que permitirão a administração e manutenção de bancos de dados multiusuários dentro do modelo servidor de arquivo.

Jet vs. MSDE

Uma novidade no Access 2000 é o Microsoft Data Engine (MSDE). O MSDE é um motor de banco de dados verdadeiramente cliente/servidor, compatível com o sistema de banco de dados SQL Server. O MSDE é incluído com as versões Professional e Premium do Office e está disponível, também, com a versão stand alone do Access 2000. mesmo este artigo colocando seu foco na sintaxe SQL como implementada no motor de banco de dados Jet 4.0, graças à maior concordância do Jet 4.0 com o ANSI 92, muitos dos comandos SQL são igualmente compatíveis e atualizáveis à sintaxe SQL implementada pelo MSDE e, também, pelo Microsoft SQL Server™. Se a idéia é usar o MSDE ou migrar um aplicativo existente para usá-lo, é interessante verificar as diferenças entre as sintaxes do SQL do Jet e do MSDE.

Convenções de Codificação SQL

Em todo este artigo você vai notar um método consistente de convenções de codificação SQL. Como ocorre com todas as convenções de codificação, a idéia é demonstrar o código de maneira que seja de fácil leitura e compreensão. Isto é obtido pela utilização de uma mistura de espaços em branco, novas linhas e palavras-chave em caixa alta. De modo geral, use caixa alta para todas as palavras-chave do SQL e, se necessária, a quebra de linha de código SQL, tente fazê-lo em uma seção lógica da declaração SQL. Isso será mais bem entendido após ver alguns exemplos.

Código SQL mal formatado

create table tblCustomers (CustomerID integer not null,[Last Name] text(50) not null,[First Name] text(50) not null, Phone text(10),Email text(50))

Código SQL bem formatado

CREATE TABLE tblCustomers (
.CustomerID INTEGER NOT NULL,
.[Last Name] TEXT(5) NOT NULL,
.[First Name] TEXT(50) NOT NULL,
.Phone TEXT(10),
.Email TEXT(50))

Linguagem de Definição de Dados Avançada

Para a Linguagem de Definição de Dados (DDL) avançada, vamos examinar algumas das novas adições à sintaxe SQL implementada no Jet 4.0. Estas incluem visões (views), procedimentos (procedures) e a sintaxe utilizada para administrar a segurança. Esta nova sintaxe alinha a implementação do SQL Jet às especificações ANSI 92 e facilitam a migração para o Microsoft SQL Server ou Microsoft Data Engine (MSDE).

Visões (Views)

Uma visão SQL é um objeto de banco de dados que permite organizar e ver os dados de uma ou mais tabelas, podendo ser referenciada como se fosse uma tabela única, virtual. É similar a uma consulta seleção do Access por estar baseada em um comando SELECT, mas é diferente por não poder ter parâmetros. O que deve ser lembrado sobre uma visão, e que a diferencia de uma tabela, é que ela não armazena dados: ela apenas devolve dados e pode permitir que estes sejam atualizados. Para criar uma visão usamos o comando CREATE VIEW para criar a visão, definir a lista de campos e associar a visão com um comando SELECT.

CREATE VIEW MyCustomersNames (FirstName, LastName)
AS SELECT [First Name], [Last Name]
FROM tblCustomers

A partir daí, podemos usar a visão em outros comandos SQL como se fosse uma tabela.

SELECT *
FROM MyCustomersNames

Note-se, entretanto, que uma visão só pode ser atualizada se o comando SELECT utilizado for capaz de ser atualizado. O nome de uma visão não pode ser idêntico ao de uma tabela existente. Para desfazer uma visão, usamos o comando DROP VIEW.

DROP VIEW MyCustomersNames

Nota: Os comandos CREATE VIEW e DROP VIEW só podem ser executados através do provedor Jet OLE DB e ADO. Será devolvida uma mensagem de erro se usados na interface de consultas do Access ou com o DAO. Note-se, também, que as visões criadas com o comando CREATE VIEW são salvas no banco de dados, mas não expostas como consultas salvas na interface de usuário do Access. Elas só podem ser usadas por código de programação ADO e ADOX.

Maiores informações sobre visões podem ser encontradas no Help do Access.

Procedimentos (Procedures)

Um procedimento é um objeto de banco de dados que pode ser usado para executar um comando SQL baseado em valores informados a eles. Estes valores são, comumente, chamados de parâmetros. Podemos pensar em procedimentos como funções baseadas em SQL. Os procedimentos permitem a passagem de parâmetros que serão utilizados pelo comando SQL, normalmente fazendo parte de uma cláusula WHERE. A vantagem é que os procedimentos podem ser bastante genéricos, podendo ser usados de diversas formas, além de poderem ser chamados de diferentes pontos do código de programação.

Nas versões anteriores do Access, podíamos usar ou uma cláusula de procedimento ou uma consulta parametrizada para criar comandos SQL que usassem parâmetros. Ambos os tipos de consultas continuam suportados. Outra sintaxe que pode ser utilizada é o novo comando CREATE PROCEDURE (ou CREATE PROC). A nova sintaxe é mais compatível com o ANSI 92, facilitando a migração para um banco de dados do SQL Server ou MSDE.

A estrutura básica de um procedimento é:

CREATE PROCEDURE ProcedureName
(Parameter1 datatype, Parameter2 datatype) AS
SQLStatement

Note-se que o nome do procedimento não deve ser igual ao nome de uma tabela existente, os parâmetros devem ser separados por vírgulas e a lista de parâmetros deve estar entre parênteses. Podemos ter até 255 parâmetros na lista, e qualquer tipo de dados SQL válidos pode ser usado (veja o artigo anterior Microsoft Jet SQL Intermediário para Access 2000, onde são apresentados os tipos de dados SQL). O comando SQL pode ser qualquer comando SQL válido e que seja reconhecido pelo motor de banco de dados Jet.

Vamos usar o comando CREATE PROCEDURE com um parâmetro DATETIME seguido de um comando DELETE para excluir todos os pedidos anteriores a 01/01/1999.

CREATE PROCEDURE DeleteInvoices
(InvoiceDate DATETIME) AS
DELETE FROM tblInvoices
WHERE tblInvoices.InvoiceDate < InvoiceDate

Note-se que, como o nome do parâmetro é o mesmo que o do campo, o nome do campo na cláusula WHERE deve ser qualificado com o nome da tabela. Para acabar com uma procedure, usamos o comando DROP PROCEDURE.

DROP PROCEDURE DeleteInvoices

Nota: Os comando CREATE PROCEDURE e DROP PROCEDURE só podem ser executados através do Jet OLE DB e ADO. Será apresentada uma mensagem de erro se usados na interface de usuário do Access ou pelo DAO. Note-se, também, que os procedimentos criados com o comando CREATE PROCEDURE são salvos no banco de dados mas não aparecem na interface de usuário do Access como consultas. Eles só podem ser utilizados em código de programação ADO ou ADOX.

Para executar o procedimento, usamos o comando EXECUTE (ou o sinônimo EXEC), passando os parâmetros requeridos. Se houver mais de um parâmetro, estes devem estar separados por vírgula.

EXECUTE DeleteInvoices '1/1/1999'

Nota: O comando EXECUTE acima só pode ser executado através do provedor Jet OLE DB e ADO. Uma mensagem de erro será apresentada se usado na interface do usuário do Access ou do DAO.

Administrando a Segurança

Como mencionado em uma seção anterior, alguns dos comandos DDL do Jet são usados para criar e manter direitos de acesso e permissões ao aplicativo de banco de dados (ou, em outras palavras, administrar a segurança). Ainda que a interface de usuário do Access nos dê a habilidade de administrar a segurança, na realidade quem faz todo o trabalho é o motor de banco de dados Jet.

O motor de banco de dados Jet oferece dois modos de segurança: nível de compartilhamento e nível de usuário. A segurança em nível de compartilhamento envolve a criação de uma senha para um banco de dados, o que é a forma mais simples de segurança. Uma vez criada esta opção de segurança, sempre que o banco de dados for aberto, o usuário deverá entrar com a senha e todos os usuários usam a mesma senha.

Uma forma mais complexa, e, também, mais flexível, de segurança é aquela baseada no nível do usuário ou do grupo de trabalho. Isto envolve o estabelecimento de grupos de trabalho e usuários e o fornecimento, a cada um, de permissões para executar determinadas tarefas no banco de dados. A utilização deste tipo de segurança permite que cada usuário tenha sua própria senha e um conjunto de permissões para a utilização dos vários objetos e atividades do banco de dados.

Alguns livros de referência falam dos comandos SQL usados para administrar a segurança como Linguagem de Controle de Dados (Data Control Language – DCL). Como o termo DCL não é padrão ANSI, não será citado na sintaxe do SQL Jet por este nome.

Segurança em Nível de Compartilhamento

Para criar segurança em nível de compartilhamento em um banco de dados Jet, usamos o comando ALTER DATABASE para criar, modificar ou remover a senha do banco de dados. Para usar o comando ALTER DATABASE o banco de dados deve ser aberto em modo exclusivo. O formato geral do comando ALTER DATABASE é o seguinte:

ALTER DATABASE PASSWORD NovaSenha SenhaAntiga

Para criar uma senha para um banco de dados que ainda não a tenha, usamos o comando ALTER DATABASE com a palavra chave NULL como parâmetro para SenhaAntiga. Digamos que queremos criar uma senha “admin” para o banco de dados:

ALTER DATABASE
PASSWORD admin NULL

Nota: O comando ALTER DATABASE só pode ser executado por meio do provedor Jet OLE DB e ADO, retornando uma mensagem de erro se usado na interface do usuário do Access ou com o DAO.

Para alterar a senha existente em um banco de dados, usamos o comando ALTER DATABASE com as senhas apropriadas. Para alterar a senha do banco de dados para “administrador”:

ALTER DATABASE
PASSWORD administrador admin

Para remover a senha de um banco de dados, usamos o comando ALTER DATABASE com a palavra chave NULL como parâmetro de NovaSenha. Para remover a senha “administrador” do banco de dados:

ALTER DATABASE
PASSWORD NULL administrador

Para maiores informações sobre o comando ALTER DATABASE, procure pelo comando no Help do Access.

Segurança em Nível de Usuário

A segurança em nível de usuário é uma forma muito mais robusta de gerenciar os direitos de acesso e permissões em um banco de dados Jet, mas, também, é mais complicada do que a utilização de uma simples senha para todo o banco de dados. A criação de segurança em nível de usuário envolve o estabelecimento de contas de grupos e usuários, que consistem de nomes e senhas e , então, da criação de permissões de acesso aos objetos do banco de dados. As informações de grupos e usuários são armazenadas no arquivo de informações do grupo de trabalho, que pode ser criado usando-se o Assistente de Segurança em Nível de Usuário do Access, e as permissões dos objetos do banco de dados são armazenadas nas tabelas de sistema do aplicativo. Como as informações sobre grupos de trabalho fogem do âmbito deste artigo, é interessante procurar por “grupo de trabalho” ou “workgroup” no Help do Access.

Contas de Usuário de Grupo

Uma vez estabelecidas as informações do arquivo de grupo de trabalho e conseguindo abrir o banco de dados com seu uso, temos uma variedade de comandos SQL que podem ser utilizados para administrar a segurança. Com o SQL avançado podemos criar, modificar e remover contas de usuários e grupos. Além disso, podemos adicionar usuários às contas de grupos. Os comandos SQL utilizados para gerenciar as contas em nível de usuário são CREATE, ADD, ALTER e DROP.

Suponhamos que temos um banco de dados Pedidos com três tabelas: tblCustomers, tblShipping e tblInvoices. Desejamos criar segurança no banco de dados para dois departamentos diferentes: Cobrança e Remessa, e cada departamento tem dois funcionários. Os comandos avançados a seguir demonstram como implementar nosso esquema de segurança.

Nota: Os comandos USER e GROUP só podem ser executados por meio do provedor Jet OLE DB e DAO, retornando uma mensagem de erro se usados pela interface de usuário do Access ou pelo DAO.

Primeiro precisamos criar nossos dois grupos, Cobrança e Remessa (Billing e Shipping). Usamos o comando CREATE GROUP para criar um grupo:

CREATE GROUP Billing

Para criar mais de um grupo de uma só vez, separamos os nomes dos grupos com vírgulas.

CREATE GROUP Billing, Shipping

Para remover um grupo usamos o comando DROP GROUP.

DROP GROUP Billing

Para remover mais de um grupo de uma só vez, separamos os nomes dos grupos com vírgulas.

DROP GROUP Billing, Shipping

Ainda que possamos criar as contas de grupos com apenas um nome, devemos incluir também um argumento opcional, conhecido como identificador pessoal ou PId. O PId é um valor de string extra que podem ser passados aos comandos CREATE GROUP e CREATE USER. O Jet vai combinar o PId com o nome de usuário ou grupo em um valor de chave único, conhecido como identificador de segurança ou SId. O SId é o valor que o Jet usa internamente para identificar e trabalhar com a conta de usuário ou grupo correspondente. A especificação de um PId na criação de uma conta de usuário ou grupo assegura que a conta é única. A especificação de um PId, além disso, permite a recriação de uma conta idêntica se o arquivo de segurança de grupo de trabalho for danificado ou se for necessário copiar conta para outro arquivo de grupo de trabalho. Uma vez criadas as contas e seus SIds correspondentes, o PId usado para criá-lo não pode ser alterado. Podemos, entretanto, mudar as senhas para as contas de usuários.

Os valores de Pid passados aos comandos CREATE GROUP e CREATE USER podem ter de 4 a 20 caracteres e são sensíveis à caixa (maiúsculas e minúsculas). Devemos usar valores de PId que sejam difíceis de adivinhar; uma combinação de letras em maiúscula e minúscula e números é o melhor. Para fortalecer, realmente, a segurança, devemos usar PIds diferentes para cada usuário e grupo. Para criar valores SID para nossos dois grupos, informamos os valores PId únicos:

CREATE GROUP Billing Gu294JxP1m, Shipping Kl27c5sI9h

Agora vamos adicionar nossos usuários. Tim e Sarah trabalham no departamento de Cobrança e Steve e Mary trabalham no departamento de Remessa. Antes de podermos adicionar estes usuários a seus respectivos grupos, precisamos criar suas contas com o comando CREATE USER, passando os nomes de usuários e senhas. Vamos usar “pwd” como senha para todos os usuários pois eles podem alterar sua própria senha no modelo de segurança do Jet.

CREATE USER Tim pwd

Para criar mais de uma conta de usuário em um único comando, separamos as informações das contas usando vírgulas.

CREATE USER Tim pwd, Sarah pwd, Steve pwd, Mary pwd

Novamente, é melhor criar as contas de usuários usando um PId. Para usar o PId em um comando CREATE USER, incluímos o PId após as senhas.

CREATE USER Tim pwd H3sJaZ9k2m

Se for necessário mudar a senha de um usuário, podemos usar o comando ALTER USER com a palavra chave PASWWORD. Devemos fornecer, também, as senhas antiga e nova. De modo geral, o formato do comando ALTER USER é o seguinte:

ALTER USER username PASSWORD NewPassword OldPassword

Digamos que a administração pediu que não fosse usado “pwd” como senha padrão e sim o nome dos usuários. Como não podemos alterar a senha de mais de um usuário por comando, teremos que enviar quatro comandos ALTER USER separados:

ALTER USER Tim PASSWORD tim pwd

ALTER USER Sarah PASSWORD sarah pwd

ALTER USER Steve PASSWORD steve pwd

ALTER USER Mary PASSWORD mary pwd

Para remover uma conta de usuário usamos o comando DROP USER.

DROP USER Tim

Para remover mais de uma conta de usuário em um único comando, separamos os nomes das contas de usuários usando vírgulas.

DROP USER Tim, Sarah, Steve, Mary

Uma vez criadas as contas de grupos e usuários, podemos usar o comando ADD USER para incluir usuários específicos em diferentes grupos de trabalho. Vamos adicionar nossos quatro usuários aos grupos apropriados. Usamos a palavra chave TO com o nome do grupo no comando ADD USER.

ADD USER Tim TO Billing

Para adicionar mais de um usuário a um grupo em um só comando, separamos os nomes de contas de usuários usando vírgulas.

ADD USER Tim, Sarah TO Billing

ADD USER Steve, Mary TO Shipping

ADD USER Tim, Sarah, Steve, Mary TO Users

Note-se que só podemos adicionar usuários a um grupo de cada vez. Também deve ser notado que devemos adicionar todos os usuários ao grupo padrão Users; se não, não será possível logar no aplicativo através da interface de usuário do Access, pois eles não terão privilégio de Leitura às tabelas de sistema do Jet, liberadas automaticamente ao grupo padrão (default) Users. Se os usuários forem incluídos através da interface de usuário do Access, eles serão incluídos automaticamente no grupo padrão Users.

Para remover um usuário de um grupo, usamos o comando DROP USER com a palavra chave FROM e o nome do grupo.

DROP USER Tim FROM Billing

Novamente, deve-se notar que só podemos remover usuário(s) de um grupo de cada vez.

Para remover mais de um usuário de um grupo apenas com um comando, separamos os nomes das contas de usuários com vírgulas.

DROP USER Tim, Sarah FROM Billing

DROP USER Steve, Mary FROM Shipping

DROP USER Tim, Sarah, Steve, Mary FROM Users

A remoção de um usuário de um grupo não exclui a conta do usuário, apenas remove aquele usuário daquele grupo. Remover a conta de usuários vai remover, automaticamente, o usuário de todos os grupos dos quais faça parte. Para remover completamente uma conta de usuário, usamos o comando DROP USER, como discutido anteriormente.

Permissões de Objetos do Banco de Dados

Para fazer uma boa implementação do modelo de segurança do Jet, devemos estabelecer permissões aos objetos do banco de dados para cada usuário ou grupo. Temos dois tipos de permissões que podem ser estabelecidas: explícitas ou implícitas. As permissões explícitas são os privilégios dados a um usuário específico, enquanto as permissões implícitas são aquelas dadas ao grupo e herdadas por todos os membros do grupo. De modo geral, a segurança em nível de usuários fica mais fácil de manter se dermos permissões apenas aos grupos e vincularmos os usuários aos grupos.

No SQL avançado, as permissões são administradas com os comandos GRANT e REVOKE.

Nota: os comandos GRANT e REVOKE só podem ser executados através do provedor Jet OLE DB e ADO, gerando uma mensagem de erro se usados através da interface de usuário do Access ou do DAO.

O comando GRANT permite que um usuário ou grupo efetue algum tipo de ação com algum objeto específico do banco de dados, e seus formatos gerais são:

GRANT privilege ON TABLE tablename TO grantee

GRANT privilege ON OBJECT objectname TO grantee

GRANT privilege ON CONTAINER containername TO grantee

GRANT privilege ON DATABASE TO grantee

O comando REVOKE faz com que não seja permitido a um usuário ou grupo executar algum tipo de ação sobre objetos do banco de dados. Seus formatos gerais são:

REVOKE privilege ON TABLE tablename FROM grantee

REVOKE privilege ON OBJECT objectname FROM grantee

REVOKE privilege ON CONTAINER containername FROM grantee

REVOKE privilege ON DATABASE TO grantee

As palavras chave usadas com os comandos GRANT e REVOKE são:

A palavra chave TABLE é usada para dar permissões de acesso a uma ou mais tabelas do banco de dados.

A palavra chave OBJECT é usada para especificar qualquer outro objeto do banco de dados que não seja uma tabela. Estes outros objetos incluem formulários, consultas, relatórios, macros, visões e procedimentos.

A palavra chave CONTAINER é usada para especificar quaisquer objetos de conteúdo, como tabelas, relacionamentos, formulários ou relatórios. Dar permissões em um objeto de conteúdo permite que sejam especificadas permissões que serão herdadas quando um objeto daquele tipo for criado. Em versões anteriores do Access, as permissões em nível de usuário podiam ser dadas em módulos ou objetos de conteúdo de módulos individuais. No Access 2000, usamos a senha de proteção de projeto VBA para criar a segurança para todos os módulos.

Quando usamos a palavra chave DATABASE fica implícito que se trata do banco de dados atual, não precisando, assim, de especificação.

O permissionário pode ser um nome de conta ou grupo, ou podemos usar as palavras chave PUBLIC ou ADMIN, com os seguintes problemas:

A utilização de PUBLIC vai dar as permissões ao grupo padrão Users e, assim, todos vão ter o privilégio.

A utilização de ADMIN vai dar permissões apenas à conta de usuário único Admin. De modo geral devemos desabilitar todas as permissões da conta do usuário Admin porque é uma conta padrão, usada para abrir todos os bancos de dados Access. Ou seja, em todas as instalações do Access, todos os usuários são logados automaticamente no arquivo de informações do grupo de trabalho padrão como usuário Admin e, assim, todas as permissões dadas à conta Admin estarão disponíveis a todos os usuários do Access. Se quisermos estabelecer uma conta administrativa, ela deverá ser criada separadamente.

Os privilégios que podem ser dados ou retirados estão resumidos na tabela abaixo.

Privilégio	Aplica-se a	Descrição
SELECT	Tabelas, Objetos, Containers	Permite que o usuário leia os dados e veja a estrutura de uma tabela, objeto ou container específico.
DELETE	Tabelas, Objetos, Containers	Permite que um usuário exclua dados de uma tabela, objeto ou container específico.
INSERT	Tabelas, Objetos, Containers	Permite que um usuário insira dados em uma tabela, objeto ou container específico.
UPDATE	Tabelas, Objetos, Containers	Permite que um usuário atualize dados em uma tabela, objeto ou container específico.
DROP	Tabelas, Objetos, Containers	Permite que um usuário remova uma tabela, objeto ou container específico.
SELECTSECURITY	Tabelas, Objetos, Containers	Permite que um usuário veja as permissões para uma tabela, objeto ou container específico.
UPDATESECURITY	Tabelas, Objetos, Containers	Permite que um usuário altere as permissões para uma tabela, objeto ou container específico.
UPDATEIDENTITY	Tabelas	Permite que um usuário altere os valores em uma coluna de auto incremento (autonumeração).
CREATE	Tabelas, Objetos, Containers	Permite que um usuário crie uma nova tabela,objeto ou container.
SELECTSCHEMA	Tabelas, Objetos, Containers	Permite que um usuário veja a estrutura de uma tabela, objeto ou container específico.
SCHEMA	Tabelas, Objetos, Containers	Permite que um usuário modifique a estrutura de uma tabela, objeto ou container específico.
UPDATEOWNER	Tabelas, Objetos, Containers	Permite que um usuário altere o proprietário de uma tabela, objeto ou container específico.
ALL PRIVILEGES	All	Dá todas as permissões a um usuário, inclusive administrativas, em uma tabela, objeto, container ou banco de dados especificado.
CREATEDB	Banco de dados	Permite que um usuário crie um novo banco de dados.
EXCLUSIVECONNECT	Banco de dados	Permite que um usuário abra um banco de dados em modo exclusivo.
CONNECT	Banco de dados	Permite que um usuário abra um banco de dados.
ADMINDB	Banco de dados	Permite que um usuário administre um banco de dados.

No nosso exemplo de banco de dados Pedidos, vamos dar permissões aos grupos Billing e Shipping. Em primeiro lugar, precisamos dar a todos a permissão de abrir o banco de dados, portanto vamos usar a palavra chave PUBLIC no nosso comando GRANT. Isto vai permitir que todos os membros do grupo Users padrão abram o banco de dados.

GRANT CONNECT
ON DATABASE
TO PUBLIC

Para a tabela de pedidos, vamos dar permissão apenas de leitura para o grupo Shipping e todas as permissões de manipulação de dados para o grupo Billing.

GRANT SELECT
ON TABLE tblInvoices
TO Shipping

GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE tblInvoices
TO Billing

Se nos logarmos no banco de dados como membros do grupo Shipping, seremos capazes de abrir a tabela de pedidos mas não vamos conseguir inserir, atualizar ou excluir nenhum registro.

Para remover permissões, usamos o comando REVOKE.

REVOKE SELECT
ON TABLE tblInvoices
FROM Shipping

REVOKE SELECT, INSERT, UPDATE, DELETE
ON TABLE tblInvoices
FROM Billing

Note-se que, no lugar da palavra chave TO, o comando REVOKE usa a palavra chave FROM.

Linguagem de Manipulação de Dados Avançada

Para a DML avançada, temos apenas dois conceitos SQL a discutir. Um é a utilização de transações e a outra é o uso da opção acesso proprietário (owner access).

Transações

Uma transação é um agrupamento lógico de trabalho, ou uma coleção de comandos SQL, que deve ser completado com sucesso como um grupo ou não executar. Este grupamento lógico tem um início, um fim e uma maneira de cancelar o trabalho se houver alguma razão. Por exemplo, digamos que temos uma série de comandos UPDATE que queremos a serem executados, mas queremos que sejam TODOS executados ou que não sejam aplicadas as alterações se ocorrer algum erro em qualquer dos comandos. Se ocorrer algum erro com qualquer dos comandos, nenhum deles será salvo no banco de dados.

Na sintaxe do Jet, usamos o comando BEGIN TRANSACTION (BEGIN TRANS) para iniciar a transação e, ou o comando COMMIT TRANSACTION para salvar os dados ou o comando ROLLBACK TRANSACTION para cancelar qualquer alteração. As transações podem estar aninhadas em até cinco níveis e devemos ter o cuidado de que todas as transações sejam confirmadas (committed) ou desfeitas (rolled back).

Nota: Os comandos BEGIN, COMMIT e ROLLBACK TRANSACTION só podem ser executados através do provedor Jet OLE DB e ADO. Será retornada uma mensagem de erro se usados através da interface de usuário do Access ou DAO. Além disso, não devemos misturar métodos de transação do Jet com os do ADO. Se isto ocorrer, podemos ter resultados inesperados.

A tabela abaixo apresenta os comandos de TRANSACTION, seus sinônimos e o que fazem.

Palavras Chave	Sinônimos	Descrição
BEGIN TRANSACTION	BEGIN WORK	Inicia a transação
COMMIT TRANSACTION	COMMIT, COMMIT WORK	Salva a transação
ROLLBACK TRANSACTION	ROLLBACK, ROLLBACK WORK	Cancela a transação

Digamos que queremos fazer uma limpeza dos dados antigos e excluir todos os pedidos com data anterior a primeiro de janeiro de qualquer ano, mas desejamos incluir um passo que permite aos usuários cancelar o processo antes de excluí-los. Para começar, emitimos o comando BEGIN TRANSACTION.

BEGIN TRANSACTION

Depois, emitimos o comando DELETE.

DELETE FROM tblInvoices
WHERE InvoiceDate < #1/1/1999#

Se abrirmos a tabela de Pedidos, veremos que os registros ainda não forma excluídos. Para cancelar as alterações antes que sejam atualizadas, enviamos o comando ROLLBACK TRANSACTION.

ROLLBACK TRANSACTION

Para salvar as atualizações no banco de dados, enviamos o comando COMMIT TRANSACTION.

COMMIT TRANSACTION

Comando Com Acesso de Proprietário

A cláusula WITH OWNERACCESS OPTION é aplicada a consultas e é mais usada para apresentar dados a usuários que não têm permissões suficientes para acessar normalmente os dados das tabelas referenciadas. Em outras palavras, podemos especificar que algumas consultas sejam executadas com as permissões do proprietário das consultas e não as permissões dos usuários que tentam abri-las.

Por exemplo, digamos que o departamento de remessa precisa saber dos pedidos dos clientes mas não queremos que eles vejam os seus valores. Criamos uma nova consulta especificando todos os campos menos o campo de valor. Usamos a cláusula WITH OWNERACCESS OPTION no final do comando SELECT, e chamamos a consulta Pedidos Limitados. Note-se que este comando pode ser executado na interface de usuário do Access.

SELECT InvoiceID, CustomerID, InvoiceDate
FROM tblInvoices
WITH OWNERACCESS OPTION

Em seguida, fazemos com que o departamento de remessa tenha permissão de leitura na tabela Pedidos Limitados.

GRANT SELECT
ON OBJECT [Limited Invoices]
TO Shipping

Nota: O comando GRANT acima só pode ser executado através do provedor Jet OLE DB e ADO, devolvendo uma mensagem de erro se usado pela interface de usuário do Access ou DAO.

Se nos logarmos como membros do grupo de remessa, não conseguiremos abrir a consulta Pedidos, mas podemos executar a consulta Pedidos Limitados. Além disso, podemos usar a cláusula WITH OWNERACCESS OPTION para dar permissões nos comandos INSERT INTO e SELECT INTO.

Usando SQL Avançado no Access

Agora que já vimos a sintaxe do SQL avançado, vamos dar uma olhada como podemos utilizá-la em um aplicativo Access.

Banco de Dados Exemplo

Junto com este artigo temos um banco de dados de exemplo: acAdvSQL.mdb.

Todo o conteúdo do acAdvSQL está baseado nos tópicos cobertos neste artigo e demonstra os diferentes comandos SQL através de consultas e códigos de exemplo. É importante, entretanto, notar que devemos criar um arquivo de informações de grupo de trabalho para o banco de dados acAdvSQL antes de usar os comandos de segurança em nível de usuário.

Muitas das consultas exemplo usadas no acAdvSQL dependem da existência de algumas tabelas e de que estas contenham dados. Se tiver dificuldade em executar alguma das consultas devido a falta de dados, abra o formulário Reset Tables e clique no botão Reset Tables. Isto vai recriar as tabelas e seus dados originais. Para executar manualmente e passo-a-passo, execute as consultas abaixo na ordem em que aparecem:

Drop Table Shipping

Drop Table Invoices

Drop Table Customers

Create Table Customers

Create Table Invoices

Create Table Shipping

Populate Customers

Populate Invoices

Populate Shipping

Setting the Database Password

Neste exemplo criamos um formulário que permite ao usuário alterar a senha do banco de dados. Vamos dizer que estamos criando um banco de dados que necessita de segurança em nível de compartilhamento. A decisão é implementar isto com a capacidade de criação de senhas do Jet, mas não podemos mostrar a interface de usuário do Access para manutenção de senhas pois ela permite apenas a criação ou remoção de senhas. Usando o SQL inline e um formulário customizado, podemos permitir que os usuários administrem sua segurança em nível de compartilhamento.

1. Crie um novo banco de dados e chame-o de Password.mdb.

2. Crie um novo formulário e chame-o de frmDBPassword.

3. Crie três caixas de texto com seus rótulos e chame estas caixas de texto como txtOldPwd, txtNewPwd, e xtConfirmPwd, fazendo com que a máscara de entrada seja Senha.

4. Crie três botões de comando e nomeie-os cmdCancel, cmdClear e cmdOk:

5. No evento Ao clicar do botão cmdCancel, digite o seguinte código:

DoCmd.Close acForm, "frmDBPassword"

6. No evento Ao clicar do botão cmdClear, entre com o código a seguir:

Dim conDatabase As ADODB.Connection
Dim SQL As String

On Error GoTo Error_Handler

Set conDatabase = Application.CurrentProject.Connection

' Limpa a senha apenas se for fornecida a senha antiga.
If Not IsNull(txtOldPwd) Then
SQL = "ALTER DATABASE PASSWORD NULL " & txtOldPwd
conDatabase.Execute SQL
MsgBox "A senha foi limpa com sucesso.", vbInformation
Else
MsgBox "Por favor, entre com a senha atual.", vbExclamation
txtOldPwd.SetFocus
Exit Sub
End If

'Fecha o formulário e todas as variáveis de objetos.
conDatabase.Close
Set conDatabase = Nothing
DoCmd.Close acForm, "frmDBPassword"

Exit Sub

Error_Handler:
MsgBox Err.Description, vbCritical

7. No evento Ao clicar do cmdOK, entre com o código:

Dim conDatabase As ADODB.Connection
Dim SQL As String
Dim intCheckPwd As Integer

On Error GoTo Error_Handler

Set conDatabase = Application.CurrentProject.Connection

'Verifica se a nova senha e a anterior são iguais.
intCheckPwd = StrComp(txtNewPwd, txtConfirmPwd, vbBinaryCompare)

'Determina se está alterando/limpando a senha atual ou
’criando uma nova senha.
If txtOldPwd.Enabled = False And intCheckPwd = 0 Then
SQL = "ALTER DATABASE PASSWORD " & txtNewPwd & " NULL"
conDatabase.Execute SQL
MsgBox "A nova senha foi atualizada com sucesso.", vbInformation

'Altera a senha atual.
ElseIf intCheckPwd = 0 Then

'Verifica se a senha anterior foi fornecida.
If IsNull(txtOldPwd) Then
MsgBox "Por favor, entre com a senha atual.", vbInformation
txtOldPwd.SetFocus
Exit Sub
End If

SQL = "ALTER DATABASE PASSWORD " & txtNewPwd & " " & txtOldPwd
conDatabase.Execute SQL
MsgBox "A senha foi alterada com sucesso.", vbInformation

'A senha não foi confirmada.
Else
MsgBox "A senha não foi confirmada. Tente novamente.", _
vbExclamation
txtConfirmPwd = vbNullString
txtConfirmPwd.SetFocus
Exit Sub
End If

'Fecha o formulário e todas as variáveis de objetos.
conDatabase.Close
Set conDatabase = Nothing
DoCmd.Close acForm, "frmDBPassword"

Exit Sub

Error_Handler:
MsgBox Err.Description, vbCritical

8. No evento Ao carregar do formulário frmDBPassword, entre com o código:

Dim conDatabase As ADODB.Connection
Dim SQL As String

On Error Resume Next

'Verifica se a senha do banco de dados está ativada.
Set conDatabase = Application.CurrentProject.Connection
SQL = "ALTER DATABASE PASSWORD NULL NULL"
conDatabase.Execute SQL

'Se o comando SQL não causar erro, a senha do banco de dados não está ativada.
If Err.Number = 0 Then
txtOldPwd.Enabled = False
cmdClear.Enabled = False
Me.Caption = "Nova Senha para o Banco"
Err.Clear
Else
Me.Caption = "Alterar senha do banco"
End If

'Close out all object variables.
conDatabase.Close
Set conDatabase = Nothing

Note o truque usado no modelo de código acima para determinar se o banco de dados tem ou não uma senha ativada. O comando SQL utiliza dois valores NULL. Se o comando gerar um erro, significa que o banco já tem uma senha.

Usando um Procedimento

Uma das boas coisas da utilização de procedimentos é que, como eles são construídos como parâmetros, podemos usá-los de forma bastante genérica. Por exemplo, no exemplo apresentado na seção sobre procedimentos, um comando DELETE era utilizado para excluir todos os pedidos mais antigos que uma data fornecida. Como o procedimento usa um parâmetro para a data, podemos solicitar ao usuário que entre com a data e, simplesmente, passa-la para o procedimento.

O código a seguir usa a função InputBox para solicitar uma data ao usuário:

Private Sub cmdExecuteProc_Click()
Dim conDatabase As ADODB.Connection
Dim RtnValue As String
Dim SQL As String

On Error GoTo Error_Handler

Set conDatabase = Application.CurrentProject.Connection
RtnValue = InputBox("Data para a exclusão:", _
"Excluir Pedidos", "1/1/1999")

If IsDate(RtnValue) Then
SQL = "EXECUTE DeleteInvoices '" & RtnValue & "'"
conDatabase.Execute SQL
MsgBox "Os Pedidos foram excluídos.", vbInformation
Else
MsgBox "A data fornecida é inválida. Tente novamente.", vbInformation
End If

conDatabase.Close
Set conDatabase = Nothing

Exit Sub

Error_Handler:
MsgBox Err.Description, vbInformation
End Sub

Um Último Comentário

Ainda que este artigo tenha posto o foco em comandos SQL encontrados, tipicamente, em soluções multiusuário, isso não quer dizer que não possam ser usados em soluções mono usuárias. Onde e quando usar comandos SQL da Linguagem de Definição de Dados e Linguagem de Manipulação de Dados dependem das necessidades dos aplicativos desenvolvidos.

Home