Tutorials to .com

Tutorials to .com » Database » Sqlserver » A safe and stable database SQLSERVER

A safe and stable database SQLSERVER

Print View , by: iSee ,Total views: 28 ,Word Count: 3234 ,Date: Wed, 26 Aug 2009 Time: 10:06 PM

A safe and stable SQL SERVER database

1, first of all you have to confirm your identification model: WIN NT authentication mode or mixed mode of it, which mixed-mode, including WIN NT authentication mode and differential mode SQL SERVER steps in the implementation of a differential mode to verify the use of a trusted connection 2, set the identification mode 3, shut down and restart the MSSQLServer service program 4, create a WIN NT group and user 5, authorization WIN NT group and user access to SQL Server6, using non-trusted connection for the user to create SQL Server login account

Second, for users and roles assigned login ID 3, the right to log on to the role assignment 4, for users and roles assigned permissions

  SQL Server 7.0 family in improving the security mechanism achieved by the process, Microsoft has established a flexible and powerful security management mechanism, which allows users to access SQL Server and database server system to conduct a comprehensive security management. In accordance with the steps described in this article, you can for the SQL Server 7.0 (or 2000) to construct a flexible, manageable security policy, and its safety tests.

First, authentication method choice   In this paper, verification (authentication) and authorization (authorization) of these two concepts for different interpretations. Authentication refers to the test the user's identity; authorization is one that allows the user what to do. In this discussion, the verification process in the SQL Server when the user logs on the emergence of the authorization process in the user attempts to access data or execute commands a time.   construct security strategy first step is to determine which method to use SQL Server authentication user. SQL Server authentication is to a group of accounts, passwords and the Master database, a list of Sysxlogins table match. Windows NT/2000 authentication is to ask the domain controller checks user identity's legitimacy. In general, if the server can access the domain controller, we should use the Windows NT/2000 authentication. Can be a Win2K domain controller server, it can be NT server. In either case, SQL Server have received an access token (Access Token). Access token is constructed in the verification process in a special list, which contains the user's SID (security identification number) and a range of users where the group SID. As described later in this article, SQL Server as the basis of these SID to grant access. Note that the operating system how to construct the access token is not important, SQL Server to use only the access token of the SID. In other words, whether you use the SQL Server 2000, SQL Server 7.0, Win2K or NT does not matter to verify the results are the same.   If you are using SQL Server authentication login, its biggest advantage is very easy to achieve through the Enterprise Manager, the biggest drawback is that SQL Server authenticated login is only valid for a specific server, that is, in a multi-server environment management more difficult. SQL Server authentication using the second important drawback is that, for each database, we must separately for its administrative privileges. If a user on the two databases have the same access requirements, we have to manually set the permissions for the two databases, or write a script to set permissions. If the relatively small number of users, such as 25 below, and these changes in the user's permission is not very frequent, SQL Server authenticated login may apply. However, in almost all other cases (with some exceptions, such as direct management of security applications), this log-style management burden will exceed its advantages.

2, Web environment, the verification   even the best security policy is often in a case before the yield, this case is the use of Web applications, SQL Server data. In this case, to verify the typical way is to a group of SQL Server login name and password embedded into the Web server to run programs, such as the asp page or CGI script; and then verified by the Web server is responsible for the user, the application is used its own logon account (or a system administrator sa account, or for convenience, use the Sysadmin server role in the login account) for the user to access the data.   This arrangement has several drawbacks, the most important include: It does not have a user on the server the ability to audit the activities of entirely dependent on Web applications to achieve user authentication, when the SQL Server need to limit user rights is different from that difficult to distinguish between users. If you are using IIS 5.0 or IIS 4.0, you can authenticate users in four ways. The first method is for each and every one a web site virtual directory to create an anonymous user's NT account. Since then, all applications when they log on SQL Server to use the security environment. We can grant the appropriate permissions to NT anonymous accounts, improved auditing and validation.   The second method is to allow all sites to use Basic authentication. At this point, only when a user in the dialog box enter a legitimate account and password, IIS will allow them to access the page. IIS on an NT security database to achieve logon authentication, NT security database either on the local server can also be the domain controller. When a user runs a program to access SQL Server database or a script, IIS the user to browse page provided identity information sent to the server. If you use this method, it should be remembered: In normal circumstances, between the browser and the server the password is not encrypted transmission in general, for those who use Basic authentication and secure and importance of the website, you must implement the SSL (Secure Sockets Layer, Secure Sockets Layer).   on the client to use only IE 5.0, IE 4.0, IE 3.0 browser case, you can use a third authentication method. You can Web sites and virtual directories on the NT authentication is enabled. IE will log on the computer the user identity information sent to the IIS, when the user tries to log on SQL Server, IIS on the use of the login information. Using this simplified approach, we can in a remote site's domain to authenticate the user (the remote site to log on to a Web server running the domain has a trust relationship domain).   Finally, if the user has a personal digital certificate, you can put those certificates mapped to this region of the NT account. Personal digital certificate and the server digital certificate to the same technology as the foundation, it proves the legitimacy of the user identity, it can replace NT's Challenge / Response (Challenge / Response) authentication algorithm. Netscape and IE are automatically on every page request, the certificate information is sent to the IIS. IIS allows administrators to provide a certificate is mapped to NT accounts tool. Therefore, we can use digital certificates to replace the usual provision of an account name and password login process.   can be seen, through the NT user account validation that we can use a variety of implementations. Even when the user through the IIS across the Internet to connect SQL Server, select the still existed. Therefore, you should NT authentication as the preferred method of user authentication.

