Ladder for SQL Server security Level 1: SQL Server Security overview

by Don Kiely, 2014/06/04

reprinted from: http://www.sqlservercentral.com/articles/Stairway+Series/109941/

This series

This article was part of the stairway Series:stairway to SQL Server Security

Relational databases are used in a wide variety of applications, through a widely distributed network, especially the Internet, from dazzling client connections, so that almost anyone can access the data anywhere. The database can hold a large part of human knowledge, including highly sensitive personal information and key data for the operation of international commerce.

These characteristics make the database an attractive target for those who intentionally steal data or tamper with the data. Ensuring data security is a critical part of configuring SQL Server and developing applications that use it to store data. This ladder explores the fundamentals of SQL Server 2012 security so that you can protect your data and server resources as thin as possible to prevent unique security threats that may affect your data. Most of the information will apply to earlier versions of SQL Server, back to SQL Server 2005, because that's where Microsoft revolutionized security in the product. But I will also discuss features only in SQL Server 2012 and later versions.

SQL Server provides everything you need to protect your servers and data from today's complex attacks. However, before you can effectively use these security features, you need to understand the threats you face and some basic security concepts. This first ladder level provides a foundation so that you can take full advantage of the security features in SQL Server without wasting time on features that do nothing to prevent specific threats to your data.

Threat

Identifying threats to specific datasets and their servers is an important first step in understanding how to configure and use SQL Server to protect your data. The database that you create to manage the equipment inventory for your school football team may not require stringent security measures. You may want to provide at least minimal access control so that team members cannot arbitrarily change the record of which box of football they have. But if someone comes in to steal or cheat, I'm afraid it's not the end of the world.

On the other hand, if your database has personal data about the children in your team, such as your home address and phone number, you may need to tighten security (and may require it legally). You can protect the privacy of your data by isolating access so that almost anyone accessing the database can change device data, but only a few people can access personal data. If the data contains a parent's credit card number, it will take a great deal of time to protect the data.

The data may be vulnerable to some threats, and the following list is some of the more common threats. There are plenty of resources on the web to help you analyze the risks of your specific situation. This list is designed to help you start thinking about threats and how to use the features of SQL Server to deal with them, or at least reduce the exposure of your data.

' Data theft: Theft of data includes various types of unauthorized access to your data, whether by outsiders invading your network or internal people scanning the dirt of a famous person. This may involve reading about the excitement of banning information or the motive for the potential sale of stolen credit card numbers.
' Data corruption: Hackers can access your data, which can lead to a range of issues, from public embarrassment to shutting down the entire operation (which may occur when all of your customer records are deleted).
' Data corruption: one of the greatest benefits of storing data in a relational database is that the database itself can help protect the integrity of the data. Data integrity includes customers that force each order to be associated, the date stored in the Date field does represent the calendar date, and the percent field contains only values from 0 to 100. Data integrity may not be the first thing you think about security, but it is an important part of protecting your data.
' Illegal storage: In the past, the data you collect in your business process is actually your own business. But now the United States, the entire European Union and other countries have a variety of federal laws that govern the types of personal data you can store, how they are stored, and how they are protected. Penalties for violations may be severe, including fines and damage to the company's public image.

This staircase covers the features in SQL Server 2012, helps mitigate these threats, and so on. You must understand the threats to your data in order to know how to protect against these threats. Don't waste your time on measures that don't protect against specific threats to specific data. You will never be able to cover all of the hypothetical scenarios, and the worst case scenario is that your database server is completely unusable by its intended users. Security is always a compromise that balances risk with the time and money required to implement and maintain safeguards.

Safety design Concept

In early 2002, Bill Gates issued a notorious "trustworthy computer" memo, which proved to be a turning point in Microsoft's thinking and achieving product security. According to the Microsoft Web site, the resulting trusted computing program "looks at creating and delivering a secure, private and reliable computing experience based on sound business practices." Our goal is to build a safer, more reliable Internet. “

In other words, more than 10 years ago, Microsoft attached great importance to security issues. The first version of SQL Server that was released after this change was the SQL Server 2005,microsoft that completely changed the security of the entire product. Since then, each version of SQL Server has retained this new security infrastructure, while gradually enhancing and adding new features to improve security and address emerging threats.

