J.M. Reixach
INDICE
INDICE... 2
BIBLIOGRAFIA ... 3
QUE ES ORACLE? ... 4
EL ADMINISTRADOS DE LA BASE DE DATOS (DBA) ... 5
ALGUNAS HERRAMIENTAS DE ORACLE ... 7
CARACTERISTICAS DE ORACLE ... 8
LA BASE DE DATOS: CAPA FISICA Y LOGICA ... 9
LOS TABLESPACES Y LOS DATAFILES... 10
SEGMENTOS, ESTENSIONES Y BLOQUES ... 12
OBJETOS DE ESQUEMA ... 15
LAS TABLAS ... 16
CREACIÓN DE TABLAS... 18
CREACIÓN DE TABLAS PARTICIONADAS... 20
PCTFREE Y PCTUSED ... 22
El ESQUEMA DE LA BD ... 23
ARQUITECTURA DE ORACLE... 25
LA INSTANCIA ORACLE ... 26
EL DICCIONARIO DE DATOS (EL CATALOGO)... 30
COMPONENTES DE UNA BD ORACLE ... 32
GESTION DE SEGURIDAD... 35
MANEJO DE DATOS ... 36
USUARIOS ... 39
CREACIÓN DE USUARIOS ( CREATE USER ) ... 40
PRIVILEGIOS ... 42
ROLES ... 50
PERFILES ... 53
GESTION DE TABLESPACES ... 56
PARAMETROS DE ALMACENAMIENTO... 61
SECUENCIAS ... 62
ENLACES DE BASES DE DATOS... 65
INDICES ... 67
CONSIDERACIONES EN EL DISEÑO DE ÍNDICES... 71
SECUENCIAS ... 72
CLUSTERS ( AGRUPAMIENTOS ) ... 73
EJERCICIO DEMOSTRATIVO... 75
GLOSARIO DE TERMINOS ... 87
BIBLIOGRAFIA
TÍTULO: “Desarrollo de aplicaciones en entornos de 4ª generación y con herramientas case” ( Unidad 9 )
AUTORES: Ramos, Montero EDITORIAL: McGrawHill
TÍTULO: “Desarrollo de aplicaciones en entornos de 4ª generación y con herramientas case”
AUTORES: Rodrigo Fernández, Juan Manuel Fernández EDITORIAL: Donostriarra
TÍTULO: “Oracle 8i para Windows NT”
AUTOR: Steve Bobrowski EDITORIAL: Osborne McGrawHill
TÍTULO: “Sistemas Gestores de Bases de dadots (ASI)”
AUTOR: Gregorio Cabrera EDITORIAL: Paranimfo
Links información Oracle:
http://www.oracle.com http://tahiti.oracle.com
QUE ES ORACLE?
Es el gestor y administrador de BD más utilizado.
Permite trabajar en cualquier tipo de ordenador (PC, Macintosh, grandes mainframes...) Fue de los primeros SGBD basados en el modelo relacional.
Actualmente es la segunda compañía de software mas importante después de Microsoft
EL ADMINISTRADOS DE LA BASE DE DATOS (DBA)
El administrador de la base de datos de una empresa es siempre considerado como la persona con más experiencia en el área de bases de datos. Por lo anterior, es conveniente tener muy claras las expectativas que se generan en torno a su trabajo y cuáles son los principales roles que debe asumir dentro del marco corporativo o de un proyecto.
Funciones del administrador:
Instalación de nuevos componentes del software
Una de las tareas principales del DBA consiste en la instalación periódica de nuevas actualizaciones de software de Oracle, tanto en lo referente a programas de aplicaciones como a herramientas administrativas. También es recomendable que el propio DBA y otros usuarios de Oracle prueben la instalación y nuevas configuraciones antes de migrarlas a los ambientes de producción.
Interacción con el administrador del sistema
En la mayoría de los casos los programas sólo pueden ser instalados o accedidos por el administrador del sistema. En este caso, el DBA debe trabajar siempre muy bien coordinado con él para garantizar que tanto la instalación y configuración de software como de hardware permita un adecuado funcionamiento del motor de base de datos y de las aplicaciones.
Garantizar la seguridad del sistema
El DBA debe siempre monitorear y administrar la seguridad del sistema. Esto involucra la incorporación y eliminación de usuarios, administración de espacios de disco (cuotas), auditorias y una revisión periódica para detectar probables problemas de seguridad.
Monitorización
El DBA debe monitorear continuamente el rendimiento del sistema y estar preparado para efectuar ajustes de sintonización de éste. En ciertas oportunidades esto involucra cambiar sólo algunos parámetros y otras veces reconstruir índices o reestructurar tablas.
Respaldos
Debido a que la tarea más importante del DBA es proteger la integridad de los datos, se deberá desarrollar una estrategia efectiva de respaldos y recuperación de datos para mantener la estabilidad de toda la información guardada. Las frecuencias de estos respaldos deberán decidirse dependiendo de la cantidad de procesos que alteran los
Prevención de riesgos
Otra tarea del DBA es la de calendarizar mantenciones a las bases de datos (archivos lógicos) o cooperar en el mantenimiento de las máquinas al administrador del sistema.
El DBA debe fortalecer sus esfuerzos en orden a eliminar problemas o situaciones potencialmente peligrosas.
Tareas adicionales del DBA:
Otras tareas de importancia que corresponden con frecuencia realizar a un DBA son:
- Analizar datos y efectuar recomendaciones concernientes a mejorar el rendimiento y la eficiencia en el manejo de aquellos datos que se encuentran almacenados.
- Apoyar en el diseño y optimización de modelos de datos.
- Asistir a los desarrolladores con sus conocimientos de SQL y de construcción de procedimientos almacenados y triggers, entre otros.
- Apoyar en la definición de estándares de diseño y nomenclatura de objetos.
- Documentar y mantener un registro periódico de las mantenciones, actualizaciones de hardware y software, cambios en las aplicaciones y, en general, todos aquellos eventos relacionados con cambios en el entorno de utilización de una base de datos.
ALGUNAS HERRAMIENTAS DE ORACLE
Oracle Server
Permite cuatro tipos de configuraciones posibles:
• Basado en anfitrión: los usuarios se conectan directamente al mismo ordenador que contiene la BD.
• Cliente Servidor: los usuarios acceden a la BD des de su ordenador (cliente) a través de una red (la BD se encuentra en otro odenador (servidor)).
• Procesamiento distribuido: los usuarios acceden a la BD que está almacenada en más de un ordenador.
Oracle Office
Conjunto de productos concebidos para gestionar las necesidades administrativas de cualquier empresa ( mensajería,...)
Oracle Loader
Permite introducir datos en una BD oracle de forma rápida.
Designer 2000
Herramienta para diseñar, programar, implementar y mantener sistemas.
Developer 2000 Consta de:
SQL*PLUS: Lenguaje de consulta a la BD
Oracle Forms: Diseña pantallas de introducción de datos y de consulta.
Oracle Reports: Generador de informes.
Oracle Book: Visualización y creación de documentos.
Oracle Graphics: Para hacer representaciones gráficas.
Personal Oracle
Es la BD oracle para ordenadores personales en entornos DOS y Windows (Implementación de oracle Server)
CARACTERISTICAS DE ORACLE
Mecanismos de Seguridad
Acceso a la BD en función de una serie de privilegios. El administradoe concede permiso a los usuarios.
Copia de seguridad y recuperación Copia secundaria de datos oracle.
Minimización de la pérdida de datos y tiempo de parada de la base de datos.
Gestión del espacio
Asignación del espacio de disco a los usuarios y a las bases de datos.
Conectividad Abierta
Es posible acceder a datos de Oracle usando Software de otros fabricantes (Visual Basic, PowerBuilder, SQL*Windows...)
Herramientas de desarrollo
Amplia gama de herramientas de desarrollo, consulta y gestión de información
Una BD es un conjunto de archivos de datos y de software que las gestiona.
LA BASE DE DATOS: CAPA FISICA Y LOGICA
La base de datos de Oracle tiene una capa lógica y otra física. La capa física consiste de archivos que residen en el disco y los componentes de la capa lógica son estructuras que mapean los datos hacia estos componentes físicos.
La Capa Física
Ya se dijo que consiste de archivos físicos que se encuentran en los discos. Estos pueden ser de tres tipos diferentes:
- Uno o más datafiles
Los datafiles almacenan toda la información ingresada en una base de datos. Se pueden tener sólo uno o cientos de ellos. Muchos objetos (tablas, índices) pueden compartir varios datafiles. El número máximo de datafiles que pueden ser configurados está limitado por el parámetro de sistema MAXDATAFILES.
- Dos o más archivos redo log (de deshacer)
Los archivos del tipo redo log almacenan información que se utiliza para la recuperación de una base de datos en caso de falla. Estos archivos almacenan la historia de cambios efectuados sobre la base de datos y son particularmente útiles cuando se necesita corroborar si los cambios que la base de datos ya ha confirmado se han efectuado realmente en los datafiles.
- Uno o más control files
Estos archivos contienen información que se utiliza cuando se levanta una instancia, tal como la información de dónde se encuentran ubicados los datafiles y los archivos redo log. Estos archivos de control deben encontrarse siempre protegidos.
La Capa Lógica
La capa lógica de una base de datos consta de los siguientes elementos:
- Uno o más tablespaces
- El esquema de la base de datos (schema), el cual consiste de objetos como tablas, clusters, índices, vistas, procedimientos almacenados, triggers, secuencias y otros.
LOS TABLESPACES Y LOS DATAFILES
Como se mencionó, una base de datos se encuentra dividida en una o más piezas lógicas llamadas tablespaces, que son utilizados para separar la información en grupos y así simplificar la administración de los datos. Los tablespaces pueden ocupar uno o más datafiles. Si se decide que utilice varios datafiles, el administrador del sistema puede gestionar que éstos queden localizados en discos diferentes, lo que aumentará el rendimiento del sistema, principalmente por la mejora en la distribución de la carga de entrada / salida.
Los tablespaces facilitan las tareas de administración de la BD. Así por ejemplo los tablespaces permiten al administrador :
• Controlar la asignación de espacio en disco a los distintos objetos de la base, decidiendo en qué tablespace se almacenarán los datos de cada objeto y estableciendo el límite de ocupación de espacio para cada objeto.
• Controlar el uso del espacio de almacenamiento por parte de los usuarios.
• Controlar la disponibilidad de los datos, poniendo determinados tablespaces online u offline.
• Realizar operaciones parciales de backup o restore de la BD.
El espacio total de almacenamiento de la BD será la suma de los espacios de cada tablespace y si, en algún momento este se agota, el administrador debe ampliarlo. Dicho espacio puede ampliarse creando un nuevo tablespace o haciendo más grande un tablespace ya existente. Un tablespace se puede ampliar añadiéndole un nuevo fichero o permitiendo que sus ficheros crezcan dinámicamente cuando se precisa más espacio.
En la figura siguiente se aprecia la diferencia entre estos tres conceptos. Una base de datos de ejemplo contiene tres tablespaces lógicos (parte superior de la figura) que
Relación entre la base de datos, los tablespaces y los datafiles
SEGMENTOS, ESTENSIONES Y BLOQUES
Dentro de los tablespaces y datafiles, el espacio utilizado para almacenar datos es controlado por el uso de ciertas estructuras; éstas son las siguientes:
Bloques:
Un bloque es la unidad de almacenamiento más pequeña en una base de datos Oracle. Contiene una pequeña porción de información (header) referente al bloque en sí y el resto a los datos que guarda. Generalmente, un bloque de datos ocupará aprox. 2 KB de espacio físico en el disco (asignación típica).
Extensiones:
Es un grupo de bloques de datos. Se establecen en un tamaño fijo y crecen a medida que van almacenando más datos. También se pueden redimensionar para aprovechar mejor el espacio de almacenamiento.
Segmentos:
Es un grupo de extensiones utilizados para almacenar un tipo particular de datos. Existen 4 tipos de segmentos: datos, índices, rollback y temporales.
Relación entre bloques, extensiones y segmentos
Para el SGBD un tablespace es un conjunto de bloques de datos en los que va almacenando los datos de los distintos objetos. Los bloques son siempre del mismo tamaño, establecido en el momento de la creación de la BD y debe ser múltiplo del tamaño del bloque físico manejado por el sistema operativo.
Cada bloque contiene datos de un solo objeto, y un objeto tendrá, normalmente, repartidos sus datos por varios bloques del mismo tablespace. La unidad de asignación de un bloque a un objeto es el “extent”.
El conjunto de extents asignados a un objeto recibe el nombre de segmento. Todos los extents de un segmento pertenecen al mismo tablespace, aunque pueden pertenecer a distintos ficheros de dicho tablespace.
Existen cuatro tipos de segmentos:
Segmentos de datos y de índice:
Se utilizan para almacenar los datos de las tablas e índices de la BD. A cada índice definido en la base el SGBD le asigna un segmento de índice y a cada tabla o clúster, le asigna un segmento de datos. Un clúster es una estructura que permite almacenar juntas una o más tablas.
Segmentos temporales:
Segmentos rollback:
Se utilizan durante el control de transacciones ( una transacción es una secuencia de operaciones que han de ejecutarse de forma atómica, es decir, o se realizan todas o ninguna ). Por cada operación de la transacción que se ejecute oracle guarda en un segmento de rollback la información necesaria que permita deshacerla más adelante si fuese necesario.
La información almacenada en los segmentos rollback se utiliza también para facilitar lecturas consistentes de los datos involucrados en una transacción. Cuando un usuario o una aplicación consultan datos que han sido modificados por una transacción aun sin conlcuir, el SGBD suministra los valores previos a la modificación, utilizando la información contenida en el correspondiente segmento de rollback, con lo que el resultado de la consulta es el mismo que si se hubiese realizado justo antes del comienzo de la transacción. De esta forma, los efectos de una transacción no son visibles hasta su conclusión.
OBJETOS DE ESQUEMA
En una BD oracle se denomina esquema a una colección de objetos.
Asociado a cada usuario definido en la BD existe un esquema con su mismo nombre.
Todos los objetos de un esquema son propiedad del usuario asociado a él.
Tipos de objetos de esquema y los que no pueden formar parte de un esquema.
OBJETOS DE ESQUEMA OBJETOS SIN ESQUEMA
TABLAS SEGMETOS DE ROLLBACK
VISTAS TABLESPACES
INDICES USUARIOS
CLUSTERS ROLES
SINÓNIMOS PROFILES
SECUENCIAS
FUNCIONES ALMACENADAS
PROCEDIMIENTOS ALAMCENADAS PAQUETES
DISPARADORES SNAPSHOTS DATABASE LINKS
No existe relación entre los tablespaces y los esquemas: objetos en el mismo esquema pueden estar almacendos en distintos tablespaces.
LAS TABLAS
La tabla es la unidad básica de almacenamiento de un sistema relacional.
Cuando creamos una tabla que no forma parte de un clúster, oracle le asigna un segmento en el que, posteriormente, se almacenarán sus datos. El segmento asignado pertenecerá al tablespace que aparece en la sentencia de creación de la tabla, aunque si no se indica nada, pertenecerá al tablespace por defecto del usuario que da la orden de creación.
Oracle almacena las filas de cada tabla en los bloques del segmento asignado. En cada bloque se almacenan una o más filas, dependiendo de su tamaño. Cada fila queda identificada por un número, único dentro de la base de datos que se denomina ROWID y que corresponde a su dirección física.
El ROWID está formado por la combinación del número de bloque ( que es único dentro del fichero al que pertenece), el número que identifica la fila dentro del bloque y el número que identifica al fichero de la base de datos.
Oracle permite consultar el ROWID de cualquier fila mediante la sentencia : SELECT rowid , nombre columna o columnas
FROM nombre tabla SELECT rowid, deptno, dname FROM dept;
ROWID DEPTNO DNAME
--- --- ---
AAAAeDAACAAAAERAAA 10 ACCOUNTING
AAAAeDAACAAAAERAAB 20 RESEARCH
AAAAeDAACAAAAERAAC 30 SALES
AAAAeDAACAAAAERAAD 40 OPERATIONS
Al almacenar una fila oracle graba los valores de cada columna en el mismo orden en que se definieron las columnas al crear la tabla, excepto en el caso de columnas de tipo LONG, que van siempre en último lugar.
su nuevo tamaño, la fila se traslada, aunque, para que su ROWID no cambie, oracle dejará en el bloque original un trozo de la fila con un enlace a la nueva dirección física.
Cuando una fila ha sido trasladada a otro bloque, el rendimiento del sistema en los accesos a esa fila decrece, ya que para localizarla es necesario leer dos bloques, el original para saber la nueva dirección dela fila y el nuevo. Y esta nueva lectura puede, en algunos casos necesitar dos accesos a disco. Para evitar en lo posible este problema oracle permite configurar en cada bloque una reserva de espacio utilizable sólo para operaciones de actualización.
En una BD oracle existen dos parámetros asociados a toda tabla, índice o cluster, denominados PCTFREE y PCTUSED. Estos parámetros tienen unos valores específicos para cada tabla, índice o cluster que se asignan en el momento de creación del correspondiente objeto y que tambien pueden ser modificados posteriormente.
PCTFREE : marca el porcentaje de espacio del bloque que se reservará para permitir realizar modificaciones en las filas del bloque. Mientras el porcentaje de bloque libre sea superior al valor de este parámetro, el SGBD utilizará el espacio libre del bloque tanto para insertar nuevos registros como para modificar los ya existentes. En el momento en que el porcentaje de espacio libre en el bloque sea igual o inferior al valor PCTFREE ya no se insertarán nuevas filas en él, utilizándose sólo para acomodar los posibles aumentos de tamaño de las filas que se modifiquen.
CREACIÓN DE TABLAS
Tablas
Una tabla se crea en un segmento. Este segmento posee una o más extensiones. Si la tabla crece hasta alcanzar el tamaño máximo de una extensión, entonces se crea uno nuevo para esa tabla. Las extensiones crecen de la manera en que se definieron cuando se creó la tabla, dentro de la cláusula Storage. Cuando la cláusula anterior no se define para una tabla, entonces se utilizan los parámetros por defecto definidos dentro del tablespace donde se está usando. Si tampoco existen, entonces se utilizan los parámetros del sistema.
CREATE TABLE nombre_tabla
(nombre_columna tipo_columna, ...)
TABLESPACE nombre_tablespace STORAGE
(INITIAL tamaño
NEXT tamaño
PCTINCREASE porcentaje MINEXTENTS número
MAXEXTENTS número o UNLIMITED );
INITIAL: Es el tamaño en bytes de la extensión inicial; la primera que se crea, en el instante mismo en que se crea la tabla (aún sin datos). También se pueden utilizar las letras K o M seguidas del número para denotar kilobytes o megabytes.
NEXT: Análogo al anterior, pero aplica a los tamaños de las extensiones posteriores.
PCTINCREASE: Este parámetro especifica el tamaño de las extensiones posteriores a la segunda. Así como initial indica la extensión de la primera extensión, next lo indica para la segunda y pctincrease es el porcentaje en que se incrementarán los tamaños de las extensiones en adelante. El valor 0 (cero) indica que todas las extensiones tendrán el mismo tamaño que lo indicado en next y el valor 100 que se incrementaran en un 100% con respecto a ese valor (es decir, el doble de next).
MINEXTENTS: Con este parámetro se puede indicar cuántas extensiones se crearán en el momento en que se cree el objeto, todas respetando el valor de lo indicado en initial.
MAXEXTENTS: Permite indicar el número máximo de extensiones que podrá tener el objeto creado.
CREACIÓN DE TABLAS PARTICIONADAS
Estos objetos siguen correspondiendo a las tablas que conocemos hasta ahora, pero la diferencia radica en cómo se va a almacenar la información físicamente.
En efecto, al instante de crear una tabla podemos elegir qué rangos de datos van a quedar almacenados en un tablespace u otro. Y aunque lo anterior no denote espacio físico de almacenamiento (un tablespace es un segmento lógico), recordemos que sí podemos elegir dónde estarán ubicados (en qué discos) los datafiles de esos tablespace y entonces sí que podremos decir que estamos escogiendo el lugar físico donde se grabarán ciertos rangos de datos de una tabla, lo que nos da las siguientes ventajas:
- Segmentos de datos más pequeños: esto influye directamente en el rendimiento de las búsquedas porque cada partición es tratada como si fuera una tabla diferente; Oracle siempre sabrá en que partición buscar cuando se referencia a la tabla particionada, entonces debe buscar en un trozo más pequeño.
- Indices más pequeños: con la partición por rangos es posible crear índices individuales para cada partición.
- Respaldo más rápido: ya que los datos se encuentran en segmentos separados, el mecanismo de respaldo puede correr en paralelo.
La sintaxis de la creación de una tabla particionada es la siguiente:
CREATE TABLE [esquema.] nombre_tabla
(nombre_columna tipo_columna) PARTITION BY RANGE (lista_columnas)
(PARTITION [nombre_particion] VALUES LESS THAN valor_columna TABLESPACE nombre_tablespace
[, (PARTITION [nombre_particion] VALUES LESS THAN valor_columna TABLESPACE nombre_tablespace])
Por ejemplo, si deseamos tener una tabla que almacene los países del mundo, con la siguiente estructura:
CREATE TABLE paises
(codigo number(3), nombre varchar2(40), población number(12), ....) PARTITION BY RANGE (codigo)
(PARTITION VALUES LESS THAN 2 -- (regiones con código=1) TABLESPACE ts_reg1
, PARTITION VALUES LESS THAN 3 -- (regiones con código=2) TABLESPACE ts_reg2 ;
PCTFREE Y PCTUSED
Al momento de crear una tabla, es posible indicar, mediante dos parámetros al momento de su creación, ciertas condiciones de almacenamiento especiales que dicen relación con la volatilidad de los datos y cómo gestionar mejor el espacio (bloques) asignado a cada extensión del objeto. Estos parámetros se denominan PCTFREE y PCTUSED.
PCTFREE
Determina el porcentaje de espacio que se reservará en cada bloque de datos de una tabla para futuras actualizaciones de los registros que se graben en ese mismo bloque. El valor que se asigne al parámetro implica conocer la frecuencia de updates que se harán a la tabla.
Los valores sugeridos para distintas frecuencias de actualización de filas proyectada para la tabla, son los siguientes:
• Alta: Cuando hay muchas actualizaciones que no necesariamente puedan hacer crecer el registro de la tabla, se puede establecer un porcentaje igual a 10.
• Cuando se incrementa el tamaño de la fila en las actualizaciones, y éstas son además de alta periodicidad, un valor de 20% es suficiente.
• Cuando casi no existen actualizaciones o la frecuencia es muy baja, basta con reservar un 5% de espacio para permitir actualizaciones dentro del mismo bloque.
PCTUSED:
Este parámetro está relacionado con la frecuencia de inserciones que se pueden hacer a una tabla. Determina el mínimo porcentaje de espacio usado que será mantenido para cada bloque de datos, antes de crear el próximo segmento.
Si la actividad de inserción es alta o baja, los valores sugeridos para setear este parámetro son los siguientes:
• Alta: Establézcase el porcentaje cercano o igual a 40.
• Si es alta y además con mucha frecuencia de actualizaciones, establézcase un valor de 60.
• Si la frecuencia de inserciones de filas es baja, un porcentaje del 60% también es válido para este parámetro.
El ESQUEMA DE LA BD
Un esquema es una colección de objetos lógicos, utilizados para organizar de manera más comprensible la información y conocidos como objetos del esquema. Una breve descripción de los objetos que lo componen es la siguiente:
Tabla:
Es la unidad lógica básica de almacenamiento. Contiene filas y columnas (como una matriz) y se identifica por un nombre. Las columnas también tienen un nombre y deben especificar un tipo de datos. Una tabla se guarda dentro de un tablespace (o varios, en el caso de las tablas particionadas).
Cluster:
Un cluster es un grupo de tablas almacenadas en conjunto físicamente como una sola tabla que comparten una columna en común. Si a menudo se necesita recuperar datos de dos o más tablas basado en un valor de la columna que tienen en común, entonces es más eficiente organizarlas como un cluster, ya que la información podrá ser recuperada en una menor cantidad de operaciones de lectura realizadas sobre el disco.
Indice:
Un índice es una estructura creada para ayudar a recuperar datos de una manera más rápida y eficiente. Un índice se crea sobre una o varias columnas de una misma tabla. De esta manera, cuando se solicita recuperar datos de ella mediante alguna condición de búsqueda (cláusula where de la sentencia), ésta se puede acelerar si se dispone de algún índice sobre las columnas-objetivo.
Vista:
Una vista implementa una selección de varias columnas de una o diferentes tablas. Una vista no almacena datos; sólo los presenta en forma dinámica. Se utilizan para simplificar la visión del usuario sobre un conjunto de tablas, haciendo transparente para él la forma de obtención de los datos.
Procedimiento Almacenado:
Son programas que permiten independizar el manejo de datos desde una aplicación y efectuarla directamente desde el motor de base de datos, disminuyendo así el tráfico de información a través de la red y mejorando el rendimiento de los procesos implementados mediante estos programas.
Trigger:
Un trigger es un procedimiento que se ejecuta en forma inmediata cuando ocurre un evento especial. Estos eventos sólo pueden ser la inserción, actualización o eliminación de datos de una tabla.
Secuencias:
El generador de secuencias de Oracle se utiliza para generar números únicos y utilizarlos, por ejemplo, como claves de tablas. La principal ventaja es que libera al programador de obtener números secuenciales que no se repitan con los que pueda generar otro usuario en un instante determinado.
ARQUITECTURA DE ORACLE
Vista general de la Arquitectura de Oracle
La Arquitectura general de Oracle consiste de varios procesos corriendo en la máquina donde reside la instancia, más los espacios de memoria dedicados a ejecutar procesos específicos o al almacenaje de información de cada proceso y la base de datos física propiamente tal, con sus archivos de control, de datos y de transacciones.
LA INSTANCIA ORACLE
Una instancia de Oracle está conformada por varios procesos y espacios de memoria compartida que son necesarios para acceder a la información contenida en la base de datos.
La instancia está conformada por procesos del usuario, procesos que se ejecutan en el background de Oracle y los espacios de memoria que comparten estos procesos.
Arquitectura de la Instancia de Oracle
EL ÁREA GLOBAL DEL SISTEMA (SGA)
El SGA es un área de memoria compartida que se utiliza para almacenar información de control y de datos de la instancia. Se crea cuando la instancia es levantada y se borra cuando ésta se deja de usar (cuando se hace shutdown). La información que se almacena en esta área consiste de los siguientes elementos, cada uno de ellos con un tamaño fijo:
El buffer de caché ( database buffer cache )
Almacena los bloques de datos utilizados recientemente (se hayan o no confirmado sus cambios en el disco). Al utilizarse este buffer se reducen las operaciones de entrada y salida y por esto se mejora el rendimiento.
El buffer de redo log: Guarda los cambios efectuados en la base de datos. Estos buffers escriben en el archivo físico de redo log tan rápido como se pueda sin perder eficiencia. Este último archivo se utiliza para recuperar la base de datos ante eventuales fallas del sistema.
El área shared pool: Esta sola área almacena estructuras de memoria compartida, tales como las áreas de código SQL compartido e información interna del diccionario. Una cantidad insuficiente de espacio asignado a esta área podría redundar en problemas de rendimiento. En resumen, contiene las áreas del caché de biblioteca y del caché del diccionario de datos.
- El caché de biblioteca se utiliza para almacenar código SQL compartido.
Aquí se manejan los árboles de parsing y el plan de ejecución de las queries. Si varias aplicaciones utilizan la misma sentencia SQL, esta área compartida garantiza el acceso por parte de cualquiera de ellas en cualquier instante.
- El caché del diccionario de datos está conformado por un grupo de tablas y vistas que se identifican la base de datos. La información que se almacena aquí guarda relación con la estructura lógica y física de la base de datos. El diccionario de datos contiene información tal como los privilegios de los usuarios, restricciones de integridad definidas para algunas tablas, nombres y tipos de datos de todas las columnas y otra información acerca del espacio asignado y utilizado por los objetos de un
PROCESOS DE LA INSTANCIA
Según lo que se advierte en la figura 5, los procesos que se implementan en una instancia de Oracle y su función principal son los siguientes:
DBWR (database writer): Es el responsable de la escritura en disco de toda la información almacenada en los buffers de bloques que no se han actualizado. Está optimizado para minimizar las operaciones de escritura en disco. En general escribe en el disco cuando se precisa introducir nuevos bloques en el caché y no hay espacio. Los bloques del buffer caché que hace más tiempo que no fueron accedidos son los primeros en ser grabados en disco.
LGWR (log writer): Es el responsable de escribir información desde el buffer de log hacia el archivo redo log Las escrituras en disco tienen lugar cuando termina una transacción o cuando se llena el buffer.
CKPT (checkpoint): Es el responsable de advertir al proceso DBWR de efectuar un proceso de actualización en el disco de los datos mantenidos en memoria, incluyendo los datafiles y control files (para registrar el checkpoint). Este proceso es opcional, si no está presente, es el proceso LGWR quien asume la responsabilidad de la tarea.
PMON (process monitor): Su misión es monitorizar los procesos del servidor y tomar acciones correctivas cuando alguno de ellos se interrumpe en forma abrupta, limpiando la caché y liberando los posibles recursos que pudieran estar asignados en ese momento.
También es responsable del restablecimiento de aquel proceso que se ha interrumpido bruscamente.
SMON (system monitor): Levanta una instancia cuando se le da la instrucción de partida (al comienzo del trabajo, encontrándose previamente en shutdown). Enseguida
ARCH (archiver): La función de este proceso es la de respaldar la información almacenada en los archivos redo log cuando éstos se llenan. Este proceso está siempre activo cuando se ha establecido el modo ARCHIVELOG. Si el sistema no está operando en este modo se hace más difícil recuperar el sistema sin problemas luego de una falla general.
EL ÁREA GLOBAL DE PROGRAMAS (PGA)
Esta área de memoria contiene datos e información de control para los procesos que se ejecutan en el servidor de Oracle (relacionados con la base de datos, por supuesto). El tamaño y contenido de la PGA depende de las opciones del servidor que se hayan instalado.
LAS TRANSACCIONES
El término transacción describe a una unidad lógica de trabajo que está compuesta de una o más sentencias SQL, que deben terminar con una instrucción commit o rollback.
En ese instante, una nueva transacción dará comienzo y estará activa hasta que se ejecute alguno de esos dos comandos otra vez.
Cabe destacar que una transacción no se considera confirmada hasta que ésta se termina de escribir en el archivo de redo log.
EL DICCIONARIO DE DATOS (EL CATALOGO)
Todo el SGBD está constituido alrededor de un diccionario de datos. En el caso de los sitemas relacionales, este dicccionario es en realidad, un diccionario / directorio gestionado por el propio SGBD, que recibe el nombre de catálogo.
El catálogo no sólo sirve al SGBD, sino también a los usuarios, que lo pueden consultar utilizando el mimo lenguaje con el que consultan el resto de la BD.
Oracle implementa el catálogo mediante un conjunto de tablas y vistas. Las tablas almacenan información sobre la BD y en ellas sólo escribe el SGBD. Los usuarios no suelen acceder directamente a ellas, porque la mayoría de sus datos estan almacenados en un formato críptico.
Las vistas permiten a los usuarios consultar la información en un formato más adecuado a sus necesidades. El catálogo se consulta a través del lenguaje SQL.
Los datos del catálogo son necesarios para el funcionamiento del SGBD. Cuando se ejecuta una operación de manipulación de datos, el SGBD accederá a las tablas del catálogo para, por ejemplo, comprobar que el objeto existe y que el usuario tiene privilegios suficientes para realizar la operación.
Durante la ejecución de una operación de definición de datos, el SGBD actualizará las tablas del catálogo para reflejar la existencia de un nuevo objeto o las modificaciones realizadas a un objeto ya extistente.
Todas las tablas y vistas estan definidas en el esquema del usuario SYS.
LISTA DE LAS VISTAS DEL CATALOGO MAS INTERESANTES
( Para obtener mas información sobre estas vistas o sobre todas las vistas del catálogo se debe consultar el manual Oracle Server Reference )
VISTAS DESCRIPCION
DICTIONARY Descripción las tablas y vistas que componen el catalogo
DICT Sinónimo de DICTIONARY
DBA, ALL, USER_TAB_COLUMNS Descripción de columnas de tablas, vistas y clusters
DBA, ALL, USER_CONSTRAINTS Definición de las restricciones definidas sobre tablas
DBA , ALL , USER , CONS_COLUMNS
Columnas que forman parte de definiciones de restricciones
TABLE_PRIVILEGES, ALL_TAB_PRIVS USER_TAB_PRIVS, DBA_TAB_PRIVS
Información sobre objetos de la BD
COLUMN_PRIVILEGES, ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
Infomación sobre privilegios sobre columnas
USER_SYS PRIVS , DBA_SYS_PRIVS
Privilegios del sistema concedidos a usuarios y roles
DBA, ALL, USER_USERS Información sobre usuarios defindos en la BD DBA, ALL, USER_INDEXES Descripción de índices definidos sobre tablas y
clusters
DBA, ALL, USER_IND_COLUMNS Columnas de los índices definidos DBA, ALL, USER_SEQUENCES Información sobre secuencias DBA, ALL, USER_SYNONYMS Información sobre sinónimos DBA_CLUSTERS,
USER_CLUSTERS Descripción de los clusters definidos DBA_CLU_COLUMNS
USER_CLU_COLUMNS
Correspondencia entre columnas de la clave del cluster y columnas de las tablas agrupadas en el cluster
DBA_DATA_FILES Información sobre los ficheros de datos DBA, USER_TABLESPACES Descripción de los tablespaces
DBA, USER_SEGMETS Información sobre segmentos
DBA_ROLLBACK_SEGS Descripción delos segmentos rollback DBA, USER_EXTENTS Extents de los segmentos
DBA, USER_FREE_SPACE Extents no asignados a lsos tablespaces
El catálogo también contiene otro tipo de tablas conocido como tablas de rendimiento dinámico . Estas tablas son actualizadas contínuamente por el SGBD durante su funcionamiento, almacenando en ellas información relativa al rendimiento del sistema.
Sobre ellas hay definidas varias vistas cuyo nombre empieza siempre por el prefijo V_$.
COMPONENTES DE UNA BD ORACLE
DATABASE FILES ( Archivos de datos)
LOG FILES (Archivos de diario o transacciones) CONTROL FILES ( Archivos de control)
DATABASE FILES ( Archivos de datos)
Contienen toda la información de la BD (Datos de usuarios y de sistema)
Antes de introducir datos en la BD es necesario crear un espacio para las tablas (TABLESPACE) y después crear una tabla, o varias, dentro de este espacio.
Los tablespace nos ayudan a organizar la información contenida en la BD.
Normalmente iformación distinta en tablespaces distintos.
Cada tablespace puede constar de uno o varios archivos en el disco.
Cuando instalamos oracle se nos crean 4 tablespaces:
SYSTEM:
Donde se almacena toda la información que oracle necesita para gestionarse a sí mismo (Nombres de los tablespaces y archivos de datos asociados, Usuarios creados, Privilegios concedidos a estos...) SYSS1ORCL.ORA, SYSS2ORCL.ORA...
USER_DATA:
Es el lugar en que el DBA nos deja almacenar las tablas para hacer pruebas. USR1ORCL.ORA
TEMPORARY_DATA:
REGISTROS “REHACER” O REDO_LOG ( Registros de transacciones)
Archivos donde oracle registra todas las transacciones o modificaciones (INSERT, UPDATE, DELETE) que se producen en una BD.
Nos permiten recuperar la BD si hay problemas. Archivos llamados LOG1ORCL.ORA, LOG2ORCL.ORA...
Un registro Redo_log contiene para cada modificación: Identificación de la transacción, Dirección del bloque, Número de fila, Número de columna,Valor anterior y nuevo.
Los archivos de “deshacer” se utilizan para almacenar la información de todas las transacciones que se llevan a cabo en la base de datos. De esta manera, se cuenta con un registro fiable de las operaciones que se han llevado a cabo para poder reconstruirlas en un eventual proceso de recuperación de la base de datos, si se hubiera producido una falla.
Una base de datos usualmente mantiene dos o más archivos de redo log, los que van guardando todas las transacciones que se van efectuando. De hecho, la instrucción COMMIT no se completa mientras no se efectúa la escritura en esos archivos.
Mecanismo de escritura en los archivos redo log
Para establecer el tamaño apropiado de un archivo de este tipo deberá considerarse el tamaño del dispositivo que contendrá el respaldo del redo log, es decir, si se va a almacenar en una cinta de 525 MB, entonces el tamaño de un archivo de este tipo no debiera superar los 520 MB.
CONTROL FILES ( Archivos de control)
Todas las modificaciones importantes que se hagan en la estructura de la BD quedan registrados en estos archivos.
Se recomienda tener dos archivos de control por si uno se estropea ( CTL1ORCL.ORA, CTL2ORCL.ORA)
Contienen la siguiente información:
→ Información de inicio y de parada.
→ Nombre de los archivos de la BD y de los Redo_log.
→ Información sobre los checkpoints (puntos de control que se
producen cuando se llena el Redo_log, cuando se para la BD,...)
→ Fecha de creación y nombre de la BD.
→ Estado on_line y off_line de los archivos.
GESTION DE SEGURIDAD
Esta gestión tiene mucho que ver con la gestión de los usuarios: concesión de permisos y privilegios.
El administración de la BD es el responsable de permitir o denegar el acceso a los usuarios a determinados objetos o recursos de la BD.
CLASIFICACION DE LA SEGURIDAD DE LA BD:
• La seguridad del sistema: Mecanismos que controlan el acceso y uso de la BD a nivel de sistema. Por ejemplo cada vez que un usuario se conecta a la BD debe comprobar si este tiene autorización
• La seguridad de los datos: Mecanismos que controlan el acceso y uso de la BD a nivel de objetos. Por ejemplo la comprobación de si un usuario puede acceder a una determinada tabla y que tipo de operación puede hacer con esta (select, insert,...)
MANEJO DE DATOS
Como se ha mencionado en los capítulos anteriores, una de las tareas fundamentales de un DBA consiste en la eficiente y completa manipulación de los conjuntos de datos que componen la base de datos de los sistemas que se encuentran en explotación (y desarrollo).
Hay varias formas diferentes (o utilidades) que implementan esta tarea, pero las más comunes son:
- EXPORT: Genera un archivo binario con toda la información de estructura y contenido de una base de datos. Estos archivos sólo pueden ser leídos por la utilidad de importación de Oracle (import).
- IMPORT: Realiza un volcado de la información contenida en un archivo binario (previamente generado con un export) en una base de datos.
EXPORT
Este utilitario está diseñado para registrar en un archivo especial todas las definiciones de objetos y los datos que se deseen dentro de una base de datos. Este archivo es conocido como “el archivo de export” y su formato es únicamente reconocido por el utilitario Import de Oracle. Las diferentes intenciones que podrían movernos para efectuar una exportación de datos pueden ser:
a) Respaldar la base de datos: El utilitario Export puede ser usado para efectuar un respaldo total de la base de datos (aunque no sea el mecanismo más eficiente para ese propósito).
b) Mover datos entre bases de datos: Los datos y objetos exportados desde una base de datos pueden perfectamente ser recuperados en otra diferente.
c) Reconstruir una base de datos: Si su base de datos tiene los tablespaces demasiado fragmentados, ésta es una buena opción para volver a compactarlos.
d) Reorganizar los datafiles: Siguiendo la misma lógica anterior, también se puede redistribuir la información en los archivos físicos que se desee.
Bajo Windows NT el programa que permite efectuar las exportaciones es “EXP80” y en otros sistemas operativos (como Unix) es simplemente “EXP”.
Sintaxis completa del comando:
EXP80 usuario/password PARFILE=archivo [opciones...]
Y las opciones son siempre del tipo PARÁMETRO=valor.
Ya sea que se utilice un archivo paramétrico o no, la mayoría de los parámetros que se pueden utilizar en la sintaxis de este comando son:
BUFFER=bytes Especifica el tamaño del buffer de copia (en bytes) usado por el utilitario. Si el valor es cero, entonces se recuperan las filas de a una.
COMPRESS=[Y o N] Este parámetro indica cómo deberá tratarse la extensión inicial. Si el parámetro está establecido como “Y”, entonces toda la información se consolidará dentro de una única extensión. Si se establece en “N”, se utilizarán los parámetros vigentes para la cláusula storage. El valor por defecto es “Y”.
CONSISTENT=[Y o N] Si se indica “Y”, entonces esperará a que la información que se está actualizando sea confirmada, para tener siempre la versión más fiable mientras dura el procedimiento de exportación. Es una opción muy costosa en tiempo y recursos. El valor por defecto es “N”.
CONSTRAINTS=[Y o N] Permite especificar si se desea exportar o no las restricciones de las tablas. Por defecto siempre las exporta.
FILE=nombre_archivo Especifica el nombre del archivo de salida, es decir, del archivo de exportación.
FULL=[Y o N] Permite indicar si se desea efectuar una exportación completa de la base de datos. El valor por defecto es “N”.
GRANTS=[Y o N] Permite indicar si se deben exportar los permisos (grants) de cada usuario sobre los objetos que son exportados.
INDEXES=[Y o N] Este parámetro especifica si se deben exportar los índices o no. El valor por defecto es “Y”.
ROWS=[Y o N] Se utiliza para exportar todos los datos de las tablas (“Y”) o solamente la estructura de los objetos (“N”).
OWNER=usuarios Es la lista de usuarios (esquemas) desde donde se realizará la exportación. Puede ser más de uno y se separan por coma.
Finalmente, se puede utilizar el parámetro HELP para obtener una lista de las posibles opciones disponibles con la utilidad. En ese caso deberíamos escribir:
EXP80 HELP=Y;
Y entonces se desplegará una pantalla con todas las opciones posibles para el comando EXP80 (o EXP) sin ejecutar ninguna acción de exportación.
IMPORT
La utilidad de importación se utiliza en conjunto con la de exportación, esto es por que no se puede importar ningún archivo que no sea el resultado de una exportación de datos hecha con anterioridad.
Las opciones de esta utilidad son similares a las de exportación; a continuación sólo presentaremos algunas de ellas, que no son comunes a ambos programas. En este caso, como en la exportación siempre será posible obtener una lista de las opciones disponibles escribiendo:
IMP80 HELP=Y;
Otros parámetros útiles son los siguientes:
FROMUSER=usuario Indica el esquema desde el cual se efectuara la importación.
Esto se especifica para no importar el archivo completo, ya que dentro de él se pueden encontrar varios esquemas diferentes.
TOUSER=usuario Es el esquema de destino hacia donde se desean importar los objetos desde el archivo de origen.
IGNORE=[Y o N] Este parámetro le indica al sistema cómo deberá comportarse ante una probable falla en la importación de algún objeto. Al establecer el valor en “Y”, no se hará ninguna advertencia ni se detendrá la ejecución del programa ante alguna eventualidad; en caso contrario, la importación se detendrá para que el administrador tome
USUARIOS
Nombre registrado en la BD que permite conectarse a esta y acceder a determinados objetos según las condiciones establecidas por el administrador.
Para acceder a la BD los usuarios deben ejecutar una aplicación como SQL*Plus, Oracle Forms o Oracle Reports y conectarse usando el nombre y el password previamente asignado.
Cuando instalamos oracle automáticamente se nos crean dos usuarios con el privilegio de administrador de la BD (DBA)
Nombre Password inicial
SYS CHANGE_ON_INSTALL SYSTEM MANAGER
USUARIO SYS
Propietario de las tablas del diccionario de datos (DD).
El diccionario de datos contiene la siguiente información:
→ Objetos de la BD
→ Nombre de los usuarios, derechos y autorizaciones.
→ Información sobre el espacio ocupado.
→ Información sobre otros objetos.
Los objetos del DD a los que un usuario puede acceder se encuentran en la vista DICTIONARY.
SQL > DESC DICTIONARY ;
SQL > SELECT TABLE_NAME FROM DICTIONARY ;
USUARIO SYSTEM
Es creado por oracle para realizar tareas de administrador de la BD.
Para crear otros usuarios es preciso que nos conectemos como usuario SYSTEM pues es este el que tiene este privilegio CREATE USER
CREACIÓN DE USUARIOS ( CREATE USER )
CREATE USER Nombre_usuario
IDENTIFIED BY Clave_de_acceso
[ DEFAULT TABLESPACE espacio_tabla ] [ TEMPORARY TABLESPACE espacio_tabla ] [QUOTA [ entero (K|M) | UNLIMITED] ON espacio_tabla ]
[ PROFILE perfil ] ;
CREATE USER Crea un nombre de usuario IDENTIFIED BY Clave de acceso
DEFAULT TABLESPACE
Asigna a un usuario el tablespace por defecto para almacenar los objetos que cree.
Si no se le asigna ningún tablespace su tablespace por defecto será SYSTEM
Es muy recomendable asignar algún tablespace que no sea SYSTEM
TEMPORARY TABLESPACE
Especifica el nombre del tablespace para trabajos temporales.
Si no le asignamos ninguno por defecto será SYSTEM QUOTA Sirve para asignar un espacio en Mb o Kb al tablespace
asignado anteriormente.
Si no se asigna ninguna quota el usuario no podrá crear objetos en el tablespace.
PROFILE Asigna un perfil al usuario.
EJEMPLOS:
IDENTIFIED BY PEPITO
DEFAULT TABLESPACE TRABAJO QUOTA 500 K ON TRABAJO
TEMPORARY TABLESPACE TRABAJO;
Usuario creado
Para obtener información sobre todos los usuarios creados en la BD debemos consultar las vistas ALL_USERS, DBA_USERS.
DESC SYS.ALL_USERS;
SELECT * FROM ALL_USERS;
DESC SYS.DBA_USERS;
Para ver el tablespace que tiene asignado cada usuario.
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;
MODIFICACIÓN DE USUARIOS (ALTER USER)
ALTER USER Nombre_usuario
IDENTIFIED BY Clave_de_acceso
[ DEFAULT TABLESPACE espacio_tabla ] [ TEMPORARY TABLESPACE espacio_tabla ] [QUOTA [ entero (K|M) | UNLIMITED] ON espacio_tabla ]
[ PROFILE perfil ] ;
Un usuario puede modificar su pasword:
ALTER USER Pepito IDENTIFIED BY Pepito1;
BORRADO DE USUSARIOS (DROP USER)
DROP USER Nombre_usuario [ CASCADE ]
CASCADE Suprime todos los objetos del usuario antes de borrarlo. Si tienen tablas debemos especificar casdade.
DROP USER Pepito CASCADE;
PRIVILEGIOS
Un privilegio es la capacidad de un usuario dentro de una BD a hacer determinadas operaciones o a acceder a determinados objetos de otros usuarios.
Ningún usuario puede llevar a cabo ninguna operación si no se le ha concedido permiso.
Cuando se crea un usuario es necesario concederle privilegios para que puede hacer algo.
Oracle nos ofrece varios roles o funciones. Estos roles predefinidos están compuestos por un conjunto de privilegios.
ROLES PRIVILEGIOS
CONNECT ALTER SESSION, CREATE CLUSTER, CREATE
DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE CREATE CLUSTER, CREATE PROCEDURE, CREATE
TABLE,
CREATE SEQUENCE, CREATE TRIGGER
DBA Todos los privilegios del sistema.
EXP_FULL_DATABASE SELECT ANY TABLE, BACKUP ANY TABLE, INSERT UPDATE, DELETE sobre las tablas SYS.INCVID, SYS.INCFIL, SYS.INCEXP
IMP_FULL_DATABASE BECOME USER
Los dos últimos roles ofrecen derechos de exportar o importar la base de datos completa.
Hay dos tipos de privilegios que podemos definir en la BD:
→ Privilegios sobre los objetos.
→ Privilegios del sistema.
PRIVILEGIOS SOBRE LOS OBJETOS
Permiten acceder y realizar cambios en los datos de otros usuarios.
TABLA VISTA SEQUENCIA PROCEDIMIENTO
ALTER x x
DELETE x x
EXECUTE X
INDEX x x
REFERENCES x
SELECT x x x
UPDATE x x
PRIVILEGIO SOBRE LOS OBJETOS
SENTENCIAS SQL PERMITIDAS ALTER ALTER objeto (Tabla o secuencia) DELETE DELETE FROM objeto (Tabla o vista) EXECUTE EXECUTE (Procedimiento)
INDEX CREATE INDEX ON objeto (tablas) INSERT INSERT INTO (tabla o vista)
REFERENCES CREATE o ALTER TABLE
SELECT SELECT… FROM (tabla, vista o instantánea) UPDATE UPDATE (tabla o vista)
La sentencia para dar privilegios es GRANT
GRANT {priv_objeto [,priv_objeto ] ... | ALL [ PRIVILEGES ]}
[(columna [,columna]... ] ON [usuario.] objeto
TO {usuario | rol | PUBLIC} [,{usuario | rol | PUBLIC}...]
[WHITH GRANT OPTION] ;
ON Especifica el objeto sobre el que se dan los privilegios
TO Identifica a los usuarios o roles a los que se conceden los privilegios ALL Concede todos los privilegios sobre el objeto especificado
WHITH GRANT OPTION
Permite que el receptor del privilegio o rol se lo asigne a otros usuarios o roles
EJEMPLOS:
CONNECT PEPITO/PEPITO1;
GRANT SELECT, INSERT ON TABLA1 TO FRANCISCO;
El usuario Pepito concede a Francisco los privilegios select y insert sobre su tabla1.
Ahora Francisco ya puede acceder a la tabla1 de Pepe.
CONNECT FRANCISCO/FRANCISCO;
SELECT * FROM PEPITO.TABLA1;
GRANT ALL ON TABLA1 TO FRANCISCO;
Pepito concede a Francisco todos los privilegios sobre la tabla1.
GRANT ALL ON TABLA1 TO PUBLIC;
Pepito concede todos los privilegios sobre la tabla1 a todos los usuarios, incluidos los que se crearan después de ejecutar esta orden.
Con la orden GRANT se pueden conceder privilegios INSERT, UPDATE O REFERENCES sobre determinadas columnas de una tabla.
Pepito puede dar a Juan privilegios sobre la tabla1 para que pueda modificar solo la columna temperatura.
GRANT UPDATE (TEMPERATURA) ON TABLA1 TO JUAN;
Juan puede ahora modificar el valor de la temperatura de Madrid por ejemplo:
UPDATE PEPITO.TABLA1 SET TEMPERATURA=20 WHERE CIUDAD=’MADRID’;
Francisco puede conceder privilegios a otros usuarios sobre sus tablas pero no los puede conceder sobre tablas que no le pertenecen. Si intenta dar el privilegio INSERT a Juan sobre la tabla1 de Pepito:
GRANT INSERT ON PEPITO.TABLA1 TO JUAN;
Error, privilegios insuficientes.
PRIVILEGIOS DEL SISTEMA
Son los que dan derecho a ejecutar un comando SQL o a hacer alguna acción sobre objetos de un tipo especificado.
Existen 80 tipos de privilegios diferentes disponibles Entre estos los mas destacados:
PRIVILEGIO DEL SISTEMA OPERACIONES AUTORIZADAS AUDIT
AUDIT ANY Auditar un objeto de la BD
CLUSTER
CREATE CLUSTER Crear un cluster en el propio esquema CREATE ANY CLUSTER Crear un cluster en cualquier esquema ALTER ANY CLUSTER Modificar cualquier cluster de la BD DROP ANY CLUSTER Borrar cualquier cluster de la BD
DATABASE
ALTER DATABASE Modificar la BD añadiendole ficheros CREATE DATABASE LINK Crear links privados para acceder a otra BD CREATE PUBLIC DATABASE
LINK
Crear links públicos para acceder a otra BD INDEX
CREATE ANY INDEX Crear un índice en cualquier esquema en cualquier tabla
ALTER ANY INDEX Modificar cualquier índice de la BD DROP ANY INDEX Borrar cualquier índice de la BD
PRIVILEGE
GRANT ANY PRIVILEGE Conceder cualquier privilegio de sistema PROCEDURE
CREATE ANY PROCEDURE Crear procedimientos almacenados, funciones y paquetes en cualquier esquema
CREATE PROCEDURE Crear procedimientos almacenados, funciones y paquetes en nuestro esquema
ALTER ANY PROCEDURE Modificarprocedimientos almacenados, funciones y paquetes en cualquier esquema
DROP ANY PROCEDURE Borrar procedimientos almacenados, funciones y paquetes en cualquier esquema
EXECUTE ANY PROCEDURE Ejecutar procedimientos almacenados, funciones o referencias a paquetes públicos en cualquier esquema
PROFILE
CREATE PROFILE Crear un perfil de usuario
ALTER PROFILE Modificar cualquier perfil
DROP PROFILE Borrar cualquier perfil
ROLE
CREATE ROLE Crear roles
ALTER ANY ROLE Modificar roles
CREATEROLLBACK SEGMENT Crear segmentos de rollback ALTER ROLLBACK SEGMENT Modificar segmentos de rollback DROP ROLLBACK SEGMENT Eliminar segmentos de rollback
SEQUENCE
CREATE SEQUENCE Crear secuencias en nuestro esquema ALTER ANY SEQUENCE Modificar cualquier secuencia de la BD DROP ANY SEQUENCE Borrar secuencias de cualquier esquema SELECT ANY SEQUENCE Referenciar secuencias de cualquier esquema
SESSION
CREATE SESSION Conectarnos a la BD
ALTER SESSION Manejar la orden ALTER SESSION
RESTRICTED SESSION Conectarnos a la BD cuando se ha levantado Con STARTUP RESTRICT
SYNONYM
CREATE SYNONYM Crear sinónimos en nuestro esquema CREATE PUBLIC SYNONYM Crear sinónimos públicos
DROP PUBLIC SYNONYM Borrar sinónimos públicos
CREATE ANY SYNONYM Crear sinónimos en cualquier esquema DROP ANY SYNONYM Borrar sinónimos en cualquier esquema
TABLE
CREATE TABLE Crear tablas en nuestro esquema y generar Índices sobres las tablas del esquema CREATE ANY TABLE Crear una tabla en cualquier esquema ALTER ANY TABLE Modificar una tabla en cualquier esquema DROP ANY TABLE Borrar una tabla en cualquier esquema LOCK ANY TABLE Bloquear una tabla en cualquier esquema SELECT ANY TABLE Hacer select en cualquier tabla
INSERT ANY TABLE Insertar filas en cualquier tabla UPDATE ANY TABLE Modificar filas en cualquier tabla DELETE ANY TABLE Borrar filas en cualquier tabla
TABLESPACES
CREATE TABLESPACE Crear espacios de tablas ALTER TABLESPACE Modificar tablespaces
MANAGE TABLESPACE Poner on-line o off-line a cualquier tablespace
DROP TABLESPACE Eliminar tablespace
UNLIMITED TABLESPACE Utilizar cualquier espacio de cualquier tablespace TRIGGER
CREATE ANY TRIGGER Crear triggers en cualquier esquema de la BD ALTER ANY TRIGGER Activar o desactivar cualquier trigger
DROP ANY TRIGGER Eliminar triggers de cualquier esquema DROP TRIGGER Crear triggers en nuestro esquema
ALTER USER Modificar cualquier usuario. Este privilegio autoriza al que lo recibe a cambiar la contraseña de otro usuario, a cambiar las cuotas sobre cualquier espacio de tablas, a establecer espacios de tablas por omisión...
DROP USER Eliminar usuarios
VIEW
CREATE VIEW Crear vistas en el esquema propio
CREATE ANY VIEW Crear vistas en cualquier esquema DROP ANY VIEW Borrar vistas de cualquier esquema
Formato de la orden GRANT para asignar privilegios del sistema
GRANT {priv_objeto | rol} [ ,{priv_objeto | rol },...]
TO {usuario | rol | PUBLIC} [,{usuario | rol | PUBLIC}...]
[WITH ADMIN OPTION] ;
WITH ADMIN OPTION
Permite que el receptor del privilegio o rol pueda conceder esos mismos privilegios a otros usuarios o roles
TO Identifica a los usuarios o roles a los que se conceden los privilegios
EJEMPLOS:
Cuando creamos un usuario tenemos que darle privilegios para que como mínimo pueda iniciar la session
CREATE USER PEDRO IDENTIFIED BY PEDRO DEFAULT TABLESPACE USER_DATA;
Usuario creado
GRANT CREATE SESSION TO PEDRO;
Ahora le concedemos a pedro el rol CONNECT GRANT CONNECT TO PEDRO;
Para conceder a dos usuarios Pedro y Juan los privilegios de DBA
Para hacer que todos los usuarios puedan hacer SELECT en cualquier tabla de cualquier usuario
GRANT SELECT ANY TABLE TO PUBLIC;
Para retirar privilegios de objetos a los usuarios o roles es:
REVOKE {priv_objeto [ ,{priv_objeto },...] ... | ALL [PRIVILEGES]}
ON [usuario.] Objeto
FROM {usuario | rol | PUBLIC} [,{usuario | rol | PUBLIC}...] ;
Para retirar privilegios de Sistema o roles a los usuarios o roles es:
REVOKE {priv_sistema | rol } [ , {priv_sistema | rol }] ...
FROM {usuario | rol | PUBLIC} [,{usuario | rol | PUBLIC}...] ;
EJEMPLOS:
Pepito retira los privilegios SELECT i UPDATE sobre tabla1 a Francisco.
REVOKE SELECT, UPDATE ON TABLA1 FROM FRANCISCO;
Pepito retira todos los privilegios a Francisco y Juan sobre tabla1.
REVOKE ALL ON TABLA1 FROM FRANCISCO, JUAN;
Retiramos los privilegios de borrar usuarios a Pepito.
REVOKE DROP USER FROM PEPITO;
Retiramos los privilegios de consultar cualquier tabla a todos los usuarios.
REVOKE SELECT ANY TABLE FROM PUBLIC;
Para conocer los privilegios que han concedido o recibido los usuarios sobre los objetos o a nivel del sistema, podemos consultar las siguientes vistas del diccionario de datos:
VISTAS CON INFORMACION DE LOS PRIVILEGIOS SESSION_PRIVS Privilegios del usuario activo
USER_SYS_PRIVS Privilegios de sistema asignados al usuario
DBA_SYS_PRIVS Privilegios de sistema asignados a los usuarios o roles USER_TAB_PRIVS Concesiones sobre objetos que son propiedad del
usuario, concedidos o recibidos por este.
USER_TAB_PRIVS_MADE Concesiones sobre objetos que son propiedad del usuario (asignadas)
USER_TAB_PRIVS_RECD Concesiones sobre objetos que recibe el usuario USER_TAB_GRANTS Concesiones en objetos para los que el usuario es el
propietario, el que concedió el privilegio o al que se concedió el privilegio
USER_TAB_GRANTS_MADE Todas las concesiones hechas en objetos que son propiedad del usuario
USER_TAB_GRANTS_RECD Concesiones en objetos en las que el usuario es aquel al que se ha concedido el privilegio (concesiones recibidas)
ALL_TAB_GRANTS
ALL_TAB_GRANTS_MADE ALL_TAB_GRANTS_RECD
Son iguales que las anteriores vistas, con la diferencia de que aparecen las concesiones de todos los usuarios.
USER_COL_GRANTS Concesiones en columnas para las que el usuario es el propietario,el que concedió el privilegio o al que se le concedió el privilegio
USER_COL_GRANTS_MADE USER_COL_GRANTS_RECD
Son iguales que las anteriores vistas, pero para columnas
ALL_COL_GRANTS
ALL_COL_GRANTS_MADE ALL_COL_GRANTS_RECD
Son iguales que las anteriores vistas, pero aparecen concesiones de todos los usuarios de la BD
USER _COL_PRIVS Concesiones sobre columnas en las que el usuario es el propietario, asigna el privilegio o lo recibe
USER _COL_PRIVS_MADE Todas las concesiones sobre las columnas de objetos que son propiedad del usuario
USER _COL_PRIVS_RECD Concesiones sobre columnas recibidas por el usuario
ROLES
Un conjunto de privilegios se puede agrupar en un rol para poder ser en un futuro asignado a un conjunto de usuarios.
COMO CREAR UN ROL?
CREATE ROLE Nombre_del_rol [IDENTIFIED BY CONTRASEÑA] ;
*IDENTIFIED BY CONTRASEÑA:
indica que el usuario que desee usar los privilegios debe de introducir la clave de acceso en la orden SETROLE para cualquier rol
Después de crear un role debemos asignar con la orden GRANT los privilegios que queremos que posea el rol creado.
EJEMPLOS:
CREAR UN ROL
Creamos un rol llamado ACCESO CREATE ROLE ACCESO;
CONCESION DE PRIVILEGIOS A UN ROL
Concedemos ciertos privilegios al rol creado.
GRANT SELECT, INSERT ON EMPLEADOS TO ACCESO;
GRANT INSERT ON DEPART TO ACCESO;
GRANT CREATE SESSION TO ACCESO; (Importante!!) CONCESION DE UN ROL A UN USUARIO
Concedemos el rol creado al usuario Miguel.
GRANT ACCESO TO MIGUEL;
SUPRESION DE PRIVILEGIOS EN LOS ROLES
SUPRESION DE UN ROL
DROP ROLE Nombre_Rol;
Ejemplo:
DROP ROLE ACCESO;
ESTABLECER UN ROL POR DEFECTO
Es posible establecer un rol por defecto mediante la orden ALTER USER ALTER USER Nombre_usuario
DEFAULT {[ROLE nombre_del_rol] | [NONE] } ;
NONE hace que el usuario no tenga rol por defecto Ejemplo:
ALTER USER MIGUEL DEFAULT ROLE NUEVOROL;
Con CREATE USER no se puede asignar un rol por defecto.
Para conocer a qué usuarios se les ha concedido un rol, o los privilegios que se han concedido a este rol se pueden consultar a las siguientes vistas del diccionario de datos.
INFORMACION SOBRE ROLS EN EL DICCIONARIO DE DATOS USER_TAB_PRIVS Concesiones sobre objetos que son propiedad del usuario,
concedidos o recibidos por este
ROLE_SYS_PRIVS Privilegios de sistema aplicados a roles ROLE_TAB_PRIVS Privilegios de las tablas aplicados a roles ROLE_ROLE_PRIVS Roles asignados a otros roles
SESSION_ROLES Roles activos por el usuario USER_ROLE_PRIVS Roles asignados al usuario
DBA_SYS_PRIVS Privilegios del sistema asignados a los usuarios o roles DBA_ROLE_PRIVS Privilegios asignados a todos los usuarios o roles DBA_ROLES Todos los roles
PERFILES
Conjunto de límites a los recursos de la BD.
Por omisión a los usuarios se les asigna el perfil DEFAULT (en un principio recursos ilimitados)
COMO CREAR UN PERFIL?
CREATE PROFILE Nombre_del_perfil LIMIT {
SESSION_PER_USER | CPU_PER _SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME | LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | PRIVATE_SGA | COMPOSITE_LIMIT }
{ Entero [K|M] | UNLIMITED | DEFAULT }
[
{ SESSION_PER_USER | CPU_PER _SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME | LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | PRIVATE_SGA | COMPOSITE_LIMIT }
{ Entero [K|M] | UNLIMITED | DEFAULT } ] ...
UNLIMITED: significa que no hay límite sobre un recurso particular.
DEFAULT: coge el límite del perfil DEFAULT
RECURSO FUNCION
SESSIONS_PER_USER Número de sesiones concurrentes permitidas por nombre de usuario
CONNECT_TIME Tiempo de conexión permitido por sesión antes de que el usuario sea desconectado (en minutos)
IDLE_TIME Tiempo de inactividad permitido antes de que sea desconectado (en minutos)
