שלום לחברי הקהילה,
לקוח מסויים מעלה את הדרישה הבאה :
- ישנם שני 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 (כאמור לעיל )
מטרה שניה:
במידה ויהיה איזשהו משתמש שינסה לבצע ALTER או DROP ו CREATE לפרוצדורה P2, הנ"ל יגרום לכך שלא ניתן יהיה להריץ אותה כלל מ-DB1.
יש לשלוח בתשובה דוגמת קוד שמדגימה איך ניתן לבצע את האמור לעיל.
כדי להשתתף באתגר יש להירשם לאתר ולשלוח את הפתרונות ל-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