‫ SQL Server Security- 9th Section- Signing Stored Procedures and Customizing Permissions with Impersonation

Number: IRCAR201408229
Date: 2014-08-13
  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. Signing Stored Procedures in SQL Server
You can sign a stored procedure with a certificate or an asymmetric key. This is designed for scenarios when permissions cannot be inherited through ownership chaining or when the ownership chain is broken, such as dynamic SQL. You then create a user mapped to the certificate, granting the certificate user permissions on the objects the stored procedure needs to access.
When the stored procedure is executed, SQL Server combines the permissions of the certificate user with those of the caller. Unlike the EXECUTE AS clause, it does not change the execution context of the procedure. Built-in functions that return login and user names return the name of the caller, not the certificate user name.
A digital signature is a data digest encrypted with the private key of the signer. The private key ensures that the digital signature is unique to its bearer or owner. You can sign stored procedures, functions, or triggers.
You can create a certificate in the master database to grant server-level permissions.
2.1.        Creating Certificates

When you sign a stored procedure with a certificate, a data digest consisting of the encrypted hash of the stored procedure code is created using the private key. At run time, the data digest is decrypted with the public key and compared with the hash value of the stored procedure. Modifying the stored procedure invalidates the hash value so that the digital signature no longer matches. This prevents someone who does not have access to the private key from changing the stored procedure code. Therefore you must re-sign the procedure each time you modify it.
There are four steps involved in signing a module:
  1. Create a certificate using the Transact-SQL CREATE CERTIFICATE [certificateName] statement. This statement has several options for setting a start and end date and a password. The default expiration date is one year
  2. Create a database user associated with that certificate using the Transact-SQL CREATE USER [userName] FROM CERTIFICATE [certificateName] statement. This user exists in the database only and is not associated with a login.
  3. Grant the certificate user the required permissions on the database objects.
A certificate cannot grant permissions to a user that has had permissions revoked using the DENY statement. DENY always takes precedence over GRANT, preventing the caller from inheriting permissions granted to the certificate user.
  1. Sign the procedure with the certificate using the Transact-SQL ADD SIGNATURE TO [procedureName] BY CERTIFICATE [certificateName] statement.
  1. Customizing Permissions with Impersonation in SQL Server
Many applications use stored procedures to access data, relying on ownership chaining to restrict access to base tables. You can grant EXECUTE permissions on stored procedures, revoking or denying permissions on the base tables. SQL Server does not check the permissions of the caller if the stored procedure and tables have the same owner. However, ownership chaining doesn't work if objects have different owners or in the case of dynamic SQL.
You can use the EXECUTE AS clause in a stored procedure when the caller doesn't have permissions on the referenced database objects. The effect of the EXECUTE AS clause is that the execution context is switched to the proxy user. All code, as well as any calls to nested stored procedures or triggers, executes under the security context of the proxy user. Execution context is reverted to the original caller only after execution of the procedure or when a REVERT statement is issued.

The Transact-SQL EXECUTE AS statement allows you to switch the execution context of a statement by impersonating another login or database user. This is a useful technique for testing queries and procedures as another user.
EXECUTE AS LOGIN = 'loginName';
EXECUTE AS USER = 'userName';
You must have IMPERSONATE permissions on the login or user you are impersonating. This permission is implied for sysadmin for all databases, and db_owner role members in databases that they own.

You can use the EXECUTE AS clause in the definition header of a stored procedure, trigger, or user-defined function (except for inline table-valued functions). This causes the procedure to execute in the context of the user name or keyword specified in the EXECUTE AS clause. You can create a proxy user in the database that is not mapped to a login, granting it only the necessary permissions on the objects accessed by the procedure. Only the proxy user specified in the EXECUTE AS clause must have permissions on all objects accessed by the module.
Some actions, such as TRUNCATE TABLE, do not have grantable permissions. By incorporating the statement within a procedure and specifying a proxy user who has ALTER TABLE permissions, you can extend the permissions to truncate the table to callers who have only EXECUTE permissions on the procedure.
The context specified in the EXECUTE AS clause is valid for the duration of the procedure, including nested procedures and triggers. Context reverts to the caller when execution is complete or the REVERT statement is issued.
There are three steps involved in using the EXECUTE AS clause in a procedure.
  1. Create a proxy user in the database that is not mapped to a login. This is not required, but it helps when managing permissions.
  1. Grant the proxy user the necessary permissions.
  2. Add the EXECUTE AS clause to the stored procedure or user-defined function.
Applications that require auditing can break because the original security context of the caller is not retained. Built-in functions that return the identity of the current user, such as SESSION_USER, USER, or USER_NAME, return the user associated with the EXECUTE AS clause, not the original caller.
3.3.        Using EXECUTE AS with REVERT

You can use the Transact-SQL REVERT statement to revert to the original execution context.
The optional clause, WITH NO REVERT COOKIE = @variableName, allows you switch the execution context back to the caller if the @variableName variable contains the correct value. This allows you to switch the execution context back to the caller in environments where connection pooling is used. Because the value of @variableName is known only to the caller of the EXECUTE AS statement, the caller can guarantee that the execution context cannot be changed by the end user that invokes the application. When the connection is closed, it is returned to the pool.

In addition to specifying a user, you can also use EXECUTE AS with any of the following keywords.
  • CALLER. Executing as CALLER is the default; if no other option is specified, then the procedure executes in the security context of the caller.
  • OWNER. Executing as OWNER executes the procedure in the context of the procedure owner. If the procedure is created in a schema owned by dbo or the database owner, the procedure will execute with unrestricted permissions.
  • SELF. Executing as SELF executes in the security context of the creator of the stored procedure. This is equivalent to executing as a specified user, where the specified user is the person creating or altering the procedure.


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


تاریخ ایجاد: 7 مهر 1393



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