SQL Server database security management mechanism

Microsoft has established a flexible and powerful security management mechanism to improve the security mechanisms implemented by the sqlserver7.0 series, it comprehensively manages the security of users' 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.

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 sqlserver, 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. Sqlserver verification matches a group of accounts and passwords with a list in the sysxlogins table of the master database. WindowsNT/2000 authentication is to request the domain controller to check the legality of the user identity. Generally, if the server can access the domain controller, we should use WindowsNT/2000 for verification. The domain controller can be a Win2k server or an NT server. In either case, sqlserver 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, sqlserver grants access permissions based on these Sid. Note: It is not important to construct an access tag in the operating system. sqlserver only uses the SID in the access tag. That is to say, whether you use sqlserver2000, sqlserver7.0, Win2k or NT for verification, the results are the same.

If you use sqlserver for login authentication, the biggest advantage is that it is easily implemented through enterprisemanager. The biggest disadvantage is that sqlserver for login authentication is only valid for specific servers. That is to say, it is difficult to manage in a multi-server environment. The second important disadvantage of using sqlserver 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, such as less than 25, and the permission changes of these users are not frequent, login verified by sqlserver 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 the web server to runProgramFor example, 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 for convenience, use the Logon account in the SysAdmin server role to access data for the user.

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, it is difficult to distinguish different users when sqlserver requires user permission limitation. If you are using iis5.0 or iis4.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 will use this security environment when logging on to sqlserver. 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 sqlserver 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 (securesocketslayer, Secure Socket Layer ).

If the client only uses ie5.0, ie4.0, and ie3.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 sqlserver. 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 of NT.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 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 the user connects to sqlserver 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 groups such as dataentryoperators and accountingdataentrymanagers. 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: sqlserveradministrators, sqlserverusers, drivers, sqlserverdbcreators, sqlserversecurityoperators, drivers, sqlserverdevelopers, and db_nameusers (where db_name is the name of a database on the server ). You can also create other groups if necessary.

After creating a global group, you can authorize them to access sqlserver. Create an NT-verified logon for sqlserverusers 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. Then we repeat the process for sqlserverdeniedusers, but this time we want to reject login access. In sqlserver, deny permissions are always given priority. After creating these two groups, we have a convenient way to allow or deny users access to the server.

When you authorize a group that is not directly registered in the sysxlogins system table, you cannot use enterprismanagr because enterprisemanager only allows you to select from the list of existing login names, rather than the list of all groups in the domain. To access all groups, open queryanalyzer 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: sqlserveradministrators becomes a member of the sysadmins role, and sqlserverdbcreators becomes a member of the dbcreator role, sqlserversecurityoperators 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, sqlserver automatically copies all changes 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 sqlserversecurityoperators to db_securityadmin and add sqlserverdevelopers 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 enterprisemanager, because the enterprisemanager user interface only allows us to grant the database access permission to a valid login account. Sqlserver 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 enterprisemanager has this restriction. Even so, as long as we use the sp_addrolemember stored procedure instead of javasisemanager, 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 use sqlserver to log on to your account, 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_nameusers 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_namedeniedusers, 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 (datadefinitionlanguage, 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_nameusers 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 accountingdataentryoperators and accountingdataentrymanagers. 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, sqlserver rejects the user's access to the object.

Next, we can join all login verified by sqlserver. User-Defined database roles can include sqlserver 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 sqlserver 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 sqlserver is not easy to manage. Since sqlserver does not have a tool that shows the valid permissions of users, it is more difficult to remember the permissions that 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 sqlserverusers group and database access through the db_nameusers group.

· You can get permissions by joining the global group, and the global group can get permissions by joining the 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.