Procedimientos Almacenados MySQL 5 - Capítulo II: Procedimiento almacenado, aportación a seguridad y velocidad

Capítulo II: Procedimiento almacenado, aportación a seguridad y velocidad

2.1.-Definición de base de datos
2.2.-Algunos mecanismos de una base de datos
2.3.-Definición de procedimientos almacenados
2.4.-Breve Historia de los procedimientos almacenados
2.5.-Razones para la existencia de los procedimientos almacenados


Capítulo II: Procedimientos almacenados, aportación a seguridad y velocidad.

En este capítulo se hablará de manera general de los elementos que deben existir para que un procedimiento almacenado pueda existir, como lo es una base de datos. Así mismo se verán otras características que también se observan en esta estructura de información, como son los índices, integridad referencial, transacciones, disparadores, vistas etc. Para documentar de manera introductoria el objeto de estudio se estudiará su definición, una breve reseña histórica así como las causas que dieron origen a su creación y sus aportaciones en seguridad y velocidad.

2.1.- Definición de base de datos.

“Una base de datos es una estructura de información que se auto describe y tiene como finalidad el almacenaje de datos de una manera óptima, esta se encuentra conformada principalmente por sub estructuras que llamamos tablas, y estas a su vez por partículas mas pequeñas llamadas campos donde las relaciones entre ellos se hacen presentes”.

Es importante tener en cuenta las limitantes del documento, donde se menciona que no se abordará otra base de datos que no sea la relacional, es por ello que la definición que se muestra está solamente abarcando el campo de la base de datos relacional.

Como se puede observar, el término “auto descriptivo” está presente en la definición, esto es por que se considera que mediante el análisis de la base de datos se puede tener una idea amplia de la razón de ser de ella, así como las funciones que realiza y como se encuentra estructurado tanto el problema que contribuye a solucionar como ella misma. Es decir, en palabras más simples una base de datos es un modelo de un fragmento de actividades de la vida humana.

2.2.-Algunos mecanismos de una base de datos

Desde luego, la definición de bases de datos que se presentó anteriormente es un tanto abstracta, y por consiguiente es conveniente mencionar al lector que existen algunos elementos que robustecen a las base de datos actuales, dotándolas de seguridad y velocidad, es por ello que de manera superficial se mencionará algunos de estos elementos.

2.2.1.- Índices.

Los índices realizan principalmente dos tareas, la primera “técnica de acceso a la información” que consiste en el plan que tiene la DBMS para conseguir la información solicitada de la manera mas rápida (estadísticas, clasificación de las consultas etc.) y la segunda que son los archivos auxiliares los cuales almacenan información que facilitan a la “técnica de acceso a la información”, su trabajo, dotándole de información clave para ello los índices se pueden definir de la siguiente manera.

“Sistema por medio del cual se puede identificar los registros individuales de manera distinta a un acceso secuencial.”

Estos elementos son usados con la finalidad de agilizar la búsqueda de información solicitada mediante una consulta. Es importante tener en cuenta que esta estructura de datos auxiliar colabora a mejorar notablemente la rapidez de respuesta de la base de datos, pero es también contraproducente en el caso de que las operaciones que se realicen en la base de datos sean mayoritariamente de actualización, inserción y eliminación.

2.2.2.-Integridad referencial.

Este mecanismo de la base de datos se puede definir de la siguiente manera.

“Sistema de validaciones de reglas de dependencia (o ausencia de ella) entre campos predefinidos por el diseñador de la base de datos”.

Si al diseñar una base de datos se establece por ejemplo que en la relación que hay entre la tabla clientes y la tabla créditos sea de uno a muchos, lo que se está asegurando con ello es que forzosamente debe de existir un cliente para que pueda existir un crédito y mas que eso, si solamente existe un cliente A y se pretende asignar un crédito al cliente B la base de datos automáticamente lo rechazará, auque existen algunas otras mas relaciones que dan vida a la integridad referencial, esta es la más comúnmente usada.

2.2.3- Transacciones.

