none
Gerar DDL Trigger de Grant pra Objetos RRS feed

  • Pergunta

  • Pessoal, tenho em um de meus ambientes o login A, no qual o mesmo é usuário de uma base de dados com permissões restritas definida através de ROLEs grp_tables, grp_views, grp_procedure e grp_function e é login de abertura de conexão de uma aplicação. Essas ROLEs comporta todos os objetos de tabelas, visões, procedimentos e funções respectivamentes desta base, sendo que tem como atributos as permissões necessárias para o funcionamento de cada objeto.

    O problema que tenho tido é a seguinte: A aplicação que roda neste banco de dados sofre muitas atualizações de versão e muito dessas vezes cria novas tabelas, visões, procedimentos e funções. Diante disso esses objetos criados recentemente acabam ficando fora das roles e consequentemente os usuários reclamam de falta de permissão. Com isso preciso acessar manualmente a ROLE e inserir o objeto, definindo a permissão devida.

    Alguém teria um script de uma DDL trigger que solucionasse essa situação?

    Agradeço desde já!

    sexta-feira, 16 de maio de 2014 02:14

Respostas

  • Deleted
    sexta-feira, 16 de maio de 2014 09:27
  • Jeferson,

    Tudo isso depende da política de segurança e mudanças adotada pela sua empresa. Se você deseja saber se é possível criar um modelo de perfil de acesso onde novos objetos (tabelas, views, procedures,...) tenham o mesmo comportamento de segurança indicado para cada usuário/ambiente eu posso afirmar que sim, isto é possível.

    Eu acredito que a equipe de desenvolvimento deve se reunir com o DBA ou com à equipe de infra para definir uma mudança nestes perfis para que ambos (desenvolvimento e DBA/infra) possam ser mais produtivos.

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    sexta-feira, 16 de maio de 2014 12:34
  • Deleted
    sexta-feira, 16 de maio de 2014 16:55

