Considerations for SQL Server Security

Security issues have always been a concern of DBAs, because the purpose of establishing a database is to allow relevant clients to access the database. Therefore, it is difficult to avoid security risks, for example, the client connection permission and the security during data transmission, so when you consider the security of the SQL Server, you must pay attention to the following aspects:

First aspect:

Client Security: First, you must ensure the security of the client. For example, you must install anti-virus software, firewall, and upgrade patches for your client.

Aspect 2:

Security during data transmission: data is easily monitored or captured during normal transmission. Therefore, you should use encryption mechanisms to ensure data confidentiality.

Third aspect:

Security mechanisms must be implemented on the database server to ensure security. The SQL Server database is a very secure database. If the client wants to operate the data in the database (such as select and insert), it must undergo three checks, that is, the three steps we need to go through (login verification, database verification, object permission ).

For example, if we look at the SQL Server as a building, we must first have the right to enter the building, that is, pass the building's door (login verification ), then you have to have access to a room (Database permission), and the right to open the safe after entering the room (Operation permission on the table ). That is, we must first create a login account, and the login account is divided into two types: windows Account and SQL account (because SQL Server has two types of identity authentication, windows Authentication and Hybrid Authentication ). Specific Operation Method: Open SSMS-SQL Server instance-security-login name, right-click to select new Login Name, select Authentication Mode (Authentication mode is different, the Account type is different, note: to create a windows Logon account, you must first create the account on the windows operating system), enter the name, and select a default database for the user (such as the master database by default ). After the account is created, we test the user through "new"-"Database Engine Query". We will find that the user can only connect to the database, you cannot perform any other operations on the database. The second approach is to create database users so that users can access the database and perform operations on the database. When creating a database user, we map the login user. Therefore, our login name and database user name are generally the same. Operation Method: the first type: Open "SSMS-SQL Server instance-specific database-security-Database User"; the second type: directly on the previously established login user to map the database can be. The last step is to grant specific permissions to specific database objects (such as tables). The three permissions are as follows: Grant, revoke, and deny.