
/*
Model bezpieczeństwa serwera SQL 2008
*/
USE master
CREATE LOGIN Szelor
WITH PASSWORD= 'CoZ@Dzien!' , DEFAULT_DATABASE=AdventureWorks2008, CHECK_EXPIRATION=ON, CHECK_POLICY=ON;

CREATE CREDENTIAL AlterEgo 
WITH IDENTITY = 'KATMAI\SqlCred', 
SECRET = 'BezpIecznEH@sl0jESTDl*^%ie';

ALTER LOGIN Szelor
WITH CREDENTIAL = AlterEgo;

USE AdventureWorks2008
CREATE USER Szelor 
FOR LOGIN Szelor;

USE master
SELECT USER_NAME();
USE AdventureWorks2008
SELECT USER_NAME();

USE AdventureWorks2008
CREATE APPLICATION ROLE testAppR 
WITH DEFAULT_SCHEMA = HumanResources , PASSWORD = N'Hasl0DoZ@szyciaWaplikacji!';

GRANT SELECT ON SCHEMA::HumanResources TO testAppR;

EXECUTE AS USER = 'Szelor';
SELECT TOP 1 *
FROM HumanResources.Department;

DECLARE @cookie varbinary(8000);
EXEC sp_setapprole N'testAppR',N'Hasl0DoZ@szyciaWaplikacji!', @fCreateCookie = true, @cookie = @cookie OUTPUT;
SELECT TOP 1 * 
FROM HumanResources.Department;
EXEC sp_unsetapprole @cookie;

REVERT;

SELECT * 
FROM fn_builtin_permissions(default);

GRANT SELECT, INSERT
ON SCHEMA ::HumanResources
TO Szelor;

DENY SELECT 
ON HumanResources.vJobCandidate
TO Szelor;

EXECUTE AS USER = 'Szelor' 
SELECT TOP 1 *
FROM HumanResources.Shift;

SELECT TOP 1 *
FROM HumanResources.vJobCandidate;

REVERT;
GO

CREATE PROCEDURE Person.GetAddress
AS
SELECT TOP 1 * 
FROM Person.Address;
GO
GRANT EXECUTE ON Person.GetAddress
TO Szelor;

EXECUTE AS LOGIN ='Szelor';
SELECT TOP 1 * 
FROM Person.Address;

EXEC Person.GetAddress;

REVERT;

SELECT *
INTO Tab
FROM Person.Address;
GO
CREATE PROCEDURE Person.TrunTab
AS
TRUNCATE TABLE Tab;
GO
GRANT EXECUTE ON Person.TrunTab
TO Szelor;
GO
EXECUTE AS USER ='Szelor';
EXEC Person.TrunTab;

REVERT;
CREATE LOGIN Admin 
WITH PASSWORD='Raz2Trzy!';
GO
CREATE USER Admin 
FOR LOGIN Admin;
GO
EXEC sp_addrolemember 'db_owner', 'Admin';
GO
ALTER PROCEDURE Person.TrunTab
WITH EXECUTE AS 'Admin' 
AS
TRUNCATE TABLE Tab;
GO
GRANT EXECUTE ON Person.TrunTab
TO Szelor;
GRANT IMPERSONATE ON USER::dbo 
TO Szelor;
GO
EXECUTE AS USER ='Szelor';
EXEC Person.TrunTab;

REVERT;

/*
Kryptografia
*/

BACKUP SERVICE MASTER KEY TO FILE = 'h:\SMK.KEY' 
ENCRYPTION BY PASSWORD = 'T0Hasl,.oMusi%$3BykjcNapraw890$#$deBe=-"zpiecz89nE!';

ALTER SERVICE MASTER KEY REGENERATE;

RESTORE SERVICE MASTER KEY FROM FILE = 'h:\SMK.KEY' 
DECRYPTION BY PASSWORD = 'T0Hasl,.oMusi%$3BykjcNapraw890$#$deBe=-"zpiecz89nE!';

ALTER SERVICE MASTER KEY
FORCE REGENERATE;
-- lub
RESTORE SERVICE MASTER KEY
FROM FILE = 'h:\SMK.KEY' 
DECRYPTION BY PASSWORD = 'T0Hasl,.oMusi%$3BykjcNapraw890$#$deBe=-"zpiecz89nE!'
FORCE;

CREATE DATABASE r07;
GO
USE r07;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'ToPowinnoBycInnE,R0wnieSilneH@sl0!';

