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:
  • 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:
  • 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:



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.

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 :)

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.

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.

May 18, 2005

Unicode in SQL Server

I hate using Unicode fields unnecessarily. If the data is going to be English only, what is the point of using nchar, nvarchar or ntext? It takes more space. It increases the size of index keys; it reduces the number of rows stored in data pages and makes the database unnecessarily bigger. The performance goes down due to this.
One DBA (a couple of them actually) recently argued with me saying, it will help the future enhancement of internationalization. Good point; you can make field Unicode compliant for the user data so that without major change you can handle non-English data.
How about the data used by the application? Can you handle internationalization by just changing the field into nchar? No. you need additional field to connect with the language data. The front end you have should read the data with the appropriate language. That will be a big change. Unless that is done, none will make use of you 'n' (nchar, nvarchar and ntext) fields.

In summary, use Unicode field only if you have non -English data for that field;
the field is an external (user entry) field and you are going to have Unicode data in the near future Don't use Unicode field for internal data if your application is not capable of handling it.

May 12, 2005

SQL Server 2005

I was able to have some hands on experiance on SQL Server 2005 express edition today.
Some of the obvious changes are really good to mention:

Schema is playing a major role in this edition. All these years it was like a simple equation where user=schema. This was giving a lot of problems to users who are not the owers. Some people even questioned the validity of schema and asked "do we really need schema with Yukon? (earlier name for SQL server 2005)"
Micorsoft has answered this question well. All the system tables are located unser "sys" schema. Schema is a way of classifying the objects where some pre-defined schemas are available.
Another change is the enhancement to TSQL. It supports CTE (Common Table Expression) which reduces the effort on query development. However, We need to check on the execution plan to see how it is actually implemented.

Apr 21, 2005

Some useful scripts on SQL Server 2000

I have developed a few SQL Server scripts. Some of them are available via internet.
As some sites re-publish what others publish, there are other sites too give the same scripts.
SQLServerCentral.com,
SearchSQLServer.com

Who Am I

This is not about a Hollywood film. It is about me!

Who am I?
  1. I am a Christian. I believe in the God Christianity teaches. It says about sinful nature of mankind and God became human to redeem mankind from the sinful nature. It also claims that every person can get salvation through having faith in the God who became man, Jesus.
  2. I am working as a database administrator in a software development company. My main roles are maintaining multiple SQL Server database servers and assisting the developers on the database layer of development. I am working specially in Query optimization, replication, data auditing and tuning of databases.
  3. I am for equality and non violence. I am for equality between sexes, ethnic groups and classes. I am totally against the use of violence by any party, especially for political gain.
  4. I am married and having a daughter. I dont have any ideas of having another one (I refer the wife)