Monthly Archives: May 2013

SQL 2012 Roles, Permissions, and Schemas

The relationship of roles, permissions, and schemas is an important security concept in SQL Server. A fully qualified database object name consists of four parts:

Usually you’ll just need to refer to objects in the current database context by using the schema and object name. A schema is a collection of objects, such as tables and code modules, as shown in the Figure below. This method simplifies user management, particularly when you have to change ownership of objects. But more importantly, it simplifies permissions management.

SQL-sample-schema

You can assign permissions on a schema that apply to all objects in the schema. For example, if you assign SELECT permission on CarolSchema to a principal, all three tables in that schema have that permission. Setting permissions individually on objects is always an option, but if you’ve designed the schemas in a database well, in some sort of functional categories that make sense for the database, you can set permissions on the schema and have them apply to dozens if not hundreds of objects. Best of all, the permissions you assign apply automatically to any future objects you add to the schema. Continuing the SELECT example, if a year from now you add Table4 to CarolSchema, all principals with SELECT permission on the schema automatically have that permission on the new table. Multiple users and roles can have the same default schema, and if a principal has no default schema set, SQL Server attempts to find the object in the dbo schema.


 Thumbnail for 637

 

 

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

User-Defined Server Roles in SQL Server 2012

A long awaited security feature in SQL Server 2012 has been user-defined server roles. SQL Server has long had flexible user-defined database roles for database-level permissions (which you’ll learn about later in this chapter), but with custom server roles you can finally get as granular with server-level permissions.

In old versions of SQL Server, the only way to grant some kinds of permissions to users was to assign them to a built-in fixed server role, which usually had way too many permissions. Making everyone a sysadmin was a horrible but common practice, particularly problematic because you can’t deny a sysadmin anything. This violates the principal of least privilege in a big way, but was often a practical necessity. SQL Server 2005 and later made all this more granular, letting you assign just about any specific server-level permission to a user, but lacked the ability to group those permissions into a server role.

SQL Server 2012 solves that problem with its support for user-defined server roles. Creating a new server role is as simple as using the CREATE SERVER ROLE statement:

Then you can grant and deny any server-level permissions you want. The following code grants the CONTROL SERVER permission to the new role— akin to granting sysadmin privileges—then denies a few permissions to narrow down the privileges of the members of the server role. This is a very flexible way to grant the users who are members of the group specific permissions.

To test the role, the code then creates a login associated with a Windows group, DBAs, on a machine named Willow, and adds the new login to the LimitedDBA role.

The code then creates a SQL Server login carol, with no permissions whatsoever within the instance of SQL Server. Then the code attempts various actions under carol’s security context that require server-level permissions: create another login, view system information, and create another server role. All of these actions fail, as you can see in the Figure below, because the carol principal has no permissions to perform these actions.

SQL-Server-2012-Failed-Actions

Next the code adds carol to the new LimitedDBA user-defined server role, and once again attempts to perform the same actions. As you can see in the Figure below, this time carol is able to get system information (the SELECT action), because that permission is granted through the CONTROL SERVER permission. But carol still can’t create logins or server roles, because those permissions were explicitly denied from the LimitedDBA role.

SQL-server-level-actions

In order to view all of the available server-level permissions that you can grant and deny to server roles, execute the following code. The Figure below shows the results.

SQL-partial-list-of-server-level-permissions

You can create user-defined server roles to grant users and groups a very specific set of permissions that they need to do their job, and no more. This is far more flexible than earlier versions of SQL Server, making security management far easier with SQL Server 2012, and easier management inevitably means a more secure server.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Contained Databases in SQL Server 2012

Contained databases is an interesting new part of SQL Server 2012. This isn’t directly a security feature as such, but it implements a new authentication feature and so has a security element. This chapter will not cover contained databases in detail, but briefly contained databases solves the problem of moving databases from one server to another. In the past, you had to move the database itself, in addition to server-level objects such as logins and SQL Agent job information. Getting everything configured on the destination server was a royal pain, particularly since you had to recreate server logins and remap security IDs (SIDs). Contained databases attempts to solve most of these problems.