SELECT name, algorithm_desc 
FROM sys.symmetric_keys;

BACKUP MASTER KEY TO FILE = 'C:\TEMP\DBK.KEY' 
ENCRYPTION BY PASSWORD = 'K09jRm,YsdeSiopEI00M+_IdfsSI43E';

CREATE CERTIFICATE Cert1
WITH SUBJECT = 'Test odszyfrowania DBK przez SMK';
SELECT name, pvt_key_encryption_type_desc 
FROM sys.certificates;

ALTER MASTER KEY 
DROP ENCRYPTION BY SERVICE MASTER KEY;
CREATE CERTIFICATE Cert2
WITH SUBJECT = 'Test odszyfrowania DBK przez SMK';

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ToPowinnoBycInnE,R0wnieSilneH@sl0!';
CREATE CERTIFICATE Cert2
WITH SUBJECT = 'Test odszyfrowania DBK przez SMK';
CLOSE MASTER KEY;

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ToPowinnoBycInnE,R0wnieSilneH@sl0!';
ALTER MASTER KEY 
ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;

CREATE USER Szelor
FOR LOGIN Szelor;
CREATE CERTIFICATE Cert3
AUTHORIZATION Szelor
WITH SUBJECT = 'Certyfikat chroniący klucz symetryczny';

CREATE ASYMMETRIC KEY AKey1
WITH ALGORITHM = RSA_2048 
ENCRYPTION BY PASSWORD = 'Wt0reK11:43';

CREATE SYMMETRIC KEY SKey1 
AUTHORIZATION Szelor
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Cert3;

BACKUP CERTIFICATE Cert3
TO FILE = 'C:\TEMP\Cert3.cer'
WITH PRIVATE KEY
(FILE = 'C:\TEMP\SKey1.KEY',
ENCRYPTION BY PASSWORD = 'DE5@#$vfr^dEy*(&DftS)');

USE tempdb 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'KluczB@zyTemp';
CREATE CERTIFICATE Cert 
FROM FILE = 'C:\TEMP\Cert3.cer'
WITH PRIVATE KEY 
(FILE = 'C:\TEMP\SKey1.KEY', 
DECRYPTION BY PASSWORD = 'DE5@#$vfr^dEy*(&DftS)')

USE r07
CREATE SYMMETRIC KEY SKey2 
WITH ALGORITHM = AES_128,
KEY_SOURCE = 'KDF9384939834kjf02930sd9f09r04395,c03945039102,c59mncxvSZUIE49CVGKSL38493KALSA;W=1-2',
IDENTITY_VALUE = 'Klucz SKey2' 
ENCRYPTION BY CERTIFICATE Cert3;

SELECT key_guid 
FROM sys.symmetric_keys
WHERE name = 'Skey2';

CREATE TABLE Tab1
(Kol1 varbinary(100));
GO
OPEN SYMMETRIC KEY SKey2 
DECRYPTION BY CERTIFICATE Cert3;
INSERT INTO Tab1
VALUES (EncryptByKey(Key_GUID('SKey2'),'Tajne dane'));
CLOSE ALL SYMMETRIC KEYS;

SELECT *
INTO tempdb..Tab1
FROM Tab1

BACKUP CERTIFICATE Cert3 
TO FILE = 'c:\Temp\Cert3.cer'
WITH PRIVATE KEY
(FILE = 'c:\Temp\KeyCert3.KEY',
ENCRYPTION BY PASSWORD = 'DE5@(*DvfSr^dE');

USE tempdb
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'Structured Query Language!';
CREATE CERTIFICATE Cert3 
FROM FILE = 'c:\Temp\Cert3.cer'
WITH PRIVATE KEY 
(FILE = 'c:\Temp\KeyCert3.KEY', 
DECRYPTION BY PASSWORD = 'DE5@(*DvfSr^dE');

CREATE SYMMETRIC KEY SKey2 
WITH ALGORITHM = AES_128,
KEY_SOURCE = 'KDF9384939834kjf02930sd9f09r04395,c03945039102,c59mncxvSZUIE49CVGKSL38493KALSA;W=1-2',
IDENTITY_VALUE = 'Klucz SKey2' 
ENCRYPTION BY CERTIFICATE Cert3;

OPEN SYMMETRIC KEY SKey2 
DECRYPTION BY CERTIFICATE Cert3;
SELECT CAST(DecryptByKey(Kol1) AS VARCHAR(50))
FROM dbo.Tab1;
CLOSE ALL SYMMETRIC KEYS; 

