jump to navigation

Ownership chain in SQL Server November 26, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
1 comment so far

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server stored procedures and ownership chain.

Security is a big thing in SQL Server and can be implemented in various levels.

Before we move on, we should define some basic terms that are very important when we talk about security in SQL Server.

The principal access data. It should be authenticated by SQL Server which basically means that the principal should “prove” that it is , whom it claims to be.

Principal is a user a group or an application granted permission to access securables.So what is a securable? Securable is an object to which access privileges are granted.

Privilege is an action that a principal is allowed to perform an a securable.

I want to show you an example where we want to deny a user to the actual table-data but grant him access through a view or a stored procedure.UserA is the owner of tableA and creates StoredProcA that gets data from tableA. Then if the UserB is granted permission to execute the StoredProcA even though he does not have direct access to tableA he will be able to get access (indirectly) to the table. This is the concept of ownership chaining.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)


USE master
GO

--Create a sample database

CREATE DATABASE mysampledb;
GO

-- create a login

CREATE LOGIN login1 WITH PASSWORD ='login1', CHECK_POLICY = OFF;

USE mysampledb;
go

--Create a User for that login
CREATE USER thelogin1 FOR LOGIN login1;

--see the user that you are now (dbo)
SELECT user
--create table and populate it with sample data

CREATE TABLE dbo.person

(id INT IDENTITY (1,1),
 firstname varchar(40),
 lastname VARCHAR(50)

);
go

INSERT dbo.person VALUES ('nick','kenteris'),('maria','negri'),('apostolis','kaperis'),('kostas','gekas');

CREATE PROCEDURE dbo.getpersons
AS SELECT id,firstname,lastname FROM dbo.person
go

--give permissions to execute the procedure to the thelogin1 user

GRANT EXECUTE ON dbo.getpersons TO thelogin1

--change the current user as thelogin1

EXECUTE AS USER = 'thelogin1'

--check if we are indeed thelogin1 user

SELECT USER

--fails
SELECT * FROM dbo.person

--works

EXECUTE dbo.getpersons

--get back to the dbo user

REVERT;

--see if the user is indeed dbo

SELECT USER

--we change the stored proc to use dynamic sql

ALTER PROCEDURE dbo.getpersons
AS
EXEC('SELECT id,firstname,lastname FROM dbo.person')
GO

--change the current user as thelogin1

EXECUTE AS USER = 'thelogin1'

--check to see if indeed the user is now thelogin1

SELECT USER

--this will fail

EXECUTE dbo.getpersons

--get back to the dbo user
REVERT;

--check to see if we are dbo user

SELECT user

--this will work

ALTER PROCEDURE dbo.getpersons
WITH EXECUTE AS OWNER
AS
EXEC('SELECT id,firstname,lastname FROM dbo.person')
GO

EXECUTE AS USER = 'thelogin1'

--check to see if we are dbo user

SELECT USER

--this will work

EXECUTE dbo.getpersons

Let  me explain what I am doing in this bit of code

  • I am creating a database in line 6
  • I create a login in line 11
  • I create a user-thelogin1 for that login in line 17
  • I create a new table in lines 23-30
  • Then I populate the table – dbo.person in lines 32
  • Then I create a procedure – dbo.getpersons in lines 34-36
  • Then I grant permissions to the stored procedure to the user thelogin1
  • Then I execute the statements like user – thelogin1
  • If I try to access the table directly then I fail. That makes sense.
  • Then I execute the stored procedure as user – thelogin1 in lines 55
  • Then I revert back to the dbo user
  • Then I make a change in lines 67-70 to the stored procedure. I use in the body of stored procedure dynamic sql.
  • Then I change the user to thelogin1 in lines 74
  • Then I execute the stored procedure in line 82 . This will fail.The dynamic SQL statement causes a break in the ownership chain.
  • Then I revert back to line 85 as the dbo user
  • In the lines 93-97 I change the stored procedure again (WITH EXECUTE AS OWNER)
  • Then I change the user to thelogin1 in lines 99 
  • Finally, I execute the stored procedure again in lines 107 and it works

