{"id":1846,"date":"2020-05-25T20:49:55","date_gmt":"2020-05-25T20:49:55","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1846"},"modified":"2020-07-17T15:56:28","modified_gmt":"2020-07-17T15:56:28","slug":"firmar-un-sp-con-certificado-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1846","title":{"rendered":"Firmar un SP con certificado SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"266\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado___D.png\" alt=\"\" class=\"wp-image-1847\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Firmar procedimientos almacenados con certificados en SQL Server<\/h2>\n\n\n\n<p>Firmar los procedimientos almacenados usando un certificado es muy \u00fatil si se desea asignar permisos para la ejecuci\u00f3n del procedimiento almacenado sin conceder expl\u00edcitamente esos derechos al usuario usando Grant (<a href=\"https:\/\/manualsqlserver.com\/?p=617\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Permisos con Grant<\/a>).<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"has-medium-font-size\"><strong>Uso de Execute As<\/strong><\/p>\n\n\n\n<p>El uso de Execute As permite la ejecuci\u00f3n de un procedimiento almacenado sin necesidad de haber iniciado la sesi\u00f3n con el usuario que tiene permisos para ejecutar el procedimiento, los certificados de SQL server permiten realizar un seguimiento para buscar al autor de la ejecuci\u00f3n original del procedimiento almacenado.<br>El uso de los procedimientos almacenados firmados permite un alto nivel de auditor\u00eda, especialmente durante las operaciones de seguridad o de lenguaje de definici\u00f3n de datos (DDL) que son las instrucciones Create, Alter o Drop.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Niveles de permisos de los certificados<\/strong><\/p>\n\n\n\n<p style=\"font-size:18px\"><strong>Permisos a nivel de servidor<\/strong><\/p>\n\n\n\n<p>Si se crea un certificado en la base de datos \u00abmaster\u00bb se permiten permisos de nivel de servidor.<\/p>\n\n\n\n<p style=\"font-size:18px\"><strong>Permisos a nivel de Base de datos de usuario<\/strong><\/p>\n\n\n\n<p>Si se crea un certificado en la base de datos de usuario se asignan los permisos a nivel de la base de datos.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Pasos para firmar un procedimiento almacenado<\/strong><\/p>\n\n\n\n<p><strong>Los pasos para firmar los procedimientos almacenados para conseguir mejor nivel de auditor\u00eda y seguridad son los que se describen a continuaci\u00f3n:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>Crear un usuario en base a un login para asignar los permisos.<\/strong><\/li><li><strong>Crear un certificado de SQL Server.<\/strong><\/li><li><strong>Crear un procedimiento almacenado y firmarlo con el certificado creado.<\/strong><\/li><li><strong>Asignar los permisos para ejecutar el SP<\/strong><\/li><\/ol>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio<\/strong><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind, crear un SP que listar\u00e1 los productos y firmarlo con un certificado.<\/strong><\/p>\n\n\n\n<p><strong>1. Crear login y usuario (Ver Logins, Ver Usuarios)<\/strong><br>use master<br>go<br>Create login TrainerSQL with password = &#8216;123&#8217;<br>go<br>use Northwind<br>go<br>Create user TrainerUserConLogin<br>from login TrainerSQL<br>go<\/p>\n\n\n\n<p><strong>2. Crear el certificado<\/strong><br>set dateformat dmy<br>go<br>Create certificate TrainerCertificado<br>encryption by password = &#8216;TSQLCertificadoSP&#8217;<br>with subject = &#8216;Certificado para prueba de cifrado SP&#8217;,<br>Expiry_date = &#8217;15\/05\/2025&#8242;<br>go<br>Para listar los certificados<br>select * from sys.certificates<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01-1024x111.png\" alt=\"\" class=\"wp-image-1848\" width=\"789\" height=\"85\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01-1024x111.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01-300x33.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01-768x83.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01-1536x167.png 1536w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__01.png 1676w\" sizes=\"auto, (max-width: 789px) 100vw, 789px\" \/><\/figure>\n\n\n\n<p><strong>3. Crear el procedimiento y firmarlo con el certificado<\/strong><br>Create procedure spProductosListado<br>As<br>Select P.ProductID As &#8216;C\u00f3digo&#8217;,<br>P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>P.UnitPrice As &#8216;Precio&#8217;<br>from Products As P<br>&#8212; Ver el usuario que lo ejecuta<br>&#8212; No es parte del SP<br>select principal_id As &#8216;C\u00f3digo Usuario&#8217;,<br>name As &#8216;Nombre&#8217;<br>from sys.user_token<br>go<\/p>\n\n\n\n<p><strong>4. Para asignar el certificado al SP se necesita obviamente<br>el nombre del certificado y su password<\/strong><br>Add signature to spProductosListado<br>by certificate TrainerCertificado<br>with password = &#8216;TSQLCertificadoSP&#8217;<br>go<\/p>\n\n\n\n<p><strong>5. Crear el usuario a partir del certificado<\/strong><br>Create user TrainerUserConCertificado<br>from certificate TrainerCertificado<br>go<\/p>\n\n\n\n<p>Existen dos usuarios, TrainerUserConLogin que no ha sido creado en base al certificado y TrainerUserConCertificado que ha sido creado en base al certificado. Si todo funciona correctamente TrainerUserConLogin no podr\u00e1 ejecutar el procedimiento almacenado y TrainerUserConCertificado si.<\/p>\n\n\n\n<p><strong>Para ver los Logins que inician con el nombre Trainer<\/strong><br>Select * from sys.database_principals where name like &#8216;Trainer%&#8217;<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02-1024x117.png\" alt=\"\" class=\"wp-image-1849\" width=\"829\" height=\"94\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02-1024x117.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02-300x34.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02-768x87.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02-1536x175.png 1536w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_FirmarunSP_con_Certificado__02.png 1670w\" sizes=\"auto, (max-width: 829px) 100vw, 829px\" \/><\/figure>\n\n\n\n<p><strong>Asignar el permiso al usuario TrainerUserConLogin para ejecutar el procedimiento almacenado (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=617\" target=\"_blank\">Ver Permisos con Grant<\/a>)<br><\/strong>Grant Execute<br>on object::spProductosListado<br>to TrainerUserConLogin<br>go<\/p>\n\n\n\n<p><strong>Asignar el permiso al usuario TrainerUserConCertificado para ejecutar el procedimiento almacenado<br><\/strong>Grant Execute<br>on object::spProductosListado<br>to TrainerUserConCertificado<br>go<\/p>\n\n\n\n<p>Ejecutar el procedimiento como el usuario TrainerUserConCertificado, se sugiere en este paso conectarse nuevamente a SQL Server con el usuario para realizar la prueba. Otra forma es cambiar el entorno de ejecuci\u00f3n usando Execute As.<\/p>\n\n\n\n<p>execute as login = &#8216;TrainerSQL&#8217;<br>go<\/p>\n\n\n\n<p><strong>Ahora ejecutar el procedimiento almacenado<\/strong><br>Execute spProductosListado<br>go<\/p>\n\n\n\n<p><strong>Para restablecer el entorno de ejecuci\u00f3n<\/strong><br>Revert<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Firmar procedimientos almacenados con certificados en SQL Server Firmar los procedimientos almacenados usando un certificado es muy \u00fatil si se desea asignar permisos para la ejecuci\u00f3n del procedimiento almacenado sin conceder expl\u00edcitamente esos derechos al usuario usando Grant (Ver Permisos con Grant).<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1846\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1847,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[233,8],"tags":[168,17,18,21],"class_list":["post-1846","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sp","category-programacion","tag-create-certificate","tag-create-login","tag-create-procedure","tag-create-user","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1846","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1846"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1846\/revisions"}],"predecessor-version":[{"id":1850,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1846\/revisions\/1850"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1847"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1846"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1846"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1846"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}