Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to configure a secure and stable SQL SERVER database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First of all, you need to confirm your identification mode:

WIN NT authentication mode is still mixed mode, in which mixed mode includes WIN NT authentication mode and SQL SERVER authentication mode.

Steps to implement authentication mode

1. Verify that a trusted connection is used

2. Set the authentication mode

3. Close and restart the MSSQLServer service program

4. Create WIN NT groups and users

5. Authorized WIN NT grouping and user access to SQL Server

6. Create a SQL Server login account for users with untrusted connections

Assign login accounts to users and roles

Third, assign login rights to roles

Assign permissions to users and roles

In the process of improving the security mechanism implemented by , Microsoft has established a flexible and powerful security management.

Mechanism, which can comprehensively manage the security of users' access to SQL Server server systems and databases. Follow the steps described in this article, you

A flexible and manageable security policy can be constructed for SQL Server 7.0 (or 2000), and its security can stand the test.

I. selection of verification methods

 this article gives different interpretations of the concepts of authentication and authorization. Authentication refers to verifying the user

Authorization refers to what the user is allowed to do. In the discussion in this article, the authentication process occurs when the user logs in to SQL Server

The authorization process occurs when a user attempts to access data or execute a command.

The first step in  's construction of a security policy is to determine how SQL Server authenticates users. The verification of SQL Server is to transfer a group of accounts and secret

The code matches a listing in the Sysxlogins table of the Master database. The verification of Windows NT/2000 is to request the domain controller to check the user.

The legality of the share. In general, if the server can access the domain controller, we should use Windows NT/2000 authentication. The domain controller can be

Win2K server, which can also be a NT server. In either case, the SQL Server receives an access Access Token.

The access tag is a special list constructed during the authentication process, which contains the user's SID (security identification number) and a series of users.

SID in the group. As described later in this article, SQL Server grants access based on these SID. Notice how the operating system constructs the access

The question tag is not important, SQL Server uses only the SID in the access tag. That is, whether you use SQL Server 2000, SQL Server,

It doesn't matter whether it is validated by Win2K or NT, and the result is the same.

If  uses SQL Server-authenticated login, its biggest advantage is that it is easy to implement through Enterprise Manager, and its biggest disadvantage is that

SQL Server-authenticated logins are only valid for specific servers, that is, it is difficult to manage in a multi-server environment. Use SQL

The second important disadvantage of Server validation is that for each database, we must manage permissions for it separately. If a user

There are the same permission requirements for both databases, and we must manually set permissions for both databases, or write scripts to set permissions. If the number of users

The amount is small, such as less than 25, and the permissions of these users do not change very frequently, SQL Server-authenticated logins may be applicable. However, in a few days

In almost all other cases (with some exceptions, such as applications that directly manage security issues), the administrative burden of this login will exceed it.

The advantages of.

II. Verification in Web environment

Even the best security policies of  often succumb to a situation where SQL Server data is used in Web applications. In this

In this case, a typical way to authenticate is to embed a set of SQL Server login names and passwords in a program running on a Web server, such as

The ASP page or CGI script; then, the Web server is responsible for authenticating the user, and the application uses its own login account (or system administration)

The administrator sa account, or, for convenience, use the login account in the Sysadmin server role) to access data for the user.

 has several shortcomings in this arrangement, the most important of which is that it does not have the ability to audit users' activities on the server.

It depends on the Web application to implement user authentication, and it is not easy to distinguish between different users when SQL Server needs to limit user permissions. If you use

It's IIS 5.0or IIS 4.0.You can authenticate users in four ways. The first method is to create one for each website and each virtual directory

The NT account of an anonymous user. Since then, all applications use this security environment when logging in to SQL Server. We can grant NT anonymity by

Account appropriate permissions, improve audit and verification functions.

The second method of  is to have all websites use Basic authentication. At this point, only if the user enters a valid account and password in the dialog box, IIS

To allow them to access the page. IIS relies on a NT security database for login authentication, and the NT security database can be used on the local server

Or on the domain controller. When a user runs a program or script that accesses a SQL Server database, IIS takes the user to browse

The identity information provided by the page is sent to the server. If you use this method, you should remember that under normal circumstances, the browser and the server

Password transmission between is generally unencrypted. For sites that use Basic authentication and are secure and important, you must implement SSL (Secure).

Sockets Layer, secure socket layer).

 you can use the third authentication method when the client only uses IE 5.0,4.0 and IE 3.0browsers. You can go to Web

NT authentication is enabled on the website and on the virtual directory. IE sends the identity information of the user logging on to the computer to IIS, when the user tries to log in to SQL

IIS uses this login information when Server. Using this simplified method, we can access the user's identity on the domain of a remote website.

Row validation (the remote Web site logs in to a domain that has a trust relationship with the domain running the Web server).

 finally, if all users have personal digital certificates, you can map those certificates to NT accounts in the local domain. Personal digital certificates and services

The digital certificate is based on the same technology, which proves the validity of the user's identity, so it can replace the Challenge/Response of NT.

(challenge / response) verify the algorithm. Both Netscape and IE automatically send certificate information to IIS in each page request. IIS provides a

A tool that allows administrators to map certificates to NT accounts. Therefore, we can replace the usual login that provides the account name and password with a digital certificate.

Cheng.

 shows that we can use a variety of implementation methods when authenticating users through the NT account. Even when the user connects across the Internet through IIS

When SQL Server, the selection still exists. Therefore, you should use NT authentication as the preferred user authentication method.

3. Set the global group

The next step in  's construction of a security policy is to determine which group the user should belong to. In general, users of every organization or application can press

It is divided into many categories according to their specific access requirements to the data. For example, users of accounting applications generally include: data entry operators, data