Hope it helps!!!

Tables with no clustered indexes or no indexes at all in SQL Server databases November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “If there is a way to find using T-SQL tables in a database that have no clustered indexes or no indexes at all”

I have answered that this is possible and I provided with some scripts.

There a lot of tasks that we cannot perform through the GUI of SSMS.So we have to search through the object metadata to get our answers.We can query the catalog views and get all the information we want

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' +t.NAME AS
[tables without clustered index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables without clustered index]

If you execute this bit of code , you will see all the tables in the AdventureWorksLT2012 database that have not go a clustered index.

As you can see I query the sys.tables view.You can use this script to query your own database for tables without a clustered index.

If I want to find all the tables in the AdventureWorksLT2012 database or any other database for tables that have not indexes you can execute the following script.


SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' + t.NAME AS
[tables_without_an_index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables_without_an_index]

Hope it helps!!!

Looking into SQL Server page types November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist.

I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes.

In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes.
If you make the calculations,16 extents make up 1 mbyte.

Extents are divided into 2 categories,

  • Uniform – 8 pages belong to the same object e.g table
  • Mixed  – 8 pages do not belong to the same object

The first 96 bytes of the page are also reserved for the page header.
Page header includes information like

  • Page number
  • Page type
  • Free space per page

The greatest size in bytes I can have in a page is 8060 bytes
So the maximum record length I can have in a table is 8060 bytes

If I have a table that its record length is 100 bytes, that means I can have 80 records per page.
If I have 200 records I need 3 pages. Each record belongs to only one page. You cannot  have half record in one page and another half in another.

Ι was asked to provide an example where we will see the various SQL Server page types and what happens when the row size is larger than 8060 bytes.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

CREATE TABLE largetable
( col1 VARCHAR(2000),
 col2 VARCHAR(8000),
 col3 VARCHAR(max)

 );

INSERT INTO largetable VALUES
(
REPLICATE('SQL Server 2012 will provide Mission Critical
Confidence with greater uptime blazing-fast performance and enhanced
security features for mission critical workloads',12), REPLICATE('SQL Server 2012', 533),
REPLICATE('SQL Server indexes', 1000))



SELECT * FROM largetable
-- Shows types of pages available inside SQL Server

SELECT OBJECT_NAME (object_id) AS tablename, rows,
 type_desc AS page_types_SQL
FROM sys.partitions part
INNER JOIN sys.allocation_units alloc
ON part.partition_id = alloc.container_id
WHERE object_id = object_id('largetable');

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called largetable in the tempdb database. As you can see the record length is much larger than 8060 bytes.
  • Insert some values into the table. I insert thousands of rows using the REPLICATE function.
  • Finally I execute a SELECT JOIN statement from the sys.partitions and sys.allocation_units table.

The results are:

tablename    rows               page_types_SQL
largetable       1                            IN_ROW_DATA
largetable       1                            LOB_DATA
largetable       1                            ROW_OVERFLOW_DATA

As you can see I have one row of data.Each of these types of pages is called an Allocation Unit.

IN_ROW_DATA refers to the situation where we have the whole row size staying within the 8,060-byte limit.

LOB_DATA refers to LOB data type columns, in this case  the NVARCHAR(MAX) column and its values.

ROW_OVERFLOW_DATA refers to the data that could not be stored to single page for a single row and that is why it is using an overflow page.

Hope it helps!!!

Significance of order of columns in an index November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “Does the order of the column in an index matter?”

Let’s give some basic definitions first and make some analogies.

A single column index is straightforward. Think of it as an index in the back of the book.
Let’s say you want to learn about “DBCC FREEPROCCACHE” . You look for that command in the index of the book. The index does not have the information. It has a pointer to the page where the command is described.You turn to that page and read about it. This is a good analogy for a single column,non-clustered index

In SQL Server you can also create an index that contains more than one column.This is known as the composite index. A good analogy for a composite index is the telephone book.
A telephone book lists everyone who has publicly available a telephone number in an area.It is organised not by one column but by two:last name and first name.To look up someone in the telephone book , we first navigate to the last name and then the first name. To find John Smith you first locate Smiths and then John. Composite indexes contain more than 1 column and can reference up to 16 columns from a single table or view.

Back to our question.I have answered this question with the demo below.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL

)
--create a clustered index on Customer_ID and
--a non-clustered composite index on the Last_Name and First_Name

