SQL Server Security – Part 3

 

In this article we present “ideas” rather than guidelines for security under SQL Server.  We will cover:

 

• Triggers (instead-of and others)
• Row level security (RLS)
• Application Database Tables  - Example Security Frameworks
• Views and Stored Procedures
• Protocol encryption
• NT Hacking / Security

 

Triggers (instead-of and others)

 

For complex views that do not permit standard CRUD access, you can implement instead-of triggers that allow us to intercept and manage all CRUD access against the view for auditing or to fulfil some other business requirement.  As the name suggests, the trigger will fire “instead of” the normal SQL Server transaction logic for insert, deletes and updates (if it was possible – complex views may not support direct CRUD  access without instead-of triggers, see BOL).  I will not discuss these triggers in detail, but highlight the fact that they do exist in SQL Server and are mentioned throughout this article.

 

An excellent overview of instead-of and other triggers can be found at:

 

http://www.sqlservercentral.com/columnists/nboyle/insteadof.asp

http://www.sqlservercentral.com/columnists/nboyle/triggerenhancement.asp

http://www.sqlservercentral.com/columnists/awarren/triggercolumnsupdated.asp

http://www.sqlservercentral.com/columnists/rmarda/auditingtriggers.asp

Row Level Security (RLS)

 

Unfortunately SQL Server does not include row-level security as any additional extras; you have to manage it yourself.  In order to do this, I would first recommend visiting Oracle’s TechNet site and reading up on label security and their built in routines for implementing virtual private databases (also query “Fine Grained Access Control”).  Why?  It is a simple matter of not reinventing the wheel to some degree and picking up ideas on how this technology can be used in your application.

 

Row-level security (i.e. horizontally partitioned data) allows us to filter rows of data based on a users access privileges.  The access privilege may be a person’s position within the company, membership to one or more security “groups”, or simply a matter of only seeing data their initially created and subsequently maintained.  This of course is very different from virtually partitioned data that SQL Server does support to some degree by placing CRUD access control down to a column level.

 

I will say up front that designing a security framework for roll-level security is tough.    From here, you will get an idea of the issues and other methods you can employee for your security requirements.

 

Note:  RLS, ideally, should be flexible enough to allow access by any source (not just the application).

 

RLS Example 1.   Organisation Chart

 

Here is an example organisation chart.

 

The idea here is that people may only see data based on their “position” within the company.   In order to do this, we “label” all incoming data rows with the division/branch/section combination:

 

            divAbrnAsecB             à        Division A, Branch A, Section B

            divAbrnBsecA             à        Division A, Branch B, Section A

 

Based on this, the following access logic is used:

 

General Managers (division)  à            div?%               {any branch/section in division ?}

Directors (branch)                    à        div?brn?%        {any section in division ? / branch ?}

Managers (section)                   à        div?brn?sec?    {only section ?}

 

Note:  substitute ? for the code for the division/branch/section.

 

All incoming data includes the “section” (lowest level of the tree) and a section code is unique within a branch and division combination.  So based on this fact, it is a simple operation to construct the above “row level security” strings.

 

As the row level label has now been added to all incoming data, such as our tables of leave entitlements, financial records, performance interviews etc  we associate our database users to a series of security groups to control access to the data.  In the example below we use an extremely simplistic example where 1 user has 1 and only 1 security string (i.e. allocated position within the org chart).

 

Create view RLS_ImportantFinanceFigures as

Select  

<all expenditure figures>

from   

<other tables to complete query>,

securityaccessprivs sap

where

            <other clause statements>

and      sap.sapusername = SUSER_SNAME()

and      (mytable.securitystring like sap.sapsecuritystring)

 

eg.        …        sap.sapusername = “LOTR\Frodo”

and       (mytable.securitystring like “divAbrnB%”)

 

Data access is controlled via pre-defined views (or stored procedures) with only the above where clause conditions (predicates) added in.  Therefore, no matter what the user does, the condition is applied and only selected rows are returned.  The above example can be easily extended to cater for multiple positions within the org chart for a single user.

 

The only problem with organisation charts is managing change over time.  Never tell yourself that change will not occur; it happens (at a minimum) in 6 monthly cycles for manage organisations (mainly to give HR people something to do <jk>).  Chart changes mean:

a)      what do you do about now “historical” RLS security strings?

b)      how do report over time with a shifted org chart and make sense of the data?

