SQL Server database Security Planning full Introduction

In the process of improving the security mechanism implemented by the SQL Server 7.0 series, Microsoft has established a flexible and powerful security management mechanism that can comprehensively manage the security of users' access to the SQL Server server system and database. By 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 stand the test.

1. Verification method selection

This article explains the two concepts of authentication and authorization differently. Authentication means checking the user's identity; authorization means allowing the user to do something. In the discussion in this article, the authentication process occurs when a user logs into SQL Server, and the authorization process occurs when a user attempts to access data or execute a command.

The first step in constructing a security policy is to determine which method SQL Server uses to authenticate users. SQL Server authentication matches a set of accounts and passwords to a list in the Sysxlogins table of the Master database. Windows NT / 2000 authentication asks the domain controller to check the validity of the user's identity. Generally, if the server has access to a domain controller, we should use Windows NT / 2000 authentication. The domain controller can be a Win2K server or an NT server. In either case, SQL Server receives an Access Token. The access token is a special list constructed during the authentication process, which contains the user's SID (security identification number) and the SID of a group of users. As described later in this article, SQL Server grants access based on these SIDs. Note that it is not important how the operating system constructs the access token, SQL Server uses only the SID in the access token. That is, it doesn't matter whether you use SQL Server 2000, SQL Server 7.0, Win2K, or NT for authentication, the results are the same.

If you use SQL Server authenticated login, its biggest advantage is that it can be easily implemented by EntERPrise Manager. The biggest disadvantage is that SQL Server authenticated login is only valid for a specific server, that is, managed in a multi-server environment. raletively hard. The second important disadvantage of using SQL Server for authentication is that for each database, we must manage permissions for it separately. If a user has the same permission requirements for two databases, we must manually set the permissions for the two databases or write a script to set the permissions. If the number of users is small, such as less than 25, and the permissions of these users change infrequently, SQL Server authenticated login may be applicable. However, in almost all other cases (with some exceptions, such as applications that directly manage security issues), the administrative burden of this login method will outweigh its advantages.

2, authentication in the Web environment

Even the best security policies often succumb to a situation that uses SQL Server data in a Web application. In this case, the typical method of authentication is to embed a set of SQL Server login names and passwords into a program running on the Web server, such as an ASP page or a CGI script; then, the Web server is responsible for authenticating the user, and the application uses Its own login account (either the system administrator sa account or, for convenience, the login account in the Sysadmin server role) is used to access data for users.

There are several disadvantages to this arrangement, the most important of which include: it does not have the ability to audit users' activities on the server, and it completely relies on Web applications to implement user authentication, which is different when SQL Server needs to restrict user permissions Not easy to distinguish between 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. Since then, all applications use this secure environment when logging into SQL Server. We can improve auditing and verification by granting appropriate permissions to NT anonymous accounts.

The second method is to make all websites use Basic authentication. At this point, IIS will only allow users to access the page if they have entered a valid account and password in the dialog. IIS relies on an NT security database for login authentication. The NT security database can be on the local server or on a domain controller. When a user runs a program or script that accesses a SQL Server database, IIS sends the identity information that the user provided to browse the page to the server. If you use this method, you should keep in mind that under normal circumstances, the password transmission between the browser and the server is generally not encrypted. For websites that use Basic authentication and are very important for security, you must implement SSL (Secure Sockets Layer).

When the client only uses IE 5.0, IE 4.0, IE 3.0 browsers, you can use the third authentication method. You can enable NT authentication on both Web sites and virtual directories. IE will send the identity information of the user to the computer to IIS. When the user tries to log in to SQL Server, IIS uses the login information. Using this simplified method, we can authenticate users on the domain of a remote website (the remote website logs in to a domain that has a trust relationship with the domain running the web server).

Finally, if users have personal digital certificates, you can map those certificates to NT accounts on the local domain. The personal digital certificate and server digital certificate are based on the same technology, which proves the validity of the user's identity, so it can replace NT's Challenge / Response verification algorithm. Both Netscape and IE automatically send the certificate information to IIS in every page request. IIS provides a tool for administrators to map certificates to NT accounts. Therefore, we can replace the usual login process that provides an account name and password with a digital certificate.

From this we can see that we can use a variety of implementation methods when authenticating users through NT accounts. Even when users connect to SQL Server across the Internet through IIS, the choices still exist. Therefore, you should use NT authentication as your preferred method of user authentication.

3, set the global group

The next step in constructing a security policy is to determine what group the user should belong to. In general, users of each organization or application can be divided into many categories based on their specific access requirements for data. For example, the users of accounting application software generally include: data entry operators, data entry managers, report writers, accountants, auditors, financial managers, etc. Each group of users has different database access requirements.

The easiest way to control data access permissions is to create a globally valid group for each group of users that meets the group's user permission requirements and is globally valid. We can create separate groups for each application, or groups that cover a wide range of user categories across the enterprise. However, if you want to know exactly what group members can do, creating groups separately for each application is a better choice. For example, in the previous accounting system, we should create groups such as Data Entry Operators and Accounting Data Entry Managers. Remember, to simplify management, it's best to give the group a name that clearly demonstrates its role.

In addition to the application-specific groups, we need several basic groups. Members of the basic group are responsible for managing the server. By convention, we can create these 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 (among them DB_Name Is the name of a database on the server). Of course, you can create other groups if necessary.

