casinos

Posts Tagged ‘Security’

Basic Column Encryption of data with SQL Server August 6th, 2014

Vinod Kumar

The more I talk with customers on basic architecture, more are the questions about how to implement. These scenarios when talked look great on whiteboard and when they implement and come back (say after 6 months) is completely different. These are some challenges when working with customer developer teams who has just started their career in writing DB level code.

One such scenario I talk to customers who have requirements of security is around column level encryption. It is one of the most simplest implementation and yet difficult to visualize. The scenario is simple where-in most of the HRMS (Hospital Management Systems) come with a simple requirement that data of one person must be masked to other.

So in this post, I thought it will be worth take a step-by-step tutorials to what I am talking. These capabilities are with SQL Server since the 2005 version and can be used by anyone. I am not talking about the infra best practices or the deployment strategy yet, that will be for a future post.

Creating Users for Demo

– Creating the Logins for demo
CREATE LOGIN doctor1 WITH password = ‘MyComplexPass@w0rd’
go
CREATE LOGIN doctor2 WITH password = ‘MyComplexPass@w0rd’
go
CREATE DATABASE hospitaldb
go
USE hospitaldb
go
CREATE USER doctor1
go
CREATE USER doctor2
go

For our example we have two doctors, we want to build a mechanism where Doctor1 patients details must not be visible to Doctor2. Let us create our simple table to keep values.

– Create tables
CREATE TABLE patientdata
(
id         INT,
name       NVARCHAR(30),
doctorname VARCHAR(25),
uid        VARBINARY(1000),
symptom    VARBINARY(4000)
)
go
– Grant access to the table to both doctors
GRANT SELECT, INSERT ON patientdata TO doctor1;
GRANT SELECT, INSERT ON patientdata TO doctor2;

Basic Encryption steps

Next step is to create our keys. To start with, we need to create our Master Key first. Then we will create the Certificates we will use. In this example, I am using a Symmetric key which will be encrypted by the certificates as part of logic.

– Create the Master Key
CREATE master KEY encryption BY password = ‘HospitalDBpwd@123′

CREATE CERTIFICATE doctor1cert AUTHORIZATION doctor1 WITH subject =
‘Doctor1cert’, start_date = ’07/07/2014′ 
GO
CREATE CERTIFICATE doctor2cert AUTHORIZATION doctor2 WITH subject =
‘Doctor2cert’, start_date = ’07/07/2014′ 
GO

CREATE symmetric KEY doctor1key AUTHORIZATION doctor1 WITH algorithm =
triple_des encryption BY certificate doctor1cert 
GO
CREATE symmetric KEY doctor2key AUTHORIZATION doctor2 WITH algorithm =
triple_des encryption BY certificate doctor2cert 
GO 

Let us next look at the Keys we just created using the DMVs.

SELECT *
FROM   sys.symmetric_keys 

A typical output looks like:

Adding Data into table

Next is to simulate as two different users and enter some data into our tables. Let us first impersonate as Doctor1 and enter values, next we will do it for Doctor2.

EXECUTE AS LOGIN = ‘Doctor1′

OPEN SYMMETRIC KEY doctor1key DESCRIPTION BY CERTIFICATE doctor1cert 

– View the list of open keys in the session
SELECT *
FROM   sys.openkeys 

Insert into our table.

INSERT INTO patientdata
VALUES      (1,
‘Jack’,
‘Doctor1′,
Encryptbykey(Key_guid(‘Doctor1Key’), ’1111111111′),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Cut’))

INSERT INTO patientdata
VALUES      (2,
‘Jill’,
‘Doctor1′,
Encryptbykey(Key_guid(‘Doctor1Key’), ’2222222222′),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Bruise’))

INSERT INTO patientdata
VALUES      (3,
‘Jim’,
‘Doctor1′,
Encryptbykey(Key_guid(‘Doctor1Key’), ’3333333333′),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Head ache’))