Todas as Respostas

  • Deleted
    sexta-feira, 16 de maio de 2014 09:27
  • Jeferson,

    Tudo isso depende da política de segurança e mudanças adotada pela sua empresa. Se você deseja saber se é possível criar um modelo de perfil de acesso onde novos objetos (tabelas, views, procedures,...) tenham o mesmo comportamento de segurança indicado para cada usuário/ambiente eu posso afirmar que sim, isto é possível.

    Eu acredito que a equipe de desenvolvimento deve se reunir com o DBA ou com à equipe de infra para definir uma mudança nestes perfis para que ambos (desenvolvimento e DBA/infra) possam ser mais produtivos.

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    sexta-feira, 16 de maio de 2014 12:34
  • Concordo plenamente e já tinha ciência disso, porém é uma questão bem difícil de ser mudada. Já se houve discussões sobre, porém as coisas vão bem mais além de um argumento dado pela equipe de infra do BD.

    Como por medida de segurança criei um login na instância, um usuário no banco e  linkei os mesmos. Depois criei as Rules grp_table, grp_views, grp_storedprocedure, grp_function e adicionei o usuário, onde teria as permissões necessárias que o sistema solicita pra funcionar perfeitamente. Depois configurei o login no arquivo.ini da aplicação. Acredito ter melhorado pra o que era antes, pois usavam o login sa no arquivo.ini da aplicação e a brecha de vulnerabilidade era bem maior. Fazendo desta forma pude alterar o username e password do login sa  e auditar os acessos vindo do login criado pra aplicação. Porém me deparo com o problema citado acima.

    Minha necessidade emergencial seria de ter um DDL trigger de banco definida, pra quando fosse definido um novo objeto na base a trigger fosse disparada executando um GRANT no objeto recém criado. 


    sexta-feira, 16 de maio de 2014 14:17
  • Deleted
    sexta-feira, 16 de maio de 2014 16:55
  • Na verdade a equipe de desenvolvimento é externa. Eles lançam atualizações no site e a equipe de suporte da empresa baixa e executar pela própria ferramenta da aplicação. Não tem acesso a base de produção, na qual somente eu tenho acesso direto.

    O problema é que quando é lançado uma atualização é com intuito de solucionar bug apresentado em algum modulo. É um sistema bastante complexo e antigo, tanto que ainda roda com a compatibilidade 80, pois venho herdado da versão 6.5 e 7.0 do SQL Server.

    Existem algumas questões difíceis de explicar por aqui, mas sempre estou conscientizando-os da necessidade de usarem a base de desenvolvimento e homologação que já existe no ambiente. É complicado mesmo...

    sexta-feira, 16 de maio de 2014 18:11
  • José, usei os comandos abaixo:

     --Gatilho configurado na base de dados produção do servidor 01 que dispara  informações pra servidor 02 linkserver no momento que é criado um objeto  na base em questão

    USE Base_producao
    
    GO
    
     SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    SET XACT_ABORT ON
    
    GO
    
    CREATE TRIGGER [trgTrace_Alteracao_Objeto]
    
    ON DATABASE
    
    FOR DDL_DATABASE_LEVEL_EVENTS
    
    AS
    
    BEGIN
    
        SET NOCOUNT ON
    
        DECLARE @Evento XML
    
        SET @Evento = EVENTDATA()
    
        INSERT INTO [IP_Servidor_02].Checklist.dbo.Trace_Alteracao_Objetos(Tp_Evento, Dt_Alteracao,
    
                            Nm_Servidor, Nm_Login, Nm_Database, Nm_Objeto)
    
        SELECT  @Evento.value('(/EVENT_INSTANCE/EventType/text())[1]','varchar(50)') Tipo_Evento,
    
                @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') PostTime,
    
                @Evento.value('(/EVENT_INSTANCE/ServerName/text())[1]','varchar(50)') ServerName,
    
                @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(50)') LoginName,
    
                @Evento.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','varchar(50)') DatabaseName,
    
                @Evento.value('(/EVENT_INSTANCE/ObjectName/text())[1]','varchar(50)') ObjectName
    
    END
    
    GO
    
    SET ANSI_NULLS OFF
    
    GO
    
    SET QUOTED_IDENTIFIER OFF
    
    GO
    
    SET XACT_ABORT ON
    
    GO
    
    ENABLE TRIGGER [trgTrace_Alteracao_Objeto] ON DATABASE
    
    GO

    segunda-feira, 26 de maio de 2014 02:03
  • Continuação....

    --Tabela que fica no servidor 02 linkserver e condiciona as informações coletadas da base de produção no servidor 01
    USE [Checklist]
    
    GO
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    SET ANSI_PADDING ON
    
    GO
    
    CREATE TABLE [dbo].[Trace_Alteracao_Objetos](
    
                    [Tp_Evento] [varchar](30) NULL,
    
                    [Dt_Alteracao] [datetime] NULL,
    
                    [Nm_Servidor] [varchar](20) NULL,
    
                    [Nm_Login] [varchar](50) NULL,
    
                    [Nm_Database] [varchar](20) NULL,
    
                    [Nm_Objeto] [varchar](50) NULL,
    
                    [Ds_Evento] [varchar](max) NULL
    
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    
    GO

     

    segunda-feira, 26 de maio de 2014 02:04
  • Continuação....

    Criado ambos os objetos normalmente, porém quando executo o teste abaixo o retorno de erro é:

    OLE DB provider "SQLNCLI10" for linked server " IP_Servidor_02" returned message "No transaction is active.".

    Msg 7391, Level 16, State 2, Procedure trgTrace_Alteracao_Objeto, Line 18

    The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " IP_Servidor_02" was unable to begin a distributed transaction.

    Sendo que até pra retornar o erro está demorando.

    --Exemplo Teste de criação de objeto na base de produção no servidor 01

    USE Base_producao
    
    GO
    
    CREATE TABLE [dbo].[Table_teste](
    
                    [Id] [int] NULL,
    
                    [name] [varchar](50) NULL
    
    ) ON [PRIMARY]
    
    GO
     

    Usando as orientações do link http://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction tentei configurar, mas mesmo assim não obtive êxito.

     

    O que poderia está fazendo de erro ou deixando de fazer?

     

    Agradeço desde já!

    segunda-feira, 26 de maio de 2014 02:04