Input administrator, report writer, accountant, auditor, financial manager, etc. Each group of users has different database access requirements.

The easiest way for  to control data access is to create, for each group of users, a

Globally valid groups within the domain. We can either create separate groups for each application, or we can create a wide range of user classes for the entire enterprise.

Another group. However, if you want to know exactly what group members can do, it is better to create separate groups for each application.

The choice. For example, in the previous accounting system, we should create Data Entry Operators, Accounting Data Entry

Managers and other groups. Keep in mind that in order to simplify management, it is best to give the group a name that clearly indicates its role.

In addition to  groups for specific applications, we need several basic groups. Members of the basic group are responsible for managing the server. According to custom

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 a database on the server)

Word). Of course, you can create other groups if necessary.

After  has created the global groups, we can then grant them access to SQL Server. First create a for SQL Server Users

NT-authenticated login and grant it login permissions, set the Master database as its default database, but do not grant it access to any other number

According to the permissions of the library, do not set this login account as a member of any server role. Then repeat for SQL Server Denied Users

This process, but this time login access is denied. In SQL Server, denying permission always takes precedence. After creating these two groups, we have

Provides a convenient way to allow or deny users access to the server.

When authorizing groups that are not directly registered in the Sysxlogins system table, we cannot use Enterpris Managr because Enter-

Prise Manager only allows us to select from a list of existing login names, not a list of all groups in the domain. To access all groups, open the

Query Analyzer, and then use the system stored procedures 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 becomes a member of the Sysadmins role, and SQL Server DB Creators becomes the Dbcreator corner

SQL Server Security Operators becomes a member of the Securityadmin role. Pay attention to sp_addsrvrolemember memory

The first parameter of the storage process requires the full path of the account. For example, the JoeS of the BigCo domain should be bigco/joes (if you want to use the local account

User, the path should be server_name/joes).

 to create users that exist in all new databases, you can modify the Model database. To simplify the work, SQL Server automatically sets the

All changes to the Model database are replicated to the new database. As long as we use the Model database correctly, we don't need to customize every newly created number

According to the library. In addition, we can use sp_addrolemember stored procedures to add SQL Server Security Operators to db_security-

Admin, add SQL Server Developers to the db_owner role.

 note that we still do not authorize any groups or accounts to access the database. In fact, we can't authorize the number through Enterprise Manager.

According to library access, 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 access the database before we set it as a member of the database role or assign object permissions, but Enter-

Prise Manager has this limitation. However, as long as we are using sp_addrolemember stored procedures instead of Enterprise

Manager, you can assign permissions to any NT account without granting database access to the NT account within the domain.

 so far, the setup of the Model database is complete. However, if your user base is interested in various enterprise-wide application databases

With similar access requirements, you can move the following operations to the Model database instead of on the application-specific database

Proceed.

Fourth, allow database access

 is inside the database, and unlike the way we have handled login authentication so far, we can assign permissions to roles instead of directly

They are assigned to global groups. This capability makes it easy for us to use SQL Server-authenticated logins in our security policy. Even if you never

If you want to use a SQL Server login account, this article still recommends assigning permissions to roles, so that you can make changes in the future.

Okay, get ready.

After  has created the database, we can use the sp_grantdbaccess stored procedure to authorize the DB_Name Users group to access it. But we should pay attention to it.

Yes, the sp_denydbaccess stored procedure corresponding to sp_grantdbaccess does not exist, that is, you cannot deny access to the server.

The method of asking denies access to the database. If we want to deny database access, we can create another one called 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 permissions for SQL statements. The role here only restricts access to objects, but not DDL (Data Definition Language, number).

According to the defined language) command access.

 is just like the login process. If any SID in the access tag is already registered in the Sysusers system table, SQL will allow the number of user visits.

According to the library. Therefore, we can either authorize the user to access the database through the user's personal NT account SID, or through the user's (or

The SID authorization of multiple) groups. To simplify administration, we can create a global named DB_Name Users that has database access

Group without granting access to all other groups. In this way, we can simply add or remove members from a global group.

Add or decrease database users.

5. Assign permissions

The final step for  to enforce security policy is to create user-defined database roles and then assign permissions. The easiest way to complete this step

The method is to create roles whose names match the names of the global group. For example, for the accounting system in the previous example, we can create an Accounting

Roles like Data Entry Operators, Accounting Data Entry Managers, etc. Due to the role and accounting office in the accounting database

It's about managing tasks, and you may want to shorten the names of these characters. However, if the role name matches the name of the global group, you can reduce confusion

It is easier to determine which groups belong to specific roles.

Once the role is created by , permissions can be assigned. In the process, we only need to use the standard GRANT, REVOKE, and DENY commands. But...

You should pay attention to the DENY permission, which takes precedence over all other permissions. If the user is a member of any role or group with DENY privileges

SQL Server will deny the user access to the object.

 then we can add all the SQL Server-authenticated logins. User-defined database roles can include SQL Server logins to

And NT global groups, local groups, personal accounts, which is one of its most valuable features. User-defined database roles can be used as access to various logins

With containers, this is the main reason why we use user-defined roles instead of assigning permissions directly to global groups.

 since built-in roles generally apply to the entire database rather than individual objects, it is recommended that you use only two built-in database corners

Color, namely db_securityadmin and db_owner. Other built-in database roles, such as db_datareader, are granted to the

Has SELECT permission for the object. Although you can grant SELECT permissions with the db_datareader role, and then selectively reject individual users or groups

Disable SELECT permissions, but when using this method, you may forget to set permissions for certain users or objects. A simpler, more direct and no

The error-prone way is to create a user-defined role for these special users, and then assign only the rights that those users need to access the object

Restrict the granting of this user-defined role.

This is the end of the content of "how to configure a secure and stable SQL SERVER database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.