Security policy for creating SQL Server2000

Microsoft has established a flexible and powerful security management mechanism that enables comprehensive management of user access to SQL Server server systems and databases. Following the steps in this article, you can build a flexible, manageable security policy for SQL Server 7.0 (or 2000), and its security will be tested.

first, the choice of verification methods

In this paper, the two concepts of authentication (authentication) and authorization (authorization) are interpreted differently. Authentication means verifying the identity of the user, and authorization refers to what the user is allowed to do. In the discussion in this article, the validation process occurs when a user logs on to SQL Server, and the authorization process appears when the user attempts to access data or execute commands.

The first step in constructing a security policy is to determine which way SQL Server authenticates users. SQL Server validation is to match a set of accounts, passwords, and a list in the master database sysxlogins table. Windows nt/2000 Authentication is the legality of requesting a domain controller to check the identity of the user. Generally, if a server can access a domain controller, we should use Windows nt/2000 authentication. The domain controller can be either an Win2K server or an NT Server. In either case, SQL Server receives an access token (Access Token). An access token is a special list constructed during the validation process that contains the user's SID (Security identification number) and the SID of a series of users ' groups. As described later in this article, SQL Server grants access rights based on these SIDs. Note that how the operating system constructs access tokens is not important, and SQL Server uses only the SIDs in the access tag. That is, whether you use SQL Server 2000, SQL Server 7.0, Win2K, or NT for validation doesn't matter, the results are the same.

If you use SQL Server-authenticated logins, the biggest advantage of this is that it is easy to implement with Enterprise Manager, with the biggest drawback being that SQL Server-authenticated logins are only valid for specific servers, that is, managing in a multiple-server environment is more difficult. The second important disadvantage of using SQL Server for validation is that for each database, we must manage permissions separately for it. If a user has the same permission requirements for two databases, we must manually set the permissions for two databases, or write scripts to set permissions. If the number of users is low, such as 25 below, and the permissions of these users are not changing very frequently, SQL Server authenticated logins may apply. However, in almost all other cases (with some exceptions, such as the application of direct management security issues), the management burden of this type of login will outweigh its advantages.

validation in the Web environment

Even the best security policies often succumb to a situation where SQL Server data is used in a Web application. In this case, the typical way to validate is to embed a set of SQL Server login names and passwords into a program running on a Web server, such as an ASP page or a CGI script; then the Web server is responsible for validating the user, The application uses its own login account (either the System Administrator sa account or, for convenience, uses the login account in the sysadmin server role) to access data for the user.

This arrangement has several drawbacks, the most important of which is that it does not have the ability to audit the user's activities on the server and relies entirely on the Web application for user authentication, which is difficult to distinguish between different users when SQL Server needs to qualify user permissions. If you are using IIS 5.0 or IIS 4.0, you can authenticate the user in four ways. The first method is to create an NT account for each Web site and for each virtual directory that is an anonymous user. Thereafter, all applications use this security environment when they log on to SQL Server. We can improve auditing and verification capabilities by granting the appropriate permissions to the NT anonymous account.

The second approach is to have all Web sites use Basic authentication. At this point, IIS allows users to access the page only if they have entered a valid account and password in the dialog box. IIS relies on an NT security database for logon authentication, and the NT security database can be on either the local server or the domain controller. When a user runs a program or script that accesses a SQL Server database, IIS sends the identity information provided by the user to browse the page to the server. If you use this approach, it should be remembered that in general, the password transfer between the browser and the server is generally unencrypted, and you must implement SSL (secure Sockets Layer, Secure Sockets Layer) for sites that are safe and important to use Basic authentication.

In the case where the client only uses IE 5.0, IE 4.0, IE 3.0 browsers, you can use the third method of authentication. You can enable NT authentication on both the Web site and the virtual directory. IE sends identity information about the user's logon computer to IIS, which IIS uses when attempting to log on to SQL Server. With this simplified approach, we can authenticate the identity of the user on the domain of a remote Web site that is logged on to a domain that has a trust relationship with the domain on which the Web server is running.

Finally, if users have personal digital certificates, you can map those certificates to NT accounts on the local domain. The personal digital certificate is based on the same technology as the server digital certificate, it proves the legality of the user identity, so it can replace the NT Challenge/response (Challenge/Response) verification algorithm. Both Netscape and IE automatically send certificate information to IIS in each page request. IIS provides a tool for administrators to map certificates to NT accounts. Therefore, we can use a digital certificate to replace the usual login process that provides the account name and password.
Thus, we can use a variety of implementation methods when authenticating users through an NT account. Even when a user connects to SQL Server across the Internet through IIS, the selection still exists. Therefore, you should use NT Authentication as the preferred user authentication method.

Total 3 page: previous 1 [2] [3] Next page