c)      Do we retain “past” security privileges for users and just add new ones?

 

These are extremely important issues that must be addressed at some time.

           

In Oracle, we have the concept of a “query rewrite”, where at parse time our row level security additions can be automatically added to the predicate.  This is the ultimate in RLS as do not have to create views to manage access to the underlying data.  For those interested, see  DBMS_RLS (pl-sql package) that allows you to define and assign fine grained access control policies to control predicates.

 

NOTE:  Be careful with the SUSER_SNAME() function as it returns the fully qualified name (domain\user-name) for NT users.  Remember this when entering data into your “user” table.  See Appendix for examples of return values.

 

RLS Example 2 – User Name only

 

Many have probably read the article and associated working examples from Narayana Vyas Kondreddi on row level security.   If not, take a look:

 

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

 

Using NT groups, you can label data via a specific NT group name and control user access based on the fact that they are a member of the group rather than labelling over a specific user name.  In the example above it works well based on the concept of individual sales persons.

 

 

The two simple examples above provide some a good starting point to deploying fine-grained access control in your applications.  As stated, designing this early in the project is very important to minimise recoding and maximise system flexibility.  This is even more important in “classic” web applications that use a single login to the database and apply security from within the application or via roles.

 

When researching RLS, look at the solutions provided by Oracle, DB2 and MySQL.  The examples, especially from Oracle can provide some great assistance in repeating similar logic within SQL Server. 

 

Application – Example Security Frameworks

 

In this section I will discuss example security frameworks used in applications that I have worked on to manage security.  I will present these as physical E-R models with comments as to the use and objectives achieved with each.  This may or may not provide you with some assistance in modelling your own security tables into your databases if NT groups / Active Directory objects do not provide the answers or desired flexibility.

 

Example 1. – Active Directory and associated database objects

 

The following example uses a mix of active directory and database tables to manage application security.  The “application” table is very simple and simply defines the numerous applications that share the single database (shared schema).  It also links to an “system parameter” table not shown in this diagram.  The “user group” table is synonymous with NT groups in which our NT users are allocated.  The applications consist of “application functions” that are the individual items (pages, buttons, frames, etc) that we need to protect.  The function access table includes a column for every right (insert, delete, update, select) for the associated user group.  The application itself uses security functions and associated application function table identifiers to lookup the tables to determine the users access then drawing pages and when users attempt a business function.

 

If you were wondering, application exists because this particular database is a corporate data model for numerous sub-modules (applications).

 

Example 2 – Resource allocation and security

 

In this example, we have removed some of the standard circular relationships associated with security database structures (see comments against each table).  A “system user” has all the necessary information about the end-user, much of which can be replaced when using active directory users.  In this particular example, when a new user hits the application (default.asp) a new entry is added into the table and their “DOMAIN/UserName” detected and inserted (why? its all to do with OLAP cell security that is outside the scope of this article).  Default “system access privileges” are given to new users.

 

All tables and associated application functions have a “resource” identifier.  This includes other database tables and external application resources that need security applied.  The hierarchy of resource group (grouping of resources) to security resource group (grouping of resource groups) and then  allocated via security access privileges is shown in the data model below.

 

Views and Stored Procedures

 

Over the years I have talked to a variety of people about application design and how data models are simplified via views and stored procedures to access and manipulate data.    Although fine, it can be a hard sell to programmers, analysts and project managers.  Why? because many programmers want complete CRUD access to virtually all schema tables, they want to code all DB access via the application and believe views are only for reports and simplifying complex lookups. 

 

When under pressure, the time to sit back and think “security” and “performance” is a tough ask.  This is very simplistic view of course as security at this level in the database is typically dictated by:

a)      Cost to implement in terms of time and effort

b)      Understanding of the requirement for such access (definition of the security requirements)

c)      Architecture of the application

d)      Need for simplicity (unnecessary complexity?)

e)      Skills to implement

f)        Ease of maintenance

 

I like to take the approach of not exposing more data than you have to in selected areas within the application.  Exposure to CRUD (create,read,update,delete) level can be implemented by views, stored procedures, roles, table priviligies, instead-of triggers and other object level and column privileges.

 