Con la finalidad de ofrecer una confiabilidad de información a los usuarios de la base de datos se han incorporado algunos métodos que contribuyen a esto, como lo son las transacciones, para describir su funcionamiento y su importancia se recurrirá al ejemplo comúnmente usado para este fin que es el del movimiento bancario del cargo de una cuenta y el abono a otra, en este caso se pueden observar una operación que cuenta con dos movimientos lógicos en la base de datos, antes de comenzar a utilizar un sistema bancario es acertado pensar que éste ofrece la seguridad que se realizarán estas dos operaciones o en el caso de una falla del sistema no se realizará ninguna. Esto es precisamente de lo que se encargan las transacciones. Su definición lo confirma.

“Una transacción es un conjunto de sentencias SQL que repercutirán en una base de datos que se rigen mediante la atomicidad grupal de su ejecución”



2.2.4.-Disparadores.

Los disparadores son aquellas acciones que la base de datos ejecuta automáticamente antes o después de que ha sido modificada, estas rutinas pueden tener varias aplicaciones según el uso que se le esté dando a la base de datos, por poner un ejemplo se mencionará que un disparador se ejecutará cuando se realice un movimiento en la tabla pagos y tendrá la tarea de evaluar cual es el adeudo de cierto cliente, si ya no hay tal, en la tabla “clientes” se buscará éste y se colocará como cliente distinguido.

Su definición es la siguiente.

“Los disparadores son una orden que el sistema ejecuta de manera automática como efecto secundario de la modificación de la base de datos.”

2.2.5.-Vistas.

Este es otro elemento que colabora en la seguridad de la base de datos y da practicidad, esto lo consigue facilitando al administrador de la base de datos, el ocultar al usuario la estructura lógica de las tablas de la base de datos y por consiguiente volviendo más difícil el conocimiento total del sistema. Otro uso común de este elemento es el utilizado para realizar resúmenes de datos almacenados en tablas virtuales (vistas), sin necedad de programarlos y referirse a ellos como si se trataran de tablas reales. Y su definición lo corrobora.

“Relaciones que no forman parte del modelo lógico pero se hacen visibles al usuario como relaciones virtuales”

2.3.-Definición de procedimientos almacenados.

Como el primer acercamiento a los procedimientos almacenados es importante tener en cuenta que estos elementos están actualmente integrados en los más DBMS importantes, y fueron incorporados a ellos por las necesidades que surgían sobretodo en empresas donde se procesan grandes cantidades de información.

Los procedimientos almacenados son una herramienta que todo desarrollador debe tener en cuenta siempre, ya que proporcionan un rendimiento en términos de velocidad e incrementan la seguridad en su sistema de base de datos, es por ello que su empleo en los diferentes proyectos incrementa la calidad del desarrollo de software. Ahora se muestra la definición de un procedimiento almacenado.

“Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor y se ejecutan en él cuando un usuario con permisos para ello lo solicite”

A continuación se muestra un gráfico con la finalidad de entender de mejor manera el concepto.



Imagen 1: Comparativa entre utilización y no utilización de un procedimiento almacenado.

Como se puede observar en el gráfico, en el caso A se envió (n) consultas al DBMS, esto trae consigo un importante tráfico en la red, en cambio en el caso B el envío de una instrucción de ejecución de un procedimiento almacenado no ocupa mas que una sola vez la red en la solicitud de ejecución y la respuesta de procedimiento ocupará solamente el tamaño que ella tenga.

Esto es especialmente útil cuando es imposible mediante una sentencia SQL el rescatar toda la información que el usuario requiere, como por ejemplo en una factura del servicio telefónico que está sujeta a promociones, tipos de cliente, tipos de llamadas, localidades, horarios pico y no pico etc, para lo cual es necesario consultar varias tablas. La factura del recibo telefónico puede llegar a su realización mediante dos diferentes caminos, el caso A, en el cual la aplicación cliente solicitaría al DBMS cada consulta que considere necesaria para formar la factura o el caso B en el que en el DBMS se almacenaría la rutina para que se ejecutarán todas las sentencias SQL necesarias y enviaría como respuesta una estructura de información en la cual la aplicación cliente tendría todas los datos necesarios para imprimir dicha factura.

