Understanding the permission system in SQL Server (I)-Subject

Introduction

Permission: one word, one power, and one restriction. In the software field, the common explanation is who can perform operations on which resources. In SQL Server, "who", "what resources", and "What operations" correspond to the three objects in SQL Server as principal ), security object (securables) and permission (permissions), while power and restriction correspond to GRENT and deny in SQL Server. A preliminary understanding of the subject, security object, and permissions is shown in Figure 1.

Figure 1. A brief understanding of the relationship between subjects, security objects, and permissions

 

For the sentence in Figure 1, there is no subject, that is, no one gives permissions (is it God ?). You can understand that the SA account granted permissions to security objects to other subjects at the beginning.

 

SQL Server Verification Method

Before giving permissions to others or checking whether you have certain permissions, SQL Server must first know whether the subject "you" is the one you claim. For example, in martial arts novels, There is a dark code "the King of Heaven is a tiger, and the Baota river demon ..." Is a way to authenticate your identity. For SQL Server, when you connect to SQL Server, SQL Server needs to confirm who you are. SQL Server provides two authentication modes:

 

Windows Authentication Mode

Windows Authentication mode is used to verify the subject, as shown in its name. SQL server does not participate in authentication. SQL Server fully believes in the Windows verification results. Therefore, you do not need to provide a password when logging on to SQL Server. However, the Windows Authentication mode is more secure, because Windows Authentication mode uses Kerberos (the term is derived from the Greek mythology "Three-headed dog-the gate of hell Guardian") protocol. This is also the safest practice recommended by Microsoft.

However, the Windows Authentication mode is used only when the domain controller controls network access (of course, a single host is also included ).

 

SQL Server and Windows Authentication Mode (Mixed Mode)

I have always thought that the name of this mode should be changed to SQL Server or Windows Authentication mode, which is easier to understand. This mode allows Windows to verify the identity of the subject and SQL Server to verify the identity of the subject. When SQL Server verifies the identity of the subject, the user name and password are required to confirm the identity of the subject, it has nothing to do with what Windows account is used. The user information is encrypted and stored in the master database.

 

Set Verification Mode

It is very easy to set the verification mode. You can either set it during installation or right-click an instance after installation, select properties, and change it on the Security tab, as shown in figure 2.

Figure 2. Change the authentication method after installing SQL Server

 

Understand the subject

"Subject" is an entity that can request SQL Server resources. The subject can be an individual, group, or process. Subjects can be divided into three categories according to their scope of action:

• Windows subjects
• Server-level Subject
• Database-level Subject

 

Windows-level subjects include Windows domain logon names and Windows Local logon names.

SQL Server-level subjects include SQL Server login names and server roles.

Database-level subjects include database users, database roles, and applicationsProgramRole.

 

Login Name

The login name is a server-level subject. However, no matter which level of the subject is described above, You need to log on to the SQL server instance. Therefore, each level of the subject requires a corresponding login name. For Windows-level entities, Windows users map to login names. For a database-level entity, the user must be mapped to the login name. Login names can not be mapped to database users, as shown in 3.

Figure 3. login name ing

 

In the username at the instance level in Figure 3, we can see some usernames added by the system in addition to custom users. First, the user starting and ending with "#" is an internal account used by SQL Server, created by the certificate and should not be deleted. The second is the SA account. The sa Login name has all the privileges and can do whatever you want on SQL Server and cannot be deleted. Therefore, sa serves as the starting point for permission allocation (that is, the subject mentioned in Figure 1). Therefore, the SA password should be set as complicated as possible. Otherwise, the consequences of sa Login Name theft are unimaginable. There are also nt authority \ Network Service and nt authority \ system accounts which are related to the account used to start the Windows Service of SQL Server. If you use a local Logon account or a network account to start the SQL Server service, do not delete these two accounts, as shown in figure 4.

Figure 4. Start the service with a local system account

 

Finally, the buildin \ Administrator account is the login name associated with the local administrator group. By default, it belongs to the SysAdmin role. This account allows any account belonging to the local administrator to have full control over SQL Server.

 

