SQL Server Security Management

When running SQL Server on the Server, we always need to try our best to protect SQL Server from illegal user intrusion, deny access to the database, and ensure data security. SQL Server provides powerful built-in security and data protection to help achieve this requirement. From the previous introduction, we can see that SQL Server provides multi-level security protection from the operating system, SQL ServerE, database to object. It also involves multiple security-related concepts such as roles, database users, and permissions. Now we are faced with the problem of how to combine these different security-related components in SQL Server, make full use of the advantages of various components, and take into account their possible shortcomings to foster strengths and circumvent weaknesses, develop reliable security policies. Make SQL Server more robust and "non-intrusive ".

Next we will introduce several security management policies. What you should do is to understand and understand these policies and methods, and then simply combine them together.

1. Use view as a security mechanism

In the cursor and view chapter, we have mentioned that a view can be considered as a security mechanism mainly because the view is a virtual table and is defined by a query statement, is a data result set. Through the view, the user can only query and modify the data he can see. Other databases or tables are invisible to the user and cannot be accessed. Through view permission settings, you only have the permission to access the view, but do not have the permission to access the basic table referenced by the view.

By using different views and granting different permissions to users, different users can see different result sets to achieve row-level or column-level data security. The following examples illustrate how a view achieves data security.

2. A Row-level and column-level security view is used.

Example 14-18: a sales point in this example can only view its own sales information. We use the sales table in the pubs database.

Create a view first

3 view and permission combination

If you grant the view access permission to the user, even if the user does not have the permission to access the basic table referenced by the view, the user can still view the corresponding data information.

What are the benefits of Combining views with permissions? The following is an example. Assume that user A has no SELECT permission on the payterms column of the sales table and has only SELECT permission on other columns. If you want to view other sales information, you cannot use the following statement:

Select * from sales

The names of other columns must be specified. This requires the user to understand the table structure. Generally, it is very unwise to let the user understand the table structure. How can this problem be solved?

If you create A view1 view that contains all the columns except the payterms column and grant the SELECT permission to user A, user A can execute the statement: select * from view1, to view the sales information.

4. Use stored procedures as a security mechanism

If you do not have access to views and tables, you can still query the corresponding data information through the stored procedure, you only need to grant the user the EXEC permission for the stored procedure. Make sure that the Stored Procedure contains a query statement. For example, you can create the following stored procedure:

Create procedure selsales

Select * from sales

Then, the EXEC permission of the stored procedure is granted to the user. When the user executes the stored procedure, the user can view the relevant information.

The advantage of using stored procedures is that you do not have to assign access permissions to views and tables.

Summary

This article mainly discusses the security management of SQL Server. Database users, roles, permissions, and so on are involved as a system administrator or security administrator. before configuring security attributes, determine which authentication mode should be used. Pay attention to the proper use of the guest user and public roles, and have a deep understanding of the advantages of the application role in the controllability of Data Query and processing.