SQL Server Security A-Z - ilDBA Portal

SQL Server Security A-Z

04/07/2011 | פורסם על ידי

שלום לכולם,

היום נערך כנס SQL Server Explore בו הצגו נושאים רבים בנושא SQL Server.

במסגרת הכנס היה לי הכבוד להעביר הרצאה אשר עסקה באבטחת בסיס נתונים מסוג SQL Server.

ב-Post זה תכלו לעיין במצגת וכן מצורפים ה-Scripts אשר שימשו להדגמות.

לכל המשתתפים, מקווה שנהנתם, ולמי שלא מקווה שתוכלו ללמוד דבר או שניים מהמצגת המצורפת – Secure SQL Server A-Z.

בהרצאה לקח חלק אלכסיי סלטובסקי ממטריקס DBA אשר הראה לנו כיצד מבצעים הצפנה ב-SQL Server.

צירפתי את ה-Scripts אשר שימשו אותנו להדגמה :

Audit –

-- Start Audit
ALTER SERVER AUDIT Explore_Audit
WITH (STATE = ON)

-- Audit backup / restore , chage server group permissions
CREATE SERVER AUDIT SPECIFICATION Explore_Server_Mgmt_Spec
FOR SERVER AUDIT Explore_Audit
  ADD (BACKUP_RESTORE_GROUP),
  ADD (SERVER_STATE_CHANGE_GROUP)--,  ADD (SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP)
WITH (STATE = ON)

-- Monitor Database Objects

USE AdventureWorks
ALTER DATABASE AUDIT SPECIFICATION Explore_Database_Spec WITH (STATE=OFF)
DROP DATABASE AUDIT SPECIFICATION Explore_Database_Spec
CREATE DATABASE AUDIT SPECIFICATION Explore_Database_Spec
FOR SERVER AUDIT Explore_Audit
  ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
  ADD (SELECT ON HumanResources.Employee BY public),
  ADD (INSERT ON HumanResources.Employee_Back BY dbo),
  ADD (UPDATE ON HumanResources.Employee_Back BY dbo),
  ADD (DELETE ON HumanResources.Employee_Back BY dbo),
  ADD (EXECUTE ON dbo.Demo_Explore BY public)
WITH (STATE=ON)

-- Running Commands Against Employee

EXEC DEMO_EXPLORE
SELECT * INTO HumanResources.Employee_Back from HumanResources.Employee
SELECT * FROM HumanResources.Employee
DELETE FROM HumanResources.Employee_Back 

SET IDENTITY_INSERT HumanResources.Employee_Back ON
GO
INSERT INTO HumanResources.Employee_Back([EmployeeID]
      ,[NationalIDNumber]
      ,[ContactID]
      ,[LoginID]
      ,[ManagerID]
      ,[Title]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate])
SELECT [EmployeeID]
      ,[NationalIDNumber]
      ,[ContactID]
      ,[LoginID]
      ,[ManagerID]
      ,[Title]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
FROM HumanResources.Employee
GO
SET IDENTITY_INSERT HumanResources.Employee_Back OFF
GO

-- Reading Audit from files
SELECT * FROM fn_get_audit_file('C:\DemoAudit\Explore_Audit*', default, default)
--SELECT object_name(object_id),* FROM fn_get_audit_file('C:\DemoAudit\Explore_Audit*', default, default)

-- Query Audit Defenition
SELECT * FROM sys.server_audits
SELECT * FROM sys.server_audit_specifications

select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address

Encryption –

--Creating infrastructure: database, login (low privilege), table

CREATE DATABASE [DataEncryptDemo]
go

USE [DataEncryptDemo]
go

CREATE LOGIN [login_low_priv] WITH PASSWORD = 'Login1 Password!'
CREATE USER [user_low_priv] FOR LOGIN [login_low_priv]
go

CREATE TABLE [dbo].[CreditCards]( CardId INT PRIMARY KEY ,
                                  CardNumber varbinary(256) )
go
 USE [DataEncryptDemo]
    go

-- Create the DB master key.
-- Notice that the password may be subject to password policy verification, depending on your system.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master key password!'
go

-- Create certificate for protection of symmetric key
CREATE CERTIFICATE [cert_SecretTable_SecretData_Key]
  WITH SUBJECT = 'SecretTable_SecretData_Key protection'
go

-- You can also use other encryption algorithms like AES_128 if your system supports it
CREATE SYMMETRIC KEY [SecretTable_SecretData_Key]
  WITH ALGORITHM = TRIPLE_DES
  ENCRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

