Datawarehouse

Tracking security changes on your SQL Server development environment

A few months ago, I was fed up to receive complains from my teammates about security settings changed on the development (or test) server. Every week, someone was at my desk complaining with a variation of “My development was working previous week and now it’s not working anymore. Someone (not me) must have change something about security but I don’t know who and why!”.

To give you a correct picture about our SQL Server environment, we’ve one instance for the dev server and another the test server but many people are developing on a given project. Consider also that we’re developing different projects at the given moment. All team members are (full) administrators of the dev and test servers. All team members know that the rule about security is “reduce privileges to the minimum required” (definitively db_owner is not a solution) and this rule will be checked during code reviews.

Some will consider a classical approach, supported by most DBA: we should restrict the administrator rights to less people: a kind of single-point-of-security. Well, it was probably my opinion seven years ago but my position has dramatically changed on this. This point of view is really based on my own environment … your mileage will vary but keep in mind that I’m speaking at the dev and test level not production. Main point for this position is that I’d not recommend a “single-point-of” for any agile development. The true names are not “single-point-of” but “bottleneck”, “not-shared-decisions”, “I’m-the-only-one-who-know-don’t-fire-me” and “not-my-business-ask-this-guy” … four things that I’m trying to avoid as much as possible.

I implemented a solution to track any change to “security” on the SQL Server. Not a word to my team members … I was the only one to know that something has been scripted and deployed. After a few weeks, my conclusion was clear: nobody in the team is trying to sabotage the work of others. After investigations, issues could be grouped as

  • You changed the code and it’s now requiring new privileges: Script them and stop blaming others.
  • During development, you manually added some privileges to the database. But the deployment script is not including all these privileges. Someone has redeployed the solution from scratch, discarding all the unscripted privileges. It’s your mistake.
  • Less frequent … indeed, someone on another solution is also developing on this shared component (SSISDB) and has a different definition than yours for this shared role. Discuss together and align your needs.

So clearly it was not a question of responsibility or trust but a problem of practices:

  • All privileges must be scripted (and reason to request them documented) and committed in the solution. It must be part of the deployment artefact (at least in dev/test).
  • All the work performed on SSISDB (or any shared component) must be executed on a shared project

That’s not enough, practices not supported by tools are pointless and will never be applied during more than a month. We must provide a tool for people to know who has changed “security”. The following trace is doing the job:

use [master];
go

create server audit
    [Audit Login Changes]
to file
(   FILEPATH = N'N:\Trace\'
    , MAXSIZE = 1024 MB
    , MAX_FILES = 10
    , RESERVE_DISK_SPACE = OFF
)
with
(   QUEUE_DELAY = 1000
    , ON_FAILURE = CONTINUE
    , AUDIT_GUID = '33e055a9-1924-47c3-9798-e8875a5bcd42'
);

alter server audit
    [Audit Login Changes]
with (state=on);
go

create server audit specification
    [Audit Login]
