• Author / Uploaded
• Erika Gyv

Citation preview

UNIVERSIDAD NACIONAL AUTÓNOMA DE HONDURAS EN EL VALLE DE SULA

Maestría en Gestión Informática Clase: Base de datos Catedrática: Ing. Josué Bladimir Jiménez Castro PROYECTO Investigación de la administración de bases de datos en Ferretería Nacional Integrantes: Carol Argeñal Erika Girón Valladares Jajaira Escalante López Raúl Galeano Roberto Hernández Fecha: 20 agosto de 2016

CONTENIDO INTRODUCCIÓN....................................................................................... 3

I. II.

RESUMEN EJECUTIVO (1 PAGINA).........................................................4

III.

DESCRIPCIÓN GENERAL DE LA EMPRESA.............................................5

Historia......................................................................................................... 5 Misión.......................................................................................................... 6 Visión........................................................................................................... 6 Valores.......................................................................................................... 6 IV.

DEFINICIÓN DEL TEMA.........................................................................7 JUSTIFICACIÓN........................................................................................ 8

V. VI.

PUNTOS SELECCIONADOS.....................................................................9

1.

Administración de usuarios........................................................................9 Recomendaciones y observaciones...................................................................9 Perfiles de Usuarios.................................................................................... 10 Políticas de Seguridad de Perfiles..................................................................15

2.

Diagrama Entidad-Relación......................................................................17

3.

Índices.................................................................................................. 17

4.

Optimización de Consultas.......................................................................22

5.

Monitoreo de procesos de base de datos.......................................................30

IBSurgen..................................................................................................... 31 HQbird....................................................................................................... 31 Aggregated performance statistics for users attachments...................................32 Sequential reads / Indexed reads...................................................................32 Attachments............................................................................................. 33 Transactions............................................................................................. 33 Statements................................................................................................ 34 6.

Respaldos y restauración de la base de datos................................................34

VII.

CONCLUSIONES Y RECOMENDACIONES..............................................53

Recomendaciones.................................................................................. 53 Conclusiones.......................................................................................... 53 VIII.

BIBLIOGRAFÍA................................................................................. 54

I.

INTRODUCCIÓN

El presente informe muestra los resultados de una investigación a la base de datos realizada en la Ferretería Nacional, dicho análisis se efectuó como un proyecto educativo en la clase de Diseño y Administración de Base de Datos de la Maestría en Gestión Informática, el cual tiene como objetivo evaluar la confiabilidad e integridad de los datos, mediante aplicaciones de herramientas de auditoria de base de datos. Los resultados de la investigación son basados en el enfoque de mejores prácticas de un DBA los cuales forman parte del contenido de la clase y como estas estrategias llevan a una organización a tener su información ordenada, estable y segura.

II.

RESUMEN EJECUTIVO

El proyecto consiste en la evaluación del sistema de base de datos que utiliza Ferretería Nacional en su sistema de información para sus procesos de ventas, inventarios, cotizaciones y finanzas. Ferretería Nacional está compuesta por un equipo profesional multidisciplinario constituido por vendedores y personal administrativo dedicados a la distribución de equipo y materiales de construcción a través de su base de datos. Con el fin de cumplir con el requisito final de la clase de Diseño y administración de la base de datos en aplicar los conceptos de la clase para traer mejoras en la base de datos de una organización, se seleccionó Ferretería Nacional para analizar e identificar oportunidades y riesgos. El objetivo del presente documento es proponer los cambios necesarios en la base de datos de la organización enfocado en las mejores prácticas de gestión de bases de datos alrededor de seguridad y desempeño. Contamos con la total colaboración de Ferretería Nacional y sus líderes para el análisis exitoso de su base de datos.

III. DESCRIPCIÓN GENERAL DE LA EMPRESA Historia El fundador, D. Luis García San Miguel, con altas dotes emprendedoras inicia su actividad empresarial con una ferretería de barrio, en el este de la ciudad de San Pedro Sula en el año 1979 en un edificio modesto y dedicado exclusivamente al negocio de ventas de ferretería. Local que todavía hoy existe en funcionamiento. Y que sería el germen de lo que hoy es Ferretería Nacional. Cabe destacar que Ferreterías Nacional es una empresa comprometida con los clientes. En general, la empresa se caracteriza por buscar el desarrollo de la región y por ello explora constantemente con la incorporación de nuevos productos y/o nuevos negocios. Dentro de su esquema de funcionamiento, es una empresa familiar con más de 30 años de historia en la cual ya trabajan activamente tres generaciones de la familia.

