SQL Server Security Planning strategy

server| Security | introduction | Planning Microsoft has built a flexible and powerful security management mechanism that can manage the security of users accessing SQL Server server systems and databases. 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 be tested.

first, the choice of verification methods

This paper interprets the two concepts of authentication (authentication) and authorization (authorization) differently. Authentication means verifying the identity of the user, and authorization refers to what the user is allowed to do. In the discussion in this article, the validation process occurs when a user logs on to SQL Server, and the authorization process appears when the user attempts to access data or execute commands.

The first step in constructing a security policy is to determine which way SQL Server authenticates users. SQL Server validation is to match a set of accounts, passwords, and a list in the master database sysxlogins table. Windows nt/2000 Authentication is the legality of requesting a domain controller to check the identity of the user. Generally, if a server can access a domain controller, we should use Windows nt/2000 authentication. The domain controller can be either an Win2K server or an NT Server. In either case, SQL Server receives an access token (Access Token). An access token is a special list constructed during the validation process that contains the user's SID (Security identification number) and the SID of a series of users ' groups. As described later in this article, SQL Server grants access rights based on these SIDs. Note that how the operating system constructs access tokens is not important, and SQL Server uses only the SIDs in the access tag. That is, whether you use SQL Server 2000, SQL Server 7.0, Win2K, or NT for validation doesn't matter, the results are the same.

If you are using SQL Server-authenticated logins, the biggest advantage is that it is easy to implement with Enterprise Manager, with the biggest drawback being that SQL Server-authenticated logins are only valid for specific servers, that is, managing in a multiple-server environment is more difficult. The second important disadvantage of using SQL Server for validation is that for each database, we must manage permissions separately for it. If a user has the same permission requirements for two databases, we must manually set the permissions for two databases, or write scripts to set permissions. If the number of users is low, such as 25 below, and the permissions of these users are not changing very frequently, SQL Server authenticated logins may apply. However, in almost all other cases (with some exceptions, such as the application of direct management security issues), the management burden of this type of login will outweigh its advantages.

validation in the Web environment

Even the best security policies often succumb to a situation where SQL Server data is used in Web applications. In this case, the typical way to validate is to embed a set of SQL Server login names and passwords into a program running on a Web server, such as an ASP page or a CGI script; then the Web server is responsible for validating the user, The application uses its own login account (either the System Administrator sa account or, for convenience, uses the login account in the sysadmin server role) to access data for the user.

This arrangement has several drawbacks, the most important of which is that it does not have the ability to audit the user's activities on the server, relies entirely on the Web application for user authentication, and when SQL Server needs to qualify user permissions, it is not easy to distinguish between different users. If you are using IIS 5.0 or IIS 4.0, you can authenticate the user in four ways. The first method is to create an NT account for each Web site and for each virtual directory that is an anonymous user. Thereafter, all applications use this security environment when they log on to SQL Server. We can improve auditing and verification capabilities by granting the appropriate permissions to the NT anonymous account.

The second method is to have all Web sites use Basic authentication. At this point, IIS allows users to access the page only if they have entered a valid account and password in the dialog box. IIS relies on an NT security database for logon authentication, and the NT security database can be on either the local server or the domain controller. When a user runs a program or script that accesses a SQL Server database, IIS sends the identity information provided by the user to browse the page to the server. If you use this approach, it should be remembered that in general, the password transfer between the browser and the server is generally unencrypted, and you must implement SSL (secure Sockets Layer, Secure Sockets Layer) for sites that are safe and important to use Basic authentication.

In the case where the client only uses IE 5.0, IE 4.0, IE 3.0 browsers, you can use the third method of authentication. You can enable NT authentication on both the Web site and the virtual directory. IE sends identity information about the user's logon computer to IIS, which IIS uses when attempting to log on to SQL Server. With this simplified approach, we can authenticate the identity of the user on the domain of a remote Web site that is logged on to a domain that has a trust relationship with the domain on which the Web server is running.

Finally, if a user has a personal digital certificate, you can map those certificates to an NT account on the local domain. The personal digital certificate is based on the same technology as the server digital certificate, it proves the legality of the user identity, so it can replace the NT Challenge/response (Challenge/Response) verification 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 a digital certificate to replace the usual login process that provides the account name and password.

This shows that we can use a variety of implementation methods when authenticating users through an NT account. Even when a user connects to SQL Server across the Internet through IIS, the selection still exists. Therefore, you should use NT Authentication as the preferred user authentication method.

third, set global group

The next step in constructing a security policy is to determine what group the user should belong to. Typically, users of each organization or application can break into many categories according to their specific access requirements for the data. For example, users of accounting applications typically include data entry operators, data entry administrators, report writers, accountants, auditors, finance managers, and so on. Each group of users has different database access requirements.

The easiest way to control data access is to create a globally valid group within a domain for each group of users, respectively, to meet the group's user rights requirements. We can create groups for each application individually, or we can create groups that cover a wide range of user categories for the entire enterprise. However, if you want to be able to understand exactly what a group member can do, it is a good choice to create groups for each application individually. For example, in the previous accounting system, we should create a group of data Entry Operators, Accounting data Entry managers, and so on. Keep in mind that in order to simplify management, it's a good idea to have a name that clearly indicates a role for the group.