The new authentication feature necessitated by contained databases is the ability to create a SQL user in a database with a password, or create a user associated with a Windows user without requiring an associated login.

Authentication takes place directly against the database, and a successful authentication results in a token that grants access to that database only. This serves to provide a tightly scoped and narrow security boundary around the database so that the authenticated user can only perform database-level operations.

The sample code demonstrates how contained databases works. You’ll need a contained database to try it out on, so there is also a PubsContained.sql script file that creates a contained version of the old pubs sample database. The main change, besides cleaning up some archaic statements, is how the code creates the PubsContained database:

The CONTAINMENT option set to PARTIAL results in a contained database, while a setting of NONE creates a regular database, which is the default. Using this clause creates the database and configures it to allow authentication against the database.

Before you run the script in PubsContained.sql, you have to enable the contained databases feature in this instance of SQL Server. A fresh installation of SQL Server minimizes the available attack surface for security vulnerabilities by installing some features but disabling them. Contained databases is one such feature.

The Contained Databases.sql file has the code to enable contained databases in an instance of SQL Server (it’s disabled by default). When making certain configuration changes in SQL Server, you have to enable the “show advanced” feature to allow the change, so this code sets that feature on, makes the change, and sets it back off. The relevant line of code for contained databases is the highlighted statement.

Now you can successfully execute the code in PubsContained.sql.

The code then creates a user floyd in the PubsContained database with a strong password, using the following statement. This part is what’s new in contained databases in SQL Server 2012: when a user attempts to log in as floyd, the authentication happens in the database, not at the server level.

Then you can start a new instance of Management Studio and log in as floyd (copy the password!). The log in fails, because the user floyd is not a server login and so cannot be authenticated using a traditional SQL login. In order to authenticate floyd in the PubsContained database, you need to click the Options button in the Connect to Server dialog box and set the Connect to database option to PubsContained, as you can see in the Figure below.

TIP: Don’t even try to select the database from the drop-down list; you’ve entered floyd’s login information, which doesn’t have the ability to even see which databases are available in the SQL Server instance. Your only option is to type in the database name.

SQL-Server-Entering-contained-database-to-connect-toWhen you click the Connect button, this time floyd is able to connect. At this point the user has access to the PubsContained database, but no permissions on any objects. And, as you can see in the Figure below, floyd has no access to any other server objects, so the only thing visible in Object Explorer is the PubsContained database. floyd is a very restricted user!

SWL-Server-Objects-visible-to-floyd-in-Object-Explorer

Thumbnail for 628

 

 

learnnowonline expert instructor don kielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Understanding Logins with SQL Server

screen shot 2013-05-24 at 10.25.02 amSQL Server logins are not part of Windowsthey are saved in and managed by SQL Server. A user who connects to SQL Server via a SQL Server login is prompted for a login name and password. If you select the Windows Only mode of authentication, all SQL Server logins will be disabled and users will be able to connect only by using their Windows logins.

Mixed mode with SQL Server logins is much more flexible—for example, it supports users on Windows 9x computers—but it is also less secure. SQL Server logins and passwords are saved in the system tables in SQL Server, which are file-based. Users who gain access to these files could conceivably hack administrative logins and passwords.

If you configure your SQL Server to support SQL Server logins, there is one built-in SQL Server login that you need to watch out forthe sa login.

Beware of the sa Login

You may have noticed a login named “sa” hanging around with the other logins in the Logins node in Object Explorer. The sa, or system administrator, login is included mainly for backward compatibility with older versions of SQL Server. The sa account is mapped to the sysadmin fixed server role, and anyone who uses sa is a full system administrator, with irrevocable rights over the entire SQL Server instance and all the databases in it.

You can’t modify or delete the sa login. If you select Mixed Mode authentication when you install SQL Server, you’re prompted for a password for the sa user. Unless you set a password, anyone can log in as sa with no password, and play “let’s administer the server.” Needless to say, this is the last thing you want your users doing. Use the sa login only as a backdoor if other system administrators are unavailable or have forgotten their Windows passwords. If that happens, you probably need new admins!

WARNING! Never, EVER, use the sa login for access to a database in an application. Doing so could give a hacker administration-level control over your database server if the hacker is able to get control of the application. This has been an easy way to attack servers and is a horrible practice. Instead, either set up a custom Windows or SQL Server login for the application to use, and give that login the absolute minimum permissions necessary to run the application (which is another application of the principle of least privilege).

Password Policy and Enforcement

In versions of SQL Server before 2005, there was no easy way for a system administrator to enforce password policies that could help make a system more secure. For example, SQL Server had no way to force users to create strong passwords of a minimum length and a mix of alphanumeric and other characters. If someone wanted to create a login with a single letter for a password, you couldn’t configure SQL Server to prevent it. Likewise, there was no way to cause passwords to expire on a regular basis, such as every three months. Some people rightly saw this as a major reason not to use SQL Server logins.

SQL Server now can hook into the password policies of Windows Server 2003, Windows Vista, or later versions. The passwords are still stored in SQL Server, but SQL Server makes a call into the NetValidatePasswordPolicy() Windows API method, which was first introduced in Windows Server 2003. This API function applies the Windows password policy to SQL Server logins and returns a value that indicates whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.


Thumbnail for 628

 

 

learnnowonline expert instructor don kielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Windows Logins via Transact-SQL

Transact-SQL provides full support for creating logins by using the CREATE LOGIN statement. The following example grants login privileges on SQL Server to the Windows user JaneAppDev on a machine named Willow (you’ll need to change the machine name if you want to try the code yourself). The brackets around the Windows login are required.

This statement adds the login but does not provide database access. You do that by using the CREATE USER statement. To add Jane to the AdventureWorks2012 database and make Production her default schema, add the following code:

 

You don’t have to name the database user the same as the server login. For example, you could instead name the user Jane in the database, assuming that it does not already contain a user Jane:

In this case, because the default schema wasn’t changed in the CREATE USER statement, the default would be dbo.

You can change a user account with the ALTER USER statement. For example, if you later wanted to make Jane’s default schema Production, you could use this code:

TIP: Only one user in any given database can be mapped to a single Windows login. A single login may, however, be mapped to users in many databases on the same SQL Server instance.

If you add one of the built-in Windows groups to SQL Server (Users, Power Users, etc.) use BUILTIN in the Windows login name instead of the machine or domain name. The following example grants login privileges to anyone in the Windows Users group. This means that anyone who can log in to Windows doesn’t have to log in a second time to get into SQL Server.

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server Security: The Threats

cloud-security

Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients, ranging from handheld devices to mainframe Web service applications. This activity exposes data over widely distributed networks, particularly the Internet, which makes it accessible to almost anyone, anywhere. The databases hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of installing and configuring SQL Server and developing applications that use it to store data.

SQL Server has everything you need to secure your server and data against today’s sophisticated attacks. But before you can use these security features effectively, you need to understand the threats you face and a few basic security concepts.

The Threats

Identifying threats to a particular set of data and its server is an important first step in understanding how to configure and use SQL Server to protect the data. A database you create to manage your grade school soccer team’s equipment inventory probably doesn’t require heavy security measures. You’ll probably want to provide at least minimal access control so that a team member can’t just randomly change the record of who has which box of soccer balls.

On the other hand, if the database has personal data about the minors on the team, such as home addresses and phone numbers, you’ll probably want to step up security protections (and may be legally required to do so). You might protect the privacy of the data by segregating access so that almost anyone with access to the database can change the equipment data but only a select few can access the personal data. If the data includes mom and dad’s credit card number, you’ll need to go to extreme lengths to protect that data.

TIP: Sometimes the best way to protect data is simply not to put it in the database—for example, credit card numbers.