Misión Somos una empresa orientada a ofrecer la mejor calidad y variedad en productos, servicios de venta y distribución de material que satisfagan a nuestros clientes, brindándoles las mejores opciones de compra, con su personal calificado y dedicado al mejoramiento continuo, fomentando una relación exitosa junto a nuestros proveedores y clientes, con el mejor ambiente, para su confianza y lealtad.

Visión Ser una empresa líder en la venta de material de ferretería, orientado a satisfacer las necesidades y expectativas de nuestro mercado con eficiencia y eficacia, brindando

calidad en nuestros servicios con responsabilidad y compromiso de ofrecerle la mejor asistencia a nivel nacional.

Valores 

Excelencia: Creemos que el vivir y realizar nuestras labores diarias con



eficiencia, mejora continua y trabajo en equipo es la clave de nuestra excelencia. Integridad: Creemos que trabajar con ética, honradez y respeto a nosotros mismos, clientes, proveedores y accionistas son la base de nuestro éxito guiados



por una moral que garantiza nuestro sello distintivo. Compromiso: Creemos en nuestra capacidad para cumplir con nuestros deberes y responsabilidades, estando comprometidos con la organización, nuestros clientes y proveedores formando así parte del valioso recurso humano de nuestra



organización. Calidad y Servicio: Creemos en la calidad y servicio como la esencia de la organización brindándoles a nuestros clientes una atención personalizada satisfaciendo sus necesidades con productos de calidad con precios competitivos



y la mejor variedad de productos. Equidad: Creemos en nuestro valor individual y en ser tratados con igualdad y



justicia siendo indiferente nuestro género, raza, religión o escolaridad. Pasión y actitud: Creemos que nuestro talento personal nos motiva a mantener una actitud optimista llevándonos con determinación a cumplir nuestros objetivos y metas.

IV. DEFINICIÓN DEL TEMA Nuestro equipo se centró en investigar cinco puntos torales para el buen funcionamiento y buenas prácticas de bases de datos, estos puntos son los siguientes: 1. 2. 3. 4.

La administración de usuarios Índices Optimización de consultas Respaldos y restauración de la base de datos 5. Monitoreo de procesos de base de datos Con este estudio se pretende brindar a la Ferrería Nacional recomendaciones para mejorar el manejo de la información.

V.

JUSTIFICACIÓN

El almacenamiento de datos es de suma importancia para toda empresa ya que constituye uno de los soportes principales para ella. De ahí surge la necesidad que la información se resguarde de forma ordenada ya que hoy en día todas las empresas resguardan su información de diferentes formas y algunas utilizan métodos actualizados como son bases de datos que les permite mantener la información de forma segura y confiable. En esta investigación se tomó como objeto de estudio la Ferretería Nacional, ubicada en la ciudad de San Pedro Sula, con el objetivo de evaluar la administración de su base de datos, los perfiles de usuarios,

VI. PUNTOS SELECCIONADOS 1. Administración de usuarios La administración de usuarios es el proceso de controlar que usuarios están permitidos comunicación con la base de datos y que privilegios tienen en la misma. Esta gestión de privilegios es fundamental en la seguridad y el desempeño que tendrá la base de datos. Toda organización debe mantener una política de seguridad de cada base de datos orientada a proteger la información de que sea destruida de forma maliciosa o que se pueda dañar la infraestructura de la base de datos. Cada base de datos debe tener un administrador(DBA) el cual en muchos casos es asociado como un administrador de seguridad. El administrador es responsable de implementar y administrar las políticas de seguridad de la base de datos a través de la administración de usuarios. En base a la magnitud de una base de datos y la cantidad de bases de datos en una organización, se puede considerar estas responsabilidades a un grupo en vez de un individuo.

Recomendaciones y observaciones

En el caso de la base de datos de nuestro proyecto, no existe una administración de usuarios adecuada. La base de datos no cuenta con perfiles y usuarios definidos a nivel de base de datos. Las aplicaciones desarrolladas se conectan a través de un usuario con el máximo nivel de privilegios (sysdba). Los accesos a nivel de registros (datos) en la base de datos son determinados por una tabla del esquema:

La base de datos debe administrar la seguridad de todas las aplicaciones en vez de depender de una tabla que forma parte de la base de datos. Una tabla interna que sea responsable de las credenciales de usuarios y aplicaciones es una vulnerabilidad seria. Esta tabla no tiene ni siquiera el campo de la clave encriptado.

