Module Signing In Practice

Solomon Rutzky shows how you can use certificate-based module signing to grant users fine-grained permissions:

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$ViewServerState] ENCRYPTION BY PASSWORD = 'UseBetterPassword!' WITH SUBJECT = 'VIEW SERVER STATE permission', EXPIRY_DATE = '2099-12-31';
/*
The password is used to protect only the Private Key, not the Public
Key or the Certificate in general.
The default expiration date is 1 year from the date the Certificate is
created. However, in many situations, such as with Module Signing, the
expiration date is actually ignored. Still, I prefer to set the
expiration date to 2099.
*/

The DBA handling this server in the year 2100 is going to be really upset.  Certificate signing works wonders in conjunction with stored procedures, as you can grant procedures all kinds of rights needed for your application but users cannot run ad hoc queries expecting those same rights.

Related Posts

Security Improvements In Kafka And Confluent Platform

Vahid Fereydouny demonstrates a number of security improvements made to Apache Kafka 2.0 as well as Confluent Platform 5.0: Over the past several quarters, we have made major security enhancements to Confluent Platform, which have helped many of you safeguard your business-critical applications. With the latest release, we increased the robustness of our security feature […]

Read More

Calculating Effective Rights For A SQL Server Principal

Louis Davidson gives us a procedure which provides effective rights for a principal: In my blog Calculating a Security Principal’s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal’s rights to objects in a database. In that blog I tested the code and showed how it works. Now I […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728