In this example the Doc1 has 3 records for him. Next is to revert back to Doctor2 and do the same set of operations.

– Close all opened keys
CLOSE ALL symmetric keys

REVERT 

Impersonate as Doctor2 and do the same steps.

EXECUTE AS login = ‘Doctor2′

OPEN symmetric KEY doctor2key decryption BY certificate doctor2cert

–view the list of open keys in the session
SELECT *
FROM   sys.openkeys

INSERT INTO patientdata
VALUES      (4,
‘Rick’,
‘Doctor2′,
Encryptbykey(Key_guid(‘Doctor2Key’), ’4444444444′),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cough’))

INSERT INTO patientdata
VALUES      (5,
‘Joe’,
‘Doctor2′,
Encryptbykey(Key_guid(‘Doctor2Key’), ’5555555555′),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Asthma’))

INSERT INTO patientdata
VALUES      (6,
‘Pro’,
‘Doctor2′,
Encryptbykey(Key_guid(‘Doctor2Key’), ’6666666666′),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cold’))

CLOSE ALL symmetric keys

– View the list of open keys in the session
SELECT *
FROM   sys.openkeys

REVERT

Check on the data

let us do a simple select on the table to check how the values are stored.

– Select data and see values encrypted
SELECT *
FROM   patientdata 

As you can see the values are not visible as-is but has some garbage.

Impersonate as Doctor and show Values

The next step is to show that Doctor1 can see his data and Doctor2 can see his data alone. The steps are simple:

EXECUTE AS LOGIN = ‘Doctor1′

OPEN SYMMETRIC KEY doctor1key decryption DESCRIPTION BY CERTIFICATE doctor1cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output would be like:

Now let us impersonate as Doctor2 and check for values.

EXECUTE AS LOGIN = ‘Doctor2′

OPEN SYMMETRIC KEY doctor2key decryption DESCRIPTION BY CERTIFICATE doctor2cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output for this stage would be:

Conclusion

As you can see this is a very simple implementation of column level encryption inside SQL Server and can be quite effective to mask data from each others in a multi-tenant environment. There are a number of reasons one can go for this solution. I thought this will be worth a shout even though the implementation has been in industry for close to a decade now.

Continue reading...


 

SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Continue reading...


 

Troubleshooting with Policy Based Management (PBM) May 26th, 2014

Vinod Kumar

SQL Server 2008 introduced a feature called Policy Based Management that allows a database administrator to manage one or more instances or SQL Server 2008 through policies. Depending on the policy and how it is defined, Policy Based Management can either audit compliance of the policy or enforce the compliance of the policy.  Policy Based Management allows database administrators to create policies and determine how they should be applied to either audit or enforce the policy.  This gives database administrators a powerful a new feature to administer one or more instances of SQL Server 2008 and above.

I have recommended using PBM in some form to most of my customers atleast to keep tab on their environment and make sure the standards / best practices have been followed. Moreover it also gives a great way for Administrators to keep an eye on their environment. Recently an administrator asked be during a session, “Vinod, is there a way to keep track on all the work I am doing with PBM?”. I got down to search my script arsenal from my sessions on PBM immediately. I got a great set of diagnostics script which I felt was worth sharing over blog – infact I was wondering how I forgot to share it with you folks :).

/* What policies exist on the server? */

SELECT p.name as ‘policy_name’, p.policy_id, p.is_enabled, p.execution_mode,

c.name as ‘condition_name’, c.facet, p.date_created, p.created_by,

p.modified_by, p.date_modified

FROM msdb.dbo.syspolicy_policies p

INNER JOIN msdb.dbo.syspolicy_conditions c

ON p.condition_id = c.condition_id

ORDER BY p.name

 

/* What conditions exist on the server? */

SELECT c.name as ‘condition_name’, c.condition_id, c.facet, c.description,

c.expression, c.date_created, c.created_by, c.modified_by