Perfiles de Usuarios

Uno de los cambios que se tiene que gestionar en la base de datos es la creación de los siguientes perfiles: 1. 2. 3. 4. 5.

Cajeros Vendedores DespachoBodega Administrador Contador

Al crear estos perfiles, el DBA solo asignara los privilegios necesarios. Luego los usuarios creados serán asignados a los perfiles de acuerdo a las necesidades de información que tienen y en base a los alcances definidos por la organización. Inevitablemente las aplicaciones desarrolladas se tendrán que actualizar en términos del usuario que utilizan para las transacciones retirando por completo el uso del usuario sysdba a nivel de aplicaciones.

Los accesos por perfil a nivel de tablas serán los siguientes: Perfil Cajero

Tabla Clientes

Acceso Escritura

Compra

Escritura

Compra_C

Escritura

Compra_Cot

Escritura

Cotizacion

Escritura

Credito

Lectura

Deposito

Escritura

Detalle_C

Escritura

Detalle_COM

Escritura

Detalle_Coti

Escritura

Detalle_DC

Escritura

Vendedores

Detalle_DP

Escritura

Detalle_V

Escritura

Devolucion_C

Escritura

Devolucion_P

Escritura

Factura_C

Escritura

Factura_V

Escritura

Orden

Escritura

Orden_Detalle

Escritura

Recibo

Escritura

Tipo_Cliente Bodega

Lectura Lectura

Cheques

Lectura

Cierre

Lectura

Clientes

Escritura

Compra

Escritura

Compra_C

Escritura

Compra_Cot

Escritura

Cotizacion

Escritura

Credito

Lectura

Defectuoso

Lectura

Deposito

Lectura

Detalle_C

Escritura

Detalle_Com

Escritura

Detalle_Coti

Escritura

Detalle_DC

Escritura

Detalle_DP

Escritura

Detalle_V

Escritura

Devolucion_C

Escritura

Devolucion_P

Escritura

Empleado

Lectura

Estante

Lectura

Factura_C

Escritura

Factura_V

Escritura

Gasto_V

Lectura

Grupo

Lectura

Inventario

Lectura

Nivel

Lectura

Orden

Escritura

DespachoBodega

Administrador

Orden_Detalle

Escritura

Producto

Lectura

Prod_R_Prov

Lectura

Proveedor

Lectura

Recibo

Lectura

Retiro

Lectura

Retiros

Lectura

Sub_Grupo

Lectura

Tipo_Cliente Bodegas

Lectura Escritura

Cierre

Lectura

Compra

Lectura

Compra_C

Lectura

Compra_Cot

Lectura

Congelar

Lectura

Cotizacion

Lectura

Defectuoso

Escritura

Devolucion_C

Escritura

Devolucion_P

Escritura

Estante

Escritura

Factura_C

Lectura

Factura_V

Lectura

Inventario

Escritura

Nivel

Lectura

Orden

Lectura

Orden_Detalle

Lectura

Producto

Escritura

Prod_R_Prov

Escritura

Proveedor

Escritura

Provee_Conta

Escritura

Recibo

Escritura

Retiro

Escritura

Retiros

Escritura

Sub_Grupo Bancos

Escritura Escritura

Bodega

Escritura

Cheques

Escritura

Cierre

Escritura

Clientes

Escritura

Compra

Escritura

Compra_C

Escritura

Compra_Cot

Escritura

Congelar

Escritura

Cotizacion

Escritura

Credito

Escritura

Cuenta

Escritura

Defectuoso

Escritura

Departamento

Escritura

Deposito

Escritura

Detalle_C

Escritura

Detalle_Com

Escritura

Detalle_Coti

Escritura

Detalle_DC

Escritura

Detalle_DP

Escritura

Detalle_V

Escritura

Devolucion_C

Escritura

Devolucion_P

Escritura

Empleado

Escritura

Estante

Escritura

Factura_C

Escritura

Factura_V

Escritura

Gasto_V

Escritura

Grupo

Escritura

Inventario

Escritura

Nivel

Escritura

Orden

Escritura

Orden_Detalle

Escritura

Prestamo

Escritura

Producto

Escritura

Prod_R_Prov

Escritura

Proveedor

Escritura

Provee_Conta

Escritura

Recibo

Escritura

Retiro

Escritura

Retiros

Escritura

Sub_Grupo

