SQL Server: security design starts from scratch

SQL Server: security design starts from scratch

8:12:42 Source: dynamic website production guide author: Anonymous

[For Me] [recommended] [print] [closed]

Developers and administrators often constantly consider program security policies-adding security measures is no longer a completely acceptable method. Program security issues must be considered from the beginning of the design, and throughout the development process. If you want to develop an SQL Server database in the future, you must follow these security design rules from the beginning. Basic Points If you cannot understand the basic concepts of SQL Server Security, stop development and read the development principles first, you cannot properly secure a database without knowing these concepts. Program security is just like a truck. You have an engine and a key. when the key is turned on, that is, the engine starts up, the entire process may occur. If you ignore some details, there will be a lot of trouble while driving. After the problem arises, you can hand over the truck to a repairer. However, for program security problems, you will be the database repairer. First, you must select the following two security modes: L Windows Authentication Mode: You can use a ready-made Windows User Account to connect to the server. When a user tries to connect to a server, SQL Server authenticates the user's Windows account name and password. Users cannot access the network and SQL server at the same time, but can only access one of them. This method is also called a dependent connection. L hybrid mode: This mode connects Windows Authentication Mode with SQL Server Authentication. You can use a Windows User Account to connect, just like Windows authentication. However, you can also create a user account in SQL Server. Each SQL Server account stores a user name and password. We recommend that you use Windows Authentication mode whenever possible. However, hybrid mode is quite usable, especially in SQL Server 7.0 (or earlier versions .. For earlier SQL Server versions, SQL Server Authentication has some disadvantages. Windows authentication has integrated the security system of the operating system, which provides more security features than SQL Server authentication, which are easy to use, more efficient, and more secure. At the beginning of the design, you should carefully consider which mode is the most suitable. Password No matter which mode you use, remember to set a password for the System Manager (SA) on SQL Server. When installing SQL Server, the installer automatically creates a management user with the SQL Server Registration Name (SA) and a blank password. If you use the hybrid security mode while keeping these users set as they are, any user with a little basic knowledge of SQL Server can easily access your database and do whatever he wants. If you are using Windows Authentication mode, theoretically you do not need to set a password for the SA user, because SQL Server registration does not accept these settings. However, setting is a good operation, especially when you are forced to move to the hybrid mode that will be used in the future. If the security mode is enabled, registration is critical. You must use the correct method to start the program, and the registration is the same. If you cannot enter the user name and password correctly, the program cannot connect to the network or SQL Server. As a manager, once you have started the program, you are ready for security management. You can achieve this by defining the following features: L user (account): An SQL Serve security account represents a unique user. A user also has only one Windows Account or one SQL Server registration, which is consistent with the user account in the database. L group (account): each user belongs to one or more groups, which is determined by the authentication mode. Each group has a specific license. As a member of a group, you will be granted permissions to all groups. L object ownership: the ownership belongs to the user who created the object. The owner can assign the access permission to the user. If you are the owner of a view, you can also decide which users can view data through the view. L license: A license represents the right to perform certain operations, such as opening a view or changing a storage program. SQL Server recognizes three states of permission: grant to give you a user access; Revoke deletes access; deny prevents users from accessing objects. L task: This is an SQL server security account. You can process the set of accounts as a simple unit. A task defines what users can do and what they cannot do in a specific database. Security considerations   The design process should effectively define where security settings are required and how to set them. Before this process, you should consider two aspects: L sensitive data; L users who can view sensitive data. Sensitive data includes all possible data, including all data in the entire database, although such a security level rarely exists. Your job is to define sensitive data and protect it. The authentication mode you select and the registration you create will limit which users can enter the database to implement the first step of security steps. Step 2: List all users who can access the database and decide whether all data is applicable to all users. Generally, you need to protect some data, such as salary or other private data. This means that only specific users can access and view data. You can also set which users change data. A rule that is always remembered is the concept of "minimum right. If someone does not need to access data in his work, do not grant him access permissions. All users should be prevented from having access permissions of SA users. Specific suggestions Experience is also a good teacher for security settings, but there are usually design guidelines for general databases: L obtain the ownership of the database and objects from the beginning. When creating a new database, you will become the database owner and be able to set everything in the database. You can register a database as a manager. However, the ownership of an object belongs to the user who created the object. Although this may cause a transfer of ownership, it can be determined that these registration can be used to create all objects. L understand the ownership chain. This security feature prevents users from setting up their own view to peek at some sensitive data. For example, if you create a view of the data in two tables, if you are the owner of the two tables, when you allow other users to use the permission of the view, SQL Server does not check related permissions. L use the view and storage program to assign users the right to access data, rather than asking users to write special query statements that directly access tables. In this way, you do not need to allocate access rights to users in the table. The view and storage program can also restrict the data to be viewed. For example, if your employee table contains confidential salary information, you can create a view that skips the Salary Bar. L if a user enters your program from a specific program, you can create a program task. A program task is assigned to a user of a specific program, and related permissions are granted to the user. When using program tasks, users cannot directly authenticate the database. On the contrary, they first authenticate their own programs, which determines which program tasks are connected to the server. L always pay attention to Program Patches. It is undeniable that patch programs are a collection of tricks. New problems will be introduced in the release and update of programs. Using Program Patches is the best and easiest way to prevent external interference and protect data. You can visit the SQL Service Pack download page on the Microsoft homepage to view the latest patch. Conclusion Program security is a problem that every developer must face. Do not wait until the database is developed and used to take security into account-security is an important part of the design process. In addition, do not randomly apply security settings to achieve the best effect. You should learn to select a security mode and apply it correctly.