Discussion on the safety and management of MSSQL Server 2000

server| Security
In layman's terms, repositories are databases that store data with certain characteristics. Typically, we divide users using database systems into four categories, database designers, database managers, application designers, and general consumers. The database Manager is responsible for the management and maintenance of the account, which determines the user's permission of all database users. Database security management can be said to be the most important work of database managers. SQL Server, a Microsoft enterprise database, is a powerful, easy-to-use database that can be directly integrated into security with Windows nt/2000 user accounts.

So, what exactly is security management? In short, security management refers to the management of the people who need to log on to the server. In the application, we set data manipulation permissions on the various types of users of the database, usually directly in the application to do account and password management, but this practice needs to write program control. SQL Server, which has a friendly, easy-to-use graphical interface, makes it easy to manage user access to SQL Server.

SQL Server Security management can be divided into 3 levels, that is, the login account, the management of the database, the right to connect to a specific database, and the user's operational rights to the part of the database that is connected. Below, we will elaborate on these 3 levels.

First, login account any user who needs to access SQL server needs a set of server-approved accounts and passwords. SQL Server supports 2 kinds of login modes, one for Windows authentication and one for SQL Server authentication. The former can be successfully connected to SQL Server by establishing a login account corresponding to the Windwos nt/2000 in SQL Server, and allowing the user to log into the Windows nt/2000 with an account that corresponds to the account in SQL Server. We have completed the integration of the Windows nt/2000 Security management mechanism.

Next, the database Manager will log into the account on Windows NT, adding a group in Windows NT directly to SQL Server to become a login account.

By doing this, members of the Windows NT login group can connect to SQL Server. If a member of the group does not allow it to log in to SQL Server, the member's personal account can be set to deny access in SQL Server. If you installed SQL Server in Windows 95, Windows 98, or Windows Me, you will not be able to use the Windows authentication method.

If you use SQL Server authentication, you must create a login account name and password in SQL Server for the user who is connecting to SQL Server, and these accounts and passwords are independent of the Windows nt/2000 account.

Second, the right to manage and connect to a specific database after the login account is established, the user can enter SQL Server, but does not mean that the user has permission to connect to the SQL Server-specific repository, and must set the user or group permissions to operate on SQL Server. The operation rights of the database in SQL Server can be divided into the operation rights of the server itself and the access rights of the database. Operation permissions on SQL Server can be set by the server role, and access permissions for the database can be set by the role and the user's access to the individual table. So what's the difference between a server role and a role?

1. The server role constructs 8 server roles within the SQL Server system (you can imagine a role as a group in a Windows NT account) and it cannot be changed or added. When a user or group has a server role set, it has the permissions that the server role has. The server role is to categorize the administration of SQL Server, such as account creation and database backup, which is not the same as the database role, which is the operational rights to the individual repositories.

We simply list the permissions that are available to the 8 types of server roles.        System administrators indicates that the administrator can perform any action.        Security administrators says manage login account.        The server administrators represents setting up the parameters for SQL Server.        The Setup administrators represents the setup and management of an extended stored procedure for replication (replication).        Process administrators represents a program that manages all of the execution of SQL Server.        Disk administrators represents the management database file.        The database administrators represents the creation and change of database properties. Bulk INSERT administrators represents the administration of an executable bulk insert operation.

2. The roles SQL Server constructs 10 database roles that cannot be changed or deleted, but can add roles to individual repositories. If the user has the database owner permission in the built-in role, it has the full operation right of the database. The detailed permissions descriptions for the remaining roles refer to SQL Server Bol (that is, SQL Server Books Online), and by querying the keyword roles, enter the project titled roles, which contains a complete description of the built-in server role and the database role, with little detail. It is important to note that after a variety of roles are set for the consumer (each user or group can have multiple roles), it has permissions for all role sets, but it loses that permission if a role has been set to reject a certain action, such as a table's select right, in other words, Denying permissions is better than granting permissions.

Third, the access rights of the parts in the database for SQL Server management and the right to connect to a specific repository, the server roles and database roles provided by SQL Server can basically meet most of our needs. In addition, you can set individual access rights to the parts of the database directly to users or groups, with SELECT, INSERT, UPDATE, delete, exec, and DRI, Among them, exec and DRI represent the execution rights of the stored procedure and the validation of the table validity respectively. When you do a direct permission setting, we can also target specific users (such as the built-in database role is not satisfied), of course, if the same permissions for the user more than a few times, you can add a database role to meet the requirements, or the users on the Windows nt/2000 to a group, Then set permissions on the group, which is easier to manage and maintain.

In addition to the above content, in the actual operation, the author of the security of the database to summarize the following recommendations.        1. Use Windows Authentication to manage users who can connect to SQL Server, unless necessary, to consolidate the Windows nt/2000 security mechanism.        2. Use the server roles and database role functions of SQL Server. 3. Use SQL Server's encryption function.

SQL Server provides the ability to log in accounts, network transmissions, virtual tables, and stored procedures. Where the password encryption of the account is a preset, and the transmission of data between the network can be encrypted using SSL, to start this feature must start net-library encryption function, but also with the Windows 2000 CA function, and server-side with the client set up to complete, so that both sides in the transmission of data before, will then be transmitted after SSL encryption. Because virtual tables and stored procedures are defined in plaintext in the system tables, to encrypt virtual tables and stored procedures, you can set encryption options in Enterprise Manager when they are established, or set up encryption with alter narration.

4. After the system is installed, be sure to change the preset sa password so that other users are "obligated" to manage your SQL Server.