Database User
A database user is a database-level entity and is used to access objects at the database level. Each database user must have a pair of login names. The database user information exists in the database, while the login name exists in the master database of the instance level (however, the contained database of SQL server2012 allows the login name to also exist in the Database level ). Generally, database-level users can be different from the mapped login name, but this method can cause confusion and is not recommended. 5.

Figure 5. the username and logon name can be different, but are not recommended.

 

By default, each database has four built-in users, as shown in figure 6.

Figure 6. built-in users in the database

 

DBO users are short for database owners. If SA is the instance-level boss, DBO is the database-level boss. This user cannot be deleted, and every server role belonging to SysAdmin is mapped to the DBO user of the database. If no schema is specified when a table is created, it is in the DBO schema by default.

The Guest user is a guest account, which allows the login name to access the database without being mapped to the database user. By default, the guest user is disabled.Code1 to enable or disable the Guest user.

 
-- Allow the Guest user to connect to grant connect to guest -- revoke the guest connection permission revoke connect to guest
 

Code 1. Enable or revoke the connection permissions of the guest user

You can also assign a role to the guest user to control the permissions of the guest (7), but this may cause potential security problems. The best practice is to create a database user separately.

Figure 7. assign a role to a guest user

 

Information_schema users and SYS users have system views, so these two database users cannot be deleted, as shown in figure 8.

Figure 8. information_schema and SYS are used to access the system view

 

Role

Role is a convenient way to manage the subject. Roles in SQL Server and user groups in windows are a concept. A user or login name that belongs to a role has the corresponding permissions, which is not hard to understand. Just like you are a manager in the company, you can reimburse the mobile phone fees. Developers at a lower level than you don't have this offer. A user or login name can belong to multiple roles, which is also not difficult to understand, just as you can be a project manager or a senior engineer in the company.

Roles in SQL Server are divided into three types:

Built-in roles-these roles already exist by default during Server Installation. Their permissions are fixed and cannot be deleted.

User-defined roles-these roles are created by users as needed

Application roles-these special roles are used to manage Application Data Access

 

The built-in roles are fixed when installing SQL Server. The corresponding permissions are fixed for both server roles and database roles. For details about the permissions of each role, see msdn (fixed server role logon). However, pay attention to a special role: public role.

Different from other roles, the public role's permissions can be adjusted, as shown in figure 9.

Figure 9. The public role is different from other roles in that its permissions can be modified.

 

The public role can be understood as the minimum permission to access the database or instance. the permissions of the public role are automatically inherited by any entity. Therefore, you must be careful when modifying the permissions of the public role.

 

User-defined roles are created by users based on their own needs.

The application role does not include any user. The application role is not so much a role as a special user. This role is a dedicated role for applications and only provides database access permissions for applications. This role is not provided to the user, but is embedded into the role name and password by the application connection string to activate the corresponding permissions.

Figure 10. Unlike other database roles, the application role requires a password

 

Understanding Architecture

The architecture (schmea) was introduced in Versions later than SQL Server 2005. The architecture can be understood as a namespace. In SQL Server2000, there are also architectural concepts, but they are different. Because the architecture in SQL Server 2000 is bound to the user, for example, when I create a user Jack, SQL Server automatically assigns a framework called Jack, which cannot be changed, any object created by Jack is under Jack. For example, if you create a new table, it is Jack. table1. When Jack leaves, this is a nightmare for management.

After SQL Server 2005, SQL Server allows you to separate the user from the architecture. This allows the architecture to have some database-level objects, such as tables and views.

The following methods are used. For example, if my default architecture is sales, I can use the first method in Code 2 without the architecture:

 
Select * From customerselect * from sales. customerselect * From adventureworks. Sales. Customer
 

Code 2. Several Different Methods for referencing objects

 

Therefore, if the customer table is created by Jack, I can allocate it to the sales architecture and use sales. Customer instead of jack. Customer for reference. This makes management much easier. In addition, you can set permissions for the structure.ArticleAs mentioned in subsequent articles.

 

Summary

This article briefly describes the SQL Server permission system. And the concept of the subject. To understand the security of SQL Server, you must first understand these three aspects. In the next article, I will talk about security objects.