USE [DataEncryptDemo]
    go

    -- In order to use the synmmetric key, you need to open it first
    OPEN SYMMETRIC KEY [SecretTable_SecretData_Key]
        DECRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
    go

    -- Now insert some secret data into the table
    DECLARE @KeyGuid AS UNIQUEIDENTIFIER
    SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')
    IF( @KeyGuid is not null )
    BEGIN
     INSERT INTO [dbo].[CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N'4388-1234-1234-1234'))
     INSERT INTO [dbo].[CreditCards] VALUES ( 2, encryptbykey( @KeyGuid, N'4549-5678-5678-5678'))
    END
    ELSE
    BEGIN
     PRINT 'Failed to obtain the symmetric key GUID'
    END

    SELECT * FROM [dbo].[CreditCards]

    SELECT CardId,
           convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number'
    FROM   [dbo].[CreditCards]
    go

    -- A good recommendation is to close the key after you have finish to encrypt data
    CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]
    go

    -- Without the key open, the unencrypt function returns NULL
    SELECT CardId,
           convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number'
    FROM   [dbo].[CreditCards]
    go

	USE [DataEncryptDemo]
    go
-- Creating the view for the low privileged user
    CREATE VIEW [dbo].[CreditCardsView]
    AS
     SELECT CardId as CardId,
     convert( nvarchar(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ),
     null, CardNumber )) as CardNumber
    FROM [dbo].[CreditCards]
    go
--Giving privileges to allow access to the key for data decryption
    GRANT SELECT ON [dbo].[CreditCardsView] TO [user_low_priv]
    go
    GRANT CONTROL on certificate::[cert_SecretTable_SecretData_Key]
     TO [user_low_priv]
    go
    GRANT VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
     TO [user_low_priv]
    go
--SELECTing the decrypted data with low privileged user
    USE [DataEncryptDemo]
    go

    EXECUTE AS USER = 'user_low_priv'
    SELECT * FROM [dbo].[CreditCardsView]
    REVERT
    go
-- The low privileged user still has far too strong privileges in the database. Need to reengineer the decryption
--process in order not to give the unprivileged user strong privileges. Using helper function.
USE [DataEncryptDemo]
go

CREATE FUNCTION [dbo].[Cards_decrypted_Helper] ( @SecretData VARBINARY(256))
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData ))
END
go

CREATE VIEW [dbo].[CreditCardsView2]
AS
 SELECT CardID as CardID, [dbo].[Cards_decrypted_Helper](CardNumber) as CardNumber FROM [dbo].[CreditCards]
go

--Cleaning up unncesessary privileges
REVOKE CONTROL on certificate::[cert_SecretTable_SecretData_Key]
 TO [user_low_priv]
go
REVOKE VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
 TO [user_low_priv]
go

GRANT SELECT ON [dbo].[CreditCardsView2] TO [user_low_priv]
USE [DataEncryptDemo]
go

-- access through new view works without unneeded permissions
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView2]
REVERT
go

-- no access through the old view
EXECUTE AS USER = 'user_low_priv'
SELECT * FROM [dbo].[CreditCardsView]
REVERT
go

SQL Injection –

ALTER PROCEDURE SQL_Inject_Demo(@in_vec_hours NVARCHAR(5) = '100')
AS
DECLARE @sql nvarchar(4000)
SELECT @sql = 'SELECT NationalIDNumber,LoginID,Title FROM HumanResources.Employee WHERE VacationHours > ' + @in_vec_hours
print @sql
EXEC sp_executesql @sql
GO

EXEC dbo.SQL_Inject_Demo N'2000000000'
EXEC dbo.SQL_Inject_Demo N'200 or 1=1'
EXEC dbo.SQL_Inject_Demo N'0 UNION SELECT ''1'',CAST(Rate as varchar(20)) , cast(EmployeeID as varchar(10)) FROM HumanResources.EmployeePayHistory'
EXEC dbo.SQL_Inject_Demo N'0 UNION SELECT name,object_name(object_id),''1'' from  sys.objects--'
EXEC dbo.SQL_Inject_Demo N'0; create table test (name varchar(10))--'
EXEC dbo.SQL_Inject_Demo N'0; drop table test --'
EXEC dbo.SQL_Inject_Demo N'0; EXEC sp_configure ''show advanced options'', 1; RECONFIGURE; EXEC sp_configure ''xp_cmdshell'', 1; RECONFIGURE;'
EXEC dbo.SQL_Inject_Demo N'0; CREATE TABLE ShellHack (ShellData NVARCHAR(MAX));INSERT INTO ShellHack EXEC xp_cmdshell ''dir c:\*''--'
select * from ShellHack

-- Fix
ALTER PROCEDURE SQL_Inject_Demo(@in_vec_hours NVARCHAR(10) = '100')
AS
DECLARE @sql nvarchar(4000)
SELECT @sql = 'SELECT NationalIDNumber,LoginID,Title FROM HumanResources.Employee WHERE VacationHours > @hours'
EXEC sp_executesql @sql, N'@hours nvarchar(10)', @in_vec_hours
GO

לא ניתן להגיב.



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: