‫ SQL Server Security- 7th Section- Managing Permissions with Stored Procedures

IRCAR201406222
Date: 2014-06-30
  1. Introduction
SQL Server has many features that support creating secure database applications. Common security considerations, such as data theft or vandalism, apply regardless of the version of SQL Server you are using. Data integrity should also be considered as a security issue. If data is not protected, it is possible that it could become worthless if ad hoc data manipulation is permitted and the data is inadvertently or maliciously modified with incorrect values or deleted entirely. In addition, there are often legal requirements that must be adhered to, such as the correct storage of confidential information. Storing some kinds of personal data is proscribed entirely, depending on the laws that apply in a particular jurisdiction.
Each version of SQL Server has different security features, as does each version of Windows, with later versions having enhanced functionality over earlier ones. It is important to understand that security features alone cannot guarantee a secure database application. Each database application is unique in its requirements, execution environment, deployment model, physical location, and user population. Some applications that are local in scope may need only minimal security whereas other local applications or applications deployed over the Internet may require stringent security measures and ongoing monitoring and evaluation.
The security requirements of a SQL Server database application should be considered at design time, not as an afterthought. Evaluating threats early in the development cycle gives you the opportunity to mitigate potential damage wherever a vulnerability is detected.
Even if the initial design of an application is sound, new threats may emerge as the system evolves. By creating multiple lines of defense around your database, you can minimize the damage inflicted by a security breach. Your first line of defense is to reduce the attack surface area by never to granting more permissions than are absolutely necessary.
The topics in this section briefly describe the security features in SQL Server that are relevant for developers, with links to relevant topics in SQL Server Books Online and other resources that provide more detailed coverage. 
 
  1. Managing Permissions with Stored Procedures in SQL Server
One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.
 
2.1.        Stored Procedure Benefits

Stored procedures have the following benefits:
  • Data logic and business rules can be encapsulated so that users can access data and objects only in ways that developers and database administrators intend.
  • Parameterized stored procedures that validate all user input can be used to thwart SQL injection attacks. If you use dynamic SQL, be sure to parameterize your commands, and never include parameter values directly into a query string.
  • Ad hoc queries and data modifications can be disallowed. This prevents users from maliciously or inadvertently destroying data or executing queries that impair performance on the server or the network.
  • Errors can be handled in procedure code without being passed directly to client applications. This prevents error messages from being returned that could aid in a probing attack. Log errors and handle them on the server.
  • Stored procedures can be written once, and accessed by many applications.
  • Client applications do not need to know anything about the underlying data structures. Stored procedure code can be changed without requiring changes in client applications as long as the changes do not affect parameter lists or returned data types.
  • Stored procedures can reduce network traffic by combining multiple operations into one procedure call.
 
2.2.        Stored Procedure Execution

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
 
2.3.        Best Practices

Simply writing stored procedures isn't enough to adequately secure your application. You should also consider the following potential security holes.
  • Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
  • Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
  • Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
  • Disable the guest account. This will prevent anonymous users from connecting to the database. The guest account is disabled by default in new databases.
  • Implement error handling and log errors.
  • Create parameterized stored procedures that validate all user input. Treat all user input as untrusted.
  • Avoid dynamic SQL unless absolutely necessary. Use the Transact-SQL QUOTENAME() function to delimit a string value and escape any occurrence of the delimiter in the input string.
 
 
References:

نظرات

بدون نظر
شما برای نظر دادن باید وارد شوید

نوشته

 
تاریخ ایجاد: 18 مرداد 1393

دسته‌ها

امتیاز

امتیاز شما
تعداد امتیازها:0