אתגר החודש – אתגר יוני 2011

שלום לחברי הקהילה,

לקוח מסויים מעלה את הדרישה הבאה :

• ישנם שני Databases שונים על אותו SQL Server Instance. שמם DB1 & DB2. שני בסיסי הנתונים אינם מקושרים עם המאפיין  TRUSTED בשרת.
• ב-SQL Server Instance, מוגדר  Login בשם Sam. עבור Login הזה יש User המוגדר ב-database שנקרא – DB1 ואין עבורו User שמוגדר ב database האחר שנקרא DB2.
• ה-Login Sam אינו מוגדר כ-Sysadmin בשרת.
• קיימת פרוצדורה בשם P2 שנמצאת בבסיס נתונים DB2.

מטרה ראשונה:

לאפשר הרצת הפרוצדורה P2 שנמצאת בבסיס נתונים DB2 מבסיס נתונים DB1 באותו שרת כאשר ה-User Sam ב-DB1 שאמור להריץ את אותה פרוצדורה אינו מוכר בבסיס הנתונים DB2 (כאמור לעיל )

מטרה שניה:

כדי להשתתף באתגר יש להירשם לאתר ולשלוח את הפתרונות ל-ildba@brillix.co.il

שיהיה בהצלחה!

צוות אתר ilDBA.

——————————————-

עידכון 6.7.2011

זוכה אתגר יולי 2011 הוא: אורי מרגלית !
מזל טוב לזוכה, אימייל יישלח אליו בשבוע הקרוב.

הפיתרון של האתגר עוסק בתחום Signed Modules ב-SQL Server.
על מנת לענות על דרישות הלקוח עלינו "לחתום" את הפרוצדורה הרצויה עם Certificate זהה בשני בסיסי הנתונים. על ידי כך, רק משתמשים מורשים יוכלו להריץ את הפרוצדורה, גם אם הם לא נמצאים באותו DB.

במידה ומתבצע שינוי בפרוצדורה (alter proc) החתימה הדיגיטלית מוסרת ,ולכן רצף ההרשאות נשבר. יש לחתום את הפרוצדורה שוב (צריך לדעת במקרה זה את הסיסמה המקורית).

מצורף קוד דוגמא לפיתרון.

לפרטים נוספים על -Signed Modules, ניתן לגשת ללינק:
http://msdn.microsoft.com/en-us/library/ms345102.aspx

מקווים שנהנתם ולמדתם מהאתגר,
נתראה באתגר הבא !
מערכת ilDBA

CREATE DATABASE DB1
CREATE DATABASE DB2

USE DB1;
GO
-- Set up a login for the test user
CREATE LOGIN sam
WITH PASSWORD = 'ASDECd2439587y'
GO 

CREATE USER TestUser
FOR LOGIN sam; 

GO
-- Step 2 - Create a certificate in the db1 database
CREATE CERTIFICATE Access_Cer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Accessing from a remote db',
EXPIRY_DATE = '12/05/2020';
GO 

-- Step 3 - Create a stored procedure that will call a remote sp and sign it
--using the certificate, note that we will create the CalledDB Later
Create Proc CallRemoteSP
as
exec db2.dbo.SP2
go 

grant execute on CallRemoteSP to TestUser
go 

ADD SIGNATURE TO CallRemoteSP
BY CERTIFICATE Access_Cer
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO 

-- step 4 backup the certificate and export it to the server or database
-- which the the second db is
BACKUP CERTIFICATE Access_Cer TO FILE = 'c:\temp\CreKey.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ,
FILE = 'c:\temp\CreKey.pvk' ,
ENCRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh' ); 

GO 

--ok we backed up the certificate to a file in order to restore it on
-- the second database.
USE DB2
go

-- step 5 create the certificate from the backup we created in step 4
CREATE CERTIFICATE RemoteCertificate
FROM FILE = 'c:\temp\CreKey.cer'
WITH PRIVATE KEY (FILE = 'c:\temp\CreKey.pvk',
DECRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh',
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
); 

GO 

-- step 6 create a table in CallDB and insert data
create table RemoteTable (UserID int ,Details nvarchar(100))
go
insert RemoteTable values(123456,'important data')
go 

-- step 7 create a user from the certificate
-- create the stored procedure and grant permissions to the user
CREATE USER RemoteCertificateAccount
FROM CERTIFICATE RemoteCertificate;
GO 

create Proc [dbo].[SP2]
as
SELECT SYSTEM_USER 'system Login'
, USER AS 'Database Login'
, NAME AS 'Context'
, TYPE
, USAGE
FROM sys.user_token 

select * from RemoteTable 

GO 

ADD SIGNATURE TO SP2
BY CERTIFICATE RemoteCertificate
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO 

grant select on RemoteTable to RemoteCertificateAccount
grant execute on SP2 to RemoteCertificateAccount 

--ok,now run the following from  db1 :
use DB1
go

EXECUTE AS LOGIN = 'TestUser';
GO 

exec CallRemoteSP
go

USE master
GO
DROP DATABASE DB1
DROP DATABASE DB2
DROP LOGIN sam

go