Como utilizar funções de aplicações com projectos do Access e SQL Server 2000 Desktop Edition

Este artigo explica as capacidades, as limitações e as soluções alternativas para utilizar funções de aplicações do Microsoft SQL Server num projecto do Microsoft Access (ADP).

No SQL Server, pode criar funções de base de dados para administração de permissões numa base de dados. Em vez de conceder permissões individuais para cada utilizador em separado, pode agrupar os utilizadores com as necessidades de permissão mesmo tornando-os membros da mesma função de base de dados normal e, em seguida, atribuir permissões a função de base de dados propriamente dito. A menos que uma permissão específica explicitamente negada noutro local, os utilizadores membro irão adquirir as permissões concedidas a essa função de base de dados.

Enquanto as funções de base de dados normal são muito útil para situações onde pretende que os utilizadores possam efectuar as suas próprias consultas ad hoc ou actualizações para uma base de dados, não são sempre adequados. Por vezes, poderá pretender que os utilizadores apenas determinadas permissões quando utilizam uma aplicação específica e não pretender conseguir ver ou modificar dados fora da aplicação.

Um método que é utilizado frequentemente para trabalho solucionar a questão é apenas dar as permissões necessárias para uma conta de utilizador do SQL Server. Os utilizadores reais poderão têm permissão para ligar a uma base de dados mas não para visualizar ou modificar quaisquer dados. Depois de um utilizador liga a base de dados utilizando a conta de utilizador individuais, o ADP foi programaticamente volte utilizando as credenciais da conta de utilizador que tenha permissões. Apesar de pode ser eficaz, não permite distinguir entre utilizadores na base de dados ou para determinar qual o utilizador executar uma acção específica.

Funções da aplicação são concebidas para contornar esta limitação. Funções da aplicação, ao contrário das funções de base de dados normal, não tem membros próprios. Em vez disso, os utilizadores inicie sessão no SQL Server e ligar a uma utilizando as suas próprias credenciais de base de dados. Nessa altura, contexto de segurança de uma função de aplicação pode liquidar programaticamente uma ligação existente utilizando o procedimento sp_setapprole armazenados. No SQL Server, utilizadores individuais ainda são diferenciados mas as permissões que estão disponíveis dentro de uma determinada ligação estão limitadas às permissões da função de aplicação. O utilizador é individual permissões, se inferior ou superior, já não são considerados.

Criar uma função de aplicação

O Microsoft Access projectos não tem quaisquer ferramentas de estrutura visual para criar objectos de segurança do SQL Server, tais como funções da aplicação. A Microsoft recomenda que utilize as cliente ferramentas incluídas com a versão normal do SQL Server ou Microsoft Office XP Developer para criar a função de aplicações e atribuir permissões. No entanto, ainda pode criar a função de aplicação e conceder-as permissões necessárias programaticamente utilizando o Transact-SQL (T-SQL) de ADP. Embora um debate inteiro de segurança do SQL Server seja fora do âmbito deste artigo, adicionais podem ser encontradas informações no SQL Server Books Online os passos seguintes mostram como criar uma função de aplicação e permissões da nova função Seleccionar numa tabela através de programação:

1. Inicie o Access.
2. Abra o projecto Adamastor do Access de exemplo.
3. Na janela Base de dados, clique em módulos em objectos e, em seguida, clique em Novo para abrir um novo módulo no ambiente do Visual Basic.
   
   Nota No Access 2007, clique em módulo no outro grupo no separador criar .
4. Escreva ou cole o seguinte código para 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. Guarde o módulo e saia do ambiente Visual Basic.
6. Criar uma cópia da tabela clientes e, em seguida, guardá-lo como tNewTable . Para o fazer, siga estes passos:
   1. Na janela Base de dados, clique com o botão direito do rato na tabela clientes e, em seguida, clique em Guardar como no menu de atalho.
      
      Nota No Access 2007, clique na tabela clientes no painel de navegação, clique no Botão do Microsoft Office , aponte para Guardar como e, em seguida, clique em Guardar o objecto como .
   2. Na caixa de diálogo Guardar como , escreva tNewTable na caixa Guardar a tabela 'Clientes' para e, em seguida, clique em OK .
7. Na janela da base de dados, clique em formulários em objectos , clique em Novo e, em seguida, clique em OK para abrir um novo formulário na vista de estrutura.
   
   Nota No Access 2007, clique em Estrutura do formulário no grupo no separador criar formulários .
8. Adicionar um botão de comando para o novo formulário.
9. Defina a propriedade AoFazerClique 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 Visual Basic Environment para regressar ao formulário.
11. Guarde o formulário e, em seguida, mude o formulário para visualizar o formulário .
12. Clique no botão de comando para executar o código subjacente.
    
    Repare que receberá duas caixas de mensagem para indicar o êxito. Recebe uma depois da função de aplicação é criada e a segunda opção depois das novas permissões de função para tNewTable são concedidos.

Implementar a função de aplicação

A principal complication quando utiliza funções da aplicação nos projectos do Access é que o Access utiliza três ligações ao SQL Server para processar várias tarefas. Idealmente, para aplicar uma função de aplicação ao projecto inteiro, teria executar sp_setapprole no contexto de todos os três ligações. Os objectos processados por cada ligação são:

1. Utilizado para determinar que objectos são visualizados na base de dados de janela e de base de dados diversas tarefas administrativas.
   
   Utilizado para abrir tabelas, vistas, procedimentos armazenados, funções e as origens de registos para formulários e subrelatórios (mas não para o relatório principal próprio).
   
   Utilizado para obter as origens de registo para as caixas de combinação, caixas de listagem e relatórios.
2. Utilizado para abrir tabelas, vistas, procedimentos armazenados, funções e as origens de registos para formulários e subrelatórios (mas não para o relatório principal próprio).
   
   Utilizado para obter as origens de registo para as caixas de combinação, caixas de listagem e relatórios.
3. Utilizado para obter as origens de registo para as caixas de combinação, caixas de listagem e relatórios.

Apesar de ligações 2 # e # 3 podem ser acedidas facilmente bastante, não existe nenhum método disponível para executar o procedimento armazenado no contexto da ligação # 1. Felizmente, esta ligação é o menos importante das três e facilmente trabalhou volta criando a suas próprias interface de utilizador (por exemplo, um formulário do tipo de painel de navegação) para processar objectos de base de dados em vez de confiar na janela Base de dados incorporada.

Os passos seguintes utilizam o projecto de exemplo Adamastor do Access para demonstrar como aplicar uma função de aplicação contra ligações 2 # e # 3:

1. Na janela da base de dados, clique em formulários em objectos , clique em Novo e, em seguida, clique em OK para abrir um novo formulário na vista de estrutura.
   
   Nota No Access 2007, clique em Estrutura do formulário no grupo no separador criar formulários .
2. Adicionar uma caixa de listagem ao formulário recém-criado e, em seguida, defina a propriedade nome da caixa de listagem para lst_AppRole .
3. Adicionar um botão de comando ao formulário.
4. Defina a propriedade AoFazerClique 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 Visual Basic Environment para regressar ao formulário.
6. Guarde o formulário e, em seguida, mude o formulário para visualizar o formulário .
7. Clique no botão de comando para executar o código subjacente.
   
   Repare que é apresentada uma caixa de mensagem que indica êxito.
8. Na janela Base de dados, clique em tabelas em objectos e, em seguida, abra a tabela tNewTable .
   
   Nota No Access 2007, faça duplo clique tNewTable tabela no painel de navegação.
9. Modificar um registo e tentar guardar as alterações.

Repare que à medida que tenta consolidar as alterações, é apresentada uma mensagem de erro sobre não ter permissões suficientes. Isto ocorre porque que atribuiu a nova função aplicação permissão Seleccionar na tabela tNewTable , mas não permissão de actualização .

