Posted in Development

SQL Server – Rollback transaction completely in case of error

Hi friends, I recently found an issue while inserting data into table during transactions that transaction will not completely rollback if we use multiple insert statement in transaction.

Let me give you a demo on this how to handle such situation.

Create table Test_tran

(

              ID Int Primary key,

                Name varchar(10)

)

Now let’s try to insert some data into this table in a transaction.

BEGIN TRAN

INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

COMMIT TRAN

As we can see that ID column has primary key defined on it so ID column can contain unique values only. But here we are trying to insert duplicate value in ID column in our third INSERT statement and it should got failed.

Let’s execute the query and see what will happen.

1

See the third statement has thrown the error and we cannot insert duplicate key in column ID.

But what happen with the first two statement will they got inserted or rollback.

We now check the data in the table test_tran.

SELECT * FROM test_tran

2

We can see that even when the transaction got failed records got inserted which is not the correct way. To avoid such situation we need to make the transaction atomic which means that either all the statements in the transaction executed successfully or none of them if any of the statement got failed.

Here now I am defining two methods to achieve the atomicity of transaction.

  1. Using Try/CATCH block
  2. Using XACT_ABORT

 

  1. TRY/CATCH block

 

We will rewrite the query using Try/Catch block

 

First I will delete all the records from the table

TRUNCATE TABLE Test_tran

 

BEGIN TRY

       BEGIN TRAN

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

       COMMIT TRAN

END TRY

BEGIN CATCH

       ROLLBACK TRAN

END CATCH

 

After executing above query we can see that no rows will get inserted into the table as it got rollback when an error occurred and we have achieved atomicity by using try/catch block.

 

  1. SET XACT_ABORT

 

We can also achieve the atomicity by setting XACT_ABORT to ON. By setting XACT_ABORT to ON we can rollback all the statement inside a transaction when an error occurred.

 

So we rewrite the code again in this manner.

SET XACT_ABORT ON

BEGIN TRAN

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Amit’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (2,’Kapil’)

       INSERT INTO Test_Tran ( ID, Name) VALUES (1,’Aditya’)

COMMIT TRAN

It will also rollback the transaction when error got occurred in third statement.

So friends we can use these two method to rollback the transaction completely and achieved atomicity.

Have a happy learning 🙂

 

Advertisements
Posted in Development

SQL Server – Conditions when value of @@error changes

Hi friends, when we do error handling in our SQL code we use different methods like TRY/CATCH, RAISERROR etc. to return the error messages. We also use global variable @@error to print the error value of the last T-SQL statement executed. But many folks doesn’t know that value of @@error gets reset with every execution of T-SQL statement. Today I will show you how @@error value gets changes automatically with every execution.

Let’s executed the below code to see what error value will return in output.

SELECT ‘Print error’

SELECT 1/0

Print @@error

1

In output we can see error value 8134 below the error message. This values comes from the system table sys.sysmessages

SELECT error as Error_Number, Severity, Description, msglangid as Language_Code

FROM sys.sysmessages

WHERE error = 8134  and msglangid = 1033

2.jpg

If we want to return our own custom error message then SQL has provide us facility to do that and we can use RAISERROR command for that.

Now let me add another SQL statement between that and re execute the query to see whether I am getting same value or not.

SELECT ‘Print error’

SELECT 1/0

Print @@error

SELECT ‘error’

PRINT @@error

As most of us still expecting the same value 8134 for the second print statement also.

What will be the error value for the second print statement?

3

Second print statement will return 0.

Why?

As we know that @@error return the error value of the last statement executed and it returns 0 if statement gets executed successfully.

Here SELECT ‘error’ is the last statement for the second Print @error statement and it’s always gets executed successfully so it returns 0.

That’s all for the day folks 🙂

Posted in Development

SQL Server – Change in Identity() Property during transactions

Recently while working on a scenario I found that in case of transaction if you are doing insertion operation on a table and when you Rollback your transaction then identity value that is inserted to the table is not rolled back.