Es necesario realizar una diferencia en el uso de los procedimientos almacenados y las UDF (funciones definidas por el usuario) ya que los dos actúan de manera muy parecida, esta diferencia consiste en que los procedimientos almacenados aceptan una entrada múltiple y múltiples parámetros de salida, mientras que una UDF al igual que el procedimiento acepta una entrada múltiple pero solamente un único valor de salida, son fáciles de diferenciar ya que las UDF son compiladas en el servidor y se incrustan principalmente en sentencias SQL similares a sum(),count() etc.. Mientras que los procedimientos no es necesario compilarlos, y se utilizan sobretodo sentencias SQL.

Los estándares se hacen presentes en este tema, y es lógico, ya que con ellos se logra la unificación, convencionalidad y posibilidad de utilización de diferentes bases de datos con las mismas sentencias SQL al menos en los procedimientos almacenados, los DBMS mas robustos como ORACLE, SQL SERVER y MYSQL se encuentran regidos bajo el estándar SQL:2003, aunque con algunas excepciones cada uno de ellos.

2.4.-Breve historia de los procedimientos almacenados.

Desde que el hombre comenzó a utilizar medios digitales para almacenar su información comenzó a enfrentarse con problemas para hacer mas práctico este proceso, es lógico pensar que desde la utilización de los archivos ( que son los antecesores de las bases de datos ) se les fueron integrando algunas mejoras hasta llegar a la concepción que actualmente se tienen de base de datos, al paso del tiempo las empresas de la industria de software en especial la relacionada con las bases de datos incorporaron mecanismos como los mencionados en el sección 2.2 hasta llegar paulatinamente a incorporar, los procedimientos almacenados.

Los procedimientos almacenados no son nuevos en la industria de las bases de datos, como referencia se tiene a ORACLE, que presentó PL/SQL 2, su implementación de un lenguaje procedimental para SQL, esto por el año de 1991, SYBASE, PROSTGRESSQL Y DB2 están entre los otros DBMS que en breve siguieron este tan socorrido lenguaje procedimental para sentencias SQL.

A la publicación de este documento es MYSQL el mas reciente poseedor de procedimientos almacenados, esto invita a algunos expertos en la materia a mencionar que con esta incorporación este DBMS ha llegado a su mayoría de edad, aunque sin aventurarse a mencionar que pudiera ser una competencia seria para los grandes en esta rama como lo son ORACLE Y SQLSERVER.

2.5.- Razones para la existencia de los procedimientos almacenados.

Cubrir las diferentes necesidades de los usuarios de un DBMS debe de ser la filosofía a seguir de la industria de las bases de datos y este comentario es seguro que no pasó desapercibido por los desarrolladores ya que la totalidad de las bases de datos están haciendo o hicieron esfuerzos por incorporar los procedimientos almacenados a su software. Y se menciona de esta manera por que en realidad en estos tiempos en los que gran parte de la información del mundo se encuentra alojada en BD esto fue una necesidad, como lo pueden corroborar los capítulos que continúan.

La tendencia de las bases de datos actualmente va encaminada a darle más conocimiento a las bases de datos que a la aplicación, esto quiere decir que el cliente esté enterado lo menos posible de la estructura lógica de la DB, o al menos esto muestra la clara incorporación de algunos elementos como la integridad referencial, actualización y eliminación en cascada, disparadores, UDF´S y ahora procedimientos almacenados, los cuales realizan labores que antes eran propias de las aplicaciones cliente.

2.5.1.- Ocultando tablas al usuario por seguridad.

Sin abordar las técnicas utilizadas para realizar delitos informáticos relacionados con el envío y recepción de información por la red. Se mencionan algunos ataques que los usuarios con permisos para realizar consultas a la base de datos podrían llegar a intentar en el caso de la no incorporación de procedimientos almacenados en el sistema.