Escritura

Contador

Tipo_Cliente

Escritura

Vinetas_Conf

Escritura

Bancos

Escritura

Bodega

Escritura

Cheques

Escritura

Cierre

Escritura

Clientes

Lectura

Compra

Escritura

Compra_C

Escritura

Compra_Cot

Escritura

Congelar

Escritura

Cotizacion

Escritura

Credito

Escritura

Cuenta

Escritura

Departamento

Lectura

Deposito

Escritura

Detalle_C

Escritura

Detalle_Com

Escritura

Detalle_Coti

Escritura

Detalle_DC

Escritura

Detalle_DP

Escritura

Detalle_V

Escritura

Devolucion_C

Escritura

Devolucion_P

Escritura

Empleado

Lectura

Factura_C

Escritura

Factura_V

Escritura

Gasto_V

Escritura

Grupo

Escritura

Inventario

Escritura

Nivel

Escritura

Orden

Escritura

Orden_Detalle

Escritura

Prestamo

Escritura

Producto

Escritura

Prod_R_Prov

Escritura

Proveedor

Escritura

Provee_Conta

Escritura

Recibo

Escritura

Retiro

Escritura

Retiros

Escritura

Sub_Grupo

Escritura

Tipo_Cliente

Escritura

Vinetas_Conf

Escritura

Políticas de Seguridad de Perfiles 1) El DBA será la única persona autorizada para la creación, edición, debilitación y eliminación de perfiles. 2) El DBA será la única persona autorizada a crear, editar, deshabilitar y eliminar usuarios. 3) Todo usuario creado debe formar parte de por lo menos un perfil. 4) La clave inicial de cada usuario es definida por el DBA pero el usuario lo tendrá que actualizar el día que reciba sus credenciales. 5) Ningún usuario está permitido en divulgar sus credenciales. 6) La contraseña de todo usuario expira cada 30 días. Todo usuario podrá actualizar su clave antes de los 30 días si se requiere. 7) La clave de todo usuario debe tener una longitud mínima de 8 caracteres consistiendo de por lo menos una letra mayúscula, un número y un símbolo. No se permitirán claves parecidas a claves usadas en el pasado por el mismo usuario. 8) Toda gestión asociada con la crear, editar, deshabilitar y eliminar perfiles o usuarios debe quedar documentada con los siguientes datos: a. ¿Quién solicita la gestión? b. Fecha de solicitud c. Usuario o Perfil d. Tipo de gestión (crear, editar, deshabilitar, eliminar) e. Duración de acción (temporal o permanente) f. Firma de Jefe de área g. Firma de DBA h. Firma de Jefe de RRHH i. Motivo detrás de la gestión 9) Toda transacción realizada por cada usuario quedara registrada en los logs de la base de datos. 10) El DBA recibirá un correo después de que un usuario realice tres intentos fallidos en validar sus credenciales. 11) Se realizará una auditoria de los accesos de cada perfil y los usuarios que forman parte de los perfiles cada seis meses para asegurar que los accesos definidos en el perfil NO son una violación a los datos confidenciales que deben acceder y para validar que los usuarios que forman parte de cada perfil son las personas

que realmente deben estar en dichos grupos. Los hallazgos de la auditoria serán comunicados a la gerencia general y a la gerencia de recursos humanos. 12) Cada perfil tendrá un horario definido de acceso a la base de datos y un tiempo de sesión establecido el cual al vencerse exige que se vuelvan a ingresar las credenciales del usuario de la sesión.

2. Diagrama Entidad-Relación Los datos se estructuran en una o varias tablas donde relacionan uno o varios conjuntos de datos. Las tablas se pueden modificar fácilmente siguiendo un diseño preestablecido por los programadores buscando la mejor manera posible de obtener una integridad total de la información. Las bases de datos con relaciones establecidas entre tablas son más rápidas y la facilidad con la que permite transformar el modelo de datos es rápido, la sencillez de la propia estructura física de este tipo de bases de datos y la posibilidad de adaptación a una serie de entornos de datos más amplia. La base datos de Ferretería Nacional no tiene casi ninguna relación entre sus tablas, salvo la relación que existen entre la tabla Factura_C y la tabla Producto. Por lo que las consultas a las tablas son lentas. Además, el usuario, podría eliminar registros sin tener ningún tipo de restricción.

