SQL Server 2008 - Modulo 04 - Curso 6231

--Aula 01 - Segurança do Servidor
====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--                    Nível 1
--###############################################


--Mudar o modo de autenticação para "SQL Server and Windows Authentication mode" (Mixed Mode)


--Criar o usuário Julia no servidor de domínio



--Windows authentication
USE [master]
GO
CREATE LOGIN [NY-SQL-01\julia]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks2008
GO


USE [AdventureWorks2008]
GO

CREATE USER [NY-SQL-01\julia]
FOR LOGIN [NY-SQL-01\julia]
GO

EXEC sp_addrolemember 'db_datareader', 'NY-SQL-01\julia'
GO



--SQL Server authentication
USE [master]
GO
CREATE LOGIN [alice]
WITH PASSWORD='Pa$$w0rd' MUST_CHANGE,
DEFAULT_DATABASE=AdventureWorks2008,
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO



--Desabilitando um login
ALTER LOGIN alice DISABLE
GO


--Habilitando um login
ALTER LOGIN alice ENABLE
GO


--Desbloqueando um login
ALTER LOGIN alice
WITH PASSWORD = 'NewPa$$w0rd' UNLOCK
GO


--Removendo um login
DROP LOGIN alice
GO


--Recriando o login da alice
USE [master]
GO
CREATE LOGIN [alice]
WITH PASSWORD='Pa$$w0rd',
DEFAULT_DATABASE=AdventureWorks2008
GO



--Aula 02 - Segurança da Instância
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--                    Nível 2
--###############################################

--Concedendo permissão - sysadmin
EXEC sp_addsrvrolemember 'alice', 'sysadmin'
GO


--Concedendo permissão - dbcreator
EXEC sp_addsrvrolemember 'alice', 'dbcreator'
GO


--Removendo permissão - sysadmin
EXEC sp_dropsrvrolemember 'alice', 'sysadmin'
GO


--Removendo permissão - sysadmin
EXEC sp_dropsrvrolemember 'alice', 'dbcreator'
GO



--Aula 03 - Criando Credencial
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--                    Nível 3
--###############################################

--Criando um CREDENTIAL
CREATE CREDENTIAL ActiveX WITH IDENTITY = 'alice',
SECRET = 'D1sn8yL@nd'    --> Opcional
GO



--Aula 04 - Permissão de Escopo de Servidor
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--        Nível 4        Server-Scope Permissions
--###############################################

--Concedendo permissão no nível de servidor
USE [master]
GO
GRANT ALTER ANY LOGIN
TO alice
GO


--Criando um login SQL Server
USE [master]
GO
CREATE LOGIN lucas
WITH PASSWORD = 'Pa$$w0rd',
DEFAULT_DATABASE = AdventureWorks2008


--Alice tem permissão para alterar o login do Lucas
GRANT ALTER ON LOGIN::lucas
TO alice
GO



--Aula 05 - Roles de Banco de Dados

-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--        Nível 5        Database Roles
--###############################################

--Criando um usuário
USE AdventureWorks2008
GO
CREATE USER alice
FOR LOGIN alice
WITH DEFAULT_SCHEMA = AdventureWorks2008


--Apagando um usuário
USE AdventureWorks2008
GO
DROP USER alice


--CONCEDENDO permissão de LEITURA para o usuário
USE AdventureWorks2008
GO
EXEC sp_addrolemember 'db_datareader', 'alice'
GO


--CONCEDENDO permissão de ESCRITA para o usuário
USE AdventureWorks2008
GO
EXEC sp_addrolemember 'db_datawriter', 'alice'
GO


--RETIRANDO permissão de ESCRITA para o usuário
USE AdventureWorks2008
GO
EXEC sp_droprolemember 'db_datawriter', 'alice'
GO


--RETIRANDO permissão de LEITURA para o usuário
USE AdventureWorks2008
GO
EXEC sp_droprolemember 'db_datareader', 'alice'
GO



--Aula 06 - Role de Aplicação
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--        Nível 6        Application Roles
--###############################################

--Criando uma Application Role
CREATE APPLICATION ROLE financeiro
WITH PASSWORD = '987Gbv876sPYY5m23'
GO


--Apagando uma Application Role
DROP APPLICATION ROLE financeiro
GO



--Aula 07 - Permissão de Escopo de Banco de Dados
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--        Nível 7        Database-Scope Permissions
--###############################################

--Concedendo permissão para criar SP
USE AdventureWorks2008
GO
GRANT CREATE PROCEDURE
TO alice
GO


--Criando um usuário
USE AdventureWorks2008
GO
CREATE USER lucas
FOR LOGIN lucas
WITH DEFAULT_SCHEMA = AdventureWorks2008


--Concedendo permissão para alterar qualquer usuário
USE AdventureWorks2008
GO
GRANT ALTER ANY USER
TO lucas



--Aula 08 - Permissão de Escopo de Schema

-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###############################################
--        Nível 8        Schema-Scope Permissions
--###############################################

--SCHEMA

--Criando o login e user da lara
USE [master]
GO
CREATE LOGIN [lara] WITH PASSWORD='123',
DEFAULT_DATABASE=[AdventureWorks2008],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

USE [AdventureWorks2008]
GO
CREATE USER [lara] FOR LOGIN [lara]
GO