Para comenzar a abordar la seguridad que brinda un procedimiento almacenado a una base de datos se cita un fragmento de texto publicado en el manual de referencia de msyql 5.0.

“Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados para todas las operaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se loguea apropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos, sólo pueden ejecutar algunos procedimientos almacenados”.

Es muy importante considerar que un usuario con la intención de hacer algún daño al sistema puede ser aquel que tenga conocimientos avanzados en bases de datos y que tenga autorización para realizar algunas operaciones dentro del sistema, es decir, que tenga la oportunidad inclusive de utilizar un equipo “validado” por el DBMS. En este caso sería una irresponsabilidad como administrador de la base de datos pensar que la aplicación cliente le impediría lograr su objetivo, ya que tendría algunas herramientas que le colaboraría en su tarea (aplicaciones de administración de bases de datos, conectores ODBC , consolas., he inclusive algunos lenguajes de programación).


Ahora se supone el trabajar sobre el esquema de la base de datos de esta imagen y sobre el ejemplo que se cita a continuación, en donde se tiene como escenario un banco.




Imagen 2: Diagrama de base de datos de un banco


Ejemplo 1:

A un usuario que tiene el puesto de cajero se le tendrían que asignar los privilegios necesarios en el DBMS para realizar una operación de retiro o depósito de cuenta, es por ello que se le tiene que asignar los privilegios mínimos en las tablas que se listan en la siguiente cuadrícula.




Tabla 1: Asignación de privilegios a usuarios sin procedimiento almacenado

Nota:

Las vulnerabilidades que se muestran en las tablas se presentarán en el caso de que el usuario ingrese desde algún medio de acceso a la base de datos diferente a la aplicación cliente, ya que la aplicación está diseñada para la manipulación de la base de datos y es de suponer que tendría algunos mecanismos diseñados para evitar estas vulnerabilidades.


Ahora se incorpora un procedimiento almacenado en el DBMS bajo el siguiente esquema de invocación, aunque antes se ejecutarían algunos mas para saber el estado de la cuenta, el nombre del cliente al cual corresponde esa cuenta etc,.

Call realiza_retiro(usuario,clave,sucursal,num_cuenta,cant_retiro)

Donde:
Usuario.- Es el usuario que está invocando el procedimiento
Clave.- La clave del usuario que está invocando el procedimiento para realizar una comprobación en la tabla user de la base de datos mySQL, con la finalidad de saber que realmente es este usuario.
Sucursal.- la sucursal desde donde se está realizando el movimiento.
Num_cuenta.- La cuenta donde se realizará el retiro.
Cant_retiro.- cantidad por la cual será el retiro.

Ahora se muestran los privilegios que son necesarios asignar sobre tablas implementando el procedimiento almacenado.



Tabla 2: Asignación de privilegios a usuarios con procedimiento almacenado



Nota:

El único privilegio que debe de tener el usuario sobre la base de datos es el de ejecución para la utilización de todos los procedimientos almacenados que se encuentren en la base de datos, aunque esto no necesariamente indica que el usuario pueda utilizarlos todos, basta con que en cada procedimiento se pida el nombre de usuario y clave para verificar quien lo está ejecutando.

Es por estas razones los procedimientos almacenados se han vuelto tan utilizados en DBMS que manejan información que tiene carácter confidencial.

2.5.2.- Velocidad en los procesos.

Desde que se comenzaron a utilizar tan ampliamente las redes en torno a las bases de datos ha sido necesario la aportación de algunos elementos a ellas con la finalidad de hacer mas ágil el procesamiento de la información. Y es que con tantos procesos que realizan dos máquinas para poderse comunicarse como por ejemplo: conexión, autentificación, encriptación de información, en ocasiones obtención de certificados, partición de información en paquetes por parte del emisor, recopilación y ordenación de paquetes por parte de receptor y por su puesto el tiempo que tarda en viajar la información entre una máquina y otra. Es por ello que en ocasiones el tiempo que consumen estos procesos supera por mucho el tiempo que tarda el DBMS en procesar las sentencias SQL. Los elementos antes expuestos no tienen importancia alguna y es difícil realizar alguna acción para optimizar cuando por ejemplo se solicita el nombre de un cliente que se encuentra almacenada en la DB, pero esto cambia considerablemente cuando la salida de una sentencia SQL es la entrada a otra, considerando que los elementos iniciales pueden ser “n”.