Por predefinição, o Access mostra apenas objectos na janela Base de dados para o qual o utilizador tem de seleccionar, pelo menos, ou executar permissões. O Access utiliza ligação # 1 para determinar quais os objectos que um utilizador tem permissões para. Depois de aplicar a aplicação de função para a janela ligações 2 # e # 3, a base de dados mostra os mesmos objectos que foram anteriormente, mesmo que o utilizador poderá não ter permissões para todos os objectos ou pode ter permissões para objectos mais que não são apresentados. Isto pode resultar num comportamento inesperado quando utiliza a janela de base de dados.

Por exemplo, quando abriu a tabela tNewTable, "aparece" que o utilizador tem permissões para editar e inserir registos. O ícone do registo inserir de novo na parte inferior da tabela está activado e o utilizador é capaz de colocar um registo em modo de edição. Não verá qualquer pista visual para indicar o contrário até tentar consolidar o editar ou inserir, o que resulta numa mensagem de erro. Acesso acredita que tiver permissões quando, na realidade, não o fizer.

A solução eficaz é fornecer uma interface personalizada para o utilizador e não confiar na janela Base de dados. Utilizando uma interface de utilizador do tipo de painel de navegação, pode controlar exactamente quais o utilizador tem acesso para os objectos.

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

os subformulários não funcionar

Ao contrário com outros objectos de base de dados Access não sempre utiliza a mesma ligação para obter a origem de dados de um subformulário. O Access com frequência (mas nem sempre) cria uma nova ligação SQL Server apenas para processar o conjunto de registos do subformulário ou para obter os dados do campo ligação que liga o subformulário no formulário principal. Uma vez que esta nova ligação não tem a função de aplicação aplicada, um erro de permissões pode ser gerado se não tiver permissões explícitas para o objecto de base de dados. Infelizmente, isto significa que não existe nenhuma forma fiável utilizar subformulários dependentes quando funções da aplicação são aplicadas. A solução alternativa eficaz apenas deve ter completamente independente subformulários, com a manipulação de dados processada por programação. Esta é a limitação mais grave quando utilizar funções da aplicação no Access.

relatórios não funcionar

Quando tiver um objecto como uma tabela ou um nome de vista listada como a origem de registos para um relatório ou subrelatório, o Access verifica se o objecto está listado na janela Base de dados antes de obter os dados do SQL Server. Uma vez que a janela de base de dados utiliza uma ligação que não tem a função de aplicação aplicada, é gerado um erro se não tiver permissões explícitas à origem de dados subjacente.

Para contornar este problema, utilize sempre instruções de Transact-SQL como a origem de registos para formulários e relatórios. Por exemplo, utilize "Seleccionar * da ViewName" em vez de apenas "ViewName" ou "" Exec "StoredProcedureName" em vez de apenas "StoredProcedureName". Desta forma, o Access transmite as instruções de Transact-SQL directamente para o SQL Server e obtém os dados baseados nas permissões da função de aplicação.

função de base de dados pública

Uma função de aplicação adquire permissões da função de base de dados pública. Por predefinição em AdamastorCS, a função Public tem todas as permissões para a maioria dos objectos. Por conseguinte, uma função de aplicação é geralmente ineficaz. Quando criou a tabela tNewTable na secção "Criar um grupo de aplicações", a função Public não foi concedida permissão para a tabela e viu mais tarde os efeitos do contexto de segurança função aplicação nessa tabela. No entanto, outras tabelas poderão não aparecer qualquer diferença em função da aplicação uma vez que a função Public tem permissões para esses objectos.

segurança VBA

Uma vez que a palavra-passe para a função Application está incorporada para a aplicação a partir da qual é chamada, um utilizador experientes conseguiria ler o nome da função aplicação e a palavra-passe do código de origem e, em seguida, utilizar essas informações para aceder ao SQL Server a partir de outra aplicação. Por conseguinte, é uma boa ideia para compilar o ADP num ficheiro ADE para que o código de origem não seja visível. No mínimo, aplica uma palavra-passe no projecto VBA.

Para obter informações adicionais sobre uma versão de Microsoft Access 2000 deste artigo, clique no número de artigo existente abaixo para visualizar o artigo na base de dados de conhecimento da Microsoft: Para obter mais informações sobre GRANT, consulte o SQL Server Books Online . O SQL Server Books Online está disponível no seguinte Web site da Microsoft: