december 2012 havi bejegyzések

Védekezzünk az SQL Injection ellen

A közelmúltban volt szerencsém Ethical Hacker-el konzultálni az SQL Injection veszélyeiről. Aki nem ismeri a technikát, annak nagyjából összefoglalom a lényeget (Akit jobban érdekel a téma, az a neten nagyon jó cikkeket talál).

Az rendszerfelépítés: user <=> alkalmazás szerver <=> adatbázis szerver.
Általában egy ún. technikai user-rel kapcsolódnak a felhasználók az adatbázis szerverhez az alkalmazás szervere(ke)n keresztül. A technikai user bizonyos jogokat kap (általában datawriter, datareader), amivel írnak, olvasnak a DB-ből. Alapból ezekkel a jogokkal bármilyen adatot lehet módosítani és listázni is az adatbázisból. A fejlesztők dolga, hogy az alkalmazásba tegyenek egy olyan logikát, ami szabályozza, hogy adott felhasználó csak saját adataihoz férjen hozzá. Ez lekérdezésben kb. így nyilvánul meg: SELECT * FROM users WHERE id = 1, ahol az egyes id-jú felhasználó legyen, mondjuk a hacker. Ha a hacker „odavarázsol” a lekérdezés végére egy ilyet: OR 1 = 1, akkor máris az összes user adatát kilistáztathatja. Általában a hacker nem elégszik meg ennyivel, hanem akarja a többi táblában lévő érzékeny adatot is, pl: bankkártya számokat. Ehhez ki kell listáztatni az adatbázisban lévő táblák listáját, amit MS-SQL-ben a sys vagy INFORMATION_SCHEMA schema-kban lévő nézeteken keresztül lehet. Ennek a listáztatásnak a tiltását mutatom itt be.

A kicsit hosszú bevezető után térjünk a lényegre. DBA-ként annyival lehet segíteni a biztonsági rések betömését, hogy mind a sys schema-t, mind pedig az INFORMATION_SCHEMA-t tiltjuk a technikai user-től. Ez viszonylag fájdalommentesen megoldható egy meglévő alkalmazásnál is. Erről a két schema-ról annyit kell tudni, hogy a public role-hoz vannak rendelve, az adatbázis meta adatait tárolják és bárki tudja listázni, akinek van legalább olvasási joga az adatbázison (Ugyebár egy ilyen technikai user nem csak olvasni, hanem írni is szokta az adatbázist). A tiltás előtt győződjünk meg róla, hogy ezeket a schema-kat a fejlesztők nem használják az alkalmazásban! A két schema tiltása között annyi a különbség, hogy a sys schema-t az adott adatbázisban kell tiltani, míg az INFORMATION_SCHEMA -t csak a masterből lehet. Én az utóbbit mutatom be.

USE [master]
GO

-- Létrehozzuk egy teszt logint.
IF NOT EXISTS (SELECT NULL FROM sys.server_principals WHERE name = N'technikai_user')
CREATE LOGIN [technikai_user] WITH PASSWORD=N'123456', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, DEFAULT_DATABASE=[master]
GO

-- Készítünk egy teszt adatbázist
IF EXISTS (SELECT NULL FROM sys.databases WHERE name = 'information_schema_deny')
DROP DATABASE [information_schema_deny]
GO

CREATE DATABASE [information_schema_deny]
GO

USE [information_schema_deny]
GO

-- Hozzá adjuk a teszt usert az adatbázishoz.
CREATE USER [technikai_user] FOR LOGIN [technikai_user]
GO

-- Megadjuk az írás és olvasás jogot is.
EXEC sp_addrolemember N'db_datawriter', N'technikai_user'
GO
EXEC sp_addrolemember N'db_datareader', N'technikai_user'
GO

-- Készítünk egy teszt táblát
CREATE TABLE tbl1 (id INT NOT NULL PRIMARY KEY, szam INT, szoveg VARCHAR(255))
GO

/***** INFORMATION_SCHEMA *****/

-- Belebújunk az technikai_user "bőrébe" tesztelés képpen
EXECUTE AS USER = 'technikai_user'
GO

-- Látjuk, hogy simán tudunk lekérdezni
SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = 'tbl1'

-- Visszabújunk a sajátunkba
REVERT;
GO

-- A master adatbázisban tudjuk tiltani az INFORMATION_SCHEMA olvasási jogot, ezt néhány lépésben megtesszük
USE [master]
GO

-- Ha még nincs olyan role ami tiltja az INFORMATION_SCHEMA használatát, akkor létrehozunk egyet
IF NOT EXISTS (SELECT NULL FROM sys.sysusers WHERE issqlrole = 1 AND name = 'udb_deny_information_schema_reader')
CREATE ROLE [udb_deny_information_schema_reader] AUTHORIZATION [dbo]
GO

-- Készítünk egy scriptet, ami letiltja az olvasási jogot
DECLARE @sql_command VARCHAR(8000) = ''

SELECT
@sql_command += 'DENY SELECT ON [INFORMATION_SCHEMA].[' + av.name + '] TO [udb_deny_information_schema_reader]'
FROM sys.schemas sch (NOLOCK)
INNER JOIN sys.all_views av (NOLOCK)
ON sch.schema_id = av.schema_id
WHERE sch.name = 'INFORMATION_SCHEMA'