Let me demo you on this scenario using an example. First we will create a table with identity property defined on a column.

CREATE TABLE Test_id

(

       ID INT IDENTITY(1,1),

       Name varchar(10)

)

After creation of a table let’s inserted some records into this table in a transaction.

BEGIN TRAN

Insert into Test_id(Name) Values(‘A’),(‘B’)

We can see that 2 rows are inserted into the table with identity values starting from 1 as defined in the table definition.

tran_1

Without committing the transaction we will rollback the transaction and check that there are no rows present in the table.

tran_2

Now, we will again insert the rows in the table and found that identity values that got inserted during the transaction didn’t rolled back and it will insert values from the next number.

tran_3.jpg

So friends, in case of transaction if we have identity property defined on table and rollback the transaction then identity values will not rolled back. In case of temporary tables also this scenario will behave same as main table.

Have a happy learning 🙂

 

Posted in Development, Performance Tuning

SQL Server – Cleaning Procedure Cache for a particular database

HI friends, most of the time we used DBCC FREEPROCCACHE to clear the procedure cache, clears the specific plan from cache by specifying plan handle or SQL handle.

DBCC FREEPROCCACHE does not require any parameters.

To clear the plan cache we just need to execute below query –

DBCC FREEPROCCACHE

Whenever we execute this command, it will results as message like –

“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

2

If you don’t want any information message to be appear after execution then execute the query in this way –

DBCC FREEPROCCACHE WITH NO_INFOMSGS

But when we required to clean the procedure cache for a particular database then in that case we use another DBCC command – DBCC FLUSHPROCINDB.

This command work is identical to FREEPROCCACHE only difference is that it accepts the parameters and can be executed for a single database only.

Suppose I want to clear the procedure cache for my database ‘Test_kapil’ then I can do it in this way-

SELECT name as DBname, dbid as DatabaseID FROM sys.sysdatabases WHERE name like ‘%Test_Kapil%’

First we will find out the DBID from the system table using above query.

After getting DBIS from that we will pass that  DBID to command.

DBCC FLUSHPROCINDB(6)

1

This is how we can also clean the procedure cache for a particular database.

Happy Learning 🙂

Posted in Development

SQL Server – Row Level Security in SQL Server 2016 – Part 3

Hi friends, continuing from the previous post in this post we will learn how to add the blocking predicate to restrict the users to perform DML operation (INSERT, UPDATE, DELETE) for others users data.

If you want to read the previous posts on Row Level Security you can find those post here:

https://kapilsqlgeek.com/2016/05/03/sql-server-row-level-security-in-sql-server-2016-part-1/

https://kapilsqlgeek.com/2016/05/05/sql-server-row-level-security-in-sql-2016-part-2/

Let’s now add a block predicate and check if it explicitly raise an error or not after adding block predicate.

There are 4 block predicates AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE available and we will test using AFTER INSERT predicate.

Now alter the security policy as per the below query –

–ADD block predicate

ALTER SECURITY POLICY PersonSecurityPolicy

 ADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)

 ON dbo.Person AFTER INSERT

We can find the information about the predicates that we have added to the security policy using system dmv like this:

SELECT * FROM sys.security_predicates

As we have added a block predicate to the security policy so it adds another row for this as previously there was no block predicate added to it, only FILTER predicate was present.

check_securitypredicates

Now, execute the query as user ‘User_CS’ and try to insert the row for another user ‘User_IT’ using following query:

EXECUTE AS USER = ‘User_CS’

INSERT INTO Person (PersonName, Department, Salary, User_Access)

SELECT ‘Sumit’, ‘IT’, 35000, ‘User_IT’

REVERT

This time it will not insert any rows and raise an error.

‘The attempted operation failed because the target object dbo.Person has a block predicate that conflicts with this operation. In case the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by block predicate.’

exec_7

