November 28, 2013 at 12:38 am
Hi all,
I am writing a simple script to allow developers to create database snapshots for the purposes of code-testing.
This is the procedure I am going to implement:
create procedure sp_snapshot_create
as
declare
@SS_Name varchar(100),
@FileName_Datavarchar(50),
@FilePathvarchar(200),
@sqlvarchar(max),
@datevarchar(10);
set @date= CONVERT(varchar(50), getdate(), 12);
set @SS_Name= 'AdventureWorks2008R2_Snapshot_' + @date;
set @FileName_Data= 'AdventureWorks2008R2_Data';
set @FilePath= 'H:\DBSnapshots\' + @SS_Name + '.ss';
set @sql= 'create database ' +
@SS_Name +
' ON (NAME = ' +
'''' +
@FileName_Data +
'''' +
', FILENAME = ' +
'''' +
@FilePath +
'''' +
')' +
'as snapshot of AdventureWorks2008R2;';
BEGIN
print 'Command:';
print @sql;
exec (@sql);
END;
I would like each developer to be able to enter a parameter value for @SS_Name at runtime so that each snapshot can be uniquely identified and related to a task/user/developer/database.
Does anyone have any idea how I can accomplish that?
Many thanks in advance.
Kev
November 28, 2013 at 5:20 pm
Change the PROCEDURE definition:
create procedure sp_snapshot_create
(
@SS_Name varchar(100)
)
as
declare
@FileName_Datavarchar(50),
@FilePathvarchar(200),
@sqlvarchar(max),
@datevarchar(10);
Note that you should take a look at using sp_executesql instead of EXEC(@SQL) to avoid possible SQL injection issues.
Edit: Oh, and @sql should be NVARCHAR(MAX).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 29, 2013 at 12:05 am
Great, thanks for your input.....I'll do it shortly and let you know how it went.
Two questions though (and please remember I am not a developer!):
1. Why NVARCHAR(MAX) and not VARCHAR(MAX)? My understanding is that NVARCHAR reserves the total amount of space required to hold the variable whereby VARCHAR is more dynamic.
2. How does @execute_sql avoid SQL Injection?
Many thanks!
November 29, 2013 at 12:30 am
kevaburg (11/29/2013)
1. Why NVARCHAR(MAX) and not VARCHAR(MAX)? My understanding is that NVARCHAR reserves the total amount of space required to hold the variable whereby VARCHAR is more dynamic.
They're both variable-length data types. It's CHAR and NCHAR that reserve the entire amount of space. sp_executesql requires nvarchar parameters, not varchar, so if you do change your procedure, you'll need to change the data types
2. How does @execute_sql avoid SQL Injection?
By itself it doesn't. It only helps if you parameterise the dynamic SQL instead of concatenating user input into a string. Since the parts you have as dynamic can't be parameterised, converting to sp_executesql will not benefit you in any way, security or performance or pretty much anything else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2013 at 12:36 am
Wow! Fantastic input!
So, the obvious question.....
How could this be rewritten so it would be more secure? Ideally if I am going to implement it, I want it done properly......
November 29, 2013 at 12:40 am
kevaburg (11/29/2013)
How could this be rewritten so it would be more secure? Ideally if I am going to implement it, I want it done properly......
This can't. To run this someone needs db_creator permissions or higher so you can't limit security to the point they can do no harm, it can't be parameterised so you can't prevent SQL injection. Best you can do is check for odd characters within the string, but if someone's determined they will find a way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2013 at 12:49 am
GilaMonster (11/29/2013)
kevaburg (11/29/2013)
How could this be rewritten so it would be more secure? Ideally if I am going to implement it, I want it done properly......This can't. To run this someone needs db_creator permissions or higher so you can't limit security to the point they can do no harm, it can't be parameterised so you can't prevent SQL injection. Best you can do is check for odd characters within the string, but if someone's determined they will find a way.
OK then this is something I may need to reconsider implementing. Thanks for helping me out.....
November 29, 2013 at 12:58 am
If the developers have enough rights to run that, they have enough rights to just log directly on to the server and do stuff anyway. If you don't trust your developers not to maliciously break a server, why are they still working there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2013 at 1:17 am
GilaMonster (11/29/2013)
If the developers have enough rights to run that, they have enough rights to just log directly on to the server and do stuff anyway. If you don't trust your developers not to maliciously break a server, why are they still working there?
In this case it isn't so much a matter of trust with regards to malicious intent, it is a case of competence. To reduce the chance of them breaking the server they don't have the ability to log on using RDP. To that end all they can do is use the SSMS to work on databases directly relevant to them. Obviously they need db_creator to create the snapshots and that level of permission doesn't make me happy but on the filp-side to that coin I don't want developers running their tests on production servers as they are prone to do.
To that end I am trying to change the policy that allows devs to work in this manner. In order to do that they will have an enormous amount of permission revoked from the production environment and in its place a mechanism whereby they can test production data by creating snapshots and performing SELECTs against it.
The challenge is winning the consent of the management team to allow me to do that.....
and in reponse to your last question, getting rid of people simply for incompetence is not as easy in Germany as it is in others countries...
December 2, 2013 at 9:51 am
One option would be to run the code as a different user with elevated permissions using the EXECUTE AS clause.
But this would require to replace the dynamic SQL with hard coded snapshot statements for each and every database you allow to take a snapshot from wrapped by a bunch of IF statements for each database.
There are not many advantages when repeating (almost) identical statements, but one side effect will be that you'll have the chance (and the duty) to control what database they're allowed to copy. (sure, there are other, more efficient ways to do it...)
Another option would be to use the dynamic SQL to create separate SQL Agent Jobs / Job steps for each database (e.g. as part of the daily maintenance tasks) using a proxy account and start the required job step based on the sproc called by your dev team.
The last alternative I can think of would be a signed stored procedure (with dynamic SQL). But this would require the certificate to exist in each database requested for a snapshot. However, dealing with certificates adds quite a level of complexity...
I'd probably go for the SQL Agent approach since this would require the minimum maintenance.
Edit: ... and yes, I know what you mean regarding the "situation" over here in Germany 😉
December 2, 2013 at 12:13 pm
Hi Lutz,
thanks for the comments. Nice....
The solution I have created (albeit far from perfect) is to allow a member from the Dev Team to have DBCREATOR rights. Only this person will obviously be able to create a snapshot image.
To complement that, I have written a Trigger that Monitors attempts to create a database and refuses that attempt while at the same time writing to a log the details of the person that attemtped it. In order to create the snapshot (and delete it) our DBAs would require a phonecall to determine whether or not it is allowed (we don't want people creating images of databases they shouldn't) and the Trigger would be disabled for the Duration of the snapshot creation. I can also imagine that the DBAs will be the ones responsible for creating the snapshots but I don't find that too much of a problem.
The biggest ting is getting Management backing. The plan is there, it just nees to be implemented.
Regards
Kev
December 2, 2013 at 12:25 pm
I don't recommend to rely on a phone call to create the snapshot.
Usually, working hours of Dev and Prod are not identical. It won't be too long until the Dev team figures when to call and not getting immediate permission.
This will be brought to "higher attention" leading to permissions as they are now (look at my signature line, if in doubt ;-)).
I'd rather go with the sproc and the Agent since this would allow maximum flexibility.
You could even check automagically against the shop hours if the snapshot is requested during business hours and deny it, perform all sorts of looging and notification... The list goes on...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply