Ways to manage SQL Server security

When running SQL Server on a server, we always try to keep SQL Server from the intrusion of illegal users, deny it access to the database, and ensure the security of the data. SQL Server provides powerful built-in security and data protection to help achieve this natural requirement. From the previous introduction, we can see that SQL Server provides multiple levels of security from the operating system, SQL ServerE, database to objects. It also involves roles, database users, permissions, and many other security-related concepts. The problem we face now is how to combine these different security-related components in SQL Server, take advantage of the advantages of various components, and consider the possible disadvantages to develop a reliable security policy. Make SQL Server more robust and "inviolable."

Here are a few security management strategies that you should be able to understand and understand, and then simply combine them together.

1 using views as a security mechanism

The main reason that a view can be used as a security mechanism in the cursor and view chapter is that the view is a virtual table, and it is defined by the query statement, is a data result set, through the view, users can only query modify the data he can see, other databases or tables for the user is neither visible nor accessible. With permission settings for a view, the user has only the appropriate access to the view, but does not have the appropriate permissions to access the underlying table referenced by the view.

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

2 view with row-level, column-level security

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

Create a view first

3 views combined with permissions

If you grant access to a view to a user, it can still view the appropriate data information, even if the user does not have permission to access the underlying table referenced by the view.

What good is a combination of views and permissions? Let's give an example to illustrate. First, assume that user A has no SELECT permission on the payterms column of the sales table, and that there are only SELECT permissions for other columns, and that you cannot use such statements if you want to see other sales information:

SELECT * FROM Sales

Instead, you must indicate the column names of the remaining columns. This requires the user to understand the structure of the table, usually to let users understand the table structure is a very smart thing, then how to solve this problem?

It's simple. If you create a view view1, the view contains all columns except the Payterms column, and the SELECT permission is granted to user a so that user a can execute the statement: SELECT * from View1 to view sales information.

4 using stored procedures as a security mechanism

If the user does not have access to the views and tables, then the stored procedure can still be able to query the appropriate data information, the method is simple, so long as the user has the stored procedure exec permissions. Of course, make sure that the stored procedure contains a query statement. For example, you can create the following stored procedures:

CREATE PROCEDURE Selsales as

SELECT * FROM Sales

The EXEC permission for the stored procedure is then granted to the user, who can view the information when they execute the stored procedure.

The advantage of using stored procedures is that they do not have to be allocated to view and table access rights.

Summary

This article mainly discusses the security management of SQL Server. It involves database users, roles, permissions, etc. as a system administrator or security administrator, first determine which authentication mode should be used before making security attribute configuration. Pay attention to the proper use of the guest user and public roles and a deep understanding of the benefits of applying roles for the controllability of data querying and processing.