Como usar funções de aplicativo com projetos do Access e SQL Server 2000 Desktop Edition

Este artigo explica os recursos, as limitações e soluções alternativas para usar funções de aplicativo do Microsoft SQL Server em um projeto Microsoft Access (ADP).

No SQL Server, você pode criar funções de banco de dados para facilitar a administração de permissões em um banco de dados. Em vez de conceder permissões individuais para cada usuário separadamente, você pode agrupar os usuários com as necessidades de permissão mesmo tornando-os membros da função mesmo banco de dados regular e, em seguida, atribuindo permissões à função de banco de dados próprio. A menos que uma permissão específica explicitamente negada em outro lugar, os usuários de membro adquirirá as permissões concedidas a essa função de banco de dados.

Embora funções regulares do banco de dados são muito úteis para situações onde você deseja que usuários possam executar suas próprias consultas ad hoc ou atualizações em um banco de dados, eles não são sempre apropriados. Às vezes, pode desejar que os usuários só têm determinadas permissões quando eles usam um aplicativo específico e deseja consiga exibir ou modificar dados fora do aplicativo.

Um método que é usado freqüentemente para trabalho resolver isso é apenas dar as permissões necessárias para uma conta de usuário do SQL Server. Os usuários reais talvez tenha permissões para se conectar a um banco de dados, mas não para exibir ou modificar os dados. Depois que um usuário se conectar ao banco de dados usando a conta de usuário individuais, o ADP foi programaticamente reconecte usando as credenciais da conta de usuário que tenha permissões. Embora isso possa ser eficaz, ele não permite para distinguir entre os usuários no banco de dados ou para determinar qual usuário realizado uma ação específica.

Funções do aplicativo são projetadas para contornar esta limitação. Funções do aplicativo, ao contrário das funções de banco de dados regular, não têm membros próprios. Em vez disso, os usuários logon em um SQL Server e se conectar a um banco de dados usando suas próprias credenciais. Nesse ponto, o contexto de segurança de uma função de aplicativo pode ser aplicado por meio de programação a uma conexão existente usando o procedimento sp_setapprole armazenados. No SQL Server, usuários individuais ainda são diferenciados mas as permissões que estão disponíveis em uma determinada conexão limitadas as permissões da função de aplicativo. O usuário é individual permissões, se menor ou maior, não são considerados.

Criar uma função de aplicativo

Microsoft Access projetos não tem as ferramentas de design visual para criar objetos de segurança do SQL Server, como funções do aplicativo. A Microsoft recomenda que você use as ferramentas de cliente que estão incluídas a versão regular do SQL Server ou Microsoft Office XP Developer para criar a função de aplicativos e atribuindo a ela permissões. No entanto, você ainda pode criar a função de aplicativo e concedê-lo as permissões necessárias programaticamente usando o Transact-SQL (T-SQL) de um ADP. Embora uma discussão completa sobre segurança do SQL Server seja fora do escopo deste artigo, adicionais informações podem ser encontradas no SQL Server Books Online as etapas a seguir mostram como criar uma função de aplicativo por programação e conceder a nova função Selecionar permissões em uma tabela:

1. Inicie o Access.
2. Abra o projeto de Access de exemplo Northwind.
3. Na janela banco de dados, clique em módulos em objetos e, em seguida, clique em novo para abrir um novo módulo no ambiente do Visual Basic.
   
   Observação No Access 2007, clique em módulo no outro grupo na guia criar .
4. Digite ou cole o código a seguir o novo módulo:

   Public Function AddNewAppRole(RoleName As String, PW As String) As Boolean
   On Error GoTo EH:
   If CurrentProject.IsConnected Then
   Dim sTSQL As String
       'Create the command
   sTSQL = "EXEC sp_addapprole '" & RoleName & "','" & PW & "'"
       'Send the command
   Application.CurrentProject.Connection.Execute sTSQL
   AddNewAppRole = True
   Else
   AddNewAppRole = False
   End If
   Exit Function
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   AddNewAppRole = False
   End Function
   					

5. Salve o módulo e feche o ambiente Visual Basic.
6. Criar uma cópia da tabela Customers e em seguida, salve-o como tNewTable . Para fazer isso, execute as seguintes etapas:
   1. Na janela banco de dados, clique com o botão direito na tabela Customers e clique em Salvar como no menu de atalho.
      
      Observação No Access 2007, clique na tabela clientes no painel de navegação, clique no Botão do Microsoft Office , aponte para Salvar como e, em seguida, clique em Salvar objeto como .
   2. Na caixa de diálogo Salvar como , digite tNewTable na caixa Salvar tabela 'Clientes' para e, em seguida, clique em OK .