for server audit [Audit Login Changes]
    add (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
    , add (SERVER_ROLE_MEMBER_CHANGE_GROUP)
    , add (DATABASE_PERMISSION_CHANGE_GROUP)
    , add (SERVER_OBJECT_PERMISSION_CHANGE_GROUP)
    , add (SERVER_PERMISSION_CHANGE_GROUP)
    , add (DATABASE_PRINCIPAL_CHANGE_GROUP)
    , add (SERVER_PRINCIPAL_CHANGE_GROUP)
    , add (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
with (state=on);
go

and the following query will help you to investigate the traces. Since this instant, I just received a few complains and it was about Windows (disk access for read/write files with SSIS) issues.

declare  @LogFilePath varchar(255);

select
	@LogFilePath=log_file_path
from
	sys.server_file_audits
where
	name='Audit Login Changes';

set @LogFilePath=@LogFilePath +'*.sqlaudit';

select
	session_server_principal_name
	,database_name
	,object_name
	,m.class_type_desc
	,statement
	,convert(datetime,
		switchoffset(
			convert(datetimeoffset, event_time)
			, datename(TzOffset, sysdatetimeoffset())
		)
	)
from
	sys.fn_get_audit_file (@LogFilePath,default,default) f
inner join
	sys.dm_audit_class_type_map m
	on m.class_type=f.class_type
order by
	event_time;

I know that if someone wanted to hijack this trace it would be easy to stop it (and restart it), but keep in mind that I’m not trying to fix a security issue but to fix a process issue during the development.

Advertisements

Evaluate an expression to validate the result of a query

During the validation process of your BI solution, it could be useful to check that a given expression is validated for each row returned by a query. A sample? Yes surely, you’ve a query returning all the customers having bought for more than $10,000 in your stores. You should ensure that the query returns only rows with a column TotalDue greater than 10000. If we apply this query to AdventureWorks2012, it should look as

select
	[FirstName] + ' ' + [LastName]
	,TotalDue
from
	[Person].[Person] pers
inner join
	(
		select
			[pers].[BusinessEntityID]
			,sum(TotalDue) as TotalDue
		from
			[Sales].[SalesOrderHeader] soh
		inner join
			[Sales].[Customer] cust
			on [cust].[CustomerID] = [soh].[CustomerID]
		inner join
			[Person].[Person] pers
			on [pers].[BusinessEntityID] = [cust].[PersonID]
		group by
			[pers].[BusinessEntityID]
		having
			sum(TotalDue)>10000
	) TotalDueByPerson
	on [TotalDueByPerson].[BusinessEntityID]=[pers].[BusinessEntityID]

The most common assertion for datasets within NBi is equalTo but it can’t help you for this task. Indeed, the assertion equalTo requires that you know exactly the count of rows that will be returned by the query. If you compare two datasets with different count of rows, the test will surely turn red complaining about missing or unexpected rows.

So if equalTo is not the graal, we should take a look to the another assertion dedicated to datasets’ validation: evaluate-rows. The assertion evaluate-rows is checking that a list of expression are validated for each row of the dataset. If at least one of these expressions for at least one row is not validated, then the test will turn red.

The assertion evaluate-rows is mainly built to support calculations on based on values and compare the result of the expression’s evaluation to a specific column. To support the case explained above, we will need to be creative. The idea is to validate the content of the cell TotalDue based on it’s own value. Indeed, we will express in our test that if the value of column TotalDue is greater than 10,000 then we’ll return this value as the the expression’s result, else we’ll return -1. When the framework will compare the value of the column and the value of the expression, each row with a TotalDue greater than 10,000 will validate and if the amount is less than (or equal to) 10,000, this value will be compared to -1 … and this row will fail the test.

<assert>
    <evaluate-rows>
         <variable column-index="1">TotalDue</variable>
         <expression column-index="1">if(TotalDue>10000,TotalDue,-1)</expression>
    </evaluate-rows>
<assert>

Tested with the query above, the test will success.

If we’re changing the query above to filter on TotalDue greater than 50 in place of 10,000

having
 sum(TotalDue)>50

The test will return a failure and turn red with following message:

7263 of the 7309 rows don't validate at least one expression.

Row 0: 
    The expression ' = if(TotalDue>10000,TotalDue,-1)' is not validated.
    The expected result was '-1' but the actual value is '162.7444'
    Variable 'TotalDue' had value of '162.7444'

Row 1: 
    The expression ' = if(TotalDue>10000,TotalDue,-1)' is not validated.
    The expected result was '-1' but the actual value is '3019.6307'
    Variable 'TotalDue' had value of '3019.6307'

Full test:

<?xml version="1.0" encoding="utf-8" ?>
<testSuite name="Acceptance Testing: members ordering" xmlns="http://NBi/TestSuite">
  <test name="Blog Post: Evaluate's expression">
    <system-under-test>
      <execution>
        <query connectionString='Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly'>
          <![CDATA[
          select
          [FirstName] + ' ' + [LastName]
          ,TotalDue
          from
          [Person].[Person] pers
          inner join
          (
          select
          [pers].[BusinessEntityID]
          ,sum(TotalDue) as TotalDue
          from
          [Sales].[SalesOrderHeader] soh
          inner join
          [Sales].[Customer] cust
          on [cust].[CustomerID] = [soh].[CustomerID]
          inner join
          [Person].[Person] pers
          on [pers].[BusinessEntityID] = [cust].[PersonID]
          group by
          [pers].[BusinessEntityID]
          having
          sum(TotalDue)>50
          ) TotalDueByPerson
          on [TotalDueByPerson].[BusinessEntityID]=[pers].[BusinessEntityID]
          ]]>
        </query>
      </execution>
    </system-under-test>
    <assert>
      <evaluate-rows>
        <variable column-index="1">TotalDue</variable>
        <expression column-index="1" type="numeric" tolerance="0"> = if(TotalDue>10000,TotalDue,-1)</expression>
      </evaluate-rows>
    </assert>
  </test>
</testSuite>

Comparing two datasets with NBi when expected values are not precisely defined

When you’re looking for insights to validate your BI solution with your stakeholders, it’s often impossible to have precise figures for some facts. If it was possible, your BI solution would probably not be needed. Your stakeholders should be able to give you estimations of these figures but not precise values. Usually you’ll end-up with something as “Last year, we’ve sold at least 10.000 units and the marketing department claims that we’re multiplying by ten our sell figures each year since three years”. You can translate this on a table by:

---------------------
| Year | Sold units |
---------------------
| 2010 | 10         |
| 2011 | 100        |
| 2012 | 1000       |
| 2013 | 10000      |
---------------------

Reminder: that these values are not exact. It means, that nobody has a precise idea of these figures so we’re not expecting that the values returned by your BI solution are precisely these figures.

NBi introduces at least three ways to represents this lack of precision in your automated test:

  • absolute tolerance
  • percentage of tolerance
  • ranges of values

The main difference between the first two methods and the third is that the first methods introduce a unique tolerance. This tolerance is valid for each row in the dataset. It means that I cannot apply a tolerance of 10% (or 10 units) for the first row and a tolerance of 20% for the second row. This option to be able to refine your tolerance row by row is introduced by the notion of ranges.

We can refine our expected dataset like this:

----------------------
| Year | Sold units  |
----------------------
| 2010 | [5;15]      |
| 2011 | ]80;100]    |
| 2012 | [900;1200[  |
| 2013 | (>=10000)   |
----------------------

As you can guess in the table above, NBi supports range with open or closed bounds and also infinite. NBi supports a large syntax to describe ranges, this let you the opportunity to be really expressive in your test.

The NBi’s syntax to define the result-set described above is:

<equalTo>
  <resultSet>
    <row>
      <cell>2010</cell>
      <cell>[5;15]</cell>
    </row>
    <row>
      <cell>2011</cell>
      <cell>]80;100]</cell>
    </row>
    <row>
      <cell>2012</cell>
      <cell>[900;1200[</cell>
    </row>
    <row>
      <cell>2013</cell>
      <cell>(>=10000)</cell>
    </row>
  </resultSet>
</equalTo>