EXEC sp_configure 'show advanced', 1;
RECONFIGURE;
EXEC sp_configure 'EKM provider enabled', 1;
RECONFIGURE;

--CREATE CRYPTOGRAPHIC PROVIDER nazwa
--FROM FILE = <plik.dll>;

USE r07
SELECT *
INTO dbo.SalesOrderDetailEncrypted
FROM AdventureWorks2008.Sales.SalesOrderDetail;
ALTER TABLE dbo.SalesOrderDetailEncrypted
ADD CarrierTrackingNumberEncrypted VARBINARY(91);

OPEN SYMMETRIC KEY Skey1
DECRYPTION BY CERTIFICATE Cert3;
UPDATE dbo.SalesOrderDetailEncrypted
SET CarrierTrackingNumberEncrypted = EncryptByKey(Key_GUID('SKey1'),CarrierTrackingNumber);
CLOSE SYMMETRIC KEY Skey1;

ALTER TABLE dbo.SalesOrderDetailEncrypted
DROP COLUMN CarrierTrackingNumber;

OPEN SYMMETRIC KEY Skey1
DECRYPTION BY CERTIFICATE Cert3;
SELECT CAST(DecryptByKey(CarrierTrackingNumberEncrypted) AS NVARCHAR(25)), CarrierTrackingNumberEncrypted, LEN(CarrierTrackingNumberEncrypted)
FROM dbo.SalesOrderDetailEncrypted
WHERE SalesOrderID = 50256;
CLOSE ALL SYMMETRIC KEYS;

USE tempdb
CREATE SYMMETRIC KEY Test
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'L(*U%^b*&8I)(*E...';
OPEN SYMMETRIC KEY Test
DECRYPTION BY PASSWORD = 'L(*U%^b*&8I)(*E...';
PRINT CHECKSUM(EncryptByKey(Key_GUID('Test'),'Dawno temu...'));
PRINT CHECKSUM(EncryptByKey(Key_GUID('Test'),'Dawno temu...'));
PRINT CHECKSUM(EncryptByKey(Key_GUID('Test'),'Dawno temu...'));
PRINT CHECKSUM(EncryptByKey(Key_GUID('Test'),'Dawno temu...'));
CLOSE SYMMETRIC KEY Test;

USE r07 
CREATE UNIQUE INDEX I_Encrypted
ON dbo.SalesOrderDetailEncrypted(CarrierTrackingNumberEncrypted)
WHERE CarrierTrackingNumberEncrypted IS NOT NULL;

SET SHOWPLAN_TEXT ON
GO
SELECT CarrierTrackingNumberEncrypted
FROM dbo.SalesOrderDetailEncrypted
WHERE CarrierTrackingNumberEncrypted IS NOT NULL;

SET SHOWPLAN_TEXT OFF
GO
OPEN SYMMETRIC KEY Skey1 
DECRYPTION BY CERTIFICATE Cert3; 
SELECT SalesOrderID, CAST(DecryptByKey(CarrierTrackingNumberEncrypted) AS NVARCHAR(25))AS Szyfrogram
FROM dbo.SalesOrderDetailEncrypted
WHERE CarrierTrackingNumberEncrypted = EncryptByKey(Key_GUID('SKey1'), 'FE9D-4B76-BB');
CLOSE ALL SYMMETRIC KEYS;

SET SHOWPLAN_TEXT ON
GO
OPEN SYMMETRIC KEY Skey1 
DECRYPTION BY CERTIFICATE Cert3; 
SELECT SalesOrderID, CAST(DecryptByKey(CarrierTrackingNumberEncrypted) AS NVARCHAR(25)) 
FROM dbo.SalesOrderDetailEncrypted
WHERE CAST(DecryptByKey(CarrierTrackingNumberEncrypted) AS NVARCHAR(25)) = 'FE9D-4B76-BB';
CLOSE ALL SYMMETRIC KEYS;

SET SHOWPLAN_TEXT OFF
GO

CREATE TABLE tIV
(TblID INT PRIMARY KEY, 
IV VARBINARY(100) NOT NULL);

CREATE CERTIFICATE CertIV 
WITH SUBJECT = 'Ochrona wektorow IV';
CREATE SYMMETRIC KEY SKeyIV 
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertIV;

