SQL Server Encryption Hierarchy June 18, 2013

http://msdn.microsoft.com/en-us/library/ms189586.aspx

Keep in mind the following concepts:
•
For best performance, encrypt data using symmetric keys instead of certificates or asymmetric keys.

•
Database master keys are protected by the Service Master Key. The Service Master Key is created by SQL Server setup and is encrypted with the Windows Data Protection API (DPAPI).

•
Other encryption hierarchies stacking additional layers are possible.

•
An Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server.

•
Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM.

•
The Service Master Key and all Database Master Keys are symmetric keys.

Cryptography

http://blogs.msdn.com/b/plankytronixx/archive/2010/10/23/crypto-primer-understanding-encryption-public-private-key-signatures-and-certificates.aspx

There are 2 broad classes of algorithm – symmetric and asymmetric. Symmetric algorithms use the same key for both encryption and decryption. Asymmetric algorithms, such as public/private key cryptography use one key for encryption and a different, though mathematically related key for decryption.

Public/private key cryptography is used to transport a symmetric key that is used for message exchanges.

The website generates the public and private keys. They have to be generated as a key-pair because they are mathematically related to each other. You retrieve the public key from the website and use it as your encryption key. You’re not just going to send your credit card information across the Internet yet. You’re actually going to generate a symmetric key and that is going to become the plain-text input data to the asymmetric encryption algorithm. The cipher-text will traverse the Internet and the ecommerce site will now use its private key to decrypt the data. The resulting output plain-text will be the symmetric key you sent. Now that both you and the ecommerce site have a symmetric key that was transported secretly, you can encrypt all the data you exchange. This is what happens with a URL that starts https://.

One reason for doing this is that asymmetric key crypto, or public/private key crypto, as it is known, is expensive, in terms of computing power, whereas symmetric key crypto is much more lightweight. When you see that a web site uses 256 bit encryption, they are talking about the symmetric key that is used after the public/private key crypto was used to transport the symmetric key from sender to receiver. Often the key lengths for public/private key cryptography is 2048 bits. You may have found yourself confused when setting up IIS with 256 bit SSL encryption and seeing keys of 1024 or 2048 bits. This is why – it’s the difference between what’s called the session key and the public/private keys.

==> So a digital signature is like a seal on the envelope. BUT it does not prove authenticity.

Do you remember earlier, I talked about hashes? Well, because a message might be quite large, it’s often best to generate a hash of the message and encrypt that. If it’s an MD5 hash, it means you’ll only have to encrypt 128 bytes. When you come to perform the validation of the signature, you have to take the plain text portion and generate a hash before you do the comparison. It just uses the CPU more efficiently.

==> Hashes are a performance improvement for seals.

Certificates are data structures that conform to a specification: X.509. But really they are just documents that do what we just talked about. The plain text data is the public key, plus other distinguishing information like the issuer, the subject name, common name and so on. It is then hashed and the hash is encrypted using the private key of a special service called a certification authority (CA) – a service that issues certificates.

==> Certificates are trustable commodities running on the X.509 standard that give authenticity to a message (a private/public key combination is from the intended sender).

When you come to check the message you’ll know that it definitely wasn’t tampered with in transit, but how do you know you can trust the public key embedded in to the message? How do you know that it’s me that created the message. That’s where digital certificates come in to play.

As you walk up the chain you have to eventually get to a point where you trust the certificate. If you don’t, you’ll get a certificate error warning and a lot of messages advising you not to continue.

http://channel9.msdn.com/posts/Crypto-Primer-Understanding-Encryption-Certificates-PublicPrivate-Key–Digital-Signatures

Video of the above slide.

http://en.wikipedia.org/wiki/Public-key_cryptography

The use of these algorithms also allows the authenticity of a message to be checked by creating a digital signature of the message using the private key, which can then be verified by using the public key. In practice, only a hash of the message is typically encrypted for signature verification purposes.

SQL Server Ownership Chaining and Schema and EXECUTE AS

http://msdn.microsoft.com/en-us/library/ms188694.aspx

Use the cross db ownership chaining option to configure cross-database ownership chaining for an instance of Microsoft SQL Server.

This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases:

http://technet.microsoft.com/en-us/library/ms188676(v=SQL.105).aspx

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Mary owns most of the underlying objects of the July 2003 view. Because Mary has the right to make objects that she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain, she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in the illustration, and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.

