SQL Server permissions System (top)

Introduction

Permission two words, one power , one limit . The popular explanation in the field of software is what people can do with which resources . In SQL Server, "who", "What Resources," "What actions" correspond to three objects in SQL Server, respectively, body (principals), securable object (securables), and Permissions (Permissions), The power and restrictions correspond to grent and deny in SQL Server. For a preliminary understanding of the subject, securable objects and permissions, see Figure 1.

Figure 1: A simple understanding of the relationship between subjects, securable objects, and permissions

For the sentence in Figure 1, there is no subject, that is to say, who gives permission (God?). You can understand that the SA account gives other principals permissions on securable objects at the very beginning.

how to verify in SQL Server

Before giving permission to others, or checking if you have a permission, SQL Server first knows whether the subject of "You" is what you claim to be. For example, the secret code of the "King of the Tiger, Pagoda Town River Demon ..." is a way to verify your identity. For SQL Server, SQL Server needs to confirm who you are when you connect to SQL Server. SQL Server provides two modes of authentication:

Windows Authentication Mode

Windows Authentication mode, as its name implies, is verified by Windows, and SQL Server does not participate in validation. SQL Server fully trusts the results of Windows validation. Therefore, you do not need to provide a password to log in to SQL Server in this way. However, Windows Authentication mode is more secure because the Windows Authentication mode uses Kerberos (the term derives from the Greek mythology "three-head dog-the gate Keeper of Hell") protocol. This is also the safest approach recommended by Microsoft.

However, Windows Authentication mode is used when the network access is controlled by a domain controller (of course, a single machine is included).

SQL Server and Windows Authentication modes (mixed mode)

I have always felt that the name of this pattern should be easier to understand than SQL Server or Windows Authentication mode. This mode allows Windows to authenticate the principal and allow SQL Server to verify the principal identity, and when the principal is authenticated by SQL Server, a user name and password are required to confirm the principal identity, and the use of what Windows account does not have a half-dime relationship. These user information is encrypted and exists in the master database.

Setting the validation mode

Setting the validation mode is straightforward. Can be set at the time of installation, or after the installation by right-click on the instance, select Properties, in the Security tab to change, 2.

Figure 2: Changing the authentication mode after installing SQL Server

Understanding the subject

A "principal" is an entity that can request a SQL Server resource. The subject can be an individual, a group, or a process. The subject can be divided into three categories according to the scope of action:

• Windows-Level Principals
• Server-level Principals
• Database-Level Principals

Windows-level principals include the Windows domain logon name and the Windows local logon name.

The SQL Server-level principals include SQL Server logins and server roles.

The database-level principals include database users and database roles, as well as application roles.

Login Name

The login name is a server-level principal, but regardless of which level of the principal is required to log on to the instance of SQL Server, each level of the principal requires a login name corresponding to it. For Windows-level principals, the Windows user maps to the login name. For database-level principals, their users must be mapped to the login name. The login name can not be mapped to the database user, as shown in 3.

Figure 3: Mapping relationships for logins

In the instance-level logins in Figure 3, we see that there are additional logins added by the system in addition to the custom additions. First, the user who begins and ends with "# #" is an account that is used internally by SQL Server and is created by a certificate and should not be deleted. followed by the SA account, the sa login has all the privileges to do whatever it wants in SQL Server and cannot be deleted. So SA serves as the starting point for assigning permissions (that is, the subject in Figure 1). Therefore, for the SA password to be set as complex as possible, otherwise the sa login will be the result of a catastrophic theft. There are also NT Authority\Network SERVICE and NT AUTHORITY\SYSTEM accounts that are related to starting the SQL Server account for this Windows service, if you start the SQL Server service using a local login account or a network account, Please do not delete these two accounts, as shown in 4.

Figure 4: Starting a service with a local system account

The last Buildin\administrator account is the login name associated with the local Administrators group, which is the sysadmin role by default. This account allows any account that belongs to the local administrator to gain full control of SQL Server.

Database user
A database user is a database-level principal that is used to access database-level objects. Each database user must have a login name to use. The database user's information exists in the database, and the login name exists in the master database at the instance level (but SQL SERVER2012 contained database allows the login to also exist at the DB level). Typically, database-level users can be inconsistent with mapped logins, but this practice is not recommended because of confusion. As shown in 5.

