Create SQL Server2000 security policies

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.

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.

The first step to construct a security policy is to determine which method SQL Server uses to authenticate users. 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 ). An access tag is a special list constructed during verification. It contains the user's SID (Security ID) 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 applications that directly manage security issues), the management burden of this login method will exceed its advantages.

Ii. Web Environment Verification

Even the best security policy often gives in before a situation where SQL Server data is 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. This security environment is used when all applications log on to SQL Server. 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 NT Security database for login identity authentication. the NT Security database can be either on a local server or on a 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, password transfer between the browser and the server is generally not encrypted, for those websites that use Basic verification and are secure and important, you must implement SSL (Secure Sockets Layer, Secure Socket 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 ).

Finally, if the user has a personal digital certificate, you can map those certificates to the NT account in the local region. A personal digital certificate is based on the same technology as a server digital certificate. It certifies the legitimacy of the user identity, so it can replace the Challenge/Response (question/Response) Verification Algorithm of NT. 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 digital certificates to replace the usual login process that provides the account name and password.

It can be seen that multiple implementation methods can be used to verify users through the NT account. Even if you connect to SQL Server over the Internet through IIS, the selection still exists. Therefore, you should use NT authentication as the preferred method for user authentication.

3. Set Global Group

The next step to construct a security policy is to determine the group to which the user belongs. Generally, users of each organization or application can be divided into many categories according to their specific data access requirements. For example, users of Accounting Application Software generally include: data input operator, data input administrator, report writer, accountant, auditor, and financial manager. Each group of users has different database access requirements.

The simplest way to control data access permissions is to create a globally valid group for each group of users that meets their permission requirements. You can create groups for each application, or create groups that apply to a wide range of user categories for the entire enterprise. However, if you want to precisely understand what group members can do, it is a good choice to create groups for each application. For example, in the previous Accounting system, we should create Data Entry Operators, Accounting Data Entry Managers, and other groups. Remember, to simplify management, it is best to give a group a name that clearly expresses its role.

In addition to groups for specific applications, we also need several basic groups. The members of the basic group are responsible for managing servers. According to our habits, we can create the following 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 (where DB_Name is the name of the database on the Server ). You can also create other groups if necessary.

After creating a global group, you can authorize them to access SQL Server. First, create an NT-verified logon for SQL Server Users and grant it the logon permission. Set the Master database as its default database, but do not grant it the permission to access any other database, do not set this Logon account as a member of any server role. Repeat this process again for SQL Server Denied Users, but this time login access is Denied. In SQL Server, deny permissions always take precedence. After creating these two groups, we have a convenient way to allow or deny users access to the server.

When you do not authorize a group that is not directly registered in the Sysxlogins system table, you cannot use container is Managr because Enterprise Manager only allows you to select from the list of existing login names, instead of the list of all groups in the domain. To access all groups, open Query Analyzer and use the system stored procedure sp_addsrvrolemember and sp_addrolemember for authorization.

For each group that operates the Server, we can use the sp_addsrvrolemember stored procedure to add each login to the appropriate Server role: SQL Server Administrators to become a member of the Sysadmins role, SQL Server DB Creators becomes a member of the Dbcreator role, and SQL Server Security Operators becomes a member of the Securityadmin role. Note that the first parameter in the sp_addsrvrolemember stored procedure must be the full path of the account. For example, the JoeS in the BigCo domain should be bigcojoes (if you want to use a local account, the path should be server_namejoes ).

To create a user that exists in all new databases, you can modify the Model database. To simplify the work, SQL Server automatically copies all changes made to the Model database to the new database. As long as the Model database is used correctly, we do not need to customize each newly created database. In addition, we can use the sp_addrolemember stored procedure to add SQL Server Security Operators to db_securityadmin and add SQL Server Developers to the db_owner role.

Note that we still do not authorize any group or account to access the database. In fact, we cannot authorize database access through Enterprise Manager, because the Enterprise Manager user interface only allows us to grant database access permissions to legal login accounts. SQL Server does not require the NT account to be able to access the database before we set it as a member of the database role or assign object permissions, But Enterprise Manager has this restriction. However, as long as we use the sp_addrolemember stored procedure instead of the Enterprise Manager, we can assign permissions to any NT account without granting the database access permission to the domain's NT account.

So far, the settings for the Model database have been completed. However, if your user group has similar access requirements for various enterprise application databases, you can move the following operations to the Model database, instead of on databases for specific applications.

5. Assign Permissions

The last step for implementing the security policy is to create a user-defined database role and assign permissions. The easiest way to complete this step is to create some roles whose names match the global group name. For example, for the Accounting system in the previous example, we can create roles such as Accounting Data Entry Operators and Accounting Data Entry Managers. Since roles in the accounting database are related to accounting tasks, you may want to shorten the names of these roles. However, if the role name matches the global group name, you can reduce confusion and determine which groups belong to a specific role more easily.

After creating a role, you can assign permissions. In this process, we only need to use standard GRANT, REVOKE, and DENY commands. However, pay attention to the DENY permission, which takes precedence over all other permissions.