FROM msdb.dbo.syspolicy_conditions c

ORDER BY c.name

 

/* What is the health of the policies ON the server? */

SELECT sh.health_state_id, p.name as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

ORDER BY sh.health_state_id

 

SELECT sh.health_state_id, p.name as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression, c.name as

‘condition_name’, c.expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

INNER JOIN msdb.dbo.syspolicy_conditions c

ON c.condition_id = p.condition_id

ORDER BY sh.health_state_id

 

/* What is the count of policy health results? */

SELECT p.name, sh.policy_id, sh.result, COUNT(*) as ‘count’

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

GROUP BY  p.name, sh.policy_id, sh.result

ORDER BY count(*) DESC

 

/* What categories and subscriptions exist ON the server? */

SELECT * FROM msdb.dbo.syspolicy_policy_categories

SELECT * FROM msdb.dbo.syspolicy_policy_category_subscriptions

 

/* What is the execution history of policy violations */

SELECT h.history_id, pp.name as ‘policy_name’, pp.policy_id,

pp.execution_mode, h.result, h.start_date, h.end_date, datediff(ss,

h.start_date, h.end_date) as ‘exec_time_ss’, d.target_query_expression,

cast(d.result_detail as XML) as ‘result_detail’, d.exception,

d.exception_message

FROM msdb.dbo.syspolicy_policies AS pp

INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS h

ON h.policy_id=pp.policy_id

INNER JOIN msdb.dbo.syspolicy_policy_execution_history_details AS d

ON d.history_id=h.history_id

ORDER BY h.history_id

 

/* What is the count and execution times of execution history policy

violations */

SELECT p.name as ‘policy_name’, p.policy_id, COUNT(*) as ‘count’,

AVG(datediff(ss, h.start_date, h.end_date)) as ‘avg_exec_time_ss’,

MAX(datediff(ss, h.start_date, h.end_date)) as ‘max_exec_time_ss’

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

GROUP BY  p.name, p.policy_id

ORDER BY count(*) DESC , avg_exec_time_ss DESC, max_exec_time_ss DESC

 

/* What exceptions have occurred? */

SELECT h.history_id, p.name as ‘policy_name’, p.policy_id, h.start_date,

h.end_date, h.result, h.exception, h.exception_message

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

WHERE datalength(h.exception) > 1

ORDER BY h.start_date DESC, h.end_date DESC

So feel free to use them to troubleshoot and know about your PBM environment. Will share other scripts as I find them from my arsenal of scripts.

Continue reading...


 

SQL Security– Demystifying Principals, Securables and Permissions November 5th, 2013

Vinod Kumar

I have written other blog posts around SQL Server: Security Practices and Checklist before. But many of my customers come back to basics and ask questions around what Principals, securables and permissions are. This blog post is inspired to those folks who want to understand from the basic. In future posts, I will dwell into more details into each of these security topics.

What are Principals?

A principal is any authenticated identity that can be given permission to access an object in the database system. SQL Server documentation distinguishes between indivisible principals, which are single identities (such as logins), and collection principals, which are collections of identities (such as fixed server roles).

Principals exist at three levels: Windows, SQL Server, and database. The types of principals possible at each of these levels are shown in the following table.

Level

Principals

Windows

Windows local user account

Windows domain user account

Windows group

SQL Server

SQL Server login

SQL Server role

Database

Database user

Database role

Database group

Application role

What are Securables?

Securables are the resources to which the SQL Server authorization system controls access. Securables are arranged in nested hierarchies called scopes, which can also be secured. The three securable scopes are server, database and schema. Securables at the Windows level include files and registry keys.

The Server Scope

Securables contained in the server scope include:

  1. Logins
  2. HTTP endpoints
  3. Certificates
  4. Event notifications
  5. Databases
The Database Scope