Apart from groups that target specific applications, we need several basic groups. The members of the basic group are responsible for managing the server. By custom, we can create the following basic groups: SQL Server administrators,sql Server users,sql server Denied users,sql server DB creators,sql server Se curity operators,sql Server Database Security Operators,sql Server developers, and db_name Users (where db_name is the name of the server's previous database. Of course, if necessary, you can also create other groups.

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

We cannot use Enterpris MANAGR for groups that are not registered directly in the sysxlogins system table, because Enterprise Manager only allows us to select from the list of existing login names, not the list of all groups in the domain. To access all groups, open query Analyzer, and then authorize using the system stored procedures sp_addsrvrolemember and sp_addrolemember.

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 becomes 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 the first parameter requirement for the sp_addsrvrolemember stored procedure is the full path to the account. For example, the Joes of 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 your 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 do not need to customize each newly created database. In addition, we can add SQL Server security operators to db_securityadmin by using the sp_addrolemember stored procedure to add SQL Server developers to the db_owner role.

Note We still do not authorize any groups or accounts to access the database. In fact, we cannot authorize database access through Enterprise Manager because the Enterprise Manager's user interface allows us to grant database access only to legitimate login accounts. SQL Server does not require NT accounts to be able to access the database until we set it up as a member of the database role or assign permissions to the object, but Enterprise Manager has this limitation. However, as long as we are using the sp_addrolemember stored procedure instead of the enterprise Manager, you can assign permissions to any NT account without granting access to the NT account database in the domain.

To this end, the setup of the model database has been completed. However, if your user community has similar access requirements for an enterprise-wide application database, you can move the following actions to the model database rather than on a specific application-oriented database.

four, allow database access

Within the database, and so far we have handled login validation differently, we can assign permissions to roles instead of assigning them directly to global groups. This capability allows us to easily use SQL Server authenticated logins in security policy. Even if you never want to use a SQL Server login account, this article still recommends assigning permissions to roles, because you can be prepared for future changes that might occur.

After the database is created, we can use the sp_grantdbaccess stored procedure to authorize the db_name Users group to access it. It should be noted, however, that the sp_denydbaccess stored procedures that correspond to sp_grantdbaccess do not exist, that is, you cannot deny access to the database in a way that denies access to the server. If you want to deny database access, we can create another global group named db_name Denied users, authorize it to access the database, and then set it to Db_denydatareader and db_ A member of the Denydatawriter role. Note the allocation of SQL statement permissions, where the role restricts access to objects, but does not restrict access to the DDL (data definition Language language) command.

As with the login process, SQL will allow users to access the database if any SID in the access tag is already enlisted in the sysusers system table. Therefore, we can authorize users to access the database either through the user's personal NT account SID, or through the SID of one or more groups of users. To simplify management, we can create a global group named db_name users that has access to the database without granting access to all other groups. In this way, we simply add or remove members from a global group to increase or decrease the database user.

v. Allocation of authority

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 some roles with names that 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, Accounting data Entry managers. Because roles in the accounting database are related to accounting processing tasks, you may want to shorten the names of these roles. However, if the role name is matched to the name of the global group, you can reduce clutter and make it easier to determine which groups belong to a particular role.

You can assign permissions after you create a good role. In this process, we only need to use the standard grant, REVOKE, and deny commands. However, you should be aware of the Deny permission, which takes precedence over all other permissions. If the user is a member of any role or group that has deny permissions, SQL Server denies the user access to the object.

Next we can join all SQL Server authenticated logins. User-defined database roles can contain SQL Server logins as well as NT global groups, local groups, and personal accounts, which is one of its most valuable features. User-defined database roles can serve as a common container for various logins, which is the main reason why we use user-defined roles instead of assigning permissions directly to global groups.

Because the built-in roles generally apply to the entire database rather than the 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 to 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 straightforward, and less prone to error approach is to create a user-defined role for these special users, and then give the user-defined role only the permissions that the user needs to access the object.

vi. simplifying Security management

sql server-Validated logins are not only easy to implement, but are easier to write into applications than NT-authenticated logins. However, if the number of users exceeds 25, or if the number of servers is more than one, or if each user has access to more than one database, or if the database has multiple administrators, SQL Server authenticated logins are not easily managed. Because SQL Server does not have the tools to display user-effective permissions, it is more difficult to remember what permissions each user has and why they are getting those permissions. Even with small systems with other responsibilities for a database administrator, simplifying security policies can also help mitigate the complexity of the problem. Therefore, the preferred approach would be to use NT-authenticated logins, and then manage database access through some carefully selected global groups and database roles.

Here are some rules of thumb for simplifying security policies:

£

• Users get server access through the SQL Server Users group and get database access through the db_name Users group.
• The user obtains permissions by joining the global group, and the global group obtains permissions by joining the role, which directly owns the permissions in the database.
• Users who require multiple permissions get permissions by joining multiple global groups.

As long as the plan is appropriate, you can complete all access and permissions maintenance on the domain controller, allowing the server to reflect the various settings adjustments you have made on the domain controller. Although the actual application may change, the basic measures described in this article still apply, and they can help you build a security policy that is easy to manage.