Véase el siguiente ejemplo:

La estructura de la base de datos de la siguiente imagen fue estructurada con la finalidad de realizar nóminas quincenales de diferentes patrones.


Imagen 3: Diagrama de base de datos de generación de nómina

Con los siguientes detalles de esta estructura


A continuación se presenta el diagrama a bloques utilizado para generar la nómina del 1ro al 15 de enero del 2007, para cierto patrón.


Imagen 4: Diagrama a bloques de generación de nómina

Visualizando el diagrama anterior es claro que al término del paso A ya no es posible recuperar la información mediante una sola consulta SQL, es necesario realizar cálculos, así como consultar varias tablas por empleado, para poder realizar los pasos C,D y E. Es por ello que si el patrón tuviera 1000 trabajadores habría necesidad de hacer 4,000 consultas, ciertamente es algo que bajo esta estructura de base de datos no se puede evitar, pero eso no es realmente el problema, el problema radica a que esas 4000 consultas las enviará una aplicación cliente de una por una. Es decir, la comunicación entre cliente y servidor sería parecida a la de la siguiente imagen.



Imagen 5: Tráfico en red por no implementación de procedimiento almacenado


Pero no se irá mas allá, obsérvese como este problema se soluciona de manera muy conveniente con la implementación de procedimientos almacenados.

Ahora analizaremos la implementación de un procedimiento almacenado al cual solamente se le pasen los siguientes parámetros.

Call Calculo_de_nomina(patron,fechainicio,fechafin)

La manera en que se procesa la información con la implementación de un procedimiento se verá en la siguiente imagen.


Imagen 6: Tráfico en red por implementación de procedimiento almacenado

Es evidente que la velocidad en la generación de la nómina se verá mejorada a causa de que gran cantidad de información que no circulará a través de la red, y además como se aprecia, los procesos se realizarán de manera local. De la misma manera es importante mencionar que algunos DBMS tienen la característica de precompilar estos procedimientos, dando aún más velocidad en la respuesta de las consultas enviadas.

2.5.3.- Mejora de tráfico en la red

Como se ha venido mencionando a lo largo del documento los procedimientos almacenados se ejecutan del lado del servidor, es por ello que si se presenta un situación parecida a la de la el capítulo dos, sección dos y sub sección dos la cantidad de consultas que se muestra en la imagen 5 sería una cantidad importante de información que viajaría por la red, esto no trae tanto problema si la red sobre la cual se da esta situación fuese una red LAN pero, que pasaría si el servidor estuviera en la ciudad de monterrey y se necesita generar la nómina en Tuxtla Gutiérrez. La distancia y el tráfico en la red ya comienzan a ser parte importante del rendimiento de la base de datos, es por ello que la implementación de los procedimientos almacenados traería como resultado el mejoramiento de tráfico en la red mediante una proceso parecida a la imagen 6.





2.5.4.- Desarrollo independiente entre sentencias SQL y aplicación

Dentro de las características que se observan en la industria del software actualmente, se encuentra la fragmentación de la empresa en departamentos, algunos dedicados al soporte técnico, implementación, analistas, programadores, redes etc. En ocasiones, sobretodo en proyectos de tamaño considerable es común que haya personas especializadas en sentencias SQL o mas en específico en procedimientos almacenados, los cuales programan rutinas del lado del servidor con la finalidad de hacer mas modular el proyecto, como se observar en la imagen 6 esto se implementa con bastante facilidad ya que los programadores y expertos en SQL acuerdan las entradas del procedimiento y la estructura de datos que tendrá como salida y de esta manera cada uno puede trabajar hasta cierto punto de manera independiente.