The use of views is an excellent method of implementing security and hiding schema relationships and relational database complexities.  Each table has an equivalent simple or complex view; the views may include instead-of triggers to re-direct DML to other "hidden" tables for complex views, for auditing or data partitioning.  If instead-of triggers to do appeal to you, another option for managing CUD (create/update/delete) access is to grant select only access to the views and implement all CUD via predefined stored procedures.   Therefore, the database users view of the actual database structure may be quite different from that of the schema owner and how they manipulate it is restricted by the stored procedures (business rules).

 

I do not recommend this approach for every table in your schema.  

 

Another approach is using stored procedures for insert, delete, update and select access.  This can have significant performance benefits also due to caching of procedure execution plans.   On top of this, you can lever the benefits of OPEN XML.  Getting back to security, you can effectively deny all access to tables, and implement all CRUD through stored procedures.  The only problem with this is a SQL Server one, which is its poor exception handling (ie. none!).  

 

The use of views and stored procedures also assist in application maintenance (no embedded SQL statements in your COM+ and ASP code) and makes impact analysis of schema changes much simpler.

 

Note:  It is important to remember that you do not need to grant CRUD access to the tables a views or stored procedure is referring to.

Multi-Protocol  Encryption

 

Before we discuss this section, let me say that I have had no formal experience with protocol encryption using the multi-protocol network library and SSL over this protocol.   Even so, I will attempt to describe the processes involved and welcome further comments.

 

SQL Serverv7

 

The multi-protocol net-lib is the only network library that supports encryption under v7.  In SQL Server 2k encryption over other libraries is supported by the operating system using SSL (see certificate manager in Windows 2k).  

 

To enable encryption over multi-protocol:

 

a)      Server

a.       Run the server network library utility

b.      Enable the multi-protocol net-lib and check the “encryption” check box

c.       Specify and appropriate alias, ok, and close utility

d.      Start and stop the SQL Server service

b)      Client

a.       As above, set the multi-protocol library and set encryption check box

c)      Client (enforced encryption)

a.       For each client you want encryption to take place, add the RPCNetLib registry key to this entry point HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\RPCNetLib
Name = Security Data Type (String data type)
Value = Encrypt

 

See Microsoft support document Q239894 for encrypted multi-protocol connections.

 

It is important to remember that all data is encrypted, including raw data, user ids and passwords.  If you enforce encryption at the server (see a) above), the all clients connecting via multi-protocol must by using encryption to establish the connection.  The encryption used is the built in NT encryption libraries via RPC calls (which provides with Windows Authentication).  The default is 40bit up to 128bit.

 

SQL Server 2k

 

Protocol encryption has reached a new level of flexibility with SQL Server 2k.  Basically any net-lib can be encrypted via SSL at the OS level.  By installing certificates from a CA (certifying authority) on the server and manipulating the client/server network utility the administrator can manage all net-lib traffic with ease.

 

The certificate must be the fully qualified name of the server and valid for server authentication, eg:  mysqlserver.myhost.com.  You must be logged in as the SQL Server service user to obtain the certificate from the CA and when installing (importing) it.  Once done, all login-packet will be automatically encrypted.  For client encryption, go to the clients network utility and set the force protocol encryption option. Once set, the client can only talk via SSL.  This option can be set programmatically via the Encrypt=yes option in OLE-DB or ODBC connection strings.

 

Note:  Multi-protocol does not support named instances or server enumeration.

NT Hacking / Security

 

I do not know anything about NT hacking, and perhaps never will.  Operating system security and internals is complex and requires a lot or reading to appreciate and then apply the skills to cracking the security models used.  Below is a range of articles that might be of interest to the DBA.

 

http://www.nmrc.org/faqs/hackfaq/hackfaq.html

http://www.sandelman.ocunix.on.ca/SSW/nt_hack/

http://local.15seconds.com/faq/Security/default.htm

http://local.15seconds.com/issue/011030.htm

Web References

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/securitydataaccess.asp 

http://www.nmrc.org/faqs/hackfaq/hackfaq.html

http://www.sandelman.ocunix.on.ca/SSW/nt_hack/

http://local.15seconds.com/faq/Security/default.htm

http://local.15seconds.com/issue/011030.htm

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_security2000.asp

Appendix

 

System Function                     Return Values in SS2k                          

select SUSER_SNAME()                                     SECA\Chris Kempster

select SUSER_NAME()                                        NULL in SQL Server 2k

select SUSER_ID()                               NULL in SQL Server 2k

select SYSTEM_USER                        SECA\Chris Kempster

select USER                                          dbo

select USER_ID()                 1 (users db identification number)