The following list is a sample of the kinds of threats your data may be susceptible to, but it is by no means an exhaustive list. Plenty of resources are available on the Web that can help you analyze the risks for your specific situation. This list is intended to help you start thinking about threats and how to use the features of SQL Server to counter them or at least reduce your data’s exposure to them.

  • Theft of data: Theft of data covers various types of unauthorized access to your data, whether by an outsider hacking into your network or an insider scanning for dirt on famous people. It may involve the thrill of reading forbidden information or be motivated by the sale of stolen credit card numbers.
  • Data vandalism: A hacker who gains access to your data can change it, which can cause a whole range of problems, from public embarrassment to shutting down your entire operation when all of your customer records are deleted.
  • Protecting data integrity: One of the biggest benefits of storing data in a relational database is that the database can help protect the integrity of the data. Data integrity includes mandating that every order have an associated customer, that a date stored in a date field really represents a calendar date, and that a percentage field contains only values between 0 and 100. Data integrity probably isn’t the first thing you think of in connection with security, but it is an important part of protecting your data.
  • Illegal storage: In the past, the data you collected during the course of business was your own business. But now myriad federal laws exist in the U.S., throughout the European Union, and other countries that control the kinds of personal data you can store, how you store it, and how you protect it. The penalties for violations can be severe—both monetary penalties and damage to the public image of your company. This is less a threat to data than a threat to your organization.

You have to understand the threats to your data to know how to protect against them. Don’t waste time on measures that don’t protect against specific threats to your data. You’ll never be able to cover all hypothetical situations, and at worst you’ll make your database server completely unusable by its intended users. Security is always a compromise that balances the risks against the time and money necessary to implement and maintain safeguards.


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals
course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Using Snapshot Isolation

2.0-bigsqllogoAs discussed in our previous article, concurrency that is based solely on locking can cause blocking and deadlocking problems. However, reading data with READ UNCOMMITTED is not a solution for applications that require row-level accuracy when retrieving data; dirty data is generally not a good thing. To solve this problem, SQL Server includes a row versioning mechanism called snapshot isolation, which is intended to improve performance by avoiding the reader-writer blocking scenarios in the previous example.

Read operations do not request shared locks on the data, so transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level.

The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits transactions to execute without being blocked by a prior incomplete transaction.

NOTE: Two write operations do block each other even while running under row versioning-based isolation levels, because two write operations cannot modify the same data at the same time.

A unique transaction sequence number identifies each transaction. Whenever a transaction modifies a row, an image of the row before modification is copied into a page in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain.

When a user or application retrieves data, it automatically gets the last saved version of each row.

Snapshot Isolation Caveats

Snapshot isolation is not recommended for every application. Here are several reasons why READ COMMITTED might be a better choice for your application:

  • READ COMMITTED consumes less tempdb space than snapshot isolation.
  • Snapshot isolation does not work with distributed transactions that span servers.
  • Snapshot isolation is vulnerable to update conflicts that do not apply to READ COMMITTED. When a transaction that’s running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with READ COMMITTED.

Enabling Snapshot Isolation

You enable snapshot isolation per database by turning on the ALLOW_SNAPSHOT_ISOLATION database option.

This allows snapshot isolation when it is explicitly invoked, but the default READ COMMITTED transaction isolation level remains in effect for implicit transactions that do not specify snapshot isolation.

To replace READ COMMITTED entirely, turn on the READ_COMMITTED_SNAPSHOT option in addition to the ALLOW_SNAPSHOT_ISOLATION option. Snapshot isolation will then be used for all transactions.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Blocking and Deadlocks

When you modify data in SQL Server, it is important to understand how isolation levels and locking can affect performance and concurrency. The term concurrency refers to the ability of multiple users or applications to access the same data. When you update data, concurrency conflicts occur when multiple users select data and then try to update the data simultaneously. SQL Server places various kinds of locks on database objects and table rows in order to ensure data integrity.

One problem is that locks on the data can delay the updates. Another problem is that users may be able to update data that has changed since they last inspected the data. Applications often prevent this by adding a WHERE clause to the update to ensure that the update will succeed only if the data in the database still has the values that the user originally retrieved. The following article discusses Blocking and Deadlocks.

Blocking and Deadlocks

SQL Server is very efficient at applying and releasing locks. If you are modifying data using the default READ COMMITTED isolation level, locks will be applied and quickly released on each row of data. But anytime a resource is locked in SQL Server, it may cause other processes to be unable to access data necessary for the other processes to perform its actions. Two situations you have to be aware of and prepared for are blocking and deadlocks.

