Create a security policy for SQLServer2000

Microsoft has established a flexible and powerful security management mechanism that comprehensively manages the security of user access to the SQL Server server system and database. As described in this article, you can create a flexible and manageable security policy for SQLServer7.0 (or 2000), and its security can withstand the test. I

Microsoft has established a flexible and powerful security management mechanism that comprehensively manages the security of user access to the SQL Server system and database. As described in this article, you can create a flexible and manageable security policy for SQL Server 7.0 (or 2000), and its security can withstand the test. I

Microsoft has established a flexible and powerfulSecurityManagement mechanism, which allows users to access the SQL Server system and databaseSecurityComplete management. As described in this article, you can create a flexible and manageable SQL Server 7.0 (or 2000)SecurityPolicyAnd itsSecurityPerformance can withstand the test.

1. Selection of verification methods

This article explains the authentication and authorization concepts differently. Authentication is used to verify the user's identity, and authorization is used to determine what the user is allowed to do. In this article, the authentication process appears when the user logs on to SQL Server, and the authorization process appears when the user attempts to access data or execute commands.

StructureSecurityPolicyThe first step is to determine which method SQL Server uses to authenticate the user. SQL Server verifies that a set of accounts and passwords match a list in the Sysxlogins table of the Master database. Windows NT/2000 authentication requests the domain controller to check the legality of the user identity. Generally, if the server can access the domain controller, we should use Windows NT/2000 for verification. The domain controller can be a Win2K server or an NT server. In either case, SQL Server receives an Access Token ). Access tag is a special list constructed during verification, which contains the user's SID (SecurityID) and the SID of a series of user groups. As described later in this article, SQL Server grants access permissions based on these SID. Note: It is not important for the operating system to construct an access tag. SQL Server only uses the SID in the access tag. That is to say, whether you use SQL Server 2000, SQL Server 7.0, Win2K or NT for verification, the results are the same.

If you use SQL Server-verified logon, the biggest advantage is that it is easily implemented through Enterprise Manager. The biggest disadvantage is that SQL Server-verified logon is only valid for a specific Server, that is, it is difficult to manage in a multi-server environment. The second important disadvantage of using SQL Server for verification is that we must manage permissions for each database separately. If a user has the same permission requirements for the two databases, we must manually set the permissions for the two databases or write scripts to set the permissions. If the number of users is small, for example, less than 25, and the permission changes of these users are not frequent, login verified by SQL Server may be applicable. However, in almost all other cases (with some exceptions, such as direct managementSecurityProblems), the management burden of this login method will exceed its advantages.

Ii. Web Environment Verification

Even the bestSecurityPolicyIn this case, SQL Server data is often used in Web applications. In this case, a typical verification method is to embed a set of SQL Server login names and passwords into programs running on the Web Server, such as ASP pages or CGI scripts. Then, the Web server is responsible for user authentication, and the application uses its own Logon account (or the system administrator sa account, or the login account in the Sysadmin server role for convenience) access data for users.

This kind of arrangement has several disadvantages, including: it does not have the ability to review users' activities on the server and relies entirely on Web applications for user verification, when SQL Server needs to limit user permissions, it is difficult to distinguish different users. If you are using IIS 5.0 or IIS 4.0, you can use four methods to authenticate users. The first method is to create an anonymous user's NT account for each website and each virtual directory. After that, all applications log on to SQL Server usingSecurityEnvironment. We can improve the review and verification functions by granting appropriate permissions to the NT anonymous account.

The second method is to allow all websites to use Basic verification. At this time, IIS only allows users to access the page when they enter a valid account and password in the dialog box. IIS relies on an NTSecurityDatabase login identity authentication, NTSecurityThe database can be either on the local server or on the domain controller. When a user runs a program or script to access the SQL Server database, IIS sends the identity information provided by the user to browse the page to the Server. If you use this method, remember: In general, the password transfer between the browser and the server is generally not encrypted, for those who use Basic verification andSecurityImportant websites, you must implement SSL (Secure Sockets Layer,SecuritySocket Layer ).

If the client only uses the IE 5.0, IE 4.0, or IE 3.0 browsers, you can use the third verification method. You can enable NT verification on the Web site and on the virtual directory. IE sends the identity information of the user logging on to the computer to IIS, which is used by IIS when the user attempts to log on to SQL Server. When using this simplified method, we can verify the user identity on a remote website domain (the remote website is logged on to a domain that has a trust relationship with the domain running the Web server ).