SQL Server: Security design from scratch

Basic points if you don't understand the basic concepts of SQL Server Security, stop developing and read these development guidelines first, and you can't safely make a database safe without knowing these concepts. The security of the program is just like a truck. You have an engine, a key, all the possible processes that occur when the key is turned on and the engine is started. If you ignore certain details, there will be a lot of trouble during the driving. After the problem arises, you can give the truck to a repairman, but for the safety of the program, you will be the database repairman. To begin, you must select the following two security modes: Windows Authentication mode: Users connect to the server through a ready-made Windows user account. When a user attempts to connect to a server, SQL Server authenticates the user's Windows account name and password. Users cannot enter the network and SQL Server at the same time. This method is also referred to as a dependent connection. Blending mode: This mode joins Windows Authentication mode with SQL Server authentication. Users can connect through a Windows user account, as is the case with Windows authentication. However, you can also create a user account directly in SQL Server. Each SQL Server account has a username and password stored. We recommend that you use Windows Authentication mode when possible. However, mixed mode is very easy to use, especially in SQL Server 7.0 (or earlier). For earlier versions of SQL Server, there are some drawbacks to SQL Server authentication. Windows certification has integrated operating system security systems, providing more security features than SQL Server authentication, easy to use, more efficient and more secure. At the beginning of design, you should seriously consider which mode is the best choice. Password whichever mode you use, you should remember to set a password for the System Manager (SA) in SQL Server. When you install SQL Server, Setup automatically creates an administrative user with a SQL Server registration name (SA) and a blank password. If you keep these user settings intact and use mixed-security mode, anyone with a little SQL Server basics can easily get into 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. But setting is a good operation, especially if you are forced to move to a hybrid model that you will use in the future. If the securityThe mode has been started and registration will become critical. You must use the correct method to start the program, registration is the same, if you do not correctly enter the username and password, the program will not be able to connect to the network, but also can not connect to SQL Server. As a manager, once you have started the program, you are ready to manage security. This can be achieved by defining the following attributes: User (account): A 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. Group (account number): 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 get permission from all groups. Object ownership: Ownership belongs to the user who created the object. The owner can assign access rights to the user. If you are a visual owner, you can also decide which users can view the data by viewing it. License: A license represents the right to perform certain actions, such as opening a view or changing a stored program. SQL Server admits three states of license: Grant gives you a user access; revoke deletes access; Task: This is a SQL Server security account that can handle the collection of accounts as a simple unit. Tasks define what users can and cannot do in a particular database. Consider from a security perspective that the design process should effectively define where security settings are needed and how to set them. Before this process you should consider two aspects: sensitive data, and people who can view sensitive data. Sensitive data includes all possible data, including all data in the entire database, although this level of security is rare. Your job is to define sensitive data and protect it. The authentication mode you choose and the registration you establish will implement the first step of the security step by restricting which users can access the database. The second step is to enumerate all the users who can access the database, and then decide whether all the data applies to all users. Typically, you need to protect some data, such as payroll or other private data. This means that only specific users can access and view data. You can also set which users to change data. One rule that always remembers is the concept of "least rights". If someone does not need to access data in his work, that is, do not give him access to the permissions. All users should be prevented from having access to the SA user. Specific recommendations when it comes to security settings, experience is also a good teacher, but there are usually design guidelines that apply to common databases: getting ownership of databases and objects from the start. When creating a new database, you will be the owner of the database and be able to set everything that happens in the database. You can register the database as a manager. However, the ownership of the object belongs to the establishment of theLike the user. While this may result in a transfer of ownership, it can be determined that these registrations can be used to establish all objects. Understand the ownership chain. This security feature prevents users from establishing their own viewing and peeking at sensitive data. For example, suppose you create a view of the data that is concentrated from two tables, and if you are the owner of the two tables, SQL Server does not check the permissions on the table when you allow other users to use the view's permission. Use visual and stored programs to assign users the right to access data, rather than having users write special query statements that directly access the table. In this way, you do not need to assign access rights to the user in the table. The visual and stored programs can also restrict the data that is viewed. For example, if your employee form contains some secret payroll information, you can create a view that omits the payroll bar. If a user enters your program from a particular program, you can create a program task. A program task is a user assigned to a particular program and gives the user permission to do so. Using program tasks, users cannot directly authenticate the database, instead, they first authenticate their own programs, which determines which program tasks are connected to the server. Always pay attention to program patches. Admittedly, patches are a collection of tricks. The release of the program, updates, and so will introduce new problems, the use of patches is to prevent external interference and protect the data the best and easiest way. You can visit the SQL Service Pack download page on the Microsoft home page to view the latest patches. Conclusion The program security mechanism is a problem that every developer must face. Don't wait for security issues to be considered in the database development process and in use--security is an important part of the design process. In addition, do not randomly apply security settings and hope to achieve the best results, should learn to choose Safe mode and apply correctly. The responsible editor Zhao Zhaoyi#51cto.com TEL: (010) 68476636-8001 give force (0 votes) to be tempted (0 Votes) nonsense (0 Votes) Professional (0 Votes) The title party (0 Votes) Pass (0 Votes) The original text: SQL Server: Security design back to network security home