3. Índices Un índice como bien se sabe, es una estructura de disco asociada con una tabla o una vista que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura (árbol b) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave. Los índices mal diseñados y la falta de índices constituyen las principales fuentes de atascos en aplicaciones de base de datos. El diseño eficaz de los índices tiene gran importancia para conseguir un buen rendimiento de una base de datos y una aplicación. La selección de los índices apropiados para una base de datos y su carga de trabajo es una compleja operación que busca el equilibrio entre la velocidad de la consulta y el costo de actualización. Los índices estrechos, o con pocas columnas en la clave de índice, necesitan menos espacio en el disco y son menos susceptibles de provocar sobrecargas debido a su mantenimiento. Por otra parte, la ventaja de los índices anchos es que cubren más consultas. Puede que tenga que experimentar con distintos diseños antes de encontrar el índice más eficaz. Es posible agregar, modificar y quitar índices sin que esto afecte al esquema de la base de datos o al diseño de la aplicación. Por lo tanto, no debe dudar en experimentar con índices diferentes. A continuación, se muestra todas las llaves primarias que posee la base de datos, como se puede observar en la figura, en la cual se ve claramente que solo existen dos índices, los cuales corresponden a la tabla FACTURA_C (factura de caja), estos índices corresponden a los campos COD_EMPLEADO Y COD_CLIENTE.

Por otra parte, la figura muestra la tabla FACTURA_C, tiene 16 campos, donde se pude observar claramente que la clase primaria es el campo factura_v. Así mismo, se observa que esta tabla contiene dos campos índices por medio de los cuales se puede acceder a la tabla empleado y cliente. En esta tabla se almacena información sobre la factura final, es por ello que existe un índice cod_empleado ya que cada factura emitida por un empleado le genera comisión al empleado. Por otro lado, el cod_cliente se utiliza para verificar compras del cliente.

Partiendo de lo anterior, se procede a crear índices en tablas que generan el mayor número de transacciones, y por ende el mayor número de información. Estas tablas son las siguientes:    

DETALLE_C FACTURA_V CLIENTE PRODUCTO

TABLA: DETALLE_C

En esta tabla se procede a crear dos índices por un lado el cod_facrura y por otro el cod_producto), con el fin de optimizar las consultas en estas dos tablas. A continuación, la figura muestra el script para la creación de estos índices.

|

TABLA FACTURA_V La figura muestra la estructura de la tabla FACTURA_V, la cual muestra los campos de la misma, así mismo, se observa que no tiene ningún campo índice. Por lo que se procede a crearlos.

A continuación, la figura muestra el script para la creación de estos índices. TABLA CLIENTES

La figura, muestra la estructura de la tabla CLIENTES la cual muestra los campos de la misma, así mismo, se observa que no tiene ningún campo índice. Por lo que se procede a crearlos.

A continuación, la figura muestra el script para la creación de estos índices.

TABLA PRODCUTOS

La figura, muestra la estructura de la tabla PRODCUTOS la cual muestra los campos de la misma, así mismo, se observa que no tiene ningún campo índice. Por lo que se procede a crearlos

A continuación, la figura muestra el script para la creación de estos índices.

4. 4. 4. Optimización de Consultas Los índices bien diseñados pueden reducir las operaciones de E/S de disco y consumen menos recursos del sistema, con lo que mejoran el rendimiento de la consulta. Los índices pueden ser útiles para diversas consultas que contienen instrucciones SELECT, UPDATE, DELETE o MERGE. Cuando se ejecuta la consulta, el optimizador de

consultas evalúa cada método disponible para recuperar datos y selecciona el método más eficiente. El método puede ser un recorrido de la tabla o puede ser recorrer uno o más índices si existen. A continuación, se explica el antes y después de algunos de los procedimientos más importantes

Procedure Cliente_Venta ANTES FOR SELECT CLIENTES.cod_cliente,factura_c.nombre, DETALLE_C.cod_produc,DETALLE_C.descripcion,detalle_c.cantidad, DETALLE_C.precio,DETALLE_C.descuento,DETALLE_C.impuesto, DETALLE_C.total,detalle_c.precio_compra,factura_C.cod_factv, factura_c.total,detalle_c.cantidad*detalle_c.precio_compra as toti, factura_c.fecha FROM factura_c, clientes,detalle_c where

factura_c.cod_cliente=clientes.cod_cliente

factura_c.cod_factv=detalle_c.cod_factc

and

factura_c.fecha>=:fecha1

factura_c.fecha=:fecha1 and factura_c.fecha=:fecha1 and factura_c.fecha