7. Na janela banco de dados, clique em formulários sob objetos , clique em novo e, em seguida, clique em OK para abrir um novo formulário no modo de design.
   
   Observação No Access 2007, clique em Design do formulário no grupo formulários na guia criar .
8. Adicione um botão de comando para o novo formulário.
9. Defina a propriedade OnClick do botão de comando novo para o seguinte procedimento de evento:

   On Error GoTo EH:
   'Code only works if ADP is connected.
   If CurrentProject.IsConnected Then
       Dim bNewAppRole As Boolean, strTSQL As String
       Dim strRoleName As String, strPW As String
       strRoleName = "AppRoleName"
       strPW = "Password"
       'Call function to create app role.
       bNewAppRole = AddNewAppRole(strRoleName, strPW)
       'Test to see if it failed.
       If bNewAppRole = False Then
           Exit Sub
       End If
       MsgBox "New Application role '" & strRoleName & "' created", vbInformation
       'Create command to grant permissions.
       strTSQL = "Grant Select on tNewTable to " & strRoleName
       'Send the command.
       Application.CurrentProject.Connection.Execute strTSQL
       MsgBox "Select permissions granted on tNewTable for " & strRoleName
   Else
   MsgBox "ADP must be connected to SQL Server"
   End If
   Exit Sub
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   					

10. Feche o ambiente do Visual Basic para retornar ao formulário.
11. Salve o formulário e, em seguida, alterne o formulário para o formulário Exibir .
12. Clique o botão de comando para executar o código subjacente.
    
    Observe que você receberá duas caixas de mensagem para indicar êxito. Você recebe uma após a função de aplicativo é criada e o um segundo depois de novas permissões de função para tNewTable são concedidas.

Implementando a função de aplicativo

A principal complicação quando você estiver usando funções do aplicativo em projetos do Access é que o Access utiliza três conexões ao SQL Server para lidar com várias tarefas. O ideal é que para aplicar uma função de aplicativo para o projeto inteiro, você teria que executar sp_setapprole no contexto de todas as três conexões. Os objetos tratados por cada conexão são da seguinte maneira:

1. Usado para determinar quais objetos aparecem no banco de dados de janela e para o banco de dados de diversas tarefas administrativas.
   
   Usado para abrir tabelas, modos de exibição, procedimentos armazenados, funções e as fontes de registro para formulários e sub-relatórios (mas não para o próprio relatório principal).
   
   Usado para obter as origens do Registro para relatórios, caixas de listagem e caixas de combinação.
2. Usado para abrir tabelas, modos de exibição, procedimentos armazenados, funções e as fontes de registro para formulários e sub-relatórios (mas não para o próprio relatório principal).
   
   Usado para obter as origens do Registro para relatórios, caixas de listagem e caixas de combinação.
3. Usado para obter as origens do Registro para relatórios, caixas de listagem e caixas de combinação.

Embora conexões # 2 e 3 podem ser acessados bastante fácil, não há nenhum método disponível para executar o procedimento armazenado no contexto de conexão # 1. Felizmente, essa conexão é o menos importante dos três e facilmente é contornado criando sua própria interface do usuário (por exemplo, um formulário do tipo de menu de controle) para manipular objetos de banco de dados em vez de confiar na janela banco de dados interna.

As seguintes etapas usam o projeto de Access de exemplo Northwind para demonstrar como aplicar uma função de aplicativo contra conexões # 2 e 3:

1. Na janela banco de dados, clique em formulários sob objetos , clique em novo e, em seguida, clique em OK para abrir um novo formulário no modo de design.
   
   Observação No Access 2007, clique em Design do formulário no grupo formulários na guia criar .
2. Adicione uma caixa de listagem para o formulário recém-criado e, em seguida, defina a propriedade nome da caixa de listagem como lst_AppRole .
3. Adicione um botão de comando ao formulário.
4. Defina a propriedade OnClick do botão de comando novo para o seguinte procedimento de evento:

   On Error GoTo EH
       'This avoids a message that no records were returned.
   DoCmd.SetWarnings False
   Dim TSQL
   TSQL = "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'Password'}, 'odbc'"
       'This sets the app role on Connection #2.
   Application.CurrentProject.Connection.Execute TSQL
       'This sets the app role on Connection #3.
   lst_approle.RowSource = TSQL
   lst_approle.Requery
   DoCmd.SetWarnings True
   MsgBox "The application Role is now in effect.", vbInformation
   Exit Sub
   EH:
   MsgBox Err.Number & ": " & Err.Description, vbCritical
   					

5. Feche o ambiente do Visual Basic para retornar ao formulário.
6. Salve o formulário e, em seguida, alterne o formulário para o formulário Exibir .
7. Clique o botão de comando para executar o código subjacente.
   
   Observe que você recebe uma caixa de mensagem que indica êxito.