CREATE CLUSTERED INDEX ix_Customer_ID ON Customers(Customer_ID)

CREATE NONCLUSTERED INDEX ix_Customer_Name ON Customers(Last_Name,First_Name)
--when we issue a query to SQL Server that retrieves data from the Customers table, the
--SQL Server query optimiser will consider the various retrieval methods at its disposal
--and select one it deems most appropriate

-- insert test row

INSERT INTO customerS VALUES('Smith','John','js@smith.com')

--use SQL Data Generator to create sample data 10000 rows

SELECT * FROM customers

-- DBCC DROPCLEANBUFFERS removes all buffers from the buffer pool
-- DBCC freeproccache removes all entries from the procedure cache

DBCC dropcleanbuffers
DBCC freeproccache

-- when we run this query and see the execution plan
-- we have an index seek on the nonclustered index
-- to locate the rows selected by the query
-- we also have a key lookup to find the values for the email
-- to retrieve the non-indexed columns
SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
 AND First_Name = 'John'

-- what happens in this case?
--where we have the WHERE statement in different order than the index order?
-- it will use the same execution plan

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'John'
 AND Last_Name = 'Smith'

-- and what happens in this case?
--where we use only last name?
--the same plan is used

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
-- what happens in this case?
-- when we use only first_name
--the index cannot be used
-- an index cannot be used to seek rows of data when the first column
-- of the index is not specified in the WHERE clause

INSERT INTO customers VALUES ('kantzelis','nikos','nikolaosk@hotmail.com')

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'nikos'

 SET STATISTICS IO on

 DBCC dropcleanbuffers
DBCC freeproccache

 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

-- now lets drop the index to see what happens

DROP INDEX ix_Customer_Name ON customers

--and rerun the query
 --we see a huge increase in logical reads without the index
 --we have in this case, in the absence of the index a clustered index scan
 --which means that each row of the table had to be read
 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I create a clustered index on the table ix_Customer_ID on Customer_ID
  • I insert a test row to the table
  • Then I use a third party generator tool to create 10.000 records
  • Then I just use a simple statement to make sure all the rows were inserted
  • Then I remove all buffers from the buffer pool and all entries from the procedure cache
  • Then (I activate the actual execution plan) and execute the Select  query in line 43.
  • We have an index seek on the non clustered index and a key lookup to get the values for the email column.
  • Then I execute the Select statement in lines 54. The order of the columns in the WHERE clause are different than the index order.
  • Still the same execution plan is used
  • Then I execute the Select statement in lines 65.  We have just the last_name column in the  WHERE clause.The same plan is used
  • Then in line 76 I insert a new value in the table
  • Then I execute another Select statement (lines 78) where just the first_name column is in the WHERE clause.In this case the index cannot be used. An index cannot be used to seek rows of data when the first column of the index is not specified in the WHERE clause.
  • Then in line 84 I set statistics IO on so i can investigate the logical reads when having or not the index.
  • In line 89 I run a Select query again and make a note of the logical reads , which is 4 in my case.
  • Then I drop the index and rerun the query (line 103). This time the logical reads are 77. So you can see that the performance of the query without the index has been dramatically decreased.

Hope it helps!!!

SQL Server SET options and index creation November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation.

I will look into the SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and how their values can affect index creation.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL,
Fullname AS first_name + '' + last_name
)

SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS OFF;
--create a clustered index on Customer_ID and
--a non-clustered composite index on the fullname

--index failed
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

-successful index creation
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I have a computed column in Fullname in the table Customers
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to OFF
  • Then I create a non clustered index on the computed column. It failed with this error “CREATE INDEX failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to ON
  • Then I create a non clustered index on the computed column again. This time works just fine.

Hope it helps!!!

Follow

Get every new post delivered to your Inbox.

Join 2,279 other followers