EXECUTE (@sql_command)
GO

-- Hozzá kell adni a felhasználót a master adatbázishoz, mert csak itt lehet tiltani az INFORMATION_SCHEMA olvasási jogát
CREATE USER [technikai_user] FOR LOGIN [technikai_user]
GO

-- Tiltjuk a felhasználótól az olvasási jogot
EXEC sp_addrolemember N'udb_deny_information_schema_reader', N'technikai_user'
GO

-- Tesztelünk
USE [information_schema_deny]
GO

-- Belebújunk az "technikai_user" bőrébe tesztelés képpen
EXECUTE AS USER = 'technikai_user'
GO

-- Itt már tiltva lesz a jog
SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = 'tbl1'

-- Visszabújunk a sajátunkba
REVERT
GO

-- Csinálunk egy eljárást, amivel jogot kap a felhasználó lekérdezni az INFORMATION_SCHEMA.COLUMNS-ból
CREATE PROCEDURE [dbo].[usp_get_infromation_schema_columns]
@table_name VARCHAR(255)
WITH EXECUTE AS OWNER
AS
SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name

GO

-- Adjunk jogot a futtatásra
GRANT EXECUTE ON [dbo].[usp_get_infromation_schema_columns] TO [technikai_user]
GO

-- Teszteljünk ismét

-- Belebújunk az "technikai_user" bőrébe tesztelés képpen
EXECUTE AS USER = 'technikai_user'
GO

-- Ez továbbra sem megy
SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = 'tbl1'

-- Így viszont igen
EXECUTE [dbo].[usp_get_infromation_schema_columns] 'tbl1'

REVERT
GO

/****** SYS schema ******/

--A sys schema-t is ugyanígy kell megcsinálni, annyi különbségel, hogy az adott adatbázisban kell tiltani azt, nem pedig a master-ben.

-- Törlünk magunk után
USE [master]
GO

DROP USER [technikai_user]
GO

IF EXISTS (SELECT NULL FROM sys.sysusers WHERE issqlrole = 1 AND name = 'udb_deny_information_schema_reader')
DROP ROLE [udb_deny_information_schema_reader]
GO

-- Csak hogy biztosan ne legyen galiba 🙂
IF EXISTS (SELECT NULL FROM sys.databases WHERE name = 'information_schema_deny')
ALTER DATABASE [information_schema_deny] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

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

IF EXISTS (SELECT NULL FROM sys.server_principals WHERE name = N'technikai_user')
DROP LOGIN [technikai_user]
GO

Amikor az alkalmazás szerverek “támadnak”

Vannak adatbázisok, amiket a felhasználók alkalmazás szerveren keresztül érnek el, tehát az alkalmazás szerverek kapcsolódnak a db-hez. A DBA általában szokta tudni, hogy melyik alkalmazás szerver mennyi kapcsolatot és mennyi tranzakciót tart nyitva egy időben, nevezzük ezt most szerverenként optimális értékeknek. Ha az optimális érték elindul felfele vagy lefele, akkor ott baj lesz, ez előre megjósolható. Erre ezt az egyszerű query-t szoktam használni:

SELECT hostname, count(1) as count_conn, sum(open_tran) count_tran
FROM sys.sysprocesses (NOLOCK)
GROUP BY hostname
-- rendezés igény szerint, ha kell

Az open_tran nagyon jó mutató a lockok figyelésére is, hiszen ahol lock van, ott elkezdenek felgyűlni a nyitott tranzakciók.

Nagyméretű táblák UPDATE-je

A nagy méret az adatbázisban mindig kényes dolog, hiszen menteni kell, karban kell tartani és mindig sokkal körülményesebb bármilyen művelet elvégzése. Ha még hozzávesszük azt is, hogy “menet közben” kell mindezt megcsinálni, akkor az már kihívás, felhasználók számától függetlenül, sok felhasználóval pedig már tartogat izgalmakat is. Egy alap szintű szkripttel mutatnám be, hogy hogyan is szoktam ezt csinálni, ami nem dönti be az adott szervert.

Először is fel kell mérni, hogy milyen problémák adódhatnak. Ez egy rövid lista lesz.

  1. Kihúzzuk valamelyik erőforrást a felhasználók alól. (CPU, Diszk, Memória)
  2. Hosszú időre lock-ot helyezünk el a táblán vagy annak sorain.

Az erőforrások használatát DMV-ből tudjuk lekérdezni, elég sok van belőlük, a lockok kivédését pedig a lock_timeout hivatott kivédeni (persze ez is lekérdezhető DMV-kből). Minél több mindent ellenőrzünk, annál nyugodtabban aludhatunk, ha a script több napig is fut 🙂
Jöjjön a demó.

Csináljunk magunknak teszt adatokat. Segítségképpen a Teszt adatok létrehozása linken találunk egy scriptet, amivel ezt megtehetjük.

1.) Adjunk hozzá új oszlopot a transactions táblához, ezt fogjuk update-lni.

USE tempdb
GO

ALTER TABLE transactions ADD created_date DATETIME NULL

Nyissuk 3 Query ablakot a Management Studio-ban, és másoljuk be sorban az alábbi scripteket.

Session 1:

USE tempdb
GO

DECLARE @i INT = 0
while @i < 10 BEGIN
    dbcc checkdb
    waitfor delay '00:00:01'
    set @i += 1
END

Session 2:

USE tempdb
GO

-- Ha lockba ütközünk, akkor megállunk.
SET LOCK_TIMEOUT 10;
-- Ha megszakadna a script futása, akkor rollback-eljük a tranzakcót, ha van.
SET XACT_ABORT ON;
-- Nem akarunk darabszám kiírásokat. Ha több napig fut egy script, akkor nem szeretem, ha teleírja a Mananagement Studiót, mert az meg az én gépem erőforrásait viszi
SET NOCOUNT ON;

GO
-- Készítsünk egy táblát, amibe logolni fogunk
IF OBJECT_ID('tbl_log') IS NULL BEGIN
  CREATE TABLE tbl_log (
    id int not null identity(1,1) primary key clustered,
    last_updated_id int not null,
    wait_type varchar(255) null,
    command_time datetime null default getdate()
)
END
GO

-- Ha még nincs sor a log táblánkba, akkor beleteszünk egy kezdő sort.
IF NOT EXISTS (SELECT 1 FROM tbl_log) BEGIN
    INSERT INTO tbl_log(last_updated_id) values (0)
END

DECLARE
    @error INT
   ,@from_id INT
   ,@to_id INT

-- Egy tábla, amibe gyűjtjük, hogy milyen id-kat fogunk UPDATE-lni (Most csak azért használjuk, hogy felolvassuk a page-t a memóriába, összetett clustered index estén már nagyobb haszna lenne a táblának).
DECLARE @tbl TABLE (id INT PRIMARY KEY)

-- Lekérjük a legutolsó update-lt id-t.
SELECT TOP 1 @from_id = last_updated_id FROM tbl_log (NOLOCK) ORDER BY id DESC

WHILE (1=1) BEGIN
-- Ide sok-sok ellenőrzés, minden ami eszedbe jut. Most csak azt tesszük bele, hogy ha karbantartás fut a szerveren (az egyébként is terheli), akkor várunk, majd kicsit később újra próbálkozunk.
  IF EXISTS (
    SELECT TOP 1 command 
    FROM sys.dm_exec_requests (NOLOCK)
    WHERE command LIKE '%DBCC%' 
        OR command LIKE '%BACKUP%' 
        OR command LIKE '%UPDATE STATISTIC%'
  )
  BEGIN
    INSERT INTO tbl_log (last_updated_id, wait_type) VALUES (@from_id, 'MAINTENANCE')
    WAITFOR DELAY '00:00:04.000'
    CONTINUE
  END

  BEGIN TRY

    DELETE FROM @tbl
    -- Felolvassuk a page(ke)-t a memoriába, így gyorsabb lesz a tranzakció.
    -- Mindig csak kevés sort frissítünk. Így a tranzackciós log sem hízik a végtelenségig és jó eséllyel a Page Life-ot sem üríti ki.
    INSERT INTO @tbl (id)
      SELECT TOP (10) id
      FROM transactions
      WHERE id > @from_id
      ORDER BY id
      OPTION (MAXDOP 1) --  Csak 1 processzort veszünk igénybe, nehogy kihúzzuk az erőforrást mások alól

    -- Ha elfogytak a sorok, akkor kilépünk
    IF @@ROWCOUNT = 0 BREAK;

    -- Eddig fogunk UPDATE-lni
    SELECT @to_id = MAX(id) FROM @tbl

    -- UPDATE parancs
    BEGIN TRAN
        UPDATE transactions
        SET created_date = getdate()
        WHERE id BETWEEN @from_id AND @to_id
    COMMIT

    -- Logolunk
    INSERT INTO tbl_log (last_updated_id) VALUES (@to_id)

    -- Beállítjuk a következő id-t.
    SET @from_id = @to_id

  END TRY
  BEGIN CATCH

    SET @error = @@error
    -- Ha van nyitott tranzakció, akkor visszagörgetjük (de nem lesz :))
    IF @@trancount <> 0 ROLLBACK
    --
    IF @error <> 1222 BREAK;
    INSERT INTO tbl_log (wait_type) VALUES ('Lock timeout')

  END CATCH

  -- Hagyjuk, hogy a szerverre kicsit eméssze a történteket
  WAITFOR DELAY '00:00:05'
END

-- Ha valami más errorba futunk, akkor lekérdezzük, hogy mi volt az.
SELECT @error, getdate()

Session 3:

USE tempdb
GO

select top 10 * from tbl_log (nolock) order by id desc
select top 10 * from transactions (nolock)

Futtassuk sorban a Session 1, Session 2 scripteket, majd a Session 3-al monitorozzuk az folyamatot. Mivel a Session 3 csak tizesével update-l, ezért nem fontos kivárni a végét. A cél az volt, hogy demózza a hosszú ideig futó update scriptet.