After the global groups are created, we can then grant them access to SQL Server. First create an NT-authenticated login for SQL Server Users and grant it login permissions. Set the Master database as its default database, but do not grant it access to any other databases, and do not set this login account to any server role. member. Then repeat this process for SQL Server Denied Users, but this time to deny login access. In SQL Server, denying permissions always takes precedence. With these two groups created, we have a convenient way to allow or deny users access to the server.

We cannot use Enterpris Managr when authorizing groups that are not registered directly in the Sysxlogins system table, because Enterprise Manager only allows us to choose from a list of existing login names, not a list of all groups in the domain. To access all groups, open Query Analyzer and use the system stored procedures sp_addsrvrolemember and sp_addrolemember for authorization.

For each group operating the server, we can use the sp_addsrvrolemember stored procedure to add each login to the appropriate server role: SQL Server Administrators becomes a member of the Sysadmins role, SQL Server DB Creators becomes a member of the Dbcreator role, and SQL Server Security Operators becomes Securityadmin Members of the role. Note that the first parameter of the sp_addsrvrolemember stored procedure is required to be the full path of the account. For example, JoeS for BigCo domain should be bigco / joes (if you want to use a local account, the path should be server_name / joes).

To create users that exist in all new databases, you can modify the Model database. To simplify work, SQL Server automatically copies all changes to the Model database to the new database. As long as the Model database is used correctly, we don't need to customize every newly created database. In addition, we can use the sp_addrolemember stored procedure to add SQL Server Security Operators to db_securityadmin and SQL Server Developers to the db_owner role.

Note that we still have not authorized any groups or accounts to access the database. In fact, we cannot authorize database access through Enterprise Manager, because the user interface of Enterprise Manager only allows us to grant database access to legitimate 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 limitation. However, as long as we use the sp_addrolemember stored procedure instead of Enterprise Manager, we can assign permissions to any NT account without granting access to the NT account database in the domain.

So far, the setup of the Model database has been completed. However, if your user group has similar access requirements for various application databases across the enterprise, you can move these operations to the Model database instead of the database for a specific application.
4. Allow database access

In the database, unlike our previous approach to login authentication, we can assign permissions to roles instead of directly assigning them to global groups. This ability allows us to easily use SQL Server authenticated logins in our security policy. Even if you never want to use a SQL Server login account, this article still recommends assigning permissions to roles, so that you can prepare for possible changes in the future.

After the database is created, 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, you cannot deny access to the database in the same way as denying access to the server. If you want to deny database access, we can create another global group named DB_Name Denied Users, grant it access to the database, and then set it as a member of the db_denydatareader and db_denydatawriter roles. Note the assignment of SQL statement permissions. The role here only restricts access to objects, but does not restrict access to DDL (Data Definition Language) commands.

As with the login process, if any SID in the access token is registered in the Sysusers system table, SQL will allow the user to access the database. Therefore, we can either authorize the user to access the database through the user's personal NT account SID, or authorize through the SID of the user's group (or groups). To simplify management, we can create a database access database named DB_Name Users Ask permission to the global group without granting access to all other groups. In this way, we can simply add or remove members to a global group to increase or decrease database users.

5. Assigning permissions

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

After you create the role, you can assign permissions. In this process, we only need to use the standard GRANT, REVOKE and DENY commands. But you should pay attention to the DENY permission, this permission takes precedence over all other permissions. If the user is a member of any role or group with DENY permissions, SQL Server will deny the user access to the object.

Next we can join all SQL Server authenticated logins. User-defined database roles can include SQL Server logins as well as NT global groups, local groups, and personal accounts. This is one of its most valuable features. User-defined database roles can be used as a universal container for various logins. The main reason we use user-defined roles instead of directly assigning permissions to global groups is this.

Since the built-in roles are generally applicable to the entire database rather than individual objects, it is recommended that you use only two built-in database roles, namely db_securityadmin and db_owner. Other built-in database roles, such as db_datareader, grant SELECT permissions on all objects in the database. Although you can grant SELECT permissions with the db_datareader role and then selectively deny SELECT permissions to individual users or groups, you may forget to set permissions for certain users or objects when using this method. A simpler, more direct, and error-prone method is to create a user-defined role for these special users, and then grant only those users the permissions required to access the object to this user-defined role.

Simplify Security Management

Not only can SQL Server authenticated logins be easily implemented, but they are easier to write into applications than NT-authenticated logins. However, if the number of users exceeds 25, or the number of servers is more than one, or each user can access more than one database, or the database has multiple administrators, SQL Server authenticated logins are not easy to manage. Because SQL Server does not have a tool that displays the effective permissions of users, it is more difficult to remember what permissions each user has and why they want them. Even for small systems where a database administrator has additional responsibilities, simplifying security policies can help reduce the complexity of the problem. Therefore, the preferred method should be to use NT authenticated login and then manage database access through some carefully selected global groups and database roles.

Here are some rules of thumb to simplify security policies:

· Users gain server access through the SQL Server Users group and database access through the DB_Name Users group.

· Users gain permissions by joining global groups, and global groups gain permissions by joining roles. Roles directly have permissions in the database.

· Users who need multiple permissions can obtain permissions by joining multiple global groups.

 As long as the planning is correct, you can complete all access and permissions maintenance work on the domain controller, so that the server reflects the various settings adjustments you make on the domain controller. Although the situation may change in actual applications, the basic measures described in this article still apply. They can help you construct a security policy that is easy to manage.