OPEN SYMMETRIC KEY SKeyIV 
DECRYPTION BY CERTIFICATE CertIV
INSERT INTO tIV
VALUES (1,EncryptByKey(Key_GUID('SKeyIV'),CAST(NEWID() AS VARCHAR(100))))
CLOSE ALL SYMMETRIC KEYS

SELECT *
FROM tIV
GO

CREATE FUNCTION udfMac(@Dane NVARCHAR(4000), @TblID INT )
RETURNS BINARY(24) AS
BEGIN
 DECLARE @Key VARBINARY(100) = NULL
 SELECT @Key = DecryptByKeyAutoCert(cert_id('CertIV'), NULL, IV)
 FROM tIV 
 WHERE TblID = @TblID
 RETURN (HashBytes( N'SHA1', CONVERT(VARBINARY(8000), @Dane) + @Key))
END

ALTER TABLE dbo.SalesOrderDetailEncrypted
ADD Mac BINARY(24);
GO
UPDATE dbo.SalesOrderDetailEncrypted
SET Mac = dbo.udfMac (LineTotal,1);

UPDATE dbo.SalesOrderDetailEncrypted
SET LineTotal = 0.99
WHERE SalesOrderID = 53593;

SELECT SalesOrderID, LineTotal
FROM dbo.SalesOrderDetailEncrypted
WHERE Mac <> dbo.udfMac (LineTotal,1);

CREATE PROC udpTrTbl AS
SELECT name,type 
FROM sys.user_token;
TRUNCATE TABLE dbo.Tab1

GRANT EXECUTE ON udpTrTbl 
TO Szelor;

EXEC AS USER = 'Szelor'
EXEC udpTrTbl;

REVERT;
CREATE CERTIFICATE CertSign 
WITH SUBJECT = 'Podpisuje proc udpTrTbl';

ADD SIGNATURE TO udpTrTbl 
BY CERTIFICATE CertSign;

CREATE USER uCertSign 
FROM CERTIFICATE CertSign;

GRANT ALTER ON dbo.Tab1
TO uCertSign;

EXEC AS USER = 'Szelor'
EXEC udpTrTbl

REVERT;

USE r07
CREATE TABLE Klienci (
 ID INT IDENTITY,
 Nazwisko VARCHAR (100) NOT NULL,
 NumerKarty CHAR (16) NOT NULL);
INSERT INTO dbo.Klienci 
VALUES ('Kowalski', '4444555566667777'),
       ('Nowak', '4444888899990000');

BACKUP DATABASE r07 TO
DISK = 'C:\Temp\r07.bak'
WITH INIT;
GO
RESTORE DATABASE r07Zaszyfrowana 
FROM DISK = N'C:\Temp\r07.bak' WITH
MOVE 'r07' TO N'C:\Temp\r07Zaszyfrowana.mdf',
MOVE 'r07_Log' TO N'C:\Temp\r07Zaszyfrowana.ldf',
REPLACE;

USE master;
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'T@jneHasl0!';
CREATE CERTIFICATE CertSrvSQL
WITH SUBJECT = 'Certyfikat TDE';

USE [r07Zaszyfrowana]
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [CertSrvSQL]
ALTER DATABASE [r07Zaszyfrowana] SET ENCRYPTION ON

SELECT DB_NAME(database_id), encryption_state, key_algorithm, key_length 
FROM sys.dm_database_encryption_keys;

SELECT B1.Nazwisko, B2.NumerKarty 
FROM r07.dbo.Klienci AS B1 INNER JOIN r07Zaszyfrowana.dbo.Klienci AS B2
ON B1.ID = B2.ID;

BACKUP DATABASE r07Zaszyfrowana
TO DISK = 'C:\Temp\r07Zaszyfrowana.bak'
WITH INIT;

SHUTDOWN;

RESTORE DATABASE r07Zaszyfrowana
FROM DISK = 'C:\Temp\ r07Zaszyfrowana.bak'
WITH MOVE 'r07' TO 'C:\Temp\Kopia.mdf',
MOVE 'r07_Log' TO 'C:\Temp\Kopia.ldf',
REPLACE;

USE master;
BACKUP CERTIFICATE CertSrvSQL
TO FILE = 'C:\Temp\CertSrvSQL'
WITH PRIVATE KEY (FILE = 'C:\Temp\CertSrvSQLKey',
 ENCRYPTION BY PASSWORD = 'Siln3h@$l0');

--SERWER B
USE master
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'InnE@leTezS1lneH@sl0';