Securables contained in the database scope include:

  1. Users
  2. Roles
  3. Application roles
  4. Assemblies
  5. Message types
  6. Service contracts
  7. Services
  8. Full-text catalogs
  9. DDL events
  10. Schemas
The Schema Scope

Securables contained in the schema scope include:

  1. Tables
  2. Views
  3. Functions
  4. Procedures
  5. Queues
  6. Types
  7. Rules
  8. Defaults
  9. Synonyms
  10. Aggregates

What are Permissions?

Permissions are the rules that govern the level of access that principals have to securables. Permissions in a SQL Server system can be granted, revoked, or denied. Each of the SQL Server securables has associated permissions that can be granted to each principal. SQL Server 2005 introduces the ability to grant permissions at the server scope. In previous releases of SQL Server, server-level permissions were managed using fixed server roles.

The specific permissions associated with individual securables vary depending on the kinds of actions that a securable supports. The following table lists a few example permissions at various scopes. For a full list of permissions, see SQL Server Books Online. Below is a sample set:

Securable

Permission

Description

Server

CONNECT_SQL

Connect to the server.

Server

CREATE LOGIN

Create a login.

Server

ALTER ANY LOGIN

Alter any login in the server scope.

Server

CONTROL SERVER

Full system administrative control.

Login

ALTER

Alter the login.

Login

IMPERSONATE

Impersonate the login.

Database

CREATE TABLE

Create table in the database.

Database

ALTER ANY USER

Alter any user in the database.

Database

CONTROL

Full control of the database.

User

ALTER

Alter the specified user.

Schema

SELECT

Select rows from any object in the schema.

Schema

ALTER

Alter any object in the schema.

Schema

TAKE OWNERSHIP

Take ownership of the schema.

Table

SELECT

Select rows from the table.

Table

ALTER

Alter the table.

Table

CONTROL

Full control of the table.

From SQL Server 2005 we support inherited permissions for securables contained in a given scope. For example, a principal granted CONTROL permission on a database object will automatically inherit CONTROL permission on all securables contained in that database and all securables contained in the schemas within that database

As I sign off, want to understand how many of you take security seriously in your SQL Server deployments? How many out there still use the SA accounts in your environments and application connections? Please understand, Security is not an after thought but something part of your fundamental application deployments.

Continue reading...


 

SQL Server 2012: ColumnStore Part II June 5th, 2013

Vinod Kumar

I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.

Datatype restrictions

In SQL Server 2012, columnstore index creation is supported only for the following data types:

  1. char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
  2. decimal (and numeric)
  3. int, bigint, smallint, and tinyint
  4. float, real
  5. bit
  6. money and smallmoney

New DDL

There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.

CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)

Index hint

We can force an index hint to use the columnstore index in a query if required:

… FROM mytable WITH (INDEX (mycsindex)) …

Query hint

We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:

SELECT DISTINCT (SalesTerritoryKey)
FROM dbo.FactResellerSalesCopy
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

CATALOG VIEWS and DMVs 

There are a number catalog views which will show different values for ColumnStore. I am listing a few here:

sys.indexes:

type : 6

type_desc: NONCLUSTERED COLUMNSTORE

sys.partitions:

data_compression: 3

data_compression_desc: COLUMNSTORE

sys.index_columns:

key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.

sys.dm_db_index_operational_stats:

Columns
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
nonleaf_allocation_count,
nonleaf_page_merge_count,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
Leaf_page_merge_count,
page_compression_attempt_count,
page_compression_success_count – Has a value of 0 for columnstore index.

Showplan Enhancement

Three additional properties have been added to the showplan data for columnstore indexes:

  1. Storage – row or columnstore
  2. EstimatedExecutionMode : row or batch
  3. ActualExecutionMode : row or batch

SSMS Enhancements for Columnstore

I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.

image

The next UI changes is for creating a New Columnstore Index.

image

The third part is around adding columnstore columns as we create the index.

image

Final Words

These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.

Continue reading...