Figure 5. You can make the user name and login inconsistent, but it is not recommended

By default, each database comes with 4 built-in users, as shown in 6.

Figure 6. Built-in user with database

The dbo user is the short name of database owner, and if SA is the boss at the instance level, then dbo is the oldest at the DB level. This user also cannot be deleted, and each server role belonging to the sysadmin will be mapped to the DBO user of the database. When each table is created without a schema specified, it defaults to the DBO schema.

The guest user is a guest account that allows the login to access the database without mapping to the database user. The Guest user is not enabled by default, and you can enable or not enable the guest user through the code.

--Allow Guest user connection permissions grant connect to guest--Reclaim Guest's connection rights revoke connect to Guest

Code 1. Enable or Reclaim connection permissions for guest users

You can also assign a guest user a role to control the permissions of Guest (7), but this may pose a potential security issue, and it is a best practice to create a separate database user.

Figure 7 Assigning roles to guest users

The INFORMATION_SCHEMA user and SYS user have a system view, so these two database users cannot be deleted, as shown in 8.

Figure 8.information_schema and SYS for accessing system views

Role

Role is an initiative to facilitate the management of the subject. roles in SQL Server and user groups in Windows are a concept. Users or logins that belong to a role have the appropriate permissions, which is easy to understand, like how much money you can charge for your mobile phone when you're a manager at the company. Developers who are one level lower than you do not have this treatment. It's also easy to understand that a user or login can belong to multiple roles, just as you can be a project manager in your company and a senior engineer as well.

Roles are divided into three categories in SQL Server, namely:

Built-in roles----These roles have been installed by default at the time of server installation, their permissions are fixed, and cannot be deleted

User-defined roles----such roles are created by the user customized to the requirements

Application roles----Such special roles are used to manage data access for applications

Built-in roles are fixed when SQL Server is installed, whether it is a server role or a database role, and the corresponding permissions are fixed. For specific permissions for each role, see MSDN (fixed server role http://msdn.microsoft.com/zh-cn/library/ms175892.aspx, fixed database role/HTTP msdn.microsoft.com/zh-cn/library/ms189121.aspx), but here's a special role to note: the public role.

The public role differs from other roles, and its permissions can be adjusted, as shown in 9.

Figure 9. The public role differs from other roles in that their permissions can be modified

The public role can be understood as the minimum permission to access a database or instance, and the permissions owned by public are automatically inherited by any principal, so be cautious about permissions modifications to the public role.

A user-defined role is a role that is created by the user in accordance with their own needs.

And the application role does not contain any users, and the application role is more of a special user than a role. This is a specially prepared role for the application, providing only database access to the application. This role is not provided to the user, but rather the application's connection string embeds the role name and password to activate the corresponding permission.

Figure 10: Unlike other database roles, the application role needs to set the password

Understanding the Architecture

The architecture (SCHMEA) is introduced in versions after SQL Server 2005. The architecture can be understood as a namespace. In SQL Server2000 There is also the concept of architecture, but the concept is different. Because the architecture in SQL Server 2000 is bound to the user, such as my new user Jack,sql Server automatically assigns a jack architecture, the user jack does not change this option, and any object built by Jack is under Jack, such as creating a new table, is the jack.table1. When Jack leaves, it's a nightmare for management.

After SQL Server 2005, SQL Server allows the user to detach from the schema. Make use of the framework to have some database-level objects, such as: tables, views and so on.

Here are a few options, such as when my default architecture is sales, I can use the first one in code 2 without architecture:

SELECT * from Customerselect * from sales. Customerselect * from AdventureWorks.sales.Customer

Code 2: Several different ways of referencing objects

So, if the Customer table was built by Jack, I could assign it to the sales framework and use Sales.Customer instead of Jack.customer when referencing. This is undoubtedly much easier to manage, and in addition, you can set permissions on the structure, which I'll cover later in this article series.

Summary

This article simply describes the SQL Server permissions system. And the concept of the subject. Understanding the security of SQL Server should first understand these three aspects. In the next article I'll go on to the security object.

SQL Server permissions System (top)