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 bigco/joes (if you want to use a local account, the path should be server_name/joes ).

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.
Iv. Allow Database Access

In the database, unlike how we process login verification so far, we can assign permissions to roles rather than directly assigning them to global groups. This capability allows us to easily use SQL Server-verified logon in security policies. Even if you never want to log on to an account using SQL Server, we recommend that you assign permissions to the role so that you can prepare for future changes.

After creating a database, 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 to say, you cannot deny access to the database by rejecting access to the server. To deny database access, we can create another global group named DB_Name Denied Users, authorize it to access the database, and set it as a member of the db_denydatareader and db_denydatawriter roles. Pay attention to the allocation of SQL statement permissions. Here, the role only limits access to objects, but does not limit access to DDL (Data Definition Language) commands.

As for the login process, if any SID in the access tag has been registered in the Sysusers system table, SQL allows the user to access the database. Therefore, you can authorize the user to access the database through the user's personal NT account SID, or through the SID authorization of one or more user groups. To simplify management, we can create a global group named DB_Name Users with database access permissions without granting access permissions to all other groups. In this way, you can simply add or delete members in a Global Group to increase or decrease database users.

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. If a user is a member of any role or group with the DENY permission, SQL Server rejects the user's access to the object.

Next, we can add all SQL Server verification logins. User-Defined database roles can include SQL Server logon, NT global groups, local groups, and personal accounts, which are one of its most valuable features. User-Defined database roles can be used as common containers for various logins. The main reason for using user-defined roles rather than directly assigning permissions to global groups is this.

Because the built-in roles are generally applicable to the entire database rather than individual objects, we recommend that you use only two built-in database roles, db_securityadmin and db_owner. Other built-in database roles, such as db_datareader, grant the SELECT permission to all objects in the database. Although you can use the db_datareader role to grant SELECT permissions and then selectively deny SELECT permissions to individual users or groups, you may forget to set permissions for some users or objects when using this method. A simpler, more direct, and less prone to errors is to create a user-defined role for these special users, then, only the permissions required by the user to access the object are granted to the user-defined role.

Vi. Simplified Security Management

Login verified by SQL Server is not only easy to implement, but also easier to write to the application than login verified by NT. However, if the number of users exceeds 25, the number of servers is more than one, or each user can access more than one database, or the database has multiple administrators, login verified by SQL Server is not easy to manage. Because SQL Server does not have a tool that shows the valid permissions of users, it is more difficult to remember what permissions each user has and why they want to obtain these permissions. Even for a small system where the database administrator has other responsibilities, simplifying the security policy can help ease the complexity of the problem. Therefore, the preferred method is to use the logon with NT authentication, and then manage database access through some carefully selected global groups and database roles.

The following are some experience rules for simplifying security policies:

The user obtains Server access through the SQL Server Users group and database access through the DB_Name Users Group.

You can get permissions by joining a global group, and a global group is granted permissions by joining a role. The role directly has permissions in the database.

Users who require multiple permissions can obtain permissions by joining multiple global groups.

As long as you plan properly, you can complete all access and permission maintenance on the domain controller, so that the server reflects the various settings and adjustments you make on the domain controller. Although the situation may change in actual application, the basic measures described in this article are still applicable, and they can help you build easily managed security policies.