3, set the global group security policy constructed   next step is to determine what the user should belong to the group. Typically, each organization or application of the user can in accordance with their specific data access requirements into many categories. For example, the accounting application software, the user generally include: data entry operator, data entry manager, report writers, accountants, auditors and financial managers. Each group has a different user database access requirements.   control data access to the easiest way is, for each group of users, respectively, for it creates a group of users permission to meet the requirements, within the global and effective group. We believe that every application can create separate groups, you can create for the entire enterprise, covering the wide range of user class group. However, if you want to be able to accurately understand the team members can do for each application separately to create a group is a better choice. For example, in the previous accounting system, we should create a Data Entry Operators, Accounting Data Entry Managers peer group. Remember, in order to simplify administration, it is best to take one for the group to demonstrate clearly that the role's name.   In addition to application-specific groups, we also need a few basic groups. Basic members of the group responsible for the management server. In accordance with convention, we can create the following 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, as well as the DB_Name Users (including DB_Name a database on the server name). Of course, if necessary, you can create other groups.   create a global group, the next we can grant them access to SQL Server permissions. First, the SQL Server Users create an NT authenticated login and grant it permission to log on to Master Database is set to its default database, but do not give it permission to access any other database, nor should this login account is set to any server roles members. Then again for the SQL Server Denied Users repeat this process, but this time to reject login access. In SQL Server, refused permission to always take precedence. After the creation of these two groups, we have to allow or deny users a convenient way to access the server. For those who are not directly in the system tables inside Sysxlogins registration group authorization, we can not use Enterpris Managr, because the Enter-prise Manager only allows us to log the name from the list of options available, rather than within a list of all the groups. To access all groups, open Query Analyzer, and then use the system stored procedures, as well as sp_addrolemember authorize sp_addsrvrolemember.   operation of the server for each group, we can use stored procedures to the various log sp_addsrvrolemember added to the appropriate server roles: SQL Server Administrators as Sysadmins a member of the role, SQL Server DB Creators to become a member of Dbcreator role, SQL Server Security Operators become Securityadmin the role of members. Note sp_addsrvrolemember stored procedure the first requirement is that an argument the full path to your account. For example, BigCo domain JoeS be bigco \ joes (if you want to use a local account, then the path should be server_name \ joes).   To create a new database of all the users exist, you can modify the Model database. To simplify the work, SQL Server automatically changes all of the Model database, copied to the new database. As long as the proper use of Model database, we do not need customization for each newly created database. In addition, we can use sp_addrolemember stored procedure to SQL Server Security Operators to join db_security-admin, the SQL Server Developers added to the db_owner role.   Note that we still do not authorize any group or account access to the database. In fact, we can not authorize Enterprise Manager database access, because Enterprise Manager user interface that only allows us to extend the database to grant access to a legitimate login account. SQL Server does not require the NT account in our database, it is set to the role of a member or prior to the allocation of object permissions to access the database, but the Enter-prise Manager have this restriction. Nevertheless, as long as we are using the sp_addrolemember stored procedure instead of Enterprise Manager, you can not grant access to domain NT account database, the case of the allocation of rights to any NT account.   here so far, the Model database settings have been completed. However, if your user community in various applications within the enterprise-wide access to the database with similar requirements, you can move these operations the following Model database, rather than a specific application-oriented database on.

