RM - PEP 2.0 - Scripts Grants para bancos de dados SQL

Dúvida 

Como instalar e configurar o banco de dados SQL no PEP 2.0 ?

Objetivo
Como instalar e configurar o banco de dados SQL no PEP 2.0.

Versão
Versão SGBD: SQL Server 2008/R2 e posteriores

Solução
1. Criação do Banco de Dados

Uma vez que a instância do SQL Server já foi instalada e configurada, é necessário a criação
do banco de dados do PEP. Por padrão, o banco é criado com dois arquivos de dados.
Executar o script abaixo com usuário com permissões SA.

Nome sugerido:

• Banco de Dados: PEP

Script para execução:

USE MASTER
GO
/* Criação do banco de dados PEP */
CREATE DATABASE PEP ON PRIMARY
( NAME = 'PEP', FILENAME = '[CAMINHO_DATAFILES]\PEP.mdf' , SIZE = 5120KB
, FILEGROWTH = 10%)
LOG ON
( NAME = 'PEP_log', FILENAME = '[CAMINHO_DATAFILES]\PEP_log.ldf' , SIZE
= 1024KB , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AI
GO

 

2. Criação de Usuários

O PEP possui dois usuários padrões:

• Usuário Owner: Usuário com permissões de owner do banco de dados, que conseguirá
  acessar todos os objetos do banco de dados (tabelas, views, procedures, etc).
• Usuário Consulta: Usuário que só tem permissão para executar comandos DML no
  banco de dados. A aplicação vai utilizar este usuário para conectar com o banco de dados.
  Usuário não deverá ter permissões para criar ou alterar estruturas de objetos, apenas acessar
  para leitura ou escrita de dados.
  
  

Nomes sugeridos:

• Usuário Owner: PEP
• Usuário Consulta: CONS_PEP
• Senha Usuário Owner: “t0tv$dba”
• Senha Usuário Consulta: “CONS_PEP”

Script para execução:

/* Limpa usuários do banco do PEP */
USE PEP
DECLARE @vUser NVARCHAR(30)
DECLARE @vSql NVARCHAR(50)
DECLARE CurCleanUsers CURSOR FOR select name from sysusers where name
like '%PEP%'
OPEN CurCleanUsers
fetch CurCleanUsers into @vUser
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vSql = 'DROP USER ' + @vUser
execute sp_executesql @vSql
fetch CurCleanUsers into @vUser
END
CLOSE CurCleanUsers
DEALLOCATE CurCleanUsers
GO

/* Criação do usuário Owner */
USE MASTER
GO
CREATE   LOGIN   PEP    WITH    PASSWORD='t0tv$dba',     DEFAULT_DATABASE=PEP,
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE PEP
CREATE USER PEP FOR LOGIN PEP
GO
EXEC sp_addrolemember N'db_owner', N'PEP'
GO

/* Criação do usuário que a aplicação utilizará */
USE MASTER
GO
CREATE LOGIN CONS_PEP WITH PASSWORD='CONS_PEP', DEFAULT_DATABASE=PEP,
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE PEP
CREATE USER CONS_PEP FOR LOGIN CONS_PEP
GO
EXEC sp_addrolemember N'db_datareader', 'CONS_PEP'
EXEC sp_addrolemember N'db_datawriter', 'CONS_PEP'
GO

/* Altera parâmetrização referente ao database */
USE PEP
update SYS140_UPDATE_DB_PARAMS
set sys140_param_value = 'PEP'
where sys140_param_name = '&USUARIO_OWNER';

update SYS140_UPDATE_DB_PARAMS
set sys140_param_value = 'CONS_PEP'
where sys140_param_name = '&USUARIO_CONSULTA';
GO

 

3. Configuração de Grants

Como o usuário da aplicação “CONS_PEP” já possui as permissões de db_datawriter e
db_datareader no database “PEP”, é necessário conceder as permissões nos demais objetos de
banco de dados (Procedures, functions, etc) com o script abaixo.
Script para execução:

/* Seta grants de procedures e funções */
USE PEP
DECLARE @Cmd NVARCHAR(100)
DECLARE CurObj CURSOR FOR
select
       case when type = 'TF'
            then 'grant select on PEP.dbo.' + name +' to CONS_PEP'
            else 'grant execute on PEP.dbo.' + name +' to CONS_PEP'

       end from sys.objects t
   WHERE TYPE IN ('FN','P','TF')
OPEN CurObj
fetch CurObj into @Cmd
WHILE @@FETCH_STATUS = 0
BEGIN
execute sp_executesql @Cmd
fetch CurObj into @Cmd
END
CLOSE CurObj
DEALLOCATE CurObj
GO