Blocking

Blocking occurs when locks are held for too long. When a transaction is blocked because another transaction has locks on data, it just sits there and waits. Over a network, blocked transactions will either hang or eventually time out with an error message.

You can reduce the chance of common blocking problems by avoiding user interaction in the middle of transactions, keeping data modifications short, and avoiding recursive operations and nested triggers.

Deadlocks

A deadlock occurs when two separate processes are each holding a resource that the other needs. Each process is waiting to release the resource it is holding until the other resource becomes available. Unless one of the processes is forced to yield, they will stay deadlocked forever. Deadlocks are difficult to simulate in a development environment; they seem to appear only when your database is running and many users attempt to complete the same operation at the same time.

The Figure below displays how a deadlock works. In this case, Transaction 1 has a lock on Resource 1 and needs a lock on Resource 2 to perform its work. Transaction 2 has a lock on Resource 2 and needs a lock on Resource 1 to perform its work. Transaction 1 can’t get a lock on Resource 2, and Transaction 2 can’t get a lock on Resource 1. So the transactions sit, deadlocked, waiting for the other transaction to let go of its locks.

Two transactions in a deadlockSQL Server uses an interval to determine which processes are running and which are blocked. If this interval passes twice and a process is still blocked, SQL Server chooses a deadlock victim. The victim’s transaction is rolled back, and error code 1205 is returned to the client application of the losing transaction. Your error handling routine in the client application can test for error 1205 and resubmit or cancel the query. If SQL Server did not select a deadlock victim, then eventually your server would run out of available processes and crash.

To avoid deadlocks, access objects in the same order every time, so that if a process is blocked it goes into a wait queue until the process that is holding the lock is complete.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Updating Large Value Types with UPDATE .WRITE

SQL Server provides an UPDATE .WRITE Transact-SQL statement to perform partial updates on columns that are defined as varchar(max), nvarchar(max), or varbinary(max). Here is the abbreviated syntax:

The following example uses the Production.Document table in the AdventureWorks database to demonstrate substitution of the word “critical” for the word “important” in one row of data with a DocumentNode value of 0x5B40.

First, select the row to view the existing data:

The first sentence in the results should look like the Figure below (you’ll need to expand the width of the column to view the entire text of the column).

Updating Large Value Types with UPDATE .WRITE

To change the word “important” to the word “critical” you need to specify the offset (the number of characters from the start of the string to the value you want to replace) and the length of the string to replace.

If you execute the SELECT statement again to view the current value, you’ll see the results as shown in the Figure below.

Updating Large Value Types with UPDATE .WRITE

Execute the following statement to restore the original value:

You can also use the CHARINDEX and PATINDEX functions to calculate the offset location of the substring you want to change. The LEN function returns the number of characters in a string, so the following statement performs the same replacement of ‘critical’ for ‘important’. (In the sample code file, these statements are wrapped in a transaction that is rolled back, so you don’t have to worry about executing another UPDATE statement to undo the change.)

WARNING! In past versions of SQL Server, WRITETEXT was used to replace sections of long text values. SQL Server 2012 still supports this statement, but it has been deprecated and will be removed in a future version of SQL Server. Therefore, you should always use the new .WRITE clause of an UPDATE statement rather than using WRITETEXT.

 

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Get Ahead with Visual Studio

url3

 

Being able to use a foreign language well, is not only useful in your personal life, it is also incredibly attractive to employers. Foreign languages are not just those that are spoken every day around the world, however. Foreign languages also include computer languages. This is why a working knowledge of Visual Studio can put you out ahead of your job competition in a big way.

Visual Studio supports a wide range of programming languages, making it extremely flexible with regards to programming networks and setting up inter-office systems. An online Visual Studio tutorial video can help you become familiar with the program quickly, so that you can begin to put your knowledge to use immediately. Additionally, a C# video course will help you familiarize yourself with one of the languages supported by Visual Studio. Your newly acquired skills and knowledge will make you more attractive to potential employers, and increase your worth in your current position. So, take the time to learn a computer-based foreign language. You never know where it might take you.

Thumbnail for 624

 

Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.