As Microsoft described in SQL Server 2005, trusted computing affects the development of SQL Servers by defining four pillars of product security design:

' Through design security: Microsoft has conducted extensive threat analysis and security audits of its design, code, and interactions to determine the point at which attackers can gain access to servers and data. As a result, Microsoft has designed SQL Server to protect the confidentiality, integrity, and availability of the data that you store there.
' Is secure by default: Out of the box, SQL Server installs and activates the critical core database components by default. This means that features that are not critical to the core database functionality are either not installed or are installed but not activated. Features that are not installed are not susceptible to attack. You must consciously intentionally install or activate non-core features. This prevents many attacks against features that you may not know about, because you never use them.
' Secure Deployment: Microsoft provides tools and support for securely installing SQL Server and ensuring its security. The SQL Server Configuration tool helps you to configure the server securely. Most importantly, updates to SQL Server are now available as part of the online Microsoft Update Service, so it's easy to get security updates and patches.
' Secure through Communication: Microsoft has established a complex infrastructure to  about its product vulnerabilities. But all this information is worthless if it stays in Microsoft. As a result, the company is committed to communicating new vulnerabilities, aggressively patching them to prevent these vulnerabilities, and regularly updating its online Help system to reflect new security information.

This "just safe" concept has an impact throughout the product. However, while SQL Server is fairly secure out-of-the-box, when you create databases and install services, you must make smart security choices to keep the SQL Server instance secure. Need work and vigilance to ensure the security of the production database server.

And remember, sometimes the best way to protect your data is not to put it in a database, for example, if you have an absolutely effective need to store credit card numbers (if not, many of them). If you do not have such a requirement, you should process the credit card transaction and store the results instead of the credit card information itself. You cannot encounter security breaches without data!

Two phases of security

The SQL Server security model, like Windows, is a two-stage process that allows users or other logins to access protected resources on the server:

' Authentication: User login, verify and gain access to the server. Authentication answered, "Who are you?" "This issue, and requires the user to prove this, is usually proved by providing a password for the user name, while other forms of proof are becoming more popular.
' Authorization: Users can interact with only those SQL Server objects that she has permission to, such as databases, tables, and stored procedures. Authorization to answer the question: "What can you do?"

Users may be able to log on to SQL Server, but these operations will not be good unless they have permission to perform certain operations, such as accessing the database. Therefore, not only do you need to provide the user with authentication credentials, but you also need to authorize them to access the data by defining the accounts for them in each database and then authenticating them.

When you think about this, it's interesting to have a user account in every database. Otherwise, the user is prevented from logging on to SQL Server and then using any of the databases that they want? This concept has a variety of adaptation scenarios, but this is the basic state of security.

Subsequent levels in this ladder include authentication and authorization, and you will learn how to implement various security features in SQL Server to prevent threats that have been identified to your data.

SQL Server Security Terminology

When you use security in SQL Server and other products, you encounter a variety of professional terminology. The following are some of the more common terms and their implications for database security.

Authentication:As described above, authentication is the process of identifying an identity by requiring her to prove that she is the person associated with the login. It answered the question, who are you?

Authorization:Once the system authenticates the user, authorization (as described above) determines the user's permissions in the server or database. This answers this question, what can you do now that you are here?

Group:In Windows, a group is the principal of the login associated with it. Any permissions granted to the group are granted the associated login name.

Simulation:All Windows processes, including various SQL Server processes, run in a specific security context, usually the principal that causes the process to start. Impersonation occurs when a process temporarily takes a different security context. This is a very powerful and necessary capability, but with the possibility of abuse.

Login:A login is a principal that has some level of access to an object in the server instance. In common usage, logins are often used interchangeably with the user. However, SQL Server logins are accounts that are used to access the server from outside. Logins sometimes include permissions to access server-scoped objects, such as configuration information, but typically do not give any permissions to the database.

Permissions:Permissions are permissions to access a protected resource, such as reading data from a table or creating a new database at the server level. Permissions usually imply additional permissions, depending on the scope of the subject permission.

Principal:A principal is any user or code component that can receive access to a protected resource in SQL Server.