http://msdn.microsoft.com/en-us/library/dd283095(v=SQL.100).aspx

SQL Server 2005 implemented the concept of a database object schema. A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment not available in previous versions of SQL Server.

In releases prior to SQL Server 2005, database object owners and users were the same things. SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. The schema owner may own one or many schemas.

A schema is separate entity within the database. It is created by using the CREATE SCHEMA statement. A schema can be owned by a user, a role, or a group (for more information about possible schema owners, see the “Principals” section in this document). A user executing CREATE SCHEMA can be the owner of the schema or it can allocate another user as the schema owner (with appropriate IMPERSONATE permissions). A schema only has one owner, but a user can own many schemas. Schema ownership is transferrable.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema

Principals

When a schema is created, it is owned by a principal. A principal is any entity or object that has access to SQL Server resources. These are:
•Windows domain logins
•Windows local logins
•SQL Server logins
•Windows groups
•Database roles
•Server roles
•Application roles

How Permissions Are Checked

A secured object has a four-part name (fully qualified name): server.database.schema.object. The object will also have permissions applied to it. Thus, the following checking is done on the object:
•Access is granted or denied directly on the object.
•Access is granted or denied on the schema containing the object.
•Access is granted or denied on the database containing the schema.
•Access is granted or denied on the server containing the database.

During the checks, DENY permissions are checked first and access is denied if it exists at any of the check levels above. Similarly, if no specific permission exists, access is denied.

Access is always granted to SA and to members of the sysadmin group.

When multiple objects are accessed sequentially, such as in a stored procedure or a view, that is known as chaining. Chaining allows the improvement in performance of checking object permissions. As a first step in the checking process, the object owner (schema) of the called object is compared to the owner of the calling object. If the owners are the same, the permissions of the called object are not checked. A chain of objects with the same owner is called an unbroken ownership chain.

In the scenario described above, views and stored procedures will normally access objects that are owned by different schemas. In this situation, the owner of the called object is compared with the owner of the calling object. If the owners are different, the full set of permissions is checked on the called object. This is referred to as a broken ownership chain.

The assessment process can be improved by using either the fully qualified name or the DEFAULT_SCHEMA option described earlier. By setting a value for DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA first, removing an unnecessary ownership checking process. This can improve performance considerably on heavily utilized systems.

For more information about the DEFAULT_SCHEMA and the performance considerations when not using fully qualified object names, see Upgrading to SQL Server 2005 and default schema setting.

http://sommarskog.se/grantperm.html

When designing an application for SQL Server, you rarely want users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures, and it is through the stored procedures users can access and update data. The procedures perform validations of business rules to protect the integrity of the database.

SQL Server Encryption

http://msdn.microsoft.com/en-us/library/bb895327.aspx

Certificates are useful because of the option of both exporting and importing keys to X.509 certificate files. The syntax for creating certificates allows for creation options for certificates such as an expiry date.

SQL Server certificates comply with the IETF X.509v3 certificate standard.

makecert

Creates certificates.

sn

Creates strong names for symmetric keys.

SQL Server Securables and Scope

http://msdn.microsoft.com/en-us/library/ms190401.aspx

Securables are the resources to which the SQL Server Database Engine authorization system regulates access. For example, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured.

The securable scopes are server, database, and schema.

The entity that receives permission to a securable is called a principal. The most common principals are logins and database users. Access to securables is controlled by granting or denying permissions, or by adding logins and user to roles which have access. For information about controlling permissions, see GRANT (Transact-SQL), REVOKE (Transact-SQL), DENY (Transact-SQL), sp_addrolemember (Transact-SQL), and sp_droprolemember (Transact-SQL).

http://msdn.microsoft.com/en-us/library/ms191465.aspx

The Database Engine manages a hierarchical collection of entities that can be secured with permissions. These entities are known as securables. The most prominent securables are servers and databases, but discrete permissions can be set at a much finer level. SQL Server regulates the actions of principals on securables by verifying that they have been granted appropriate permissions.

http://msdn.microsoft.com/en-us/library/ms191291.aspx

•CONTROL

Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

•TAKE OWNERSHIP

Enables the grantee to take ownership of the securable on which it is granted.

TAKE OWNERSHIP

