First, first you have to confirm your identification mode: WIN NT authentication mode or mixed mode, where the mixing mode includes step 1 of the WIN NT authentication mode and SQL Server authentication mode implementation authentication mode, verify the use of trusted connection 2, set identification Mode 3, turn off and restart the MSSQLServer service program 4, create WIN NT packets and user 5, authorize WIN NT packets and users to access SQL Server6, create SQL Server login account for users with non-trustful connection
Second, allocate the login account for the user and the role, assign the login to the role 4, allocate license permissions for users and roles
In the process of improving the security mechanism implemented by the SQL Server 7.0 series, Microsoft has established a flexible and powerful security management mechanism that can be fully managed by users to access the security of the SQL Server server system and the database. According to the steps described in this article, you can construct a flexible, manageable security policy for SQL Server 7.0 (or 2000), and its security can be tested.
1. Verification Method Selection This article explains the two concepts of authentication and authorization (Authorization). Verification refers to the identity of the user; authorization means that the user is allowed to do. In the discussion herein, the verification process appears when the user logs in to SQL Server, the authorization process appears when the user tries to access the data or execute the command. The first step in constructing a security policy is to determine which way SQL Server verifies the user. The authentication of SQL Server is to match a set of accounts, passwords to a list in the Master database SysxLogins table. Verification of Windows NT / 2000 is the legality of the request domain controller to check the user identity. Generally, if the server can access the domain controller, we should use Windows NT / 2000 verification. The domain controller can be a Win2K server or a NT server. In any case, both SQL Server receives an access tag (Access token). Access tags are a special list constructed during the verification process, which contains the user's SID (Safety Identity Number) and the SID of a series of users. As mentioned later, SQL Server grants access to access by these SIDs. Note that how the operating system constructs access tags, and SQL Server uses only the SID in the access tag. That is, no matter whether you use SQL Server 2000, SQL Server 7.0, Win2K or NT is verified, and the result is the same. If you use SQL Server authentication, its biggest benefit is easy to implement through Enterprise Manager, the biggest disadvantage is that SQL Server authentication is only valid for a specific server, that is, managing in a multi-server environment. more difficult. The second important disadvantage of verifying using SQL Server is that for each database, we must manage privileges separately. If a user has the same permission requirements for two databases, we must manually set the permissions of the two databases, or write script settings permissions. If the number of users is small, such as less than 25, and these users' permissions are not very frequent, SQL Server authentication logins may apply. However, in almost all other circumstances (there are some exceptions, such as direct management of security issues), this management burden will exceed its advantages.
Second, the verification in the web environment Even the best security strategy is often yard before a situation, this situation is the use of SQL Server data in web applications. In this case, the typical method for verification is to embed a set of SQL Server login names and passwords to the program running on the web server, such as an ASP page or CGI script; then, the web server is responsible for verifying the user, the application is used Its own login account (or the system administrator SA account, or for convenience, using the login account in the sysadmin server role) to access the data. This arrangement has several disadvantages, including: it does not have the ability to review the activities of the user on the server, fully dependent on the web application to implement user authentication, when SQL Server needs to limit user rights It is not easy to distinguish between users. If you are using IIS 5.0 or IIS 4.0, you can verify the user with four ways. The first method is to create an anonymous NT account for each website and each virtual directory. Thereafter, the security environment is used when all applications log in to SQL Server. We can improve auditing and verification capabilities by granting the right permissions of NT anonymous accounts. The second method is to let all sites use Basic validation. At this point, IIS will allow them to access the page only when the user enters a legitimate account and password in the dialog box. IIS rely on a NT secure database to implement login authentication, the NT secure database can be on the local server or on a domain controller. When the user runs a program or script accesses the SQL Server database, IIS sends the user to the server to browse the identity information provided to the server. If you use this method, you should remember that in general, the password transfer between the browser and the server is generally not encrypted. For websites that are safe and important to use Basic authentication, you must implement SSL (Secure Sockets Layer, a security socket layer). In the case of using IE 5.0, IE 4.0, IE 3.0 browser in the client, you can use the third authentication method. You can enable NT validation on a web site and virtual directory. IE will send the user's identity information to IIS when the user is trying to log in to SQL Server, and IIS uses these login information. When using this simplified method, we can verify the user's identity on a remote website (the remote website is logged in to a domain with a trust of the Web server). Finally, if the user has a personal digital certificate, you can map those certificates to the NT account of the local domain. The personal digital certificate is based on the same technology, which proves the legality of the user identity, so it can replace NT's Challenge / Response verification algorithm. Netscape and IE are automatically sent to IIS in each page request. IIS provides a tool that allows administrators to map certificates to NT accounts. Therefore, we can use digital certificates to replace the usual provision of account names and passwords. This shows that we can use a variety of implementation methods when verifying users through a NT account. Even when the user accesses SQL Server through IIS, select still exists. Therefore, you should use NT to verify as the preferred user authentication method.
Third, setting global group Constructing the next step in constructing a security policy is to determine which group should belong to. Typically, users of each organization or application can divide a lot of categories in accordance with their specific access requirements. For example, users of accounting applications generally include: data input operator, data input administrator, report writing staff, accountant, auditor, finance manager, etc. Every group of users have different database access requirements. The easiest way to control data access rights is that for each group of users, a group that meets the user rights requirements for this group of users, respectively. We can create groups for each app, or you can create a group that is available throughout the enterprise, covering a wide range of user categories. However, if you want to know what you can do, you can create a group for each application. For example, in the previous accounting system, we should create a Data Entry Operators, Accounting Data Entry Managers, and other groups. Remember, in order to simplify management, it is best to set a name that can clearly represent the role. In addition to the group of specific applications, we also need several basic groups. The members of the basic group are responsible for managing the server. By convention, we can create the following these basic groups: SQL Server Administrators, SQL Server Users, SQL Server Denied Users, SQL Server DB Creators, SQL Server Security Operators, SQL Server Database Security Operators, SQL Server Developers, and DB_Name Users (which DB_Name Is the name of a database on the server). Of course, if necessary, you can also create other groups. After a full bureau, we can grant them to access SQL Server permissions. First, create a NT authentication login and grant it to the SQL Server Users, set the Master database to its default database, but do not grant it to access any other database permissions, do not set this login account to any server role member. Then repeat this process for SQL Server Denied users, but this time you have to reject login access. In SQL Server, denial is always prioritized. After creating these two groups, we have a convenient way to allow or reject users to access servers. When we are authorized to be registered directly in the Sysxlogins system table, we cannot use Enterpris Managr because Enter-Prie Manager only allows us to select a list of existing login names instead of all groups in the domain. To access all groups, open Query Analyzer and authorize the sp_addsrvrolemember and sp_addroleMember with system stored procedures. For each group operating server, we can use stored procedures to each login sp_addsrvrolemember added to the appropriate server roles: SQL Server Administrators role to become a member Sysadmins, SQL Server DB Creators to become a member Dbcreator role, SQL Server Security Operators become Securityadmin Members of the role. Note The first parameter requirements for the sp_addsrvrolemember stored procedure are the full path to the account.
For example, the joes of the Bigco domain should be Bigco / Joes (if you want to use a local account, the path should be server_name / joes). To create a user existing in all new databases, you can modify the Model database. To simplify work, SQL Server automatically copies all changes to the Model database to the new database. As long as you use the Model database correctly, we don't need to customize each newly created database. In addition, we can use the sp_addrolemember stored procedure to add SQL Server Security-Admin to db_security-admin, add SQL Server Developers to the DB_WNER role. Note We still have not authorized any group or account to access the database. In fact, we cannot authorize database access by Enterprise Manager, because the user interface of Enterprise Manager allows us to grant database access to legitimate login accounts. SQL Server does not require a NT account to access the database before we set it to a member or assignment object permissions we set it to a database role, but Enter-Prie Manager has this limit. Despite this, as long as we use the sp_addrolemember stored procedure rather than Enterprise Manager, you can assign permissions to any NT account without granting the domain NT account database access. Here, the settings for the Model database have been completed. However, if your user group has similar access requirements for each application database in the enterprise, you can move the following to the Model database, not on a database for a particular application. Fourth, allowing database access to the database, in the database, we have different ways to log in to verification, we can assign permissions to roles instead of directly assigning them to global groups. This ability allows us to easily use SQL Server authentication to log in in a security policy. Even if you never want to use the SQL Server login account, this article is still proposed to allocate the right to limit the role, because so you can prepare for future changes. After the database is created, we can use the sp_grantdbaccess stored procedure to authorize the DB_Name Users group to access it. However, it should be noted that the sp_denydbaccess stored procedure corresponding to sp_grantdbaccess does not exist, that is, you cannot refuse access to the database in accordance with the rejection of the server access. If you want to reject database access, we can create another global group called DB_NAME DENIED Users, authorize it to access the database, and set it to a member of DB_DenyDataReader and DB_DenyDataWriter role. Note that the assignment of SQL statement permissions, the roles here only restrict access to objects, but do not limit access to DDL (Data Definition Language, Data Definition Language) command. Just the processing of the login process, if any SID in the access tag is already registered in the Sysusers system table, SQL will allow the user to access the database. Therefore, we can access the database either through the user's personal NT account SID, or can also be authorized by the user's SID authorization. To simplify management, we can create a global group named DB_Name Users with database access, while not granting access to all other groups. This way, we only need to add or delete members to add or reduce database users in a global group.
V. Allocation rights implementing the last step of the security policy is to create a user-defined database role and then assign permissions. The easiest way to complete this step is to create some names and global group names. For example, for the accounting system in the previous example, we can create an Accounting Data Entry Operators, an Accounting Data Entry Managers, such as roles. Since the roles in the accounting database are related to the account processing task, you may want to shorten the names of these characters. However, if the character name is supported by the name of the global group, you can reduce confusion and make it easier to determine which groups belong to a specific role. You can allocate permissions after creating a good role. In this process, we only need to use standard Grant, Revoke, and Deny commands. But you should pay attention to DENY permissions, this permission is preferred over all other permissions. If the user is a role or group of a Deny permission, SQL Server will reject the user access object. Next we can join all SQL Server authentication logins. User-defined database roles can include SQL Server login and NT global groups, local groups, personal accounts, which is one of its most valuable features. The main reason why the user-defined database role can be used as a variety of login universal containers, and we use user-defined roles instead of directly assigning permissions to global groups. Since the built-in role is generally applicable to the entire database rather than a separate object, this suggests that you only use two built-in database roles, namely DB_SecurityAdmin and DB_OWNER. Other built-in database roles, such as DB_DATAREADER, which grants SELECT privileges for all objects in the database. Although you can grant SELECT permissions with DB_DataReader roles, then selectively reject SELECT permissions for individual users or groups, but you may forget to set permissions for certain users or objects. A method of easier, more direct and incapable of error is to create a user-defined role for these special users, and then grant the permissions required by users to access objects to this user-defined role. 6. Simplify safety management SQL Server verification login can not only be easily implemented, but it is easier to write to the application compared to NT verification. However, if the number of users exceeds 25, or the number of servers is more than one, or each user can access more than one database, or the database has multiple administrators, SQL Server authentication is not easy to manage. Since SQL Server does not display tools for user valid privileges, it is more difficult to remember what permissions each user and why they have to get these permissions. Even for a small system that is responsible for a database administrator, simplifies security policies also help to mitigate the complexity of the problem. Therefore, the preferred method should be to use NT verification login and then manage database access by some carefully selected global group and database role.
The following is some experience rules for simplifying security strategies: Users get server access through the SQL Server User Group, get database access via the DB_NAME USERS group. Users obtain permissions by joining global groups, and global groups get permissions by adding roles, and roles directly own the permissions in the database. Users requiring a variety of privileges have obtained permissions by adding multiple global groups. As long as the plan is planned, you can do all access and permissions maintenance work on the domain controller, so that the server reflects the various settings adjustments you made on the domain controller. Although the situation in practical applications may vary, the basic measures introduced in this article are still applicable, and they can help you construct a very easy-to-manage security strategy.