Privilege: A privilege is a broad right or license owned by a principal. This word can sometimes be used in exchange with a license, and more often means a specific narrow right. A privilege implies a broader set of permissions.

role:SQL Server roles are similar to Windows groups, but are limited to the scope of an instance of SQL Server. Just like a group, you can assign logins and users to a role that passes all the permissions that the role has to the login and the user.

User:A user is a principal that has some level of access to an object in a particular database. Users are typically mapped to logins. In short, logins can access the SQL Server instance and the user can access the data.

You will see most of the terminology used in this ladder.

Management and implementation SQL Server Security

As with most administrative and administrative tasks in SQL Server, there are almost always several ways to manage and implement security features, including the use of Management Studio's graphical interface to write and execute T-SQL code, As well as using SQL Server with PowerShell to integrate command-line garbage between us. Using PowerShell is not within the scope of this staircase, but on the entire staircase you will see a large number of examples using the other two techniques. (To learn about tight integration between PowerShell and SQL Server, look at the steps for SQL PowerShell.) ）

To use Managementstudio for security functions, you typically simply right-click the object in Object Explorer and select Properties, and then use the corresponding page in the dialog box to make the desired changes. For example, to set permissions for the ADVENTUREWORKSLT2012 sample database, right-click the database name in Object Explorer, select Properties, and then select the Permissions tab, shown in 1.1.

Figure 1.1: Permissions to modify AdventureWorksLT2012 Database Security page

At the database and server instance levels, Object Explorer includes a security node that allows you to manage and implement other kinds of security features. Figure 1.2 Shows this node of the ADVENTUREWORKSLT2012 database and the server instance level. Each highlighted node contains a variety of child nodes that allow you to access securable objects that are compatible with the selection range.

Figure 1.2: Security objects at the server and database level in Object Explorer.

You'll find a variety of security features hidden in Managementstudio and its various windows, so it's worthwhile to do some exploring when you hit them.

One of the changes introduced in security checks in SQL Server2005 is the ability to assign permissions in a more granular manner. As you will learn at a later level, you can assign multiple permissions to a variety of principals in order to implement the important security principals of least privilege-each subject has the right to perform the tasks that they need to accomplish: no less. With this change, Microsoft has strengthened T-SQL to include strong support in languages to manipulate security objects directly.

The code in Listing 1.1 shows a simple example of a T-SQL that you can use to create a securable object. The code first creates a server-level login linked to an existing Windows login. Then, in the ADVENTUREWORKS2012 database, the code creates the user that maps to the login and assigns the user the default mode. The last part of the code shows how to drag and drop the user into the database and create another user that maps to the same login name. Of course it's very artificial, but it shows what you can do in the code. Or in the GUI tools of Management Studio, if you like.

--Add a Windows login to SQL Server

CREATE LOGIN [Marathon\joestairway] from WINDOWS;

GO


Use AdventureWorks2012;

GO


--Name the user the same name as login

CREATE USER [Marathon\joestairway] for LOGIN [Marathon\joestairway]

with default_schema = Production;

GO


--or, rename the user in the database

DROP USER [Marathon\joestairway];

GO

CREATE USER Jane for LOGIN [Marathon\joestairway];

GO


--Query metadata to show the user was created

SELECT * from sys.database_principals WHERE name = ' Marathon\joestairway ';

SELECT * from sys.database_principals WHERE name = ' Jane ';

Listing 1.1: Sample code for creating server logins and database users

If you want to try to run this code, you may need to make some changes first. Unless you are using a computer named Marathon, and the existing user name is Joestairway, you will need to change these names in your code. The code creates the database user in AdventureWorks2012 and uses its production mode, so you need to install the database or change the code to use other databases and existing schemas. However, you may want to install the sample database for the AdventureWorks Suite, as this stairway will often use them.

Profile

The first layer of this ladder provides an overview of the basic security concepts in SQL Server 2012. You've seen some of the more common data threats and explored the design behind SQL Server security. You learned about the two phases of security-authentication and authorization, the security terminology you'll see throughout the ladder, and the ability to manage and enforce security through Management Studio's GUI tools and T-SQL code.

At the next level, you will learn more about using authentication and the available authentication options in SQL Server.


Infringement must be deleted!!!

Ladder for SQL Server security Level 1: SQL Server Security overview