CREATE CERTIFICATE CertSrvSQL
FROM FILE = 'C:\Temp\CertSrvSQL'
WITH PRIVATE KEY (FILE = 'C:\Temp\CertSrvSQLKey',
	DECRYPTION BY PASSWORD = 'Siln3h@$l0');

RESTORE DATABASE r07Zaszyfrowana
FROM DISK = 'C:\Temp\ r07Zaszyfrowana.bak'
WITH MOVE 'r07' TO 'C:\Temp\Kopia.mdf',
MOVE 'r07_Log' TO 'C:\Temp\Kopia.ldf',
REPLACE;

/*
Monitorowanie i wykrywanie włamań
*/
USE r07
ALTER TABLE dbo.Klienci
ADD Kto VARCHAR(200), Kiedy DATETIME;
GO

CREATE TRIGGER tAudit
ON dbo.Klienci INSTEAD OF UPDATE AS
BEGIN
 IF (@@ROWCOUNT>0)
 UPDATE dbo.Klienci
 SET Nazwisko = I.Nazwisko,
 NumerKarty = I.NumerKarty,
 Kto = SYSTEM_USER + ' ' + USER_NAME(),
 Kiedy = CURRENT_TIMESTAMP
 FROM dbo.Klienci AS K JOIN INSERTED AS I
 ON K.ID = I.ID
END;

UPDATE dbo.Klienci
SET Nazwisko = 'Majewski'
WHERE ID=1;

SELECT *
FROM dbo.Klienci;

CREATE TABLE master.dbo.logDDL
(id INT IDENTITY PRIMARY KEY,
uzytkownik SYSNAME,
czas DATETIME,
instrukcja SYSNAME,
obiekt SYSNAME,
opis XML);
GO
CREATE TRIGGER trLogDDL
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE, DROP_DATABASE,DDL_LOGIN_EVENTS
AS
DECLARE @doc AS XML
SET @doc = EVENTDATA()
INSERT INTO master.dbo.logDDL (uzytkownik, czas,instrukcja, obiekt, opis)
VALUES (
CAST(@doc.query ('data(//LoginName)') AS SYSNAME),
CAST(@doc.query ('data(//PostTime)') AS VARCHAR(23)),
CAST(@doc.query ('data(//EventType)') AS SYSNAME),
CAST(@doc.query ('data(//ObjectName)') AS SYSNAME),
@doc );

CREATE DATABASE r07Test;
DROP DATABASE r07Test;
ALTER LOGIN Admin WITH PASSWORD='dusza';

SELECT uzytkownik, czas, instrukcja, obiekt
FROM master.dbo.logDDL;

USE master
CREATE SERVER AUDIT [AuditR07]
TO FILE 
(	FILEPATH = N'C:\Temp'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO

CREATE SERVER AUDIT SPECIFICATION ServerAuditSpec
FOR SERVER AUDIT AuditR07
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP) 
WITH (STATE = ON);

USE r07
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecR07]
FOR SERVER AUDIT AuditR07
ADD (SELECT, INSERT, UPDATE ON dbo.Klienci BY Szelor)
WITH (STATE = ON);

GRANT SELECT, INSERT ON dbo.Klienci TO Szelor;

USE master
ALTER SERVER AUDIT AuditR07
WITH (STATE = ON);

USE r07;
GO
EXECUTE AS LOGIN = 'Marcin';
SELECT USER_NAME ();
GO
SELECT * FROM dbo.Klienci;
GO
INSERT INTO dbo.Klienci (Nazwisko,NumerKarty)
VALUES ('Nowak', '134-124-642-134');
GO
UPDATE dbo.Klienci 
SET Nazwisko = ' Kowalski'  
WHERE Nazwisko = 'Nowak';

REVERT;

SELECT AuditFile.action_id, AuditFile.succeeded,AuditFile.database_principal_name, Dict.class_type_desc , AuditFile.statement
FROM sys.dm_server_audit_status AS AuditStatus
CROSS APPLY sys.fn_get_audit_file (
 AuditStatus.audit_file_path, default, default) AS AuditFile
INNER JOIN sys.dm_audit_class_type_map AS Dict
ON Dict.class_type = AuditFile.class_type;

USE master 
ALTER SERVER AUDIT AuditR07 WITH (STATE = OFF);
ALTER SERVER AUDIT AuditR07 TO APPLICATION_LOG;
ALTER SERVER AUDIT AuditR07 WITH (STATE = ON);