--Concedendo permissão de SELECT no schema Production
USE AdventureWorks2008
GO
GRANT SELECT ON SCHEMA::Production
TO [lara]
GO


-------------------------------------------------------
-----  Testando em OUTRA SESSÃO logado com lara   -----
-------------------------------------------------------
SELECT TOP 10 * FROM Person.Person

SELECT TOP 10 * FROM Production.Product

SELECT TOP 10 * FROM Production.WorkOrder

SELECT TOP 10 * FROM HumanResources.Employee
-------------------------------------------------------



--TABLE

--Concedendo permissão de SELECT na tabela Person.AddressType
USE AdventureWorks2008
GO
GRANT SELECT ON Person.AddressType
TO lara
GO

-------------------------------------------------------
-----  Testando em OUTRA SESSÃO logado com lara   -----
-------------------------------------------------------
SELECT TOP 10 * FROM Person.AddressType

SELECT TOP 10 * FROM Person.Person
-------------------------------------------------------


--COLUMN

--Concedendo permissão de SELECT para a View

CREATE VIEW vw_Sales_Store
AS
SELECT [name], CountryRegionCode
FROM Sales.SalesTerritory


USE AdventureWorks2008
GO
GRANT SELECT ON vw_Sales_Store
TO [lara]
GO


-------------------------------------------------------
-----  Testando em OUTRA SESSÃO logado com lara   -----
-------------------------------------------------------
SELECT [name], CountryRegionCode
FROM Sales.SalesTerritory


SELECT * FROM vw_Sales_Store
-------------------------------------------------------


--Aula 09 - Chave e Certificado
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###################################################################
--        Nível 9        Managing Keys and Certificates in SQL Server
--###################################################################s

CREATE DATABASE DB6231
GO

USE DB6231
GO

CREATE TABLE users
(
    cod_user    INT                IDENTITY    PRIMARY KEY,
    nome_user    CHAR(30)        NOT NULL,
    pwd_user    VARBINARY(300)
)
GO



--> Step 1 - Criando a master key com uma senha
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '2008M@sterKeyDB6231'



--> Step 2 - Criando o certificado digital
CREATE CERTIFICATE certificado_users
WITH SUBJECT = 'CertificadoUsers'



--> Step 3 - Criando a chave
CREATE SYMMETRIC KEY SK_users
WITH ALGORITHM = Triple_DES ENCRYPTION
BY CERTIFICATE certificado_users



--> Step 4 - Abrindo a chave
OPEN SYMMETRIC KEY SK_users
DECRYPTION BY CERTIFICATE certificado_users



--> Step 5 - Inserindo os dados
INSERT INTO users VALUES
('Ana Maria',
ENCRYPTBYKEY(KEY_GUID('SK_users'),'Pa$$w0rd'))

INSERT INTO users VALUES
('Lineu',
ENCRYPTBYKEY(KEY_GUID('SK_users'),'password'))

INSERT INTO users VALUES
('Julia',
ENCRYPTBYKEY(KEY_GUID('SK_users'),'Juli@007'))



--> Step 6 - Testando
SELECT    nome_user,
        pwd_user
FROM users



--> Step 7 - Testando a descriptografia
SELECT    nome_user,
        CONVERT(VARCHAR, decryptbykey(pwd_user)) AS [Senha]
FROM users



--> Step 8 - Criando uma STORED PROCEDURE para simplificar a visualização dos dados
CREATE PROC usp_users
AS
--Abrindo a chave
OPEN SYMMETRIC KEY SK_users
DECRYPTION BY CERTIFICATE certificado_users
--Buscando os dados
SELECT    nome_user,
        CONVERT(VARCHAR, decryptbykey(pwd_user)) AS [Senha]
FROM users



--Testando
EXEC usp_users




--Aula 10 - Descobrindo Senhas
-- ====================================================================================
-- Autor        : Lineu Alves Lima Filho
-- Data Criação    : 19/11/2009
-- Descrição    : Módulo 03 -> Managing Security
-- ====================================================================================
-- Versão        : 1.0
-- ====================================================================================
-- Versão            :
-- Data Atualização    :
-- ====================================================================================


--###################################################################
--        EXTRA        Descobrindo Senhas
--###################################################################s

USE [master]
GO

IF EXISTS    (SELECT * FROM sys.databases
             WHERE [name] = 'criptografia')
DROP DATABASE criptografia
GO

CREATE DATABASE criptografia
GO

USE criptografia
GO


CREATE TABLE usuario
(
    nome    VARCHAR(10),
    senha    VARBINARY(100)
)
GO


--Inserindo um registro na tabela "usuario" com a senha criptografada
INSERT INTO usuario VALUES ('Lineu', PWDENCRYPT('Pa$$w0rd')),
('Maria', PWDENCRYPT('password')), ('Julia', PWDENCRYPT('Julia007'))


--Visualizando os dados da tabela com a coluna senha criptografada
SELECT * FROM usuario


--Se retornar 1, significa que o valor é o MESMO
--Se retornar 0, significa que o valor é o DIFERENTE
SELECT nome, PWDCOMPARE('Pa$$w0rd', senha) FROM usuario


--uma consulta de usuário pode ser essa:
SELECT PWDCOMPARE('Pa$$w0rd', senha)
FROM usuario
WHERE nome = 'Lineu'