4, allowing database access   within the database, and so far we have login authentication are handled differently, we can assign permissions to roles rather than directly to allocate them to the global group. This capability enables us to easily use the SQL Server security policy authentication login. Even if you never want to use SQL Server login accounts, this article still recommend the allocation of permissions to roles, because you can possible changes for the future ready.   created a database, we can use stored procedures sp_grantdbaccess authorized DB_Name Users group to access it. It should be noted that, with sp_grantdbaccess corresponding sp_denydbaccess stored procedure does not exist, that is, you can not deny server access in accordance with the method denies access to the database. If you want to refuse to database access, we can create another one called DB_Name Denied Users global group, authorized it to access the database, and then set it to db_denydatareader and db_denydatawriter role members. Note that SQL statement, the distribution of authority, where the role of restricted access to objects, but does not limit the DDL (Data Definition Language, Data Definition Language) commands access.   As with the login process to address, if any of the access token SID has been registered Sysusers system table, SQL will allow users to access the database. Therefore, we not only can the user's personal NT account SID authorized users access the database, you can also where a user (or more) group SID authority. To simplify management, we can create a database named DB_Name Users have access to the global group, while not to grant access to all the other groups. In this way, we simply add in a global group or remove members can increase or reduce the database user.

5, the allocation of authority to implement security policies   The final step is to create a user-defined database roles, and then assign permissions. The completion of this step, the easiest way is to create a global group name and the name of a number of supporting roles. For example, for the previous example, the accounting system, we can create Accounting Data Entry Operators, Accounting Data Entry Managers like role. Since the accounting database processing tasks related to the roles and accounts, you may want to shorten the names of these roles. However, if the role name and the name of supporting the global group, you can reduce the confusion that can more easily determine which group belongs to a particular role.   after you've created the roles can be assigned permissions. In this process, we simply use the standard GRANT, REVOKE, and DENY commands. It should be noted DENY permissions, the permissions take precedence over all other permissions. If the user is the role of any authority or with the DENY members of the group, SQL Server will reject the user access the object.   then we can join all the SQL Server Authentication login. User-defined database role can contain SQL Server logins and NT global group, local group, individual accounts, which is its most valuable characteristics. A user-defined database roles can be used as a common variety of log container, we use user-defined roles, rather than directly to the permissions assigned to the global group the main reason for this is it.   As the role of the built-in general apply to the entire database rather than individual objects, so here I suggest you only use two built-in database roles, that is, db_securityadmin and db_owner. Other built-in database roles, such as db_datareader, it is inside the database to grant SELECT permissions to all objects. Although you can grant SELECT permission db_datareader role, and then selectively for individual users or groups refuse to SELECT permissions, but using this method, you may forget to set permissions to certain users or objects. A more simple, more direct and not error-prone method is for these special users to create a user-defined roles, and then only those users to access the object needs to grant this permission to a user-defined roles.

6, simplified security management     SQL Server authenticated login is not only easily achieved, but also when compared with the NT login authentication, it is more easy to write to the application inside. However, if the number of users over 25, or the number of servers in more than one, or each user can access more than one database or the database has more than one administrator, SQL Server authenticated login is not easy to manage. Because SQL Server does not show the user an effective tool for access to memory what each user has access, as well as why they want to obtain the permissions on the more difficult. Even for a database administrator but also other responsibilities of the small-scale systems, simplifying security policy also help to reduce the complexity of the issues. Therefore, the preferred method should be is to use NT authentication login, and then through a number of carefully selected global group and database role management database access.

  The following are some rules of thumb to simplify security policy:   users to SQL Server Users Group receives server access, through the DB_Name Users group access to database access.   users to join the global group access permissions, while the global group by adding the role of access rights, the role of direct ownership of the database permissions.   users need to access a variety of global group by adding more than a way to obtain permission.   as long as the planning is done properly, the domain controller you can access and permissions to complete all the maintenance work, making the server a domain controller, you reflect on the various settings to adjust. Although the practical applications may change, but the basic measures described in this article continue to apply, they can help you construct a very easy to manage security policy.



Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.