8. Na janela banco de dados, clique em tabelas sob objetos e, em seguida, abra a tabela tNewTable .
   
   Observação No Access 2007, clique duas vezes tNewTable tabela no painel de navegação.
9. Modificar um registro e tente salvar as alterações.

Observe que, como tentar confirmar as alterações, você recebe uma mensagem de erro sobre não ter permissões suficientes. Isso ocorre porque você deu a nova função aplicativo permissão Select na tabela tNewTable , mas não permissão de atualização .

Por design, o Access somente mostra objetos na janela banco de dados para o qual o usuário selecionar pelo menos ou executar permissões. O Access usa a conexão 1 para determinar quais objetos de um usuário tem permissões para. Depois de aplicar o aplicativo função a janela Conexões de 2 e 3, o banco de dados ainda mostra os mesmos objetos que tinha antes, mesmo que o usuário pode não ter permissões para todos os objetos ou tenha permissões para objetos mais que não são mostrados. Isso pode resultar em comportamento inesperado quando você usa a janela banco de dados.

Por exemplo, quando você abriu a tabela tNewTable, ele "aparece" que o usuário tenha permissões para editar e inserir registros. O ícone de registro Inserir nova na parte inferior da tabela é ativado e o usuário é capaz de colocar um registro no modo de edição. Você não vê qualquer indicação visual para indicar, caso contrário, até que você tentar confirmar edição ou inserção, o que resulta em uma mensagem de erro. Acesso acredita que você tem permissões quando você realmente não.

A solução mais eficiente é fornecer uma interface personalizada para o usuário e não para confiar na janela banco de dados. Usando uma interface de usuário do tipo de menu de controle, você pode controlar exatamente quais objetos o usuário tem acesso para.

Outras limitações e considerações de segurança

os subformulários não funcionando

Ao contrário com outros objetos de banco de dados, Access não sempre usa a mesma conexão para recuperar a fonte de dados de um subformulário. Acesso com freqüência (mas não sempre) cria uma nova conexão para o SQL Server apenas para manipular o conjunto de registros subformulário, ou recuperar os dados de campo de vinculação que se conecta o subformulário ao formulário principal. Porque esta nova conexão não tem a função de aplicativo aplicada, um erro de permissões pode ser gerado se você não tiver permissões explícitas para o objeto de banco de dados. Infelizmente, isso significa que não há nenhuma maneira confiável para usar subformulários vinculados ao funções do aplicativo são aplicadas. A solução alternativa só é completamente ter não acoplado subformulários, com a manipulação de dados manipulada programaticamente. Isso é a limitação mais séria ao usar funções do aplicativo no Access.

relatórios não funcionando

Quando você tiver um objeto como uma tabela ou um nome de modo de exibição listado como fonte do Registro para um relatório ou sub-relatório, o Access verifica se o objeto estiver na janela banco de dados antes de recuperar os dados do SQL Server. Como a janela banco de dados usa uma conexão que não tem a função de aplicativo aplicada, é gerado um erro se você não tiver permissões explícitas à fonte de dados subjacente.

Para contornar este problema, use sempre instruções Transact-SQL como fonte de registros para formulários e relatórios. Por exemplo, use "Selecionar * de nomedaexibição" em vez de apenas "Nomedaexibição" ou "Exec StoredProcedureName" em vez de apenas "StoredProcedureName". Dessa forma, acesso passa as instruções Transact-SQL diretamente para o SQL Server e recupera os dados com base nas permissões da função de aplicativo.

função de banco de dados pública

Uma função de aplicativo adquire as permissões da função de banco de dados pública. Por padrão no NorthwindCS, a função pública tem permissões completas para a maioria dos objetos. Portanto, uma função de aplicativo é geralmente ineficaz. Quando você criou a tabela tNewTable na seção "Criando uma função de aplicativo", a função pública não foi concedida permissão para a tabela, e você viu posteriormente os efeitos de contexto de segurança do aplicativo função nessa tabela. No entanto, outras tabelas não podem mostrar qualquer diferença na função de aplicativo porque a função pública tem permissões para esses objetos.

segurança do VBA

Como a senha para a função de aplicativo está incorporada no aplicativo do qual ele é chamado, um usuário experiente poderão ler o nome de função de aplicativo e a senha do código-fonte e, em seguida, usar essas informações para obter acesso ao SQL Server de outro aplicativo. Portanto, é uma boa idéia para compilar o ADP em um arquivo ADE para que o código-fonte não seja visível. No mínimo, aplicar uma senha no projeto VBA.

Para obter informações adicionais sobre uma versão Microsoft Access 2000 deste artigo, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft: Para obter mais informações sobre GRANT, consulte os MANUAIS online do. Os MANUAIS online está disponível do seguinte site da Web: