Seguridad con Funciones de Aplicación en SqlServer 2000
Escrito por Maximiliano Damian Accotto
Como todos saben, Sql maneja la seguridad con usuarios a los cuales se les puede dar acceso a los diferentes objetos (Tablas, Vistas, Procedimientos,etc) y diferentes funcionalidades por ejemplo:
Quizás un usuario A le demos acceso a solo lectura en la tabla Clientes y este mismo usuario no tenga acceso a la tabla Proveedores.
Bien todo esto es muy hermoso de verdad pero… Que sucede si queremos que los usuarios tengan acceso a los objetos pero solo por medio de una aplicación?, bueno todos sabemos que si al usuario A le damos un Excel y con los permisos dados aquí se puede bajar toda la lista de clientes y mandarla a la competencia por Ej. no? Bien entonces como hacer para que este usuario solo pueda Acceder a los clientes pero solo por una aplicación que nosotros le generaremos?
Bien aquí es donde entran en juego las Funciones de Aplicación.
Cual es la idea aquí, la función de aplicación funcionara como un usuario cualquier otro pero con esta salvedad.
Al usar una función de aplicación la que tendrá acceso a los datos es esta y no el usuario sin perder el origen de quien llamo a la función (el usuario en este caso).
Gracias a esta herramienta podríamos hacer algo así como:
Al usuario A le denegamos todos los permisos a todos los objetos de nuestra Base de Datos (por lo cual no puede leer ni hacer nada desde ningún lado), bien creamos nuestra función de aplicación y cuando el usuario por nuestra aplicación se conecta activamos la función de aplicación (esta misma tiene acceso total a todos los objetos).
Bien esto nos es de gran ayuda pero que pasa con la seguridad? Como se llama a una función de aplicación? Bien las funciones de aplicación se basan principalmente por un nombre y una clave (la cual pasare desde mi aplicación y hasta se puede enviar encriptada).
Ahora veremos unos ejemplos de todo esto así nos ponemos mas en tema.
/* Creamos primero un Usuario de Ej. a la Base Nortwind, este usuario se autentificara vía SQL para simplificar la cosa, pero recuerde que es aconsejable usar Autentificación Windows
*/
sp_addlogin 'user_a','prueba' -- Generamos el usuario User_a con Clave Prueba
GO
use Northwind
GO
sp_grantdbaccess 'user_a' -- Le damos Acceso al User_A a la Base Northwind
GO
Hagamos una prueba (Prueba A), entremos a nuestro analizador de consultas y pongamos los siguientes datos:
Usuario: User_A
Clave: Prueba
Al entrar probaremos esta consulta.
Use Northwind
Go
Select * from Customers
(91 filas afectadas)
Como se observa verán que se muestra el listado de clientes de nuestra Base.
Bien como dijimos antes, quizás necesitemos que esto solo sea valido desde nuestra aplicación, ya que esto mismo lo podría hacer el usuario desde un Excel y sacar el listado de Clientes de nuestra compañía.
Muy bien acá es donde entran las funciones de aplicación para poder resolver esto.
Entremos a nuestro Analizador de Consultas (como Admin o SA) y hagamos esto:
-- Le decimos que el User_a no podrá Escribir
sp_addrolemember 'db_denydatawriter','user_a'
go
-- Le decimos que el User_a no podrá leer
sp_addrolemember 'db_denydatareader','user_a'
Bien para probar esto deberían realizar la prueba A y verán que no hay acceso para este usuario ahora en la tabla Customers ni en ninguna otra.
Bueno ahora crearemos una función de aplicación la cual tendrá acceso total a nuestra Bdd y veremos como poder usar ello, manos a la obra.
/* Crearemos nuestra función de aplicación Mifuncion_app con clave Yukon
Recuerde de entrar al QA como SA por Ej. o Admin
*/
use Northwind
go
EXEC sp_addapprole 'mifuncion_app', 'yukon'
Go
Bien ahora hagamos la siguiente prueba y veamos como usar la función de aplicación:
Iniciemos nuestro Analizador de consultas (QA) con el inicio User_a y pass Prueba.
Como recuerdan user_a no tenía más acceso a la tabla Customers de nuestra Base Northwind.
Ahora usaremos la función de aplicación para que User_a tenga acceso mientras esta activada la función de aplicación a la tabla Customers, para ello usaremos el siguiente Store: sp_setapprole
Ejecutemos las siguientes líneas.
use northwind
go
EXEC sp_setapprole 'mifuncion_app', {Encrypt N 'yukon'}, 'odbc'
Como podrán observar este store nos habilita la función de aplicación anteriormente creada, la cual ahora tendrá el control de acceso sobre nuestros objetos (siempre dentro de la misma base de datos)
Ahora ejecutemos la siguiente línea:
Select * from customers
Como observaran se han devuelto los clientes como si User_a tuviera acceso a los datos.
Realmente la función de aplicación tiene acceso a los objetos (a esta función de aplicación se le pueden además dar permisos y esos será los que tomara).
La activación de aplicación la podría poner en su aplicación cliente y al cerrar la conexión se pierde dicho privilegio.
Además de esto ha algo muy interesante para mirar, escriban en su QA lo siguiente:
Select Suser_sname().
El usuario para SQL Sigue siendo User_a, por lo que no perdemos el origen de quien esta conectado a nuestras Bdd.
Conclusiones finales:
Si necesitamos que el acceso a nuestros objetos no sea directo por los usuarios, las funciones de aplicación son una herramienta poderosa y simple para poder resolver dicha cuestión.
Como dijimos antes, las funciones de aplicaciones tienen una gran limitación y es que son solo aplicables al amibito de la Bdd por lo cual hacer consultas distribuidas (un Select que busque en mas de una Bdd o Servidor) seria un problema.
Para resolver dicho problema se puede implementar lo siguiente (un consejo de mi amigo Miguel Egea).
Crear una vista (la que tiene la consulta distribuida) luego darle acceso a la función de aplicación a esa vista, lo cual nos da una enorme aplicación de este tipo de funciones.
Particularmente utilizo mucho esta herramienta de SQL ya que me da un gran resultado en su aplicación del mundo real.
Les recomiendo leer los siguientes Store en sus manuales on line (BOL)
sp_addapprole
sp_dropapprole
sp_setapprole
Y funciones de aplicación, donde podrán encontrar más información.
Espero les sea útil de verdad, a mi me han salvado la vida y de una forma muy simple.
Maximiliano Damian Accotto
Maxi_accotto@speedy.com.ar