This time it will throw an error and will not insert any rows for user ‘User_IT’. Hence after adding block predicate DML operations are restricted for unauthorized users.

Here are the few limitation and restrictions in Row Level Security that are as:

Limitation and Restrictions in Row Level Security

  1. Predicate function must be created with WITH SCHEMABINDING. If function is created without Schemabinding and try to bind it to a Security Policy you will get an error.
  2. Indexed views cannot be created on a table on which Row Level Security is implemented.
  3. In-Memory tables are not supported for Row Level Security
  4. Full text indexes are not supported.
Posted in Development

SQL Server – Row Level Security in SQL 2016 – Part 2

Hi friends, in my previous post we have learnt how to implement Row Level Security feature in our database. You can find the link of Part1 here:

https://kapilsqlgeek.com/2016/05/03/sql-server-row-level-security-in-sql-server-2016-part-1/

In this post we will discuss more on Row Level Security and see what will happen if we give users DML operation rights other than SELECT operation what we have done till now.

As no block predicate filter has been applied to security policy lets test whether a user is able to perform any DML operation (INSERT, UPDATE, DELETE) for other users.

Execute the below query to grant the DML operation access to all newly created users –

—-Grant DML rights to all users

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_CS

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_EC

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_IT

Let’s execute the below query as user ‘User_IT’ and tried to insert the data for user ‘User_CS’.

/* To check other user have access to perform DML operation for other users */

EXECUTE AS USER = ‘User_IT’

INSERT INTO Person (PersonName, Department, Salary, User_Access)

SELECT ‘Soniya’, ‘CS’, 35000, ‘User_CS’

REVERT

exec_5

Ohhh…No error appears and query gets executed successfully.

Let’s execute the below query to check whether newly insert record is appearing for ‘User_IT’ or not.

EXECUTE AS USER = ‘User_IT’

SELECT * FROM dbo.Person

REVERT

We get only those rows which comes appears previously in output.

exec_2

So where that row gone…

Let’s execute the query for user ‘User_CS’ and see what outcome will appear –

–Rows will appear which is inserted by different user

EXECUTE AS USER = ‘User_CS’

SELECT * FROM dbo.Person

REVERT

exec_6

So friends, in this blog we have learnt that how to perform DML operation to made changes to the data of other user. In next blog we will learn how to restrict the user to perform such operation.

Have a great day!! 🙂

Posted in Development

SQL Server – Row Level security in SQL Server 2016 – Part 1

Hi friends, in SQL 2016 several new security features gets introduced which will help users to protect their data in many ways. Today, I will discuss new security feature Row Level Security (RLS) which implements the security inside the database itself, not at application level.

Suppose we have some critical business data and want to control the data on the basis of characteristics of the user. We want user to see the information of persons which is tied to their respective departments only and cannot see the data of other department. For this, Row Level Security allows you easy control with complete transparency that which data will visible to which users.

From BOL:

Row level security enables customers to control access to rows in a database based on the characteristics of the user executing a query.

To implement the RLS we need to consider three main parts:

  1. Create Predicate Function
  2. Security Predicates
  3. Create Security Policy
  1. Predicate function

A predicate function is an inline tabled valued function which checks whether a user executing a query has the access to data based on logic defined on it while creation.

  1. Security Predicates

It helps in binding the predicate function to the table.

RLS supports two types of security predicates:

  • Filter Predicate
  • Block Predicate

Filter Predicate: It filters the data silently without raising any error for SELECT, UPDATE, DELETE operations as logic defined in predicate function.

Block Predicate: It explicitly raise the error and blocks the user from AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE operation on the data which violated the logic as defined in predicate function.

  1. Security Policy

It creates a security policy for row level security adding security predicates that referenced the predicate function.

Now let’s see the demo on this which we discussed in theory:

I have created a Person table where multiple department information is stored and we want each user should access the information of their departments only.

Create table dbo.Person

