I was asked to speak at the Sri Lankan SQL Server User group Meeting on Table Partitioning.
I did it last week (August 16 2007) and as usual I took another 15 minutes from the next speaker too.
The slides and example are available at
http://sqlserveruniverse.com/files/folders/meeting05/entry301.aspx
http://sqlserveruniverse.com/files/folders/meeting05/entry302.aspx
Interestingly, I found a method to change (Cerate/drop) the identity property of a column during my preparation for this presentation. I was told by many members that they were forced to add a column and drop a column to the table just because they wanted to have the identity property added or dropped.
Is it Interesting? Check out at the examples
Sep 1, 2007
Jan 24, 2006
Database Auditing: Method 3 - Service broker (2005 only)
Another method of database auditing is using Service broker. It again sends the details to the queue and service broker takes care of the transaction handling.
This too like replication, works in seperate thread and a possible solution if the application works in a critical manner.
Could be used against different server
However, it is quite limited to SQL Server 2005 only.
If you are using SQL Server 2000 or 7.0, you can consider the option of using message queue (e.g.MSMQ) however, message queue is not a database system. Thus, transaction management is available. In simple terms, if one entry fails, MSMQ will not rollback. Also, it has its own limitations.
This too like replication, works in seperate thread and a possible solution if the application works in a critical manner.
Could be used against different server
However, it is quite limited to SQL Server 2005 only.
If you are using SQL Server 2000 or 7.0, you can consider the option of using message queue (e.g.MSMQ) however, message queue is not a database system. Thus, transaction management is available. In simple terms, if one entry fails, MSMQ will not rollback. Also, it has its own limitations.
Nov 30, 2005
Database Auditing: Method 2 - Replication
Transact replication is another method of auditing tables.
Description:
Modify the stored procedures generated by replication to suit the needs. Optionally you can use triggers discussed in the previous post against the replicated tables as well.
Advantages:
Disadvantages:
Description:
Modify the stored procedures generated by replication to suit the needs. Optionally you can use triggers discussed in the previous post against the replicated tables as well.
Advantages:
- Replication works in a separate thread. Thus, application will not be affected directly.
- Replication reads from Transaction log. It is much faster as it is sequential.
Disadvantages:
- The replicated data should reside in a separate database. This is the first limitation.
- Some editions do not support transact replication. Merge replication may require complex stored procedures/triggers
- Replication is one of the least published mechanism. Troubleshooting may be a nightmare.
Links:
- I should create some links.
Nov 29, 2005
Database Auditing: Method 1 - Triggers
I am going to talk about different methods for database auditing. Auditing could be done at different levels:
1. Capture who did what (e.g. "Jim modified with invoice")
1. Capture the last updated datetime of a record as well. ("Invoice 1003 last modified by Jim at 21 Nov 2005 12:35:42.767")
2. Capture the previous data and the current data with CURRENT_TIMESTAMP.
To do auditing, we need to establish a few priciples.
Any layer can audit the data available only at that layer. For example, real user name (the user who uses the application may be different from the application's user name to connect to the database. The DB won't know the application user unless specified explicitly.
There are different techniques available for auditing.
Today, its about using triggers for database auditing.
Description:
Use either instead of or after triggers to capture the data.
Advantages:
1. Capture who did what (e.g. "Jim modified with invoice")
1. Capture the last updated datetime of a record as well. ("Invoice 1003 last modified by Jim at 21 Nov 2005 12:35:42.767")
2. Capture the previous data and the current data with CURRENT_TIMESTAMP.
To do auditing, we need to establish a few priciples.
Any layer can audit the data available only at that layer. For example, real user name (the user who uses the application may be different from the application's user name to connect to the database. The DB won't know the application user unless specified explicitly.
There are different techniques available for auditing.
Today, its about using triggers for database auditing.
Description:
Use either instead of or after triggers to capture the data.
Advantages:
- Easy to write.
- Useful when the database is already in place.
Disadvantages:
- Performance goes down; database needs more resources; application waits until trigger is completed.
- Blob fields are not audited; update to blob field only will not be audited.
- Complexity increases when additional triggers are placed.
- Triggers are fired automatically; application has no control on that.
Some useful links:
- http://www.sqlservercentral.com/scripts/contributions/521.asp This script creates the triggers to audit table
- http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp An article on this subject
- http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=259&messageid=234334 Comments posted on above article including from me.
Nov 16, 2005
What's New That's Not So New
SQL Server 2005 is finally out with a Lot of "new" things! People are so exited!
Microsoft has released the list of things that are new in SQL Server. One of the Nice feature is change of definition of Schema. Schema is no more refers to a user.
However, in practice most of the companies used the same technique. They created a dummy user and created the objects under that schema. Somewhere in 2000 when we faced same set of tables for two different - interconnected - systems (Accounts Payables and Accounts receivables) we came up with the mechanism of using schemas.
The application connects to the database based on the application user.
However, we had issues of db users connecting to server for gathering information. Some may search on wrong tables and tell us "The data I stored is missing".
However, those are simple issues and were resolved without much delay.
So is redefining schema a new concept. Yes and No. It is yes as at the database level it is redefined. No as it is the way the industry uses.
Microsoft has released the list of things that are new in SQL Server. One of the Nice feature is change of definition of Schema. Schema is no more refers to a user.
However, in practice most of the companies used the same technique. They created a dummy user and created the objects under that schema. Somewhere in 2000 when we faced same set of tables for two different - interconnected - systems (Accounts Payables and Accounts receivables) we came up with the mechanism of using schemas.
The application connects to the database based on the application user.
However, we had issues of db users connecting to server for gathering information. Some may search on wrong tables and tell us "The data I stored is missing".
However, those are simple issues and were resolved without much delay.
So is redefining schema a new concept. Yes and No. It is yes as at the database level it is redefined. No as it is the way the industry uses.
Oct 16, 2005
Security in SQL Server.
At once, security was the least feature I checked on SQL Server. In our office environment, the database is always on the backend, no direct access is allowed to the server. the appliation server is behind the fiewall, and the database server is always accessed through stored procedures. I had a few dynamic sql in stored procedures, however, they too are really secure (or I felt so). Most of the time, the security threat comes from inside. Some are dure to the limitations in SQL Server 2000.
- dbo rights are given to developpers so that they can create objects in DBo schema;
- sa rights are given to developpers so that they can use profiler to to debug a stored procedure.
The first issue comes as a by product of how schema defined in SQL Server 2000; It follows a simple formula of schema = user. To avoid users from creating tables in their user schema (and move to another schema when they leave the company) DBAs encouraged all to use DBO schema. That gives full access to developpers even to drop existing objects.
SQL 2005 handles it very well by seperating users from schema. User may use a default schema. However, schema is simply a logical collection of objects.
Second scenario is even serious. To run profiler, a user should be able to have sa rights. A person with sa right can do anything. including removing all other users (this includes the actual "DBA" as well) BANG! Currently I was avoiding this by running a profiler against the "profile user" to identify whats going on!
SQLS erver has solved this issue by allowing explicit access to run profiler.
Let me explore and share more security features later.
Oct 1, 2005
How DBCC CHECKIDENT behaves?
DBCC CHECKIDENT is a handy command to reset the value of the identity fields. However, they behave slightly differently at different situations
What will be the outcome of the following command:
DBCC CHECKIDENT ('Table_a', reseed, 100) . Is it hundred?
Well, it depends. it depends on whether the table is brand new (vergin?) or got some data already. If the table ever had data, even if you truncate table, the identity will start from next value.
That is, if the table even had data the next identity value will be 101 and not 100.
Try yourself. If anyone gets a different value, check your code :)
What will be the outcome of the following command:
DBCC CHECKIDENT ('Table_a', reseed, 100) . Is it hundred?
Well, it depends. it depends on whether the table is brand new (vergin?) or got some data already. If the table ever had data, even if you truncate table, the identity will start from next value.
That is, if the table even had data the next identity value will be 101 and not 100.
Try yourself. If anyone gets a different value, check your code :)
Aug 28, 2005
Preventing SQL Injection
May - June Issue of SQL Server Standard carried Dinesh Asanka’s great article on SQL Injection. I would like to share some more ways to prevent SQL injection.
SQL Injection is based on injecting code into a user input and makes it interpreted differently by SQL Server. For Example, an html page rendered for user login may have two text fields (one for user name and the other one for password). Now the hacker may try to inject some code into these textboxes. What is the prevention?
This is the principle: Make sure the user input is always treated as user input and not as part of your code. This has two implementations:
1. Always use stored procedures to execute a query. User input will be treated as literal storing if we use parameters. For example ;
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SELECT * FROM users
WHERE username = @UserName AND
Password = @Password
GO
Now whatever the user sends through user input has only one meaning; they are literal string. They are NOT part of the SQL code. This prevents the hacker from changing the query.
2. If you are using dynamic SQL inside stored procedures (and execute them using EXECUTE or sp_ExecuteSQL methods) where one or more user input is in string format (char, varchar, nchar and nvarchar) replace all single quotes of the user input with two single quotes.
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SET @UserName = REPLACE(@UserName,'''','''''')
SET @Password = REPLACE(@Password,'''','''''')
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO
I understand this part of code may be a bit difficult to read. Let me explain what is happening here and why should we use this method.
First let me assume that you have used the parameters as it is within your stored procedure. Your procedure will look like this:
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO
Now, a user sends some malicious code through input box on username
His value is ' or 1=1 --
Password is blank
Remember his first character for is single quote.
The application takes the parameters and replaces each quote with two quotes.
The application code may look like this: (I have used C# for this example)
public Boolean verifyUser(string user, string pass)
{
// Create command
SqlCommand command = new SqlCommand("find_user");
command.CommandType = CommandType.StoredProcedure;
// Attach parameters
SqlParameter UserName = new SqlParameter("@UserName", SqlDbType.string);
username.Value = user;
command.Parameters.Add(username);
SqlParameter password = new SqlParameter("@Password", SqlDbType.string);
password.Value = pass;
command.Parameters.Add(password);
// Execute command
return this.ExecuteReader(command);
}
Your parameters will take the values as they are, and passed to your dynamic SQL
Your @SQL variable will look like this before execution
SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''
Now the user has injected his SQL code into your code.
Some of you would have implemented the code like this:
public Boolean verifyUser(string user, string pass)
{
// Replace single quotes with two single quotes in parameter(s)
user = user.Replace("'", "''");
pass = pass.Replace("'", "''");
// Create command with parameters
SqlCommand command = new SqlCommand("find_user '"+ user + "', '"+ pass + "'");
command.CommandType = CommandType.Text;
// Execute command
return this.ExecuteReader(command);
}
In the second method even though the parameter replaces each single quote with two single quotes, that is necessary for internal usage only
When it comes to SQL Server, it will recongnize it as one quote only
Your parameters will take the values as they are and passed to your dynamic SQL
Your @SQL variable will look like this before execution
SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''
Now the user has injected his SQL code into your code.
As you all know, your application won’t know (and shouldn’t know if we follow the best practices) how you have implemented the stored procedure Find_User. It will simply pass the parameter as it is. It is the stored procedure which uses dynamic SQL, should do the validation.
I know it is an extra piece of work. But it is well worth when you compare the price you may pay otherwise.
SQL Injection is based on injecting code into a user input and makes it interpreted differently by SQL Server. For Example, an html page rendered for user login may have two text fields (one for user name and the other one for password). Now the hacker may try to inject some code into these textboxes. What is the prevention?
This is the principle: Make sure the user input is always treated as user input and not as part of your code. This has two implementations:
1. Always use stored procedures to execute a query. User input will be treated as literal storing if we use parameters. For example ;
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SELECT * FROM users
WHERE username = @UserName AND
Password = @Password
GO
Now whatever the user sends through user input has only one meaning; they are literal string. They are NOT part of the SQL code. This prevents the hacker from changing the query.
2. If you are using dynamic SQL inside stored procedures (and execute them using EXECUTE or sp_ExecuteSQL methods) where one or more user input is in string format (char, varchar, nchar and nvarchar) replace all single quotes of the user input with two single quotes.
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
SET @UserName = REPLACE(@UserName,'''','''''')
SET @Password = REPLACE(@Password,'''','''''')
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO
I understand this part of code may be a bit difficult to read. Let me explain what is happening here and why should we use this method.
First let me assume that you have used the parameters as it is within your stored procedure. Your procedure will look like this:
CREATE PROCEDURE find_User
@UserName nvarchar(100), @Password nvarchar(100)
AS
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM users
WHERE username = ''' + @UserName + ''' AND
Password = ''' + @Password + ''''
EXEC SP_EXECUTESQL @SQL
GO
Now, a user sends some malicious code through input box on username
His value is ' or 1=1 --
Password is blank
Remember his first character for is single quote.
The application takes the parameters and replaces each quote with two quotes.
The application code may look like this: (I have used C# for this example)
public Boolean verifyUser(string user, string pass)
{
// Create command
SqlCommand command = new SqlCommand("find_user");
command.CommandType = CommandType.StoredProcedure;
// Attach parameters
SqlParameter UserName = new SqlParameter("@UserName", SqlDbType.string);
username.Value = user;
command.Parameters.Add(username);
SqlParameter password = new SqlParameter("@Password", SqlDbType.string);
password.Value = pass;
command.Parameters.Add(password);
// Execute command
return this.ExecuteReader(command);
}
Your parameters will take the values as they are, and passed to your dynamic SQL
Your @SQL variable will look like this before execution
SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''
Now the user has injected his SQL code into your code.
Some of you would have implemented the code like this:
public Boolean verifyUser(string user, string pass)
{
// Replace single quotes with two single quotes in parameter(s)
user = user.Replace("'", "''");
pass = pass.Replace("'", "''");
// Create command with parameters
SqlCommand command = new SqlCommand("find_user '"+ user + "', '"+ pass + "'");
command.CommandType = CommandType.Text;
// Execute command
return this.ExecuteReader(command);
}
In the second method even though the parameter replaces each single quote with two single quotes, that is necessary for internal usage only
When it comes to SQL Server, it will recongnize it as one quote only
Your parameters will take the values as they are and passed to your dynamic SQL
Your @SQL variable will look like this before execution
SELECT * FROM users
WHERE username = '' or 1=1 --' AND
Password = ''
Now the user has injected his SQL code into your code.
As you all know, your application won’t know (and shouldn’t know if we follow the best practices) how you have implemented the stored procedure Find_User. It will simply pass the parameter as it is. It is the stored procedure which uses dynamic SQL, should do the validation.
I know it is an extra piece of work. But it is well worth when you compare the price you may pay otherwise.
Jul 20, 2005
DDL scripts and Transaction Control
Maintaining blog is a serious task. After around two months, I am again writing :-)
Today I got a chance to draft a template for DDL scripts.
DDL scripts are often useful to take the DB structure offline. It is used in installations, source control and documentation
As you all know some of the DDL statements like CREATE TABLE demands them to be the first statement of the batch. Because of this reason, DBAs often include "GO" statement after each DDL script.
What about transaction management is those scripts? Even though BEGIN TRAN... COMMIT TRAN pair will work fine with the GO statement, variable declarations, GOTO statements cannot be separated by GO statement. That means you can't declare a variable in the top of the script and use them in the middle (if you have a GO statement in between!). Worried.... There is more to this
When you have multiple statements and you want to rollback all if error occurs in the middle. You can't use GOTO, variable declaration in the middle of the script.
What Red Gate does is a cool thing: Create a temporary table and send the errors into it.
Nothing much, some simple code, and it works nicely.
Interesting...? Mail me if you like to have a look on that piece of code.
Today I got a chance to draft a template for DDL scripts.
DDL scripts are often useful to take the DB structure offline. It is used in installations, source control and documentation
As you all know some of the DDL statements like CREATE TABLE demands them to be the first statement of the batch. Because of this reason, DBAs often include "GO" statement after each DDL script.
What about transaction management is those scripts? Even though BEGIN TRAN... COMMIT TRAN pair will work fine with the GO statement, variable declarations, GOTO statements cannot be separated by GO statement. That means you can't declare a variable in the top of the script and use them in the middle (if you have a GO statement in between!). Worried.... There is more to this
When you have multiple statements and you want to rollback all if error occurs in the middle. You can't use GOTO, variable declaration in the middle of the script.
What Red Gate does is a cool thing: Create a temporary table and send the errors into it.
Nothing much, some simple code, and it works nicely.
Interesting...? Mail me if you like to have a look on that piece of code.
May 23, 2005
My recent article.
Recently one of my articles got published in a site dedicated to SQL Server performance.
Please visit http://www.sql-server-performance.com/pk_or_clause.asp
Please visit http://www.sql-server-performance.com/pk_or_clause.asp
Subscribe to:
Posts (Atom)
