How to use module signing for SQL Server security
By: Tibor Nagy | Updated: 2014-09-18 | Comments (2) | Related: More > Security
I heard that certain SQL Server security requirements can be fulfilled by module signing. Could you please explain the purpose of module signing and provide an example in SQL Server?
When it comes to security considerations, it is always recommended to give the lowest required access to the users in the SQL Server database. It is a usual practice to minimize the access and grant execute permissions only to views and stored procedures, instead of providing access to the underlying database tables and objects. This way the end users will require permissions only for a limited number of entry level objects which will access the underlying objects on behalf of the user (this is ownership chaining). An additional benefit is that the permission management is reduced to a smaller set of objects and the schema layout can be hidden from the end users.
However, some permissions cannot be inherited through ownership chaining like dynamic SQL and TRUNCATE TABLE permissions. The popular EXECUTE AS command can be used to impersonate users when executing stored procedures, but it changes the execution context and the audit log contains only the impersonated user, not the original caller of the stored procedure. Module signing has been introduced in SQL Server 2005 to overcome the ownership chaining issues and to provide a tool to impersonate users without changing the execution context.
We can sign the following module types:
- Stored Procedures
There are five steps of module signing:
- Create a certificate
- Create a user and map to the certificate
- Sign the module
- Grant the permissions for the certificate user
- Grant permission to the module for the users/groups
You can import a key pair from certificate files with private key length between 384 and 4096 bits. Alternatively you can create a self-signed certificate with 1024 bits long private keys with SQL Server. The SQL Server follows the X.509 standard so the certificates will have standard Subject, Expiry Date, etc. fields. If you create the certificate in the master database then you can allow server-level permissions. If you create it in a user database then you can allow database-level permissions.
Please note that the certificate is invalidated when the module changes so you have to sign the module again after every code change. This is a useful protection against unauthorized modification of the signed modules.
SQL Server Module Signing Example
After reading the theoretical background, it is time to see how we can implement module signing in the SQL Server environment.
Scenario: We have
the DemoCertSP stored procedure which selects
data from the DemoSchema.DemoTable. DemoUser
would like to run the stored procedure and we plan to grant this permission without any additional permission to the table. To achieve this goal, we will
create the DemoCert certificate
and a certificate account called DemoCertAccount.
We sign the stored procedure with this certificate and grant the
necessary rights to the certificate account. The last step is to grant
execute permission on the stored procedure to DemoUser.
1A. We can create a certificate in the database by loading a key pair from certificate files:
CREATE CERTIFICATE DemoCert
FROM FILE = 'C:\SQLDemo\Certificates\Demo.cer'
WITH PRIVATE KEY (FILE = 'C:\SQLDemo\Certificates\Demo.pvk',
DECRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord');
1B. If we do not have a certificate to import then we can easily create a self-signed certificate (do not forget the password, it will be required for signing the module):
CREATE CERTIFICATE DemoCert
ENCRYPTION BY PASSWORD = 'This1$AC0MPL3XPassWord'
WITH SUBJECT = 'Demo Certificate Subject',
EXPIRY_DATE = '01/01/2015';
2. Now we create a certificate account with the following statement:
CREATE USER DemoCertAccount
FROM CERTIFICATE DemoCert;
3. The next step is to sign the stored procedure with the certificate and the password:
ADD SIGNATURE TO DemoCertSP
BY CERTIFICATE DemoCert
WITH PASSWORD = 'This1$AC0MPL3XPassWord';
4. The certificate account requires select permission on the table and execute permission on the stored procedure:
5. Finally we grant execute permission on the stored procedure to the user who requested the permission:
It is also possible to remove the signature from a module if it is no longer required:
DROP SIGNATURE FROM DemoCertSP
BY CERTIFICATE DemoCert;
- Create a certificate and test the module signing in your test environment.
- Read more about this topic on Microsoft Technet:
- Additional information about the CREATE CERTIFICATE statement.
- More detailed information about the ADD SIGNATURE statement.
- You can find more articles in the Security category.
- Read more tips by the author here
Last Updated: 2014-09-18
About the author
View all my tips