Procedures (Transact-SQL and CLR)

Scalar and aggregate functions (Transact-SQL and CLR)

Synonyms

Tables

Table-valued functions (Transact-SQL and CLR)

Views

Sequence objects

Checking permissions can be complex. The permission check algorithm includes overlapping group memberships and ownership chaining, both explicit and implicit permission, and can be affected by the permissions on securable classes that contain the securable entity. The general process of the algorithm is to collect all the relevant permissions. If no blocking DENY is found, the algorithm searches for a GRANT that provides sufficient access. The algorithm contains three essential elements, the security context, the permission space, and the required permission.

Security context

This is the group of principals that contribute permissions to the access check. These are permissions that are related to the current login or user, unless the security context was changed to another login or user by using the EXECUTE AS statement. The security context includes the following principals:
◦
The login

◦
The user

◦
Role memberships

◦
Windows group memberships

◦
If module signing is being used, any login or user account for the certificate used to sign the module that the user is currently executing, and the associated role memberships of that principal.

Permission space

This is the securable entity and any securable classes that contain the securable. For example, a table (a securable entity) is contained by the schema securable class and by the database securable class. Access can be affected by table-, schema-, database-, and server-level permissions. For more information, see Permissions Hierarchy (Database Engine).

Required permission

The kind of permission that is required. For example, INSERT, UPDATE, DELETE, SELECT, EXECUTE, ALTER, CONTROL, and so on.

Access can require multiple permissions, as in the following examples:
◦
A stored procedure can require both EXECUTE permission on the stored procedure and INSERT permission on several tables that are referenced by the stored procedure.

◦
A dynamic management view can require both VIEW SERVER STATE and SELECT permission on the view.

SELECT * FROM fn_builtin_permissions(default);
GO

SELECT * FROM sys.database_permissions
WHERE major_id = OBJECT_ID(‘Yttrium’);
GO

SQL Server Security June 17, 2013

http://www.sqlservercentral.com/articles/Security/89923/

SELECT *
FROM sys.server_principals
WHERE type = ‘R’
and is_fixed_role =0 and name’public’;

SELECT
SRM.role_principal_id,
SP.name AS Role_Name,
SRM.member_principal_id,
SP2.name AS Member_Name
FROM sys.server_role_members AS SRM
JOIN sys.server_principals AS SP
ON SRM.Role_principal_id = SP.principal_id
JOIN sys.server_principals AS SP2
ON SRM.member_principal_id = SP2.principal_id
WHERE sp.name=’customServerRole’

http://blogs.msdn.com/b/sqlsecurity/archive/2011/08/25/database-engine-permission-basics.aspx

Most permission statements have the format :

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL

•AUTHORIZATION must be GRANT, REVOKE or DENY.
•PERMISSION is listed in the chart referenced below.
•ON SECURABLE::NAME is the server, server object, database, or database object and its name. Some permissions do not require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context. For example the CREATE TABLE permission doesn’t require the ON SECURABLE::NAME clause.
•PRINCIPAL is the login, user, or role which receives or loses the permission. Grant permissions to roles whenever possible.

Sample grant statement: GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam

http://www.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/

When a user presents a statement to SQL without specifying the owner of an object, SQL first looks for an object owned by the user. If one is not found, then SQL looks for an object owned by dbo.

If our friend Fred tries to query test2, he will actually retrieve the rows from [fred].[test2]. Assuming Fred has permission to query [dbo].[test2], he must qualify the table name with the owner. He will need to query [dbo].[test2] to see the same results that any of the other users will see. Obviously, this could get messy if the software doesn’t specify ownership in its T-SQL statements, and objects are owned by multiple users, usually by accident.

Once a user is the owner of one or more objects, the user can not be removed from the database without transferring ownership to another account.

Interesting SQL Queries

http://www.sswug.org/editorials/readed.aspx?id=2820

Long running queries.

FROM
sys.dm_exec_requests
WHERE
command = ‘DbccFilesCompact’

http://technet.microsoft.com/en-us/magazine/hh534404.aspx

Contained database users.

After creating the contained user, you can examine the sys.database_principals catalog view within the contained database. The recordset returned by querying the catalog view will show the new column named authentication_type set to a value of 2. It will also show the authentication_type_desc column set to a value of “DATABASE” when the user is a contained user.