(

            PersonId INT IDENTITY(1,1),

            PersonName varchar(100),

            Department varchar(100),

            Salary INT,

            User_Access varchar(50)

)

GO

INSERT INTO Person (PersonName, Department, Salary, User_Access)

SELECT ‘Ankit’, ‘CS’, 40000, ‘User_CS’

UNION ALL

SELECT ‘Sachin’, ‘EC’, 20000, ‘User_EC’

UNION ALL

SELECT ‘Kapil’, ‘CS’, 30000, ‘User_CS’

UNION ALL

SELECT ‘Ishant’, ‘IT’, 50000, ‘User_IT’

UNION ALL

SELECT ‘Aditya’, ‘EC’, 45000, ‘User_EC’

UNION ALL

SELECT ‘Sunny’, ‘IT’, 60000, ‘User_IT’

UNION ALL

SELECT ‘Rohit’, ‘CS’, 55000, ‘User_CS’

GO

Create_Table

Now table is created with some dummy records into it.

To retrieve all the person data execute the below query:

SELECT * FROM Person

Table_Data

As mentioned in User_Access column of Person table, we will create three different user login to access the information for a particular department only.

–For CS department

CREATE USER User_CS WITHOUT LOGIN

–For EC department

CREATE USER User_EC WITHOUT LOGIN

— For IT Department

CREATE USER User_IT WITHOUT LOGIN

Create_user

After the login gets created we will grant the SELECT permission to all new user login by execute below statement-

—Grant select permission to all new login users

GRANT SELECT ON Person TO User_CS

GRANT SELECT ON Person TO User_EC

GRANT SELECT ON Person TO User_IT

Grant_select

Now we will create a predicate function which will work like WHERE predicate of select statement but it is invisible to user who is executing a query.

—-Create function predicate

CREATE FUNCTION dbo.PersonPredicate

( @User_Access AS varchar(50) )

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN  SELECT 1 AS AccessRight

    WHERE @User_Access = USER_NAME()

GO

Create_functionpredicate

This function is simply returning rows if current user who is executing the query matches with the parameter passing to the function and will return only those rows which is accessible to it.

After creating function predicate, security policy needs to be created and above created predicate function dbo.PersonPredicate is bind to it which will work as filter predicate on table Person to which information will return.

—Create Security Policy

CREATE SECURITY POLICY PersonSecurityPolicy

ADD FILTER PREDICATE

dbo.PersonPredicate(User_Access) ON dbo.Person

WITH (STATE = ON)

Create_Securitypolicy

The State should always be ON to enable the policy. In case, when you want to disable the policy you can change the state to OFF.

We can the information about the security policy that we have created using below query:

SELECT Name, object_id, schema_id, type, type_desc,

            Is_enabled, is_schema_bound

FROM sys.security_policies

check_securitypolicy

Now, when I execute the below query to test that with the current login whether I am still able to fetch all the records as I getting previously before creating predicate function and security policy –

SELECT * FROM Person

EXEC_1

So, this query didn’t return any rows as the user with which I am executing the query don’t have access to any row as per the definition of predicate function.

Let’s execute the query for the different users for which we have data in Person table.

First we will execute the query for user User_CS and check what will be the output –

EXECUTE AS USER = ‘User_CS’

SELECT * FROM dbo.Person

REVERT

exec_2

After executing above query we will only get the rows for user User_CS only.

So when we execute the query it calls the predicate function and db engine internally execute the query like this –

SELECT * FROM dbo.Person

WHERE User_Name() = ‘User_CS’

Let’s execute the query for other user also –

EXECUTE AS USER = ‘User_EC’

SELECT * FROM dbo.Person

REVERT

exec_4

EXECUTE AS USER = ‘User_IT’

SELECT * FROM dbo.Person

REVERT

exec_3

After executing the query for different user we get only those rows which has access to it. That’s pretty cool features of SQL Server 2016 in terms of security.

I will continue more on it in my next blog.

Have a happy learning 🙂