Download Sin título de diapositiva
Document related concepts
Transcript
Administración de Bases de Datos Diseño de Bases de Datos y Seguridad de la Información Curso 2010-2011 1 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 2 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 3 Introducción ¿Qué es la Administración? El Administrador de la Base de Datos (DBA- DataBase Administrator) es el responsable del Diseño de la Arquitectura, Control y Administración de la Base de Datos. Función que puede ser desempeñada por una persona o un grupo de personas. Función reconocida por diferentes grupos de estandarización (Codasyl, ANSI/X3/SPARC, ...). 4 Introducción Arquitectura ANSI/X3/SPARC propone tres tipos de administradores Adm. aplicaciones NIVEL EXTERNO ESQUEMA EXTERNO 1 ESQUEMA EXTERNO n Correspondencia Externa/Conceptual Adm. empresa NIVEL CONCEPTUAL ESQUEMA CONCEPTUAL Correspondencia Conceptual/Interna Adm. BD NIVEL INTERNO ESQUEMA INTERNO DATOS ALMACENADOS 5 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 6 Administrador de la Base de Datos Tareas del Administrador de la Base de datos Instalación del Sistema Gestor de la Base de Datos (p.e. Oracle10g) Diseño de la arquitectura de la BD y de modificaciones posteriores Creación de la Base de Datos Ajustes en la Base de Datos Eficiencia Seguridad de la Información (Confidencialidad) Copias de Seguridad y Recuperación de Información (Disponibilidad) Mantener el gestor y sus herramientas en un perfecto estado de funcionamiento para conseguir una mayor eficacia y rapidez en el acceso de la información. 7 Administrador de la BD Objetivo principal: Mantener la integridad, la confidencialidad y la disponibilidad de los datos del sistema. 8 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Gestión de Segmentos de Rollback 10.Bibliografía 9 Arquitectura de Oracle ¿Qué es Oracle10g? Sistema Gestor de Bases de Datos ObjetoRelacionales Principales características de la arquitectura Oracle: o Única copia del núcleo de Oracle (Oracle Server) o SGA (Área Global Compartida) o Mecanismos de Recuperación de Errores Arquitectura Interna: Física: ficheros del Sistema Operativo Lógica: conceptos de almacenamiento a nivel de BD La estructura lógica y física no tienen porqué coincidir 10 Arquitectura de Oracle Conexión a una BD Servidor Cliente • Incluye User Program Interface (UPI) • Genera llamadas al proceso servidor Proceso Servidor Proceso Usuario • Configuración de Servidor Dedicado: 1 a 1 • Usa una PGA (Program Global Area) exclusiva • Incluye Oracle Program Interface (OPI) • Procesa peticiones de cliente • Entrega de resultados a cliente Servidor ORACLE Usuario: SQL*Plus, Oracle Enterprise Manager, Developer/2000, ... 11 Arquitectura de Oracle Procesamiento de una consulta SELECT * FROM Alumnos ORDER BY apellidos; Sentencia Manejador Manejador Proceso Servidor Proceso Usuario Resultados Compilar Ejecutar Cargar OK OK 12 Arquitectura de Oracle Procesamiento de una modificación UPDATE Alumnos set edad=19 where nombre=‘María’; Sentencia Manejador Proceso Servidor Proceso Usuario Compilar Ejecutar OK OK 13 Arquitectura de Oracle ¿Qué es una instancia de BD? Consiste en una estructura de memoria SGA (System Global Area) y un conjunto de procesos usados por el servidor de Oracle para gestionar una BD. Es una forma de acceder a una BD. Se identifica por un ORACLE_SID. Puede abrir y usar sólo una BD en cualquier momento. Instancia SGA 14 Arquitectura de Oracle ¿Qué es una BD Oracle? Información BD Arranque y parada de la BD Ubicación de ficheros de datos Redo Log por Actual y Secuencia está compuesta un … Representa la estructura física conjunto de ficheros (SO). Se identifica por un nombre de BD: DB_NAME. Se recomienda: ORACLE_SID=DB_NAME Tablespaces Parameter file Password file Transacciones y cambios en la BD Control files Data files Redo log files Archived log files Base de Datos 15 Arquitectura de Oracle Instancia de BD Database Buffer Cache Redolog Buffer Instancia SGA Shared Pool Library Cache Dictionary Cache Procesos DBWR (Database Writer) LGWR (Log Writer) SMON (System Monitor) PMON (Process Monitor) CKPT (Checkpoint Process) 16 Arquitectura de Oracle SGA: Shared Pool Shared pool Library cache Data dictionary cache – Se usa en la fase de compilación – Tamaño se define en el parámetro SHARED_POOL_SIZE – Dos componentes usados para compilar una sentencia SQL: • Library cache contiene la sentencia SQL, el código compilado, y un plan de ejecución • Data dictionary cache contiene las definiciones de tabla y columnas así como los privilegios (bloqueos) 17 Arquitectura de Oracle SGA: Database Buffer Cache – Número de búffers se define en DB_BLOCK_BUFFERS – Tamaño de cada bloque se basa en el tamaño del bloque (DB_BLOCK_SIZE) – Almacena los últimos bloques usados 18 Arquitectura de Oracle Segmento de Rollback Tabla Valor Antiguo Nuevo Valor Update Proceso Servidor guarda el valor antiguo en el Segmento de Rollback − Poder deshacer cambios − Consistencia en lectura − Recuperación de la BD en caso de fallo 19 Arquitectura de Oracle SGA: Redo Log Buffer – – – – Tamaño definido en el parámetro LOG_BUFFER (en bytes) Registra los cambios realizados en la instancia Se usa de forma secuencial (intercalando transacciones) Es circular 20 Arquitectura de Oracle Procesamiento de una sentencia de manipulación Bloqueos UPDATE Alumnos set edad=19 where nombre=‘María’; 3 Instancia 4 1) Compilación 2) Ejecución Proceso Servidor 5 “dirty” SGA Shared pool Library cache Database buffer cache Redo log buffer Data dictionary cache 2 1 Data files Control files Base de Datos Redo log files 21 Arquitectura de Oracle PROCESOS DE UNA INSTANCIA: • DBWR (Database Writer) responsable de escribir las modificaciones de los datos en la BD • LGWR (Log Writer) registra los cambios registrados en el buffer redo log en la BD • SMON (System Monitor) su principal función es comprobar la consistencia e iniciar la recuperación de la BD si está abierta • PMON (Process Monitor): refresca los recursos si falla algún proceso • CKPT (Checkpoint Process): responsable de actualizar la información del estado de la BD siempre que los cambios en la caché del buffer se almacenan permanentemente en la BD V$BGPROCESS: Tabla que contiene los procesos en segundo plano disponibles en la BD 22 Arquitectura de Oracle Database Writer (DBWR) Instancia SGA dirty buffers Shared pool Database buffer cache DBWR Data files Control files Redo log files Base de Datos 23 Arquitectura de Oracle Log Writer (LGWR) Instancia SGA Shared pool Redo log buffer LGWR Data files Control files Redo log files Base de Datos 24 Arquitectura de Oracle Procesamiento de COMMIT 1 registro Commit (SCN) Proceso Servidor Se informa al usuario Transacción completada 4 Instancia SGA Database buffer cache Shared pool Redo log buffer LGWR 3 Secuencial Proceso Usuario Data files SCN (System Change Number) 2 commit Control files Base de Datos Redo log files 25 Arquitectura de Oracle Ficheros de la Base de Datos o Los datos se almacenan en uno o más ficheros físicos situados en uno o varios discos. o Cada fichero sólo puede estar asociado a una BD. o Oracle crea un espacio físico del tamaño que elija el DBA y luego lo va rellenando con datos. Espacios de Tabla (Tablespaces) o Unidades lógicas de almacenamiento en las que se divide una BD. o Un espacio de tabla puede estar almacenado en varios ficheros físicos; para Oracle son una única unidad. o Sirven para racionalizar la información. Mejora de funcionamiento. 26 Arquitectura de Oracle Segmentos o Agrupación de bloques Oracle no necesariamente contiguos. o Los tipos de segmentos más importantes son: tablas, índices y segmentos de rollback. Extensiones o Una extensión es un conjunto de datos (bloques Oracle) contiguos. 27 Arquitectura de Oracle ¿Cómo se almacena una BD? Nivel Lógico: Tablespaces Base de Datos System Temp Rollback Tools Datos Índices 28 Arquitectura de Oracle ¿Cómo se almacena una BD? Nivel Lógico: Base de Datos Tablespace2 TablespaceN Tablas, Índice y Agrupaciones Tablas, Índice y Agrupaciones Tablas, Índice y Agrupaciones Seg1 Seg1 Tablespace1 E1 SegN EN E1 EN SegN E1 EN E1 EN Seg1 E1 SegN EN E1 EN 29 Arquitectura de Oracle ¿Cómo se almacena una BD? Nivel Físico: Base de Datos F1_System F1_Temp F1_Rollback F1_Tools F1_Datos F1_Índices F2_Datos F2_Índices F3_Datos F3_Índices F2_Tools 30 Arquitectura de Oracle Database Tablespace Lógico Segment ME/R de BD Oracle Data file Físico Extent Oracle block O/S Block 31 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 32 Arranque y Parada de una BD Se puede hacer manualmente desde el Server Manager. Automáticamente cada vez que se arranque o pare la máquina. 33 Arranque y Parada de una BD Arranque de la BD Etapas de arrancar una base de datos: Startup [open] Startup mount Startup nomount NOMOUNT Leer INIT.ORA Identificar ficheros de control Crear e inicializar SGA Lanzar procesos de segundo plano Información BD Arranque y parada de la BD Ubicación de ficheros de datos Secuencia Redo Log Actual … DBWR (Database Writer) LGWR (Log Writer) SMON (System Monitor) PMON (Process Monitor) CKPT (Checkpoint Process) MOUNT Abrir ficheros de control Monta la BD (obtener “instance lock”) OPEN Abrir los ficheros de datos (open and lock) Abrir los ficheros redolog que estén on-line ¿Recuperación? 34 Arranque y Parada de una BD Arranque de la BD Formas de arrancar una base de datos: o Arrancar la instancia pero sin montar la BD (creación BD o pérdida fichero control): sqlplus / as sysdba; set oracle_sid = BDA; startup nomount [dbname]; o Arrancar la instancia, montar la BD pero no abrirla (ALTER DATABASE): startup mount [dbname]; o Arrancar la instancia, montar la BD y abrirla ( modo general y modo restringido): startup open [dbname]; o startup restrict; o Arrancar la BD con un fichero de parámetros específico: startup pfile=/disco0/oracle/dbs/initSID.ora; 35 Arranque y Parada de una BD Parada de la BD Formas de parar una base de datos: o Parada normal: permite terminar trabajos pendientes. No permite nuevas conexiones. set ORACLE_SID=SID shutdown; o shutdown normal; o Parada inmediata: sin esperar a que los usuarios que están conectados cierren su sesión normalmente. Todas las transacciones sin validar se les ejecuta un Rollback. Proceso de recuperación automático (sin intervención DBA). shutdown immediate; o Interrupción de una instancia: si BD no funciona bien y ninguna de las anteriores opciones funciona. Transacciones no válidas no se les ejecuta un Rollback. Desconexión de todos los usuarios. Recuperación al arrancar la BD (a partir de versión 8i de ORACLE se realiza de forma automática). shutdown abort; 36 Arranque y Parada de una BD OPEN MOUNT NOMOUNT Se abren todos los ficheros, tal y como se describe en los ficheros de control de esta instancia. Ficheros de control abiertos para esta instancia Instancia iniciada SHUTDOWN 37 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 38 Creación de una Base de Datos Pasos Previos: o Estimar tamaño que vamos a necesitar (¿BD anterior?) o Modelo Conceptual--> Modelo Lógico Estándar / Específico o Tipo de Protección y Seguridad necesaria o Opcionalmente: Copia de Seguridad de Datos de BD anterior BD 39 Creación de una Base de Datos Pasos para la creación de la BD: o Crear fichero de parámetros INITSID.ORA: DB_NAME, DB_BLOCK_SIZE, DB_BLOCK_BUFFERS, PROCESSES, ROLLBACK_SEGMENTS, ... o Activar la instancia de BD que vamos a crear: STARTUP NOMOUNT; o Creación de la BD: CREATE DATABASE basedatos Generación automática de ficheros de control controlfile_reuse logfile ‘log1’, ‘log2’ size 500K datafile ‘ora1.dbf’ size 50M; − Creación de: ficheros de datos, ficheros de control, ficheros redolog, tablespace SYSTEM, segmentos de rollback de SYSTEM, diccionario de datos, administradores SYS y SYSTEM. − Se monta y abre la BD. ALTER DATABASE OPEN; 40 Creación de una Base de Datos Ajuste de Parámetros # Initialization Parameter File: initSID.ora db_name = prueba control_files = (/DISK1/control01.ctl, /DISK2/control02.ctl) db_block_size = 8192 db_block_buffers = 2000 2000*8 ~ 15,6 M shared_pool_size = 30000000 log_buffer = 64K processes = 50 db_files = 100 log_files = 10 max_dump_file_size = 10240 background_dump_dest = (/home/disk3/users/BDUMP) user_dump_dest = (/home/disk3/users/UDUMP) core_dump_dest = (/home/disk3/users/CDUMP) rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08) ... 41 Creación de una Base de Datos Ajuste de Parámetros DB_NAME: indica el identificador de la BD que en este caso es „prueba‟. Debe tener como máximo 8 caracteres. Único parámetro obligatorio. CONTROL_FILES: Este parámetro contendrá el nombre de los ficheros de control y su ubicación, como, por ejemplo: /DISK1/control01.ctl (mínimo 2 en distintos discos) DB_BLOCK_SIZE: Tamaño del bloque de datos de Oracle. Por defecto: 2K o 4K. Una vez fijado no puede modificarse. DB_BLOCK_BUFFERS: Especifica el número de buffers de la caché en la SGA. Cuanto más grande, menos acceso a disco tendremos. Sin embargo, ocuparemos más memoria y será necesario realizar más swapping. Compromiso de eficacia y rapidez. SHARED_POOL_SIZE: Tamaño en bytes del pool compartido. Por defecto: 3.500.000. LOG_BUFFER: Número de bytes asignados al buffer de redo log en la SGA. PROCESSES: Máximo número de procesos de SO que pueden acceder de forma concurrente. Mínimo: 1 por usuario + procesos de arrancados con cada instancia. DB_FILES: Número de ficheros que se podrán crear en una BD. Mín 1, máx depende del SO. LOG_FILES: Máximo número de ficheros de log. MAX_DUMP_FILE_SIZE: Máximo tamaño en bloques de SO de los ficheros de traza. 42 Creación de una Base de Datos Ajuste de Parámetros BACKGROUND_DUMP_DEST: Ubicación de los ficheros de las trazas de los procesos. USER_DUMP_DEST: Ubicación de los ficheros de traza de los usuarios. CORE_DUMP_DEST: Ubicación de los ficheros de traza de los ficheros core. ROLLBACK_SEGMENTS: Nombres de los segmentos de rollback. Otros parámetros relevantes: • AUDIT_TRAIL • DML_LOCKS • IFILE • LICENSE_MAX_SESSIONS • LICENSE_MAX_USERS • LOG_ARCHIVE_BUFFER_SIZE • LOG_ARCHIVE_BUFFER • LOG_ARCHIVE_DEST • LOG_ARCHIVE_START • NLS_DATE_FORMAT • NLS_CURRENCY • NLS_LANGUAGE • NLS_TERRITORY • OPEN_CURSORS • SESSIONS • TRANSACTIONS http://otn.oracle.com/pls/db92/db92.initora?remark=homepage 43 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 44 Gestión de Espacios de Tablas (Tablespaces) Tablespace: Es una unidad lógica que permite particionar la BD y asignar recursos de forma individual y más racionalizada, separando lógicamente los datos. Sascha Ossowski Creación de un nuevo Tablespace CREATE TABLESPACE nombre_tablespace DATAFILE „nombre_fichero‟ SIZE tamaño [REUSE] [AUTOEXTEND ON] [,„nombre_fichero‟ SIZE tamaño [REUSE] [AUTOEXTEND ON]] [MINIMUM EXTENT tamaño [K|M]] /*control de la fragmentación*/ [DEFAULT STORAGE( INITIAL tamaño [K|M] /*mínimo 2* tamaño del bloque*/ NEXT tamaño [K|M] /*mínimo tamaño del bloque*/ MINEXTENTS número /*mínimo 1*/ MAXEXTENTS número | UNLIMITED /*mínimo 1*/ PCTINCREASE valor) ] [ONLINE|OFFLINE]; CREATE TEMPORARY TABLESPACE nombre_tablespace_temp TEMPFILE „nombre_fichero‟ SIZE tamaño; Extensiones de tamaño fijo de 1M 45 Gestión de Espacios de Tablas (Tablespaces) Ampliación de un Tablespace Añadir un nuevo fichero: ALTER TABLESPACE nombre_tb ADD DATAFILE ‘fichero’ SIZE tamaño [REUSE]; Agrandar el tamaño de un fichero existente en un tablespace: ALTER DATABASE DATAFILE ‘fichero.dbf’ RESIZE tamaño; 46 Gestión de Espacios de Tablas (Tablespaces) Modificación de un Tablespace Cambiar los valores de almacenamiento por defecto: ALTER TABLESPACE nombre DEFAULT STORAGE (...); Sólo para los objetos nuevos que se creen. Disponibilidad de un Tablespace Un tablespace puede estar activo o desactivado. Si está desactivado los usuarios no pueden acceder al mismo. Para cambiar su estado: ALTER TABLESPACE nombre ONLINE|OFFLINE {normal|temporary|immediate|for recover}; Borrado de un Tablespace ¡CUIDADO! DROP TABLESPACE nombre [INCLUDING CONTENTS] [CASCADE CONSTRAINTS]; No borra los ficheros asociados. DROP TABLESPACE nombre INCLUDING CONTENTS AND DATAFILES; Fuerza el borrado del contenido y de los ficheros asociados al tablespace. 47 Gestión de Espacios de Tablas (Tablespaces) Modificación de un Tablespace Poner un tablespace en modo de SÓLO LECTURA: ALTER TABLESPACE nombretb READ ONLY; Volver a poner un tablespace en modo de ESCRITURA: ALTER TABLESPACE nombretb READ WRITE; Tamaño de una base de datos Select SUM(bytes) FROM DBA_DATA_FILES; Ficheros Autoextensibles ALTER DATABASE DATAFILE „fichero.dbf‟ AUTOEXTEND ON [MAXSIZE tamaño K]; Para desactivar la opción de autoextensible: ALTER DATABASE DATAFILE „fichero.dbf‟ AUTOEXTEND OFF; DBA_DATA_FILES: Vista del diccionario de datos que contiene información sobre todos los ficheros de la BD. 48 Gestión de Espacios de Tablas (Tablespaces) Renombrar ficheros de un Tablespace Poner el tablespace “off-line” Copiar los ficheros que queremos renombrar con su nuevo nombre o a una nueva ubicación. ALTER TABLESPACE nombre RENAME DATAFILE „fichero_ant‟ TO „fichero_nuevo‟; Poner el tablespace “on-line” Restricciones: El máximo número de tablespaces en una base de datos es 64K. El máximo número de ficheros asociados a un tablespace no puede exceder el número máximo de ficheros de datos (parámetro DATA_FILES). DBA_TABLESPACES o USER_TABLESPACES: Vista del diccionario de datos que contiene información sobre todos los espacios de tabla de la BD. 49 Gestión de Espacios de Tablas (Tablespaces) Creación de una Tabla dentro de un Espacio de Tabla CREATE TABLE [esquema.] tabla (columna tipo_datos [ , columna tipo_datos ] ...) [TABLESPACE nombre_tablespace ] [ PCTFREE integer ] [ PCTUSED integer ] [ STORAGE storage-clause ] Cabecera [ INITRANS integer ] [ MAXTRANS integer ] [LOGGING | NOLOGGING] ESPACIO [CACHE | NOCACHE]]; LIBRE DATOS BLOQUE de ORACLE storage_clause:= = ( [INITIAL integer[K|M]] Minimum Extent del TB [NEXT integer[K|M]] [MINEXTENTS integer] [MAXEXTENTS {integer|UNLIMITED}] [PCTINCREASE integer]) Minimum Extent del TB Redondeo a la alza 50 Gestión de Espacios de Tablas (Tablespaces) Parámetros: esquema es el propietario de la tabla tabla es el nombre de la tabla columna es el nombre de la columna tipo_datos es el tipo de datos de la columna TABLESPACE identifica el espacio de tabla donde se creará la tabla PCTFREE es la cantidad de espacio reservada en cada bloque (porcentaje del espacio total) para el crecimiento de las filas 10 por defecto - Actualizaciones Frecuentes: ↑ PCTFREE - ↑ PCTFREE ↓ Densidad PCTUSED determina el límite inferior de espacio usado en cada bloque antes de que esté disponible para inserciones de filas adicionales 40 por defecto - Asegurar que el bloque se libera cuando admite una fila DBA_TABLES (también USER_TABLES, ALL_TABLES): Vista del diccionario de datos que contiene información sobre todas las tablas de la BD . 51 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 52 Gestión de Seguridad Usuarios: Entidad que tiene acceso a la BD bajo ciertas condiciones y pudiendo realizar determinadas tareas definidas por el administrador. Creación de Usuarios CREATE USER usuario IDENTIFIED BY password [DEFAULT TABLESPACE nombre_tb] /*por defecto SYSTEM*/ [TEMPORARY TABLESPACE nombre_tb_temp] [QUOTA espacio ON nombre_tb ] /*por omisión 0*/ [PROFILE perfil_de_usuario]; /*perfil_de_usuario debe existir*/ Modificación de Usuarios Cambiar la contraseña: ALTER USER usuario IDENTIFIED BY new_password; Función: PASSWORD Cambiar otras características: ALTER USER usuario ... /*cualquier característica del usuario */ Borrado de Usuarios DROP USER usuario [CASCADE]; 53 Gestión de Seguridad DROP USER usuario CASCADE; − El usuario tiene objetos creados: SQL> DROP USER pepe; DROP USER pepe * ERROR en línea 1: ORA-01922: debe especificarse CASCADE para borrar PEPE − El usuario está conectado: SQL> DROP USER pepe CASCADE; DROP USER pepe CASCADE * ERROR en línea 1: ORA-01940: no se puede borrar un usuario conectado actualmente − El usuario está desconectado y tiene objetos: SQL> DROP USER pepe CASCADE; Usuario borrado. 54 Gestión de Seguridad Usuarios DBA o administradores: SYS (change_on_install) Usuario propietario de las tablas y vistas del diccionario de datos, que son las que ORACLE utiliza para los procesos internos. Sólo consulta de estas tablas. SYSTEM (manager) Este usuario es el que se utilizará para realizar las tareas de administración. Creación de vistas para Administración (p.e. DBA_USERS). 55 Gestión de Seguridad Privilegios: Sirven para determinar qué derecho tiene a realizar determinadas sentencias SQL o bien qué tipo de acceso puede tener un usuario a los objetos de otro usuario. Privilegios de Sistema Permiten realizar una determinada operación de la BD (Lenguaje de Definición de Datos): CREATE SESSION, CREATE TABLE, CREATE INDEX (aprox. 80) Privilegios de Objetos Permiten gestionar privilegios sobre los objetos de la BD (Lenguaje de Manipulación de Datos): ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT y UPDATE 56 Gestión de Seguridad Roles: Conjunto de privilegios que reciben un nombre común para facilitar la tarea de asignación de los mismos (de forma global) a los usuarios. Creación de Roles CREATE ROLE nombre [NOT IDENTIFIED|IDENTIFIED BY password| IDENTIFIED EXTERNALLY]; Identificación para habilitar o deshabilitar un rol en la sesión actual (SET ROLE …) Existen roles predefinidos CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, … Modificación de Roles ALTER ROLE rol [NOT IDENTIFIED|IDENTIFIED BY password| IDENTIFIED EXTERNALLY]; Borrado de Roles DROP ROLE nombre; 57 Gestión de Seguridad Establecer privilegios y roles Podemos asignar privilegios de sistema y roles de la misma manera: GRANT privilegio/rol TO usuario/rol/PUBLIC [WITH ADMIN OPTION]; Podemos asignar privilegios sobre objetos de la manera siguiente: GRANT privilegio/ALL ON objeto TO usuario/rol/PUBLIC [WITH ADMIN OPTION]; Revocar privilegios y roles Privilegio de Sistema/Rol REVOKE privilegio/rol FROM usuario/rol/PUBLIC; Privilegio sobre objetos REVOKE privilegio/ALL ON objeto FROM usuario/rol/PUBLIC; 58 Gestión de Seguridad Visualización de Privilegios del Sistema DBA_SYS_PRIVS: Vista del diccionario de datos para visualizar los privilegios concedidos a los usuarios y/o roles. SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN (‘Usuario1’, ‘Rol1’) ORDER BY grantee; Comprueba los privilegios y roles concedidos al usuario Usuario1 y al rol Rol1. 59 Gestión de Seguridad Recursos: Capacidad de un usuario de utilizar un determinado recurso de la BD, como puede ser el espacio en un tablespace. Establecer Recursos en Creación de Usuarios Tablespace por defecto Tablespace temporal Cuotas de espacio y uso Limitar Recursos Se pueden limitar determinados recursos a los que tiene acceso un usuario. − Nivel de Sesión: A cada sesión se le asigna un determinado tiempo de CPU y una determinada cantidad de memoria. − Nivel de Llamadas: Podemos establecer límites a nivel de órdenes SQL. Cuando un usuario ejecuta una de ellas, el sistema se encarga de controlar que ninguna de sus fases o pasos sobrepase los límites establecidos a este nivel. 60 Gestión de Seguridad Perfiles: Conjunto de recursos agrupados bajo un mismo nombre que permiten al DBA gestionar de manera más eficaz, rápida y racional dichos recursos. Creación de Perfiles CREATE PROFILE nombre LIMIT SESSIONS_PER_USER N /*Número de Sesiones Concurrentes del usuario*/ CPU_PER_SESSION unlimited /*Tiempo de CPU por Sesión: centésimas*/ CPU_PER_CALL N /*Tiempo de CPU para una llamada*/ LOGICAL_READS_PER_SESSION unlimited /*Número de bloques leídos en una sesión, incluidos los leídos de memoria*/ LOGICAL_READS_PER_CALL N /*Número de bloques leídos por llamada en una sentencia SQL*/ IDLE_TIME N /*N minutos de inactividad*/ CONNECT_TIME N /*Tiempo total para una sesión*/ FAILED_LOGIN_ATTEMPTS N; /*Número intentos de conexión fallidos*/ 61 Gestión de Seguridad Modificación de Perfiles ALTER PROFILE perfil LIMIT ...; Borrado de Perfiles DROP PROFILE perfil [CASCADE]; /* PERO: no borra los usuarios que tengan el perfil asignado, sólo se modifica el usuario que tenga dicho perfil asignado.*/ Para bloquear/desbloquear una cuenta de usuario: ALTER USER usuario ACCOUNT LOCK | UNLOCK; 62 Gestión de Seguridad Algunas Vistas del Diccionario de Datos: DBA_USERS -> todos los usuarios de la BD USER_USERS -> información sobre el usuario actual DBA_ROLES -> todos los roles de la BD DBA_ROLE_PRIVS -> roles concedidos a usuarios o roles USER_TS_QUOTAS -> cuota del usuario conectado en cada tablespace DBA_TS_QUOTAS -> cuota de cada usuario en cada tablespace USER_PASSWORD_LIMITS -> limitaciones en la password del usuario USER_RESOURCE_LIMITS-> limitaciones de recursos para el usuario DBA_PROFILES -> todos los perfiles de la BD RESOURCE_COST -> limitaciones de recursos V$SESSION -> información de las sesiones activas V$SESSTAT -> estadísticas sobre las sesiones activas V$STANAME -> nombres las estadísticas que se encuentran en V$SESTAT 63 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 64 Auditoría de Bases de Datos ¿Qué es una Auditoría de una BD? Controlar y registrar las acciones de determinados usuarios de la BD (investigar acciones sospechosas, información sobre el uso del sistema, estadísticas,…). Compromiso Rendimiento / Seguridad − Sólo auditorías controladas Usos: − Aud. General Accesos No Autorizados − Aud. Específica (a usuarios o terminales concretos): Resultado de la anterior (auditoría general) − Aud. de Rendimiento (bloqueos, etc…) Diseñar plan en función del uso concreto 65 Auditoría de Bases de Datos Arrancar una Auditoría Especificar el parámetro AUDIT_TRAIL con el valor TRUE o DB (tabla de la BD) o bien OS (fichero del SO) en el fichero INITSID.ORA para arrancar la auditoría. Tabla donde se almacena la información de la auditoría: SYS.AUD$. Ejecutar fichero @cataudit.sql creación vistas para visualizar y gestionar información de auditoría. (@catnoaud.sql) Información que se obtiene: Usuario que realizó una determinada operación. El objeto o los objetos a los que accedió el usuario. Fecha y hora del proceso. Código de la acción. No se refleja la información que se modificó. Auditoría basada en valores: aplicación, proc.alm., disparador 66 Auditoría de Bases de Datos Ejemplo de “Value-based Auditing” (con un disparador) CREATE TRIGGER [usuario.]auditar_empleados AFTER UPDATE ON [usuario.]empleados FOR EACH ROW BEGIN INSERT INTO [usuario.]datos_auditoria_modificados VALUES (:NEW.id_empleado, :NEW.nombre_empleado, :OLD.nombre_empleado, USER, SYSDATE); END; DATOS_AUDITORIA_MODIFICADOS :NEW.id_empleado :NEW.nombre_empleado :OLD.nombre_empleado USER SYSDATE 67 Auditoría de Bases de Datos Recomendación Crear un tablespace exclusivo para los datos de la auditoría - Crear el tablespace CREATE TABLESPACE audit_db DATAFILE „/db1/ORACLE/test/audit1.dbf‟ SIZE 25M; - Crear una copia de la tabla AUD$. CREATE TABLE audit_tmp TABLESPACE audit_db STORAGE (INITIAL 10M NEXT 1M MINEXTENTS 1 MAXEXTENTS 10 PCTINCREASE 1) AS SELECT * FROM sys.aud$; - Borrar la tabla original: DROP TABLE sys.aud$; - Renombrar la copia de la tabla: RENAME audit_tmp TO aud$; - Si ya habíamos creado las vistas, volver a lanzar el fichero. 68 Auditoría de Bases de Datos Tipos de niveles en la Auditoría de tipo de órdenes o comandos (statement auditing) •Activar una auditoría sobre una orden: AUDIT comando_a_auditar BY usuario /*Sólo órdenes de un usuario*/ BY SESSION /*Una sola línea por órdenes ejecutadas en una sesión*/ BY ACCESS /*Una línea por cada acceso (elevado núm. registros)*/ WHENEVER SUCCESSFUL /*Sólo las órdenes que han tenido éxito*/ WHENEVER NOT SUCCESSFUL /*Sólo órdenes fallidas*/ • Ejemplos: SQL> AUDIT SESSION; /*colecciones de sentencias SQL*/ Auditoría terminada con éxito. SQL> AUDIT CREATE TABLE BY SESSION; Auditoría terminada con éxito. SQL> AUDIT TABLE WHENEVER NOT SUCCESSFUL; Auditoría terminada con éxito. 69 Auditoría de Bases de Datos Tipos de niveles en la Auditoría de privilegios de sistema (privilege auditing) • Podemos auditar un privilegio de sistema: AUDIT CREATE ANY INDEX | SELECT ANY TABLE|...; • Ejemplos: SQL> AUDIT CREATE ANY INDEX; Auditoría terminada con éxito. SQL> AUDIT CREATE ANY INDEX WHENEVER NOT SUCCESSFUL; Auditoría terminada con éxito. 70 Auditoría de Bases de Datos Tipos de niveles en la Auditoría sobre los objetos (schema object auditing) • Podemos auditar procesos sobre un determinado objeto: AUDIT SELECT[, CREATE]|DEFAULT... ON objeto; • Para todos los usuarios de la BD. • Objeto del propio esquema o usuario con privilegio AUDIT ANY. • Podemos establecer opciones por defecto, para los objetos que aún no se han creado. AUDIT DELETE ON DEFAULT BY ACCESS WHENEVER NOT SUCCESFUL; 71 Auditoría de Bases de Datos Opciones disponibles para auditar cada tipo de objeto: Objeto Opción ALTER AUDIT COMMENT DELETE EXECUTE GRANT INDEX INSERT LOCK READ RENAME SELECT UPDATE Table View X X X X X X X Sequence X X X Stored Program X X X Snapshot X X X X X X X X X X X X X X X X X X X X X X X X X X 72 Auditoría de Bases de Datos Desactivar opciones de Auditoría Permite parar una determinada auditoría ejecutada. NOAUDIT { statement | system_priv } [, {statement | system_priv }...] [ BY usuario [, usuario ]...] [ WHENEVER [NOT] SUCCESSFUL NOAUDIT statement [,statement] ... ON {[schema.] object | DEFAULT } [ WHENEVER [NOT ] SUCCESSFUL 73 Auditoría de Bases de Datos Obtener información de Auditoría Vistas sobre la tabla SYS.AUD$: − AUDIT_ACTIONS: Tabla con tipos de acción de la auditoría (acción,nombre) − ALL_DEF_AUDIT_OPTS: Opciones por defecto de la auditoría (ALT, AUD, COM, DEL, GRA, IND, INS, LOC, REN, SEL, UPD, REF, EXE) − DBA_AUDIT_EXISTS: Registros de auditoría del sistema (AUDIT EXISTS) − DBA_AUDIT_OBJECT: Todos los objetos auditables del sistema − DBA_AUDIT_SESSION: CONNECT y DISCONNECT − DBA_AUDIT_TRAIL: Todo el sistema − DBA_STMT_AUDIT_OPTS: Opciones actuales sobre el sistema y por el usuario − DBA_OBJ_AUDIT_OPTS: Opciones sobre las tablas y vistas − USER_AUDIT_EXISTS: Reg. auditoría del usuario (AUDIT EXISTS) − USER_AUDIT_OBJECTS: Los objetos del usuario − USER_AUDIT_SESSION: CONNECT y DISCONNECT usuario − USER_AUDIT_TRAIL: Reg. Auditoría por usuario − USER_OBJ_AUDIT_OPTS: Tablas y vistas del usuario 74 Auditoría de Bases de Datos • Ejemplo: Controlar los intentos de conexión fallidos SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY') 2 from dba_audit_session 3 where returncode<>0 -- 0 indica que la conexión fue exitosa 4 group by username,terminal,to_char(timestamp,'DD-MON-YYYY'); COUNT(*) USERNAME TERMINAL TO_CHAR(TIMESTAMP) ---------- --------------- ---------- ---------------------- 1 BILL pts/3 09-APR-2003 3 FRED pts/3 09-APR-2003 4 ZULIA pts/1 Intentos fallidos 09-APR-2003 75 Auditoría de Bases de Datos • Ejemplo: Controlar los intentos de conexión fallidos (II) SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode 2 from dba_audit_session 3 group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode; COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE ---------- --------------- ------ ----------- ---------- 1 BILL pts/3 09-APR-2003 1017 1 EMIL pts/1 09-APR-2003 0 1 EMIL pts/2 09-APR-2003 0 1 EMIL pts/3 09-APR-2003 0 1 EMIL pts/4 09-APR-2003 0 3 FRED pts/3 09-APR-2003 1017 3 SYS pts/1 09-APR-2003 0 1 SYS pts/2 09-APR-2003 0 1 SYSTEM pts/5 09-APR-2003 0 4 ZULIA pts/1 09-APR-2003 1017 1 ZULIA pts/1 09-APR-2003 0 76 Auditoría de Bases de Datos Guías para realizar auditorías Sólo lo imprescindible Mejor sobre objetos Si se necesita a nivel de sentencia, restringir usuarios Por sesión mejor que por usuario Sucessful o Unsuccesful (por defecto ambas) Mantenimiento del espacio para la auditoría Controlar el tamaño de la tabla Protegerla de accesos no deseados AUDIT delete ON SYS.AUD$ BY ACCESS; 77 Índice 1. Introducción 2. Administrador de la Base de Datos 3. Arquitectura de ORACLE 4. Arranque y Parada de una Base de Datos 5. Creación de una Base de Datos 6. Gestión de Espacios de Tabla (Tablespaces) 7. Gestión de Seguridad 8. Auditoría de Bases de Datos 9. Copias de Seguridad y Recuperación 10.Bibliografía 78 Copias de Seguridad y Recuperación Modo de trabajo ARCHIVELOG Permite guardar una copia de los ficheros redolog utilizados (antes de ser reutilizados). Proceso cíclico (fichero redolog del último al primero). En caso de pérdida podemos recuperar hasta la última transacción. •Para activar este modo de trabajo: Tiramos la BD: shutdown; Arrancamos la instancia y montamos la BD con: startup mount BD; Tecleamos la siguiente orden: alter database [dbname] archivelog; Abrimos la BD con: alter database [dbname] open; Guardamos (de forma manual) todos los ficheros redo log pendientes de archivar: alter system archive log all; 79 Copias de Seguridad y Recuperación o Además, es necesario modificar los siguientes parámetros del fichero INITSID.ORA: LOG_ARCHIVE_START: True o False LOG_ARCHIVE_DUMP: lugar físico donde se almacenan los ficheros LOG_ARCHIVE_FORMAT: formato de los ficheros (reol%s.arc) o Para ver si la BD está operando en modo ARCHIVELOG y obtener el número de secuencia más antiguo: SVRMGLR> archive log list Database log mode Automatic archivel Archive destination Oldest online log sequence Next log sequence to archive Current log sequence ARCHIVELOG | NOARCHIVELOG DISABLED{manual}|ENABLED {automático} /disco0/Oracle/arch 75 76 76 o Para parar el modo ARCHIVELOG: ALTER SYSTEM ARCHIVE LOG STOP; 80 Copias de Seguridad y Recuperación oVentajas y coste del modo ARCHIVELOG: Permite realizar una recuperación completa y de un determinado momento del tiempo. Especialmente indicado para aplicaciones críticas donde no se pueda tolerar una mínima pérdida de información. Se requiere espacio adicional en disco. Administrador tiene trabajo adicional (gestión de espacio y seguimiento de los ficheros de log). Si no hubiera espacio en el disco destino de los redolog archivados, la BD se pararía de forma brusca. 81 Copias de Seguridad y Recuperación Copias de Seguridad En caso de error o pérdida por error físico permiten recuperar la información. Si estamos en modo ARCHIVELOG podremos recuperar hasta la última transacción efectuada, si no hasta la última copia de seguridad. Existen tres tipos de copias de seguridad en Oracle: Backup lógico: EXPORT Este comando genera un fichero (FILE= …) con todos o parte de los datos que en este momento haya en la BD abierta. Existen los siguientes modos: • Copia de la BD completa (FULL=Y) • Copia de los objetos de un usuario (OWNER=(usuario1, usuario2,…)) • Copia de tablas específicas (TABLES=(usuario1.tabla1, tabla2,…)) También podemos hacer distintos tipos de copia de seguridad lógica (INCTYPE): − Completa: Copia todos los datos de la BD (p.e. domingos). Resetea la información sobre export incremental de cada tabla. − Acumulativa: Copia sólo las tablas modificadas o creadas desde la última copia completa o acumulativa. − Incremental: Copia todos los datos modificados desde la última copia incremental, acumulativa o completa (Lunes-Sábado). Registra detalles de export para tabla exportada. 82 Copias de Seguridad y Recuperación Backup físico off-line • Este tipo de copias de seguridad se realizan con la BD parada. • Se realiza una copia de los ficheros físicos que la componen: ficheros de datos, ficheros de control y ficheros de redolog. • Es indiferente que la BD esté en modo ARCHIVELOG o no. 83 Copias de Seguridad y Recuperación Backup físico on-line (BD 24 horas y full-tolerant) • • • • • • • • • • Este tipo de copias de seguridad se realizan mientras los usuarios están trabajando. Para realizarla se necesita estar en modo ARCHIVELOG. Con ella podemos copiar uno o varios tablespaces. Para ello tecleamos las siguientes órdenes: ALTER TABLESPACE „Nombre‟ BEGIN BACKUP; Comando del SO para copiar los ficheros que pertenecen al Tablespace: cp ALTER TABLESPACE „Nombre‟ END BACKUP; Debemos copiar también los ficheros redo log y los ficheros de control. Podemos realizar la copia con el tablespace OFF-LINE: ALTER TABLESPACE „Nombre‟ OFFLINE; Podemos copiar el fichero de control con: ALTER DATABASE BACKUP CONTROLFILE TO „Fichero‟; Ejecutar archive log list para obtener el número de secuencia actual. Será el último archivo redo log que se debe mantener como parte del backup online. Se debe forzar un cambio de log de forma que se cree un archivo de log archivado: ALTER SYSTEM SWITCH LOGFILE; Copiar los archivos de redo log archivados mediante un comando del SO. 84 Copias de Seguridad y Recuperación Recuperación La recuperación depende en gran medida del tipo de copia de seguridad que se haya hecho. Será de gran importancia si se ha realizado en modo ARCHIVELOG o no. Existen cuatro tipos de recuperación en Oracle: Recuperación completa Se recupera la última copia de seguridad completa que existe. Este método se utilizará si estamos en modo NOARCHIVELOG (recuperación no completa). Debemos recuperar todos los ficheros de datos, de control y de redo log. La recuperación será física o con un IMPORT Pero: se recrea la BD. Recuperación on-line Permite recuperar / arreglar una BD mientras se está trabajando (NO en los Tablespaces implicados en la recuperación). Recuperación off-line Este tipo de recuperación se dará en el caso de que los ficheros implicados se encuentren en el tablespace SYSTEM o bien no podamos poner los tablespaces implicados on-line. 85 Copias de Seguridad y Recuperación Recuperación incompleta Con ella podemos recuperar pérdidas de ficheros redolog, de control o de datos. Recuperación manual Se realiza hasta la hora en la que un fichero redolog se estropeó. Se utiliza sobre todo para pérdidas de ficheros redolog o de control. Recuperación en un punto del tiempo Se recupera hasta una fecha y hora determinada. Se puede utilizar para ficheros de redolog o de datos. Hay dos tipos: − Automática: usa el fichero de control para saber que fichero de redolog debe utilizar. − Manual: el operador decide que ficheros de redolog utiliza. 86 Copias de Seguridad y Recuperación Comando RECOVER RECOVER DATABASE /MANUAL /UNTIL fecha TABLESPACE nombre DATAFILE fichero −DATABASE: recuperar una BD (todos los ficheros). Sólo off-line. −TABLESPACE: recuperar un Tablespace. Sólo on-line. Recover tablespace Nombre_TB; −DATAFILE: recuperar un fichero de datos. On-line y off-line. Recover datafile Nombre_fichero; −MANUAL: recuperación manual. −UNTIL fecha: recuperar hasta un determinado punto del tiempo de la BD. 87 Bibliografía http://www.oracle.com/technology/dba/ Tutoriales, Ejemplos de código, etc. http://otn.oracle.com/pls/db92/ Acceso a todos los libros a cerca de Oracle9i, versión electrónica (pdf, HTML) Oracle9i DBA Handbook Kevin Loney, Marlene L. Theriault Ed.: Osborne/McGraw-Hill 2001 88