Diseño de Bases de Datos

Diseño de bases de datos (../PID_00223656/PID_00223656.
html)
Procesamiento de consultas y vistas

Seguridad en bases de datos
0 0
Joan Anton Pérez Braña Santiago Ortego Carazo
Ver anotacionse
PID_00223669
(http://creativecommons.org/licenses/by-nc-nd/3.0/es/legalcode.es)
Los textos e imágenes publicados en esta obra están sujetos –excepto que se indique lo contrario– a una licencia de
Reconocimiento-NoComercial-SinObraDerivada (BY-NC-ND) v.3.0 España de Creative Commons. Podéis copiarlos, distribuirlos y
transmitirlos públicamente siempre que citéis el autor y la fuente (FUOC. Fundación para la Universitat Oberta de Catalunya), no
hagáis de ellos un uso comercial y ni obra derivada. La licencia completa se puede consultar en
http://creativecommons.org/licenses/by-nc-nd/3.0/es/legalcode.es
Índice
Introducción
Objetivos
1. Procesamiento de consultas
1.1. Descomposición de consultas
1.1.1. Validación léxica y sintáctica
1.1.2. Normalización de la consulta
1.1.3. Análisis semántico

1.1.4. Simplificación
1.2. Optimización semántica
1.3. Optimización sintáctica

/
1.3.1. Reglas de equivalencia
Diseño
1.3.2.deEstrategias
bases dededatos (../PID_00223656/PID_00223656.html)
procesamiento heurístico
1.4. Estimación de costes para las operaciones de álgebra relacional
1.4.1. Estadísticas de la base de datos

1.4.2. Operación de selección
1.4.3. Operación de ordenación
1.4.4. Operación de proyección
0 0
1.4.5. Operación de combinación
1.4.6. Operaciones de conjuntos de álgebra relacional
1.4.7. Agregación
Ver anotacionse
1.5. Optimización física
1.5.1. Optimización heurística
1.5.2. Optimización basada en costes
2. Procesamiento de vistas
2.1. Mecanismos de implementación de vistas
2.2. Actualización de vistas
2.2.1. Actualización con disparadores de sustitución
2.3. La vista como elemento de diseño externo
2.4. Las tablas derivadas
2.5. Tablas temporales

2.6. Ventajas e inconvenientes en la utilización de vistas
3. La seguridad
3.1. Identificación y autenticación
3.2. Control de acceso
3.2.1. Control de acceso discrecional
3.2.2. Control de acceso obligatorio
3.2.3. Clasificación de los sistemas de seguridad

3.3. Implementación del control de acceso discrecional a SQL:2011
3.4. Auditoría
3.5. La Ley de protección de datos de carácter personal
3.5.1. Principios generales de protección de datos
3.5.2. Los niveles de seguridad
3.5.3. La Agencia de Protección de Datos
4. Anexos
4.1. Reglas de equivalencia de operaciones de álgebra relacional

4.2. Consideraciones sobre vistas en el SGBD Oracle 11g
4.2.1. Vistas del diccionario de datos
4.2.2. Operaciones de actualización sobre vistas
4.3. Aspectos referentes a la seguridad en el SGBD Oracle 11g
4.3.1. Gestión de usuarios
4.3.2. Definición de perfiles
4.3.3. Identificación de usuarios

/
4.3.4. Gestión de los privilegios
Diseño
4.3.5.deRoles
bases de datos (../PID_00223656/PID_00223656.html)
Resumen
Glosario
Bibliografía
0 0
Introducción
Ver anotacionse
El lenguaje SQL es un lenguaje declarativo. Como tal, las consultas realizadas con este lenguaje especifican qué se quiere
obtener en lugar de indicar cómo se quiere conseguir. En el presente módulo se explica la forma en que los sistemas
gestores de bases de datos (SGBD) relacionales evalúan sistemáticamente las posibles estrategias alternativas que se
pueden presentar, de acuerdo con las necesidades de los usuarios y las aplicaciones, con el objetivo de escoger la
estrategia que se considera óptima.
También veremos que las vistas proporcionan mecanismos de seguridad y permiten al diseñador de la base de datos
personalizar el modelo lógico al modelo de los usuarios. Se estudiará en qué casos una vista es actualizable y qué
mecanismos incorporan los SGBD para permitir que las vistas sean actualizables.
Por último, estudiaremos las técnicas empleadas para proteger la base de datos contra accesos no autorizados y los
mecanismos para otorgar/revocar privilegios a los diferentes usuarios según el modelo de negocio implícito en el sistema
de información. También trataremos las obligaciones legales derivadas del hecho de estar en posesión de determinados
datos especialmente protegidos.
Objetivos
Los materiales didácticos asociados a este módulo os permitirán lograr los siguientes objetivos:
1. Saber cuáles son los mecanismos de procesamiento y optimización de consultas, y así poderlas plantear de la forma
más eficiente posible.
2. Conocer las diferentes estrategias de implementación de las operaciones de álgebra relacional con el fin de evaluar el
coste de las consultas.
3. Interpretar y optimizar el plan de ejecución de una consulta de forma adecuada.
4. Presentar las vistas como elementos de diseño externo que permiten la actualización de datos.
5. Conocer nuevas aplicaciones de las vistas para mejorar el diseño de la base de datos.
6. Conocer el alcance de los mecanismos de seguridad de una base de datos.
7. Tomar conciencia de las obligaciones legales derivadas del cumplimiento de la Ley orgánica de protección de datos de
carácter personal.
/
Diseño de bases de datos (../PID_00223656/PID_00223656.html)
1. Procesamiento de consultas
0 0
Una de las principales críticas a los primeros sistemas gestores de bases de datos (SGBD (1) ) relacionales fue el bajo
rendimiento en el procesamiento de consultas. En los lenguajes no procedimentales, como es el caso del SQL, el usuario
Ver anotacionse
especifica los datos que quiere obtener en lugar de indicar cómo quiere conseguirlos.
El procesamiento de consultas hace referencia al conjunto de actividades que el SGBD lleva a cabo para
extraer información de una base datos con el objetivo de lograr la estrategia más eficiente y que le permita tener
un mayor control sobre las prestaciones del sistema.
Figura 1. Etapas del procesamiento de consultas.
En la figura 1 podéis observar gráficamente las cuatro etapas que componen el procesamiento de consultas:
1) descomposición de la consulta, /
2) optimización de la consulta,
3) generación de código, y
4) ejecución de la consulta.
La descomposición de consultas implica la traducción de consultas expresadas en lenguaje SQL a una

representación interna basada en el álgebra relacional que suele ser más útil.
0 0
En primer lugar, se comprueba la corrección sintáctica y semántica de la consulta en SQL y después se crea un árbol
sobre el cual se realiza el análisis de la consulta, que se transformará en una expresión de álgebra relacional.
Por lo que respecta a las expresiones de álgebra relacional, utilizaremos el siguiente convenio de símbolos:
Ver anotacionse
Concepto Representación Observaciones
Relaciones R, S La lista de atributos de cada relación se define según el

esquema siguiente: R = (A1, A2, ..., An).
Predicados p, q Estos predicados se evaluarán lógicamente en las operaciones

de selección y combinación.
Selección σp(R) Operación que devuelve las tuplas de la relación R que

satisfacen la evaluación del predicado p.
Proyección ∏L(R) Donde L corresponde a una lista de atributos que se mostrarán

de la relación R.
Unión (R) ∪ (S) Se obtiene una relación nueva que incluye las tuplas de la
relación R y S menos las repeticiones.
Intersección (R) ∩ (S) Se obtiene una relación nueva que incluye las tuplas que
pertenecen a las dos relaciones, R y S .
Diferencia (R) – (S) Se obtiene una relación nueva que incluye las tuplas que
pertenecen a la relación R pero que no están incluidas en la
relación S.
Producto cartesiano (R) × (S) Se obtiene una relación nueva formada por todas las tuplas
que resultan de concatenar tuplas de la relación R con tuplas
de la relación S.
Combinación Theta (R) ⋈ p (S) Se obtiene una relación nueva que incluye los atributos de los
pares de tuplas correspondientes a R y S que satisfacen el
predicado p.
A partir del primer árbol de consulta lógico, que representa la expresión relacional, empieza el proceso de optimización de
la consulta.
La optimización de consultas consiste en encontrar el plan de ejecución más eficiente de entre varias
estrategias disponibles para procesar una consulta dada.
/
La optimización de consultas se lleva a cabo desde tres vertientes:
1) Optimización semántica, que consiste en reescribir la consulta basándose en las restricciones especificadas en el
esquema de la base de datos.
2) Optimización sintáctica, que consiste en transformar heurísticamente la expresión relacional original en otra
equivalente, pero que sea mucho más eficiente. Del mismo modo que una consulta se puede expresar de diferentes
formas en SQL, una consulta SQL también se puede traducir a diferentes expresiones de álgebra relacional.
Supongamos el esquema de la base de datos COMPANY definido a continuación:
0 0
(1) SGBD es la sigla de sistema gestor de bases de datos.
Ver anotacionse
Jobs(jobId,jobName)
Locs(locId,streetAddress,postalCode,stateProvince,city,countryId)
Emp(empId,firstName,lastName,jobId,deptId,hireDate,
salary,manager)
{jobId} REFERENCES Jobs(jobId),
{manager} REFERENCES Emp(empId),
{deptId} REFERENCES Dept(deptId)
Dept(deptId, deptName, managerId, locId),
{locId} REFERENCES Locs(locId),
{managerId} REFERENCES Emp(empId)
Imaginemos que queremos consultar los empleados de la base de datos COMPANY que pertenecen al departamento “IT”
y que son alta en la empresa a partir del 1 de septiembre del 2007.
1. SELECT *
2. FROM Emp e, Dept d
3. WHERE e.dept Id = d.dept Id
4. AND (e.hireDate > ';01-SEP-07';
5. AND d.deptName LIKE ';IT';) ;
A partir de esta consulta, podemos obtener tres expresiones de álgebra relacional equivalentes distintas:
/
a) Se realiza el producto cartesiano entre las relaciones Emp y Dept para posteriormente realizar las operaciones de
Diseño
selección de bases de
correspondientes: datos (../PID_00223656/PID_00223656.html)
σ(deptName=′IT’)∧(hireDate>′01-SEP-07’)∧(Emp:deptId=Dept:deptId)(Emp × Dept)
b) Se combinan las tablas y después se realizan las operaciones de selección:
σ(deptName=’IT’)∧ (hireDate>′01-SEP-07’)(Emp ⋈ (Emp:deptId=Dept:deptId) Dept)
c) Primero se realizan las operaciones de selección correspondientes a cada tabla y después se combinan los resultados:
0 0
(σ(deptName=′IT’) Dept) ⋈ (Emp:deptId=Dept:deptId) (σ(hireDate>′01-SEP-07’)(Emp))
Reflexión
Ver anotacionse
Prestad atención al hecho de que la serie de operaciones de álgebra relacional es diferente en cada caso.
3) Optimización física, que permite escoger, de entre los distintos planes de evaluación, cada uno con costes
diferentes, el que resulte más eficiente. Para determinar el coste más eficiente de cada consulta, es preciso conocer el
coste de la secuencia de operaciones del álgebra que la forman y, por lo tanto, de cada operación, lo cual a menudo
depende de diferentes parámetros. Así pues, para especificar completamente cómo se evaluará la consulta, hay que
determinar qué algoritmos se utilizarán para cada operación y cuáles serán los índices empleados. Estas especificaciones
se denominan primitivas de evaluación, y la secuencia de estas primitivas se denomina plan de ejecución de una
consulta.
El plan de ejecución es la secuencia de pasos que realizará el SGBD para ejecutar una sentencia SQL.
El SGBD construye un plan de ejecución que minimiza el uso de recursos. Así, trata de reducir en la medida de lo posible
el tiempo de ejecución de la consulta, ya sea minimizando el tiempo de cada operación o maximizando el número de
operaciones paralelas. Por último, el motor de ejecución será el que ejecutará el plan trazado y obtendrá el resultado
de la consulta a partir de los datos almacenados.
Reflexión
Antes del estudio de la optimización física, se ofrecerá una visión de la estimación de costes para las diferentes operaciones del
álgebra relacional.
1.1. Descomposición de consultas

Esta es la primera fase del procesamiento de una consulta. Se comprueba que la consulta sea sintáctica y
semánticamente correcta y, si es el caso, se transforma la consulta expresada en lenguaje SQL en un conjunto de
operaciones de álgebra relacional.
Las etapas típicas de la descomposición de consultas son:
1) validación léxica y sintáctica,
2) normalización de la consulta,
3) análisis semántico, y
4) simplificación. /
1.1.1. Validación léxica y sintáctica

La consulta se analiza léxica y sintácticamente empleando las técnicas de compiladores de los lenguajes de
programación.
0 0
Se accede al diccionario de datos y se comprueba que todas las tablas y atributos que se mencionan en la consulta
realmente existen. También se comprueba que el usuario tiene los derechos de acceso correspondientes y que las
Ver anotacionse
operaciones realizadas son adecuadas para aquel tipo de objeto.
Ejemplo de validación léxica y sintáctica
Supongamos el esquema de la base de datos COMPANY y la siguiente consulta:
1. SELECT emplId
2. FROM Emp
3. WHERE deptId LIKE ';10';;
Esta consulta sería rechazada por dos motivos:
1) El atributo emplId no está definido por la relación Emp: habría que denominarlo empId.
2) La comparación LIKE ‘10’ es incompatible con el tipo de datos deptId, que es numérico en lugar de ser una
cadena de caracteres.
Finalizado este análisis, la consulta se ha transformado en un árbol de consulta construido de la siguiente manera:
1) Por cada relación base de la consulta se crea un nodo hoja.
2) Por cada operación intermedia producida por una operación de álgebra relacional se crea un nodo no hoja.
3) El resultado de la consulta se representa como la raíz del árbol.
4) La secuencia de operaciones se dirige de los nodos hoja al nodo raíz.
Ejemplo de transformación de una consulta en un árbol de operaciones relacionales
Consideremos la siguiente consulta:
1. SELECT e.firstName, e.lastName, e.hireDate, d.deptName

3. WHERE e.dept Id = d.dept Id
4. AND (e.hireDate > ';01-SEP-07'; AND d.deptName LIKE ';IT';);
Esta consulta genera el plan de evaluación siguiente:
/
0 0
Ver anotacionse
Que corresponde a la siguiente expresión relacional:
∏firstName;lastName((σ(hireDate>′01-SEP-07’)(Emp)) ⋈ (Emp:deptId=Dept:deptId) (σ(deptName=′IT’)(Dept)))
Ved también
Los derechos de acceso se estudian en el apartado 3 de este módulo didáctico.
1.1.2. Normalización de la consulta

Se evalúa el predicado de la cláusula WHERE, que a menudo suele ser lo bastante complejo como para convertir la
consulta en una de estas formas normalizadas:
Forma normal conjuntiva: genera una secuencia de conjunciones conectadas con el operador ∧ (AND). Cada
conjunción puede contener uno o más predicados conectados con el operador ∨ (OR).
Ejemplo
(jobId =′STClerk′ ∨ salary > 2000) ∧ manager = 121
Forma normal disyuntiva: genera una secuencia de disyunciones conectadas con el operador ∨ (OR). Cada
disyunción puede contener uno o más predicados conectados con el operador ∧ (AND).
Ejemplo
(jobId =′STClerk′ ∧ salary > 2000) ∨ (jobId =′STClerk′ ∧ manager = 121)
1.1.3. Análisis semántico

En esta etapa se lleva a cabo un análisis semántico con el objetivo de rechazar las consultas normalizadas que sean
contradictorias o que no estén bien formuladas.
/
Una consulta incorrecta es aquella cuyos componentes, una vez formulada, no permiten la generación de resultado, lo
Diseño
que puede de bases
suceder de datos
si falta alguna (../PID_00223656/PID_00223656.html)
especificación de combinación.
Ejemplo de consulta incorrecta
1. SELECT e.firstName, e.lastName

2. FROM Emp e, Dept d, Locs l
3. WHERE e.deptId=d.deptId
0 0
4. AND l.city LIKE ';Paris';
5. AND e.salary > 2000;
Ver anotacionse
Esta consulta se considera incorrecta porque las conexiones entre relaciones no están completas, ya que se ha
omitido la condición de combinación d.locId=l.locId.
Una consulta contradictoria es aquella que contiene en su formulación algún predicado que ninguna tupla puede
satisfacer.
Ejemplo de consulta contradictoria
1. SELECT firstName, lastName, salary

2. FROM Emp
3. WHERE salary < 1200 AND salary > 2000;
Esta consulta es contradictoria porque las dos condiciones de la cláusula WHERE son incompatibles.
1.1.4. Simplificación
El objetivo de esta etapa es detectar predicados redundantes, eliminar expresiones comunes y transformar una consulta
en otra semánticamente equivalente pero que se pueda calcular de una forma más eficiente. Para realizar este proceso,
se aplican las reglas de la lógica.
Ejemplo de simplificación
Consideremos la siguiente consulta:

2. FROM Emp
3. WHERE salary > 1200 AND salary > 2000;
Esta consulta se puede simplificar, ya que la segunda condición incluye la primera:
/
2. FROM Emp
3. WHERE salary > 2000;
Ejemplo de simplificación de predicados redundantes
0 0
Consideremos también esta otra consulta:
1. SELECT firstName, lastName, salary, managerId, jobId
Ver anotacionse
2. FROM Emp
3. WHERE (jobId LIKE 'STClerk' AND manager = 121)
4. AND manager = 121;
Se puede transformar en otra consulta con una condición WHERE equivalente:
1. SELECT firstName, lastName, salary, managerId, jobId

2. FROM Emp
3. WHERE jobId LIKE 'STClerk' AND manager = 121;
1.2. Optimización semántica

La optimización semántica utiliza una técnica que emplea las restricciones especificadas en el catálogo de la base de
datos para reducir el espacio de búsqueda.
Ejemplo de optimización semántica
Suponiendo una restricción que indica que el salario debe ser superior a 500 euros, la consulta que presentamos
a continuación:

2. FROM Emp
3. WHERE salary > 500 AND deptId = 3 ;
podría simplificarse así:

2. FROM Emp
3. WHERE deptId = 3 ;
/
Ejemplo
Diseñodede
optimización
bases desemántica
datos (../PID_00223656/PID_00223656.html)
Del mismo modo, consideremos esta otra consulta:
1. SELECT e.deptId
3. WHERE e.deptId = d.deptId;
0 0
Dado que todos los valores de e.deptId deben ser valores de d.deptId, la consulta que presentamos a
continuación es equivalente a la anterior:
Ver anotacionse
1. SELECT e.deptId
2. FROM Emp e;
La implementación de este tipo de optimizadores en el SGBD permite incrementar el rendimiento, sobre todo en aquellos
sistemas que tengan una semántica muy rica y completamente implementada, ya que evita buscar según aquellas
condiciones que no se cumplen para ninguna fila de la tabla.
1.3. Optimización sintáctica

Mediante la aplicación de diferentes reglas de transformación, el optimizador puede convertir una expresión de álgebra
relacional en otra expresión equivalente pero más eficiente. Para llevar a cabo este proceso, se aplican las reglas de
equivalencia entre las operaciones de álgebra relacional, con el objetivo de encontrar una expresión equivalente.
1.3.1. Reglas de equivalencia
Las reglas de equivalencia y las estrategias de procesamiento heurístico que se utilizan para reestructurar el árbol de
operaciones de álgebra relacional son las siguientes:
1) Capacidad de transformación de las operaciones conjuntivas de selección en una cascada de operaciones individuales
de selección.
2) Conmutatividad en las operaciones de selección.
3) En una secuencia de operaciones de proyección, solo hace falta la última proyección de la secuencia.
4) Conmutatividad de la selección y la proyección.
Ved también
Podéis consultar varios ejemplos de reglas de equivalencia en los anexos del apartado 4.
/
5) Conmutatividad de la combinación Theta (y del producto cartesiano).
6) Conmutatividad de la selección y de la combinación Tetha (y del producto cartesiano).
7) Conmutatividad de la proyección y de la combinación Tetha (y del producto cartesiano)
8) Conmutatividad de la unión y de la intersección, pero no de la diferencia.
9) Conmutatividad de la selección y de las operaciones de conjuntos (unión, intersección y diferencia).
10) Conmutatividad de la proyección y de la unión.
0 0
11) Asociatividad de la combinación Tetha (y del producto cartesiano).
12) Asociatividad de la unión y de la intersección (pero no de la diferencia de conjuntos).
Ver anotacionse
Combinaciones Theta
Combinaciones que utilizan los operadores de comparación como condición de combinación.
1.3.2. Estrategias de procesamiento heurístico
Una serie de reglas heurísticas permiten encontrar una buena expresión equivalente, muchas veces la mejor, a partir de
la aplicación de las propiedades mencionadas anteriormente.
Los heurísticos normalmente aceptados son los que se presentan a continuación:
1) Realizar las operaciones de selección lo antes posible. Debido a que estas operaciones reducen la cardinalidad de la
relación resultante, utilizaremos la regla 1 para “conectar en cascada” todas las operaciones de selección. Después
aplicaremos las reglas 2, 4, 6 y 9, definidas anteriormente, referentes a la conmutatividad de la selección con
operaciones unarias y binarias. Las operaciones de selección aparecerán cerca de los nodos hoja del árbol indicando que
son las primeras en realizarse. Se mantendrán juntos los predicados referentes a la misma relación.
2) Transformar el producto cartesiano de dos relaciones seguido de una operación de selección en una operación de
combinación.
3) Emplear la asociatividad de las operaciones binarias para reordenar los nodos hoja. El objetivo es que las operaciones
de selección más restrictivas se ejecuten primero.
4) Realizar las operaciones de proyección lo antes posible.
5) El cálculo de las expresiones comunes sólo se realizará una vez.
Ved también
Observad cómo se han aplicado las estrategias de procesamiento en el árbol representado en el subapartado 1.1.1 de este
módulo didáctico.
1.4. Estimación de costes para las operaciones de álgebra relacional

/
Las implementaciones de operaciones del álgebra relacional son diferentes para cada SGBD, y cada una de ellas tiene un
costeDiseño
asociado.de bases
Cuando de datos
evaluamos (../PID_00223656/PID_00223656.html)
el coste de una consulta, es decir, el tiempo de respuesta para el plan de evaluación
de una consulta, hay que tener en cuenta diferentes factores, como el coste de CPU, los accesos a memoria, los accesos
a disco e incluso el tiempo empleado por las comunicaciones con sistemas remotos (en el caso de los SGBD distribuidos).
En los grandes SGBD, el coste más importante corresponde al acceso a disco, puesto que es el dispositivo más lento. Así
pues, para comparar las diferentes estrategias de manera relativa solo se utilizará el número de páginas de disco a las
que se accede (por operaciones de lectura/escritura) en el peor de los casos.
0 0
Consideraciones sobre el coste de los accesos a disco
Ver anotacionse
Consideremos que el subsistema de disco tarda tT segundos en realizar una operación de transferencia de un
bloque de datos con un tiempo de acceso a bloque tB, que corresponde al tiempo de búsqueda en disco más la
latencia rotacional. Entonces, una operación que transfiera nBlocks y ejecute N buscas tendría un coste temporal
de nBlocks · tT+ N · tB.
En las siguientes consideraciones, no se tendrán en cuenta las operaciones de escritura a disco que tardan el doble de
tiempo ni tampoco si hay alguna información en memoria intermedia. Este valor de estimación de coste se expresará
como CE.
Parámetros de acceso a disco
Para tener una idea de los parámetros asociados a los accesos a disco, podemos suponer que tT = 0,1 ms y tB = 4 ms en caso
de que la medida de un bloque sea de 4 kilobytes y se disponga de una velocidad de transferencia de 40 megabytes por
segundo.
1.4.1. Estadísticas de la base de datos

El éxito de la estimación de la medida y del coste de las operaciones intermedias depende de la calidad y del grado de
actualización de la información estadística almacenada en un SGBD. Para poder estimar el coste de cada uno de los
algoritmos, es preciso disponer de valores estadísticos de las tablas que intervienen en la consulta, la mayoría de los
cuales son valores medios que se mantienen calculados (o se recalculan de vez en cuando) en el diccionario de datos. Los
estadísticos más importantes son los que se relatan a continuación.
Para cada relación (o tabla) R:
1) nTuples(R): número de tuplas de la relación R; es decir, su cardinalidad.
2) blockFactor(R): factor de bloqueo de R; el número de filas de R que caben en una página.
3) nBlocks(R): número de bloques requeridos para guardar todas las tuplas de R, donde:
nBlocks(R) = nTuples(R)/blockFactor(R)
4) t(R): tamaño en bytes de una fila de R.
Para cada atributo A de la tabla R:
1) nDistinctA(R): número de valores diferentes del atributo A que aparecen en R.
2) minA(R): menor valor del atributo A en la tabla R.

/
3) maxA(R): mayor valor del atributo A en la tabla R.
4) CSA(R): cardinalidad de la selección del atributo. Es el número medio de filas que cumplen una condición sobre el
atributo A.
Para una condición de igualdad sobre el atributo A, el cálculo es:
a) Si A es una clave candidata:
CSA(R) = 1
0 0
b) Si los valores están distribuidos de forma uniforme:
CSA(R) = nTuples(R)/nDistinctA(R)
También es posible, suponiendo una distribución uniforme, calcular la cardinalidad de selección para otros casos
Ver anotacionse
diferentes de la igualdad:
a) Para predicados de comparación del tipo A > a:
CSA>a(R) = nTuples(R) · (maxA(R) – a)/(maxA(R) – minA(R))
b) Para predicados de comparación del tipo A < a:
CSA<a(R) = nTuples(R) · (a – maxA(R))/(maxA(R) – minA(R))
c) Para A ⊂ (a1, ... , an), donde n es el número de elementos del conjunto (a1, ... , an):
CSA⊂(a1, ..., an)(R) = n · (nTuples(R)/nDistinctA(R))
d) Para la disyunción de dos predicados, A ∨ B:
CSA∨B (R) = CSA(R) · CSB(R)/nTuples(R).
e) Para la conjunción, A ∧ B:
CSA∧B (R) = CSA(R) + CSB(R) – (CSA(R) · CSB(R)) / nTuples(R)
Para cada índice multinivel I de un atributo A:
1) nLevelsA(I): el número de niveles en I.
2) nLfBlocksA(I): el número de páginas de hojas en I.
Otro dato importante es el número de páginas de memoria, M, que se pueden utilizar como memoria intermedia.
1.4.2. Operación de selección

Hay muchos algoritmos para la selección de valores en una tabla. A continuación, hacemos un repaso de los más
importantes e indicamos su coste:
1) Búsqueda lineal sobre ficheros no ordenados: con este algoritmo se exploran todas las páginas que forman la
tabla y se comprueba cada una de las filas para ver si contiene el valor adecuado del atributo:
Si se trata de un atributo que no es una clave candidata, el coste será:
CE = nBlocks(R)
Si el atributo sobre el cual se hace la búsqueda es una clave candidata, de media lo encontraremos hacia la mitad de la
tabla, a veces antes y a veces después. En este caso el coste será:
/
CE = nBlocks(R)/2
2) Búsqueda binaria sobre ficheros ordenados: consiste en examinar primero la posición central de la tabla de
forma que se elimina la mitad que no corresponde y se repite el procedimiento hasta que se encuentra el valor buscado:
Para encontrar una tupla con un atributo clave, el coste será:
CE = log2(nBlocks(R))
Si se trata de un atributo no clave y se supone una distribución uniforme de los valores, tendremos:
0 0
CE = [log2(nBlocks(R)] + [CSA(R)/blockFactor(R)] – 1
3) Igualdad con una clave hash: si el atributo es una clave hash, se aplica el algoritmo hash para calcular la dirección
correspondiente a una tupla determinada. Si no hay desbordamiento, el coste será 1.
Ver anotacionse
4) Igualdad de clave primaria con un índice B+ tree: si la búsqueda se hace con respecto a la igualdad de una clave
primaria sobre la cual se ha definido un índice con estructura de árbol B+, el número de páginas leídas será igual al
número de niveles del árbol más la página de los datos, es decir:
CE = nLevelsA(I) + 1
5) Desigualdad con la clave primaria: si la condición de búsqueda es la de desigualdad con la clave principal, se
puede emplear el índice para localizar la tupla que satisface el predicado A = x y así, posteriormente, leer las tuplas que
se encuentran situadas antes o después de la localizada.
6) Igualdad con un índice secundario: si se trata de un índice que no representa una clave candidata y, por lo tanto,
pueden existir diferentes valores que cumplen la igualdad, se deberán leer todas las páginas que los contengan y el coste
será:
CE = nLevelsA(I) + CSA(R)/blockFactor(R))
Ejemplo de estimación de coste para una operación de selección
Para lograr el propósito de este ejemplo, se dan los siguientes supuestos referentes a la relación Emp descrita
anteriormente en el esquema de la base de datos COMPANY.
Hay un índice hash sobre el atributo de clave principal empId.
Hay un índice de agrupamiento sobre el atributo de clave externa deptId.
Hay un índice árbol B+ sobre el atributo salary.
La relación Emp tiene las siguientes estadísticas almacenadas en el catálogo del sistema:
nTuples(Emp) = 3000
blockFactor(Emp) = 30) ⇒ nBlocks(Emp) = 100
nDistinctjobId(Emp) = 50) ⇒ CSjobId(Emp) = 60
nDistinctfirstName(Emp) = 3000) ⇒ CSfirstName(Emp) = 1
nDistinctsalary(Emp) = 500) ⇒ CSsalary(Emp) = 6
minsalary(Emp) = 10.000
maxsalary(Emp) = 50.000
nLevelsjobId(I) = 2
/
nLevelssalary(I) = 2
nLfBlockssalary(I) = 50
El coste estimado de una búsqueda lineal sobre el atributo deptId es de 50 bloques y el coste de una búsqueda
lineal sobre un atributo no clave es de 100 bloques.
Se calcula el coste estimado para las siguientes operaciones:
1) σ(empId=′T450′)(Emp): la operación de selección contiene una condición de igualdad sobre la clave principal. El
0 0
atributo empId está almacenado mediante un método hash, por lo tanto el coste estimado será de un bloque (CE
= 1) y la cardinalidad estimada de la relación resultante será:
CSempId(Emp) = 1
Ver anotacionse
2) σ(firstName=′Smith′)(Emp): el atributo es no clave y no indexado, por lo que no se puede mejorar el método de
búsqueda lineal. El coste estimado obtenido será de CE = 100. La cardinalidad estimada se ha calculado
previamente:
CSfirstName(Emp) = nTuples(Emp) / nDistinctfirstName(Emp) = 3.000 / 3.000 = 1.
3) σ(jobId=′prog′)(Emp): el atributo del predicado es una clave externa con un índice de clustering, por lo tanto el
coste será CE = 2 + [60/30] = 4 bloques. La cardinalidad estimada será:
CSdeptIp(Emp) = 60
4) σ(salary>20.000)(Emp). El predicado implica una búsqueda dentro del rango del atributo salary que tiene un
índice árbol B+, por lo que para calcular el coste estimado se realizará el cálculo CE = 2 + [50/2] + [3.000/2] =
1.527. La cardinalidad estimada será:
CSsalary>20.000(Emp) = [3.000 · (50.000 – 20.000)/(50.000 – 10.000)] = 2.250
7) Condición de igualdad en un índice secundario sin agrupamiento: si en el predicado aparece una condición de
igualdad para el atributo A, que no es clave principal, pero es un índice secundario de tipo B+– tree sin agrupamiento,
entonces las tuplas se ubican en diferentes bloques. En este caso, el coste estimado sería:
CE=nLevelsA(I) + SCA(R)
8) Condición de desigualdad en un índice secundario de tipo B+–tree: si el predicado implica una condición de
desigualdad para el atributo A y este fuera un índice secundario de tipo B+–tree, entonces, a partir de los nodos hoja del
árbol, se puede realizar la exploración del nodo más pequeño hasta el valor x, en el caso de que las condiciones de
desigualdad fuesen A<x o A<=x, o de otro modo, desde x hasta el valor máximo, en los casos A>x o A>=x.
Considerando una distribución uniforme, se puede estimar un acceso a la mitad de los bloques y a la mitad de las tuplas,
el coste estimado sería:
CE = nLevelsA(I) + nLfBlocksA(I)/2 + nTuples(R)/2
Con lo que queda explicado el cálculo del punto 4; σ(salary>2000)(Emp); CE=2+50/2+3000/2.
B+ tree
Un árbol B+ es un tipo de estructura de datos en forma de árbol donde toda la información se guarda en las hojas, ya que los
nodos internos solo contienen claves y punteros. De este modo, se pueden crear índices multinivel y dinámicos, con un límite
máximo y mínimo en el número de claves por nodo.
/
1.4.3. Operación
Diseño de bases dede ordenación
Muchas veces resulta necesario ordenar los datos de una tabla, ya sea porque se desea el resultado ordenado o porque
se utiliza para implementar una operación de combinación de forma más eficiente.
La ordenación se puede conseguir creando un índice sobre el atributo objeto de la ordenación y utilizándolo para realizar
una lectura ordenada. No obstante, hay que tener en cuenta que esta ordenación se efectúa en el nivel lógico, pues
0 0
físicamente se puede traducir en un acceso a disco para cada tupla más la transferencia del bloque correspondiente. Esto
puede ser muy costoso, ya que el número de registros suele ser superior al número de bloques, dado que las tuplas no
tienen por qué estar almacenadas físicamente de manera consecutiva.
Ver anotacionse
Quicksort
El ordenamiento rápido, quicksort en inglés, es un algoritmo basado en la técnica del “divide y vencerás” que permite, de
media, ordenar n elementos en un tiempo proporcional a n·log(n).
En el caso de que toda la relación se pueda almacenar en la memoria principal, se pueden emplear técnicas de
ordenación rápida, como el algoritmo quicksort.
Cuando la relación no cabe en la memoria, se suele utilizar el algoritmo de ordenación-fusión externa. Consideremos que
se dispone de M marcos de página en la memoria intermedia de la memoria principal. Esta operación consta de dos
fases:
En la primera fase, el algoritmo divide la relación en N = [nBlocks(R)/M] partes, las carga sucesivamente en la memoria
para ordenar cada una de ellas y crear un archivo de secuencias Si.
En la segunda etapa, y en caso de que el número de secuencias N sea inferior a M:
1) En el primer bloque de cada archivo de secuencia, se lee la primera tupla de cada bloque, para proceder a la fusión
ordenada de los primeros bloques de cada secuencia.
2) En caso de que el contenido del bloque se haya agotado, se lee el siguiente bloque del mismo archivo de secuencias
hasta agotar todos los bloques de cada secuencia, lo cual implicará que se ha obtenido la ordenación.
3) En el caso de que el número de secuencias N todavía sea superior a M, se fusionan las M – 1 primeras secuencias para
formar otra secuencia ordenada. En este punto, se ha reducido el número de secuencias en M – 1.
4) En caso de que el número de secuencias sea menor que M, entonces se puede proceder a la fusión definitiva. En caso
contrario, se repite el procedimiento con otras M – 1 secuencias hasta obtener un número de secuencias menor que M.
El coste total de este algoritmo es tal como se indica a continuación.
El número inicial de secuencias es N = [nBlocks(R)/M]. Debido a que este decrece en un factor M – 1 en cada ciclo de
fusión, el número total de ciclos requeridos será de logM–1 · [nBlocks(R)/M]. En cada ciclo se leen y se escriben los
bloques de la relación una sola vez, por lo que el número de transferencias de bloque será nBlocks(R)[logM–
1[(nBlocks(R)/M] + 1]. También es necesario añadir los costes de búsqueda en disco. Durante la fase de fusión, si se leen
simultáneamente nBlocks[S] de cada secuencia, entonces cada paso implica como mínimo nBlocks(R) = nBlocks(S)
búsquedas para la lectura de datos. También hay que tener en cuenta que, aunque la salida se escriba secuencialmente,
el cabezal puede haberse desplazado, por lo que habrá que añadir 2[nBlocks(R)/nBlocks(S)] búsquedas por cada paso,
excepto en el paso final.
Así pues, el coste estimado será:
/
CE = 2[nBlocks(R)/nBlocks(S)] +
+ [nBlocks(R)/nBlocks(S)](2[logM–1[(nBlocks(R)/M] – 1])
Marcos de página
Consideramos que el número de marcos de página, M, es el número de bloques de disco que se pueden almacenar en la
memoria intermedia de la memoria principal.
0 0
1.4.4. Operación de proyección
Ver anotacionse
Una proyección implica dos operaciones: eliminar los atributos no deseados y eliminar, si es preciso, las tuplas repetidas
que se han generado con la cláusula DISTINCT.
Se puede implementar fácilmente la eliminación de duplicados empleando la ordenación de las tuplas y utilizando como
clave de ordenación los atributos resultantes de la proyección. Las tuplas idénticas aparecen contiguas durante la
operación de ordenación y, por lo tanto, se pueden eliminar todas menos una. El coste estimado en el peor de los casos
es el mismo que el coste estimado en el peor de los casos en la operación de ordenación.
Observación
Si dentro de la lista de atributos de la proyección se incluyen todos los que forman la clave primaria, no se producirán
duplicados.
1.4.5. Operación de combinación

Las operaciones de combinación, aparte de las del producto cartesiano, suelen ser las que consumen más tiempo durante
el procesamiento de consultas, por lo que marcarán de manera importante la eficiencia global del SGBD.
Los algoritmos más importantes son: la combinación de ciclo anidado, la combinación de ciclo anidado indexado, la
combinación por ordenación por fusión, la combinación por función resumen y la combinación por agrupación (clúster).
Combinación de ciclo anidado
La combinación de ciclo anidado (2) consiste en dos ciclos anidados: el bucle externo recorre todas las tuplas de la primera
relación R y el bucle interno recorre todas las tuplas de la relación S. El coste estimado para esta solución es:
CE = nBlocks(R) + nBlocks(R) · nBlocks(S)
Podemos apreciar claramente que, para reducir el coste, es preciso que la primera relación que se utiliza en el bucle
externo ocupe el menor número posible de bloques.
(2) En inglés, nested loop join.

/
Una mejora
Diseño consiste en leerde
de bases tantos bloques
datos como sea posible de la relación más pequeña, y reservar un bloque para la
(../PID_00223656/PID_00223656.html)
relación interna y otro para la relación resultante. Si la memoria intermedia (3) permite almacenar un número de bloques,
nBuffer, entonces se leen (nBuffer – 2) bloques de R y un bloque de S cada vez. Con esta técnica, la estimación del coste
sería:
CE = nBlocks(R) + nBlocks(S) · nBlocks(R)/(nBuffer – 2)
También deberíamos considerar si la memoria es suficiente para que se puedan leer de la memoria intermedia de la base
de datos después de la primera lectura todos los bloques de R. En este caso, la estimación del coste sería:
0 0
CE = nBlocks(R) + nBlocks(S)
(3)
Ver anotacionse
En inglés, buffer.
Combinación de ciclo anidado indexado
El coste de la combinación de ciclo anidado indexado (4) varía según el método de indexación que se utilice. Si existe un
índice o función resumen (5) sobre los atributos de combinación de la relación interna, entonces en el bucle interior no se
efectúa un recorrido por todas las páginas de la tabla, sino que se va directamente, mediante el índice o la función
resumen, a la página adecuada.
Si se tiene un índice árbol B+ sobre el atributo A, para encontrar el coste de la consulta, será necesario conocer:
nLevelsA(I), el número de niveles del árbol B+ del índice I sobre el atributo A; y CSA(R), la cardinalidad de selección del
atributo A de la tabla R y del número de filas que caben en una página blockFactor(R). Si se trata de un índice sobre una
clave candidata de R, el coste será:
CE = nBlocks(R) + nTuples(R) · (nLevelsA(I) + 1)
Si el atributo es un índice de agrupación, entonces la estimación del coste será:
CE = nBlocks(R) + nTuples(R) · (nLevelsA(I) + [CSA(R)/blockFactor(R)])
(4) En inglés, index nested loop join.
(5) En inglés, hash function.
Combinación por ordenación por fusión
En una combinación por ordenación por fusión (6) , la combinación más eficiente se logra cuando las relaciones están
ordenadas según los atributos de combinación. De no ser así, se requerirá un paso previo para ordenarlas. Una vez
ordenadas, solo hay que leer secuencialmente cada una de las tablas y añadir a los resultados aquellos valores que
coinciden. Si asumimos que la combinación es de tipo muchos a muchos, es decir, que hay diferentes tuplas de R y de S
con el mismo valor de combinación, y también asumimos que cada conjunto de tuplas con el mismo valor puede caber en
/
la memoria intermedia, entonces solo habrá que leer cada bloque de la relación una única vez. Así, la estimación del
costeDiseño
sería: de bases de datos (../PID_00223656/PID_00223656.html)
CE = nBlocks(R) + nBlocks(S)
Si es necesario ordenar una de las relaciones, entonces hay que añadir el coste de ordenación:
CE = nBlocks(R) + nBlocks(S) · log2(nBlocks(S))
teniendo en cuenta que se redondea por exceso el cálculo de cada logaritmo.
0 0
(6) En inglés, sort-merge join.
Ver anotacionse
Combinación por función resumen
El algoritmo de combinación por función resumen (7) se basa en emplear una función resumen (hash) que presente
características de uniformidad y aleatoriedad para dividir las filas de las dos tablas en conjuntos que tengan el mismo
valor de la función resumen. Después se realiza la combinación de las filas de cada partición.
El coste de la combinación por función resumen es:
CE = 3 · (nBlocks(R) + nBlocks(S)), si el índice hash se almacena en memoria y no se tienen en cuenta los

desbordamientos.
CE = 2 · (nBlocks(R) + nBlocks(S)) · [lognBuffer–1(nBlocks(S)) – 1] + nBlocks(R) + nBlocks(S), en otro caso.
Ejemplo de estimación de coste para una operación de combinación
Para lograr el propósito de este ejemplo, se dan los siguientes supuestos referentes a las relaciones Emp y Jobs.
Existe hash sin desbordamiento sobre el atributo de clave principal depId.
Existen unos cien bloques de memoria intermedia de la base de datos.
Tenemos las siguientes estadísticas almacenadas en el catálogo del sistema:
nTuples(Emp) = 3.000
blockFactor(Emp) = 30) ⇒ nBlocks(Emp) = 100
nTuples(Jobs) = 50
blockFactor(Jobs) = 10) ⇒ nBlocks(Jobs) = 5
Se quiere evaluar el coste de las diferentes estrategias para la combinación siguiente:
(Emp ⋈ Emp.jobId=Jobs.jobIdJobs)
1) La combinación de ciclo anidado:
La memoria intermedia sólo contiene un bloque de Emp y Jobs:
CE = nBlocks(Emp) + nBlocks(Emp) · nBlocks(Jobs) = 100 + 100 · 5 = 600
El número de bloques que se tratarán por Emp es (nBuffer – 2):
CE = nBlocks(Emp) + nBlocks(Jobs) · nBlocks(Emp)/(nBuffer – 2) =
= 100 + 100 · 5/98 = 106

/
Todos los bloques de la relación Emp caben en la memoria intermedia:
CE = nBlocks(Emp) + nBlocks(Jobs) = 100 + 5 = 105
2) La combinación de ciclo anidado indexado:
nTuples(Emp) + nBlocks(Emp) = 3.000 + 100 = 3.100
3) La combinación de ordenación por fusión:
Tuplas desordenadas:
0 0
CE = nBlocks(Emp) + nBlocks(Jobs) + nBlocks(Emp) · log2(nBlocks(Emp) +
+ nBlocks(Jobs) · log2(nBlocks(Jobs)) = 100 + 5 + 100 · 7 + 5 · 3 = 820
Ver anotacionse
Tuplas ordenadas:
CE = nBlocks(Emp) + nBlocks(Jobs) = 100 + 5 = 105
4) La combinación por función resumen. Si el índice hash cabe en la memoria:
CE = 3 · (nBlocks(Emp) + nBlocks(Jobs)) = 3 · (100 + 5) = 315
(7) En inglés, hash join.
Funciones resumen
Una función resumen es un algoritmo u operación que permite obtener un sumario o resumen a partir de un conjunto de datos.
Este sumario o resumen está asociado a los datos originales, y cualquier cambio en los datos originales tiene que repercutir en
el sumario o resumen.
1.4.6. Operaciones de conjuntos de álgebra relacional

Las operaciones de unión R ∪ S, intersección R ∩ S y diferencia R – S se pueden implementar ordenando primero las dos
relaciones según un mismo criterio y después produciendo el resultado.
Los resultados para cada operación se calculan de la siguiente manera:
Cuando se realiza la unión de R y S (R ∪ S), se realiza una lectura concurrente de las tuplas de las dos relaciones, y si
se detecta la misma tupla en ambas, se elimina el duplicado.
Cuando se realiza la intersección de R y S (R ∩ S), sólo se almacenan las tuplas que aparezcan como duplicados en
ambas relaciones.
Cuando se realiza la diferencia R – S, se almacenan las tuplas de R que no aparezcan en S.
Todo esto tendría un coste CE = nBlocks(R) + nBlocks(S), más el coste de la ordenación de R y S.
1.4.7. Agregación
/
Cualquier operación de agrupación se puede implementar de una manera parecida a la eliminación de duplicados, pero en
lugarDiseño delas
de eliminar bases
tuplas de
que datos
tienen el(../PID_00223656/PID_00223656.html)
mismo valor, se reúnen por grupos y se aplica la operación correspondiente para
obtener el resultado.
Operaciones de agrupación
Son operaciones de agrupación las funciones min, max, sum, count y avg.
0 0
1.5. Optimización física
Ver anotacionse
El optimizador físico de consultas es el componente del SGBD que prepara los planes de ejecución de las consultas para
que se lleven a cabo en el mínimo tiempo. Hay varias posibilidades para conseguirlo:
que el coste de CPU sea mínimo;
que la necesidad de memoria sea mínima;
que se minimicen los accesos a disco, y
otras posibilidades.
Los algoritmos de optimización física se pueden clasificar en dos grandes familias:
1) Optimización heurística: También conocida como optimización basada en reglas, consiste en escoger un plan
físico de ejecución con aquellos algoritmos que normalmente son más eficientes.
2) Optimización basada en costes: Consiste en encontrar todas las implementaciones físicas una vez se dispone de
todo el espacio de posibles realizaciones de la consulta. Para cada plan se calcula el coste (tiempo, número de accesos a
disco, etc.) y, finalmente, se escoge el de coste más bajo.
Los SGBD comerciales acostumbran a permitir escoger un tipo de implementación u otro, aunque la tendencia es ofrecer
solo optimización basada en costes, puesto que aunque la heurística es muy rápida de calcular, puede escoger planes de
ejecución muy poco eficientes.
Soluciones comerciales optimizadas
Si aumentamos la memoria de nuestro servidor, conseguiremos que muchos de los procesos de combinación se puedan hacer
completamente en la memoria. Habrán cambiado todos los tiempos de ejecución, mientras que los planes de ejecución se
mantendrán.
1.5.1. Optimización heurística

Para poder realizar una optimización heurística, es preciso que el SGBD tenga definido un orden de eficiencia para las
diferentes operaciones lógicas y pueda escoger aquel que suele tener un coste más bajo.
Como la mayoría de las opciones de diseño, la optimización heurística tiene virtudes y defectos: su principal virtud es que
no hay que efectuar ningún tipo de cálculo, la obtención de un buen plan de ejecución se realiza de manera inmediata; y
el principal defecto es que a veces los planes propuestos pueden diferir mucho de los óptimos.
/
En una base de datos compleja, una consulta puede involucrar varias tablas, cada una con varios índices y condiciones de
Diseño
selección de bases
complejas. de datossignifica
Esta complejidad (../PID_00223656/PID_00223656.html)
que podría haber una gran cantidad de opciones resultantes, y el sencillo
conjunto de reglas utilizadas por el optimizador basado en reglas no podría diferenciar lo suficientemente bien las
elecciones como para asegurar la mejor elección.
Otra de las debilidades del optimizador basado en reglas es la resolución de las opciones de optimización en caso de
obtener dos o más planes de ejecución con el mismo coste. En este caso, el optimizador considera la sintaxis de la
sentencia SQL para resolver el empate. La ruta de ejecución ganadora se basa en el orden en que las tablas se presentan
en la sentencia SQL.
0 0
Ver anotacionse
1.5.2. Optimización basada en costes
Este método selecciona la ruta de ejecución que requiere el menor número de operaciones lógicas de E/S. Con esta
finalidad se utilizan estadísticas que se consideran relevantes, recogidas sobre la composición de las estructuras de datos
proporcionados. De este modo, el optimizador basado en costes puede conocer qué tabla es la más grande y puede
seleccionarla como la tabla de la derecha para iniciar la consulta, independientemente de la sintaxis de la sentencia SQL.
Así reduce el número de iteraciones y, si se tercia, el número de accesos a disco.
Ejemplo de reducción del número de iteraciones
Se puede entender el impacto potencial sobre la elección del orden en las tablas observando una situación
sencilla en la que se hace una combinación de una tabla pequeña con diez registros, SmallTable, con una tabla
con diez mil, LargeTable.
1. SELECT *
2. FROM SmallTable s, LargeTable l
3. WHERE s.id = l.id;
La sentencia anterior obtendría un resultado equivalente a esta otra:
1. SELECT *
2. FROM LargeTable l, SmallTable s
3. WHERE l.id = s.id;
El tamaño de las tablas y el orden en que éstas se leen tiene repercusión en el tiempo de ejecución de la
consulta:
Si el optimizador elige para leer SmallTable en primer lugar, el SGBD lee las diez filas y después LargeTable
diez veces para encontrar las filas coincidentes de cada una de las diez filas.
Si el optimizador elige LargeTable en primer lugar, el SGBD tiene que leer las diez mil filas de LargeTable y
después las de SmallTable diez mil veces para encontrar los registros coincidentes.
Además, en el primer caso, las filas de SmallTable probablemente se podrían almacenar en memoria caché, lo
que reduciría el impacto de lecturas de disco.
Independientemente del orden en que aparecen las tablas en la sentencia SQL, gracias a la información obtenida
a partir de las estadísticas, el optimizador escogerá la tabla más grande como si esta apareciera la última en la
/
sentencia, es decir, más a la derecha.
A partir de las estadísticas, se seleccionan los operadores físicos y se determina la estrategia de ejecución.
Las estadísticas y los histogramas
0 0
Las estadísticas registran la distribución de los datos y las características de almacenamiento de las tablas, columnas,
índices y particiones a partir de los cuales el optimizador estima la cantidad de accesos a disco y memoria necesaria para
Ver anotacionse
ejecutar un plano físico particular.
Los parámetros estadísticos que los SGBD almacenan más a menudo son los siguientes:
1) Tabla: número de filas, número de páginas, número de páginas vacías, longitud media de una fila.
2) Columna: número de valores diferentes en la columna, número de valores NULL en la columna, histograma de
frecuencia de aparición de cada uno de los valores.
3) Índice: número de páginas, niveles, factor de agrupamiento.
En el SGBD comercial Oracle, se puede emplear el paquete PL/SQL DBMS_STATS para generar y gestionar las estadísticas
de las tablas, columnas, índices, particiones y otros objetos de la base de datos.
Generar y gestionar estadísticas
Por ejemplo, podemos recopilar estadísticas para el esquema ‘COMPANY’ utilizando la siguiente instrucción SQL,
donde también se especifica en el segundo parámetro que calcule automáticamente el alcance del tamaño de la
muestra.
1. EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('COMPANY',
2. DBMS_STATS.AUTO_SAMPLE_SIZE);
Ved también
Algunos de los parámetros que almacenan los estadísticos se ven en el subapartado 1.4.1. de este módulo didáctico.
Disponemos de diferentes opciones a la hora de recopilar estadísticas que permiten especificar el tipo de muestreo:
muestreo basado en filas, de forma que se ignora la ubicación física en disco; y
muestreo basado en bloques, de modo que se escoge una muestra aleatoria de bloques y las estadísticas se
generan a partir de los datos almacenados en ellos.
Generalmente, un muestreo utiliza menos recursos que si se calcula el valor exacto de la estructura completa.
Las estadísticas se almacenan en el diccionario de datos.
Un histograma es una estructura de datos que se puede utilizar para mejorar las estimaciones de las que puede
disponer el optimizador.
/
Reflexión
También se puede hacer que Oracle recopile las estadísticas al crear o reconstruir índices especificando la opción COMPUTE
STATISTICS en las sentencias CREATE INDEX o ALTER INDEX.
Un histograma recoge un conjunto de valores junto con sus frecuencias relativas, y proporciona al optimizador las
mejores estimaciones en presencia de una distribución uniforme. Existen dos tipos de histogramas:
0 0
Histograma equilibrado de ancho, que divide los datos en un número fijo de rangos con el mismo ancho y para
cada uno de ellos se realiza el cálculo del número de valores que les pertenece.
Histograma equilibrado de altura, donde se determinan los rangos una vez distribuidos equinuméricamente los
Ver anotacionse
valores y se generan rangos de diferente ancho pero con un mismo valor de frecuencia relativa.
Reflexión
En Oracle, es el usuario quien crea y mantiene los histogramas para las columnas apropiadas empleando el paquete PL/SQL
DBMS_STATS.
Operadores físicos y estrategias de ejecución
El término operador físico se utiliza para referenciar a un algoritmo específico que implementa la operación lógica de la
base de datos. Por ejemplo, se puede escoger el operador físico de combinación mediante un bucle anidado indexado con
encarrilamiento (8) .
Sustituir las operaciones lógicas en un árbol de álgebra relacional por operadores físicos produce una estrategia de
ejecución, plan de evaluación de la consulta o plan de acceso para la consulta.
Consideremos la consulta de ejemplo del subapartado 1.1.1.:
Aunque los diferentes SGBD tienen distintas implementaciones para los diferentes operadores físicos, podemos considerar
varios operadores abstractos para implementar las funciones especificadas en cada nodo del árbol: /
1) tableScan(R): exploración de la tabla. Los diferentes bloques de R se leen en un orden arbitrario.
2) sortScan(R, L): exploración ordenada. Las tuplas de R se leen por orden de acuerdo con los criterios de ordenación
especificados en la lista de atributos L.
3) indexScan(R, P): exploración indexada. El predicado P especifica una comparación entre el valor del atributo A y un
valor c. Se accede a las tablas mediante un índice sobre el atributo A.
4) indexScan(R, A): exploración indexada. A es un atributo de R. Se extrae la relación R completa empleando un índice
sobre el atributo A.
0 0
Además, la estrategia de ejecución soporta una interfaz iteradora uniforme que permite ocultar los detalles de
implementación de cada operador:
1) open: inicia el estado del iterador antes de extraer la primera tupla y asigna un espacio de memoria intermedia para
Ver anotacionse
las entradas y salidas. Se pueden indicar varios parámetros que permitan definir condiciones de selección de forma que
modifiquen el comportamiento del operador.
2) getNext: devuelve la siguiente tupla del resultado y la coloca en la memoria intermedia de salida. El estado del
iterador se actualiza para reflejar las acciones que ya se han realizado.
3) close: una vez finalizado el proceso de iteración, es decir, una vez generadas todas las salidas solicitadas, el operador
realiza las operaciones de limpieza y desasigna espacio de memoria.
Al definir este tipo de operadores, el sistema puede implementar de manera natural el concepto de encarrilamiento o de
materializar los valores intermedios.
1) Materialización: con este enfoque se inicia la evaluación para las operaciones de nivel más bajo a partir del árbol de
operadores. Se ejecutan las operaciones con los algoritmos estudiados y después se almacenan los resultados en
relaciones temporales. Así pues, el coste de toda la expresión es la suma de los costes de cada una de las operaciones
más el coste de los resultados intermedios en disco.
(8) En inglés, pipelining.
Encarrilamiento
El encarrilamiento, o pipelining, como se ve más adelante, es una técnica que consiste en aprovechar la salida de una operación
como entrada de la siguiente con el objetivo de incrementar la eficiencia de las operaciones, ya que ahorra el coste de
lectura/escritura de las relaciones intermedias.
2) Encarrilamiento: consiste en aprovechar la salida de una operación como entrada de la siguiente; de este modo nos
ahorramos el coste de lectura/escritura de las relaciones intermedias. Para cada operación del encarrilamiento se
modeliza un proceso aislado (9) que toma un flujo de tuplas de sus entradas y produce otro para su salida. Para esto es
necesario definir en cada operación una memoria intermedia para la entrada y otra para la salida. Con el encarrilamiento
también se puede conseguir que varias operaciones se ejecuten en paralelo, de forma que se gana velocidad a cambio de
una mayor necesidad de memoria para llevar a cabo los diferentes procesos.
(9) En inglés, thread.
Visualización del plan de ejecución
/
Oracle permite visualizar el plan de ejecución que escogerá el optimizador mediante la sentencia EXPLAIN PLAN.
Esta sentencia resulta bastante útil cuando se desea analizar por qué la eficiencia de una consulta no es la
esperada. La salida de esta sentencia se escribe de manera predeterminada en la tabla PLAN_TABLE.
Ved también
Podéis consultar la sintaxis y funcionalidad de la sentencia EXPLAIN PLAN en la documentación de Oracle.
0 0
Ver anotacionse
2. Procesamiento de vistas
Desde un punto de vista teórico, una vista se puede definir como el resultado dinámico de una o más operaciones
relacionales sobre tablas para producir una relación nueva.
Las vistas son relaciones virtuales que sólo están representadas por su nombre y su definición; no existen
físicamente en la base de datos. Sólo sirven a partir del momento en que el usuario las utiliza.
La sintaxis de creación de vistas según el estándar SQL:2011 es la siguiente:
1. CREATE VIEW ViewName [(column_name), ... ]

2. AS query
3. [WITH [CASCADED|LOCAL]CHECK OPTION ]
El estándar SQL
Desde que el lenguaje SQL fue aceptado como un estándar, en primer lugar por el ANSI en 1986 y después por la ISO en
1987, se han ido añadiendo nuevas funcionalidades y características, que se han recogido en las diferentes versiones,
documentadas en la referencia ISO/IEC 9075.
La cláusula WITH CHECK OPTION asegura que nunca podremos actuar sobre partes de una tabla que no están a la vista.
Si se incluye la cláusula LOCAL, se valida la integridad de la vista para aquellas operaciones de inserción o actualización
que se efectúen, mientras que si se utiliza la cláusula CASCADED, también se valida la integridad de todas las otras vistas
que dependen de ella.
Ejemplos de creación de vistas
Si se tiene en cuenta la base de datos COMPANY definida anteriormente, algunas definiciones de vistas son las
siguientes:
/
1. CREATE VIEW View01 AS
2. SELECT firstName, lastName, hireDate, salary
3. FROM Emp e
4. WHERE salary < 30000;
5.
7. SELECT firstName, lastName, deptName
0 0
9. WHERE e.deptId = d.deptId;
10.
12. SELECT *
Ver anotacionse
13. FROM View02
14. WHERE (firstName, lastName) IN
15. (SELECT firstName, lastName
16. FROM View01);
De manera opcional, se puede asignar un nombre a las diferentes columnas de la vista. En caso contrario, las
columnas tomarán el mismo nombre que tienen en la subconsulta. Si la subconsulta incluye columnas calculadas
o funciones, será imprescindible definirles un nombre.
Fijaos en que, tal como se puede ver en el tercer ejemplo, en la subconsulta de una vista puede aparecer otra
vista.
Por otro lado, ¿que pasaría si se realizara la siguiente actualización?:
1. UPDATE View01
2. SET salary = 45000
3. WHERE salary = 29000;
Se daría el caso inverosímil de que se ocultaría una fila a la vista. Una situación parecida se daría si se realizara
la siguiente inserción:
1. INSERT INTO View01

2. VALUES (';John';, ';Smith';, ';12/3/2012';, 50000);
Si la vista se hubiera creado con la cláusula WITH CHECK OPTION, las operaciones se habrían comprobado antes
para asegurar que las nuevas filas insertadas o actualizadas cumpliesen la condición de la vista.
Aunque una vista no ocupa mucho lugar en la base de datos, recordad que sólo se trata de una definición y que también
se puede destruir.
Según el estándar SQL:2011, la sintaxis de eliminación de vistas es la siguiente:
1. DROP VIEW View_Name [CASCADE|RESTRICT]
/
La cláusula opcional CASCADE destruirá todos los objetos creados a partir de esta vista. En cambio, si se usa la cláusula
Diseño
RESTRICT, de bases
se impedirá de datosde(../PID_00223656/PID_00223656.html)
la eliminación la vista si existen otros objetos que dependen de ella.
Ejemplo de destrucción de una vista
Con la sentencia siguiente destruiremos la primera de las vistas creada en el ejemplo anterior:
1. DROP VIEW View01 CASCADE;
0 0
Al ejecutar esta sentencia, además de eliminar la vista View01, también se elimina la vista View03, ya que en su
definición se utiliza View01. En caso de haber empleado la cláusula RESTRICT, no se habría eliminado ninguna de
Ver anotacionse
las dos vistas por esta dependencia.
2.1. Mecanismos de implementación de vistas

Existen dos estrategias fundamentales para implementar las vistas: reescribir la consulta o materializar la vista.
La estrategia de reescritura es la más utilizada por el SGBD. Según esta estrategia, la consulta se reescribe de
forma que referencie las tablas de la base de datos.
Ejemplo de implementación de vistas por reescritura
Considerad la base de datos definida por el diagrama relacional de la base de datos COMPANY y las vistas
siguientes:
1. CREATE VIEW DeptSummaryView(department, salary, employees) AS

2. SELECT d.deptName, AVG(e.salary), COUNT(*)
3. FROM Emp e, Dept d, Jobs j
4. WHERE e.deptId = d.deptId
5. AND e.jobId = j.jobId
6. AND j.jobName NOT IN (';President';, ';Manager';)
7. GROUP BY d.deptName;
Una consulta como la que presentamos a continuación:
1. SELECT department, salary

2. FROM DeptSummaryView
3. WHERE employees > 4;
se convertiría en lo siguiente:
/
1. SELECT d.deptName, AVG(e.salary)
2. FROM Emp e, Dept d, Jobs j
4. AND e.jobId = j.jobId
5. AND j.jobName NOT IN (';President';, ';Manager';)
6. GROUP BY d.deptName
7. HAVING COUNT(*) > 4;
0 0
La consulta que realmente se ejecuta con esta estrategia es mucho más compleja que la que se plantea inicialmente, y
esta complejidad hará que las consultas requieran mucho tiempo de ejecución. El colmo de la ineficiencia se da cuando
Ver anotacionse
hay que hacer varias consultas seguidas sobre una misma vista. También puede ser difícil la conversión en caso de
consultas muy complejas.
Reescritura o cálculo inline
La estrategia de implementación de vistas por reescritura también se conoce con el nombre de cálculo bajo mandato o, en
inglés, inline.
Con la segunda estrategia, llamada materialización de la vista, cuando se hace la consulta se crea una tabla
temporal con el contenido físico de la vista.
Ejemplo de implementación de vistas por materialización
Si se aplicara la estrategia de materialización de vistas en el ejemplo anterior, se crearía la tabla temporal

DeptSummaryView con los datos del resultado de la vista y las consultas se harían físicamente sobre esta tabla.
Según esta estrategia, el caso de mínima eficiencia que presentaba la primera estrategia se transformaría en máxima
eficiencia, dado que sólo tendría que calcular la tabla una única vez.
En contraste con esta ventaja, la estrategia de materialización de la vista presenta un inconveniente: si las tablas básicas
a partir de las cuales se construye la vista varían de contenido, habrá que modificar la tabla temporal (actualización
incremental). Esto puede resultar bastante costoso, sobre todo para algunos casos de funciones agregadas o en las que
aparezcan cláusulas GROUP BY.
Cabría esperar de un buen SGBD que utilizara la primera estrategia para las consultas sobres vistas simples y la segunda
para las complejas.
Duración de las vistas
Si durante un periodo de tiempo la vista no se consulta, el propio sistema la destruye y la vuelve a construir de nuevo, si hace
falta, en otro momento.
2.2. Actualización de vistas

/
Las vistas siempre se pueden consultar, pero no siempre se pueden actualizar. Consideremos, por ejemplo, una tabla con
Diseño
los datos de losde bases de
empleados datos
y una (../PID_00223656/PID_00223656.html)
vista con el nombre del departamento al que están asignados y el salario medio del
departamento. ¿Qué significado tendría aumentar un 10% la media de los salarios de un departamento? Este aumento se
puede conseguir de muchas formas, subiendo y bajando los salarios de los diferentes miembros del departamento. Por lo
tanto, estamos ante una ambigüedad.
De una manera más formal, sea D una base de datos y V, una vista que se construye con la función V = F(D). Si se hace
0 0
una operación de actualización A sobre la vista A(V) = A(F(D)), habrá que encontrar una operación de actualización A’ tal
que A(V) = F(A’(V)). En muchos casos habrá muchas operaciones A’ que cumplirán la igualdad anterior, aunque dejarán
la base de datos en un estado diferente.
Ver anotacionse
Solo es posible asegurar que una vista será actualizable si está construida sobre una única tabla y sus atributos
contienen una clave candidata de la tabla original. Las vistas definidas sobre más de una tabla acostumbran a no
ser actualizables. Las vistas que incluyen cláusulas GROUP BY o funciones agregadas nunca son actualizables.
Reflexión
Cualquier operación de actualización sobre una vista deberá respetar las restricciones de integridad definidas en la tabla
original.
El concepto actualizable tiene una base semántica, no sintáctica; se pueden encontrar definiciones de vistas que a priori
parecen no actualizables, pero que sí lo son.
Expresiones actualizables equivalentes
La vista que presentamos a continuación parece no actualizable:

2. SELECT empId
3. FROM Emp
4. WHERE salary > 30000
5. UNION
6. SELECT empId
7. FROM Emp
8. WHERE deptId = 20
Pero sí que lo es, dado que es equivalente a esta otra:
1. SELECT empId
2. FROM Emp
3. WHERE salary > 30000 OR deptId = 20
Y esta expresión sí que es actualizable.
Ved también
/
Podéis consultar las consideraciones referentes a las vistas actualizables en el SGBD Oracle 11g en el anexo.
2.2.1. Actualización con disparadores de sustitución
0 0
Existen varios mecanismos para romper las posibles ambigüedades en las acciones de actualización sobre vistas, y se
basan en el hecho de que se almacena el criterio de actualización adecuado junto con la definición de la vista. El estándar
SQL:2008 incorpora el uso de los disparadores de sustitución (10) .
Ver anotacionse
(10) En inglés, triggers instead of.
Los disparadores de sustitución son disparadores (11) que no se ejecutan ni antes ni después, sino “en lugar de” la
orden de modificación que se quiere realizar con la vista. Este tipo de disparadores permiten efectuar acciones de
inserción, eliminación y actualización sobre vistas que no son actualizables. Solo se pueden definir sobre vistas.
Ejemplo de actualización de vistas con disparadores de sustitución
Consideremos el ejemplo clásico de implementación de vistas. Supongamos que se define la vista siguiente:
1. CREATE VIEW DeptAvgSalaryView(department, salary) AS

2. SELECT d.deptName, AVG(e.salary)
5. GROUP BY d.deptName
Como ya hemos planteado, esta vista es claramente no actualizable. Ahora bien, si consideramos que la política
de la organización es la de realizar incrementos de sueldo proporcionales, un disparador que hiciera actualizable
esta vista podría ser el siguiente (la sintaxis utilizada es Oracle 11g):
/
1. CREATE TRIGGER updatingDeptAvgSalary
2. INSTEAD OF UPDATE ON DeptAvgSalaryView
3. DECLARE
4. v_increase NUMBER;
5. v_old_deptId Dept.deptId%TYPE;
6. BEGIN
7. IF :NEW.salary != 0 and :OLD.salary != 0 THEN
8. v_increase := :NEW.salary / :OLD.salary;
0 0
9. SELECT deptId INTO v_old_deptId
10. FROM Dept d
11. WHERE d.deptName = :OLD.department ;
12. UPDATE Emp SET salary = v_increase * salary
Ver anotacionse
13. WHERE deptId = v_old_deptId
14. AND salary IS NOT NULL;
15. END IF ;
16. IF :NEW.department != :OLD.department THEN
17. UPDATE Dept SET deptName = :NEW.department
18. WHERE Dept.deptName = :OLD.department;
19. END IF;
20. END updatingDeptAvgSalary;
En este ejemplo se puede observar que, para actualizar el salario a un valor nuevo, el salario de cada empleado
se incrementa de forma proporcional al incremento que tendría la media de salarios de todos los trabajadores. Si
la política de actualización de salarios de nuestra organización es proporcional, la actualización funcionará. Del
mismo modo, si se cambia el nombre de un departamento, se cambiará en la tabla correspondiente.
Si la política de la organización es que cuando desaparece un departamento todos sus empleados quedan a la
espera de destino, dicho de otro modo, con el campo departamento en nulo, el correspondiente disparador sería
el siguiente:
1. CREATE TRIGGER deletingDeptAvgSalary

2. INSTEAD OF UPDATE ON DeptAvgSalaryView
3. DECLARE
4. v_deptId Emp.deptId%TYPE;
5. BEGIN
6. SELECT deptId INTO v_deptId
7. FROM Dept d
8. WHERE d.deptName = :OLD.department;
9.
10. UPDATE Emp SET deptId = NULL
11. WHERE deptId = v_deptId;
12.
13. DELETE FROM Dept
14. WHERE deptName = :OLD.department;
15.
16. END deletingDeptAvgSalary;
El uso de este tipo de disparadores facilita el diseño interno y la encapsulación de la información.

/
(11) En inglés, triggers.
2.3. La vista como elemento de diseño externo

Siempre habría que diseñar las bases de datos teniendo en cuenta cómo son la organización o el sistema, nunca la
utilización que se hará de los datos, a menos que sea necesario por razones de eficiencia. Una base de datos corporativa
0 0
será utilizada por múltiples aplicativos, y a todos les gustaría que la base de datos fuera diseñada según sus necesidades
(por lo menos, a sus diseñadores). La manera de conseguir este doble objetivo recibe el nombre de diseño externo.
Ver anotacionse
El diseño externo consiste en crear una interfaz entre la base de datos y la aplicación.
En el siguiente esquema se puede ver una representación gráfica de esta situación:
Figura 2. Función del diseño externo en una base de datos corporativa
Una manera bastante sencilla y muy segura de implementar el diseño externo es usar una colección de vistas con los
respectivos disparadores de sustitución para cada una de las aplicaciones cliente; de este modo, la complejidad del
esquema conceptual de la base de datos es transparente a las diferentes aplicaciones y estas se “hacen la ilusión” de que
tienen una base de datos diseñada a medida, en la cual toda la información se encuentra tal como les conviene.
Ejemplo de implementación de diseño externo con vistas
Podemos considerar una aplicación que podría tener asignada la vista DeptSummaryView en su esquema, vista
que hemos tratado en ejemplos anteriores, sin ningún tipo de disparador de sustitución porque solo tendrá que
hacer consultas.
También podemos tener otra aplicación que “verá” la base de datos como si solo hubiera la tabla
DeptAvgSalaryView, sobre la cual puede borrar y actualizar gracias a los disparadores de sustitución
implementados, pero no podrá realizar ninguna acción de inserción.
Así, se logra la independencia lógica de los datos, puesto que en caso de que el esquema conceptual sea modificado,
la visión que tienen de él las diferentes aplicaciones no debe verse afectada.
En este apartado se utilizan la notación y las facilidades del SGBD comercial Oracle 11g.
Implementación del diseño externo

/
Para implementar el diseño externo, se podrán usar (según las posibilidades del SGBD) vistas, consultas, vistas objeto,
Diseño defunciones,
procedimientos, bases disparadores
de datos de (../PID_00223656/PID_00223656.html)
sustitución, métodos, etc.
2.4. Las tablas derivadas
0 0
La mayor parte de los SGBD incorporan unas estructuras muy parecidas a las vistas, las tablas derivadas, también
conocidas como instantáneas (12) , agregados o vistas materializadas.
Ver anotacionse
Ejemplo de tabla derivada
En un supermercado, se podría tener una tabla de ventas, con la suma de las ventas de cada sección. No haría
falta insertar ninguna fila, sino que la tabla se actualizaría automáticamente a medida que se fueran insertando
filas.
(12) En inglés, snapshots.
Las vistas materializadas son tablas que, además de almacenar la definición de la vista, también almacenan los datos
calculados (o derivados) de la ejecución de la sentencia SELECT definida en la vista. Esta tabla se puede definir con los
mismos parámetros de almacenamiento que una tabla normal, como por ejemplo el espacio de tablas o la utilización de
índices.
Cuando una sentencia SQL o PL/SQL accede a una vista materializada, el SGBD se dirige directamente a los datos de la
vista que están almacenados en lugar de utilizar los datos de las diferentes tablas empleados en la definición de la vista.
Es conveniente utilizar vistas materializadas cuando la definición de la vista hace referencia a muchas tablas enlazadas de
manera compleja, así como en las vistas que se utilizan con mucha frecuencia, puesto que permiten mejorar el
rendimiento al ejecutarse la sentencia SQL solo una vez.
Por otro lado, hay que considerar si la vista materializada se reutilizará en el futuro. Esto implicará actualizar su
contenido, puesto que probablemente el contenido de las tablas base será modificado.
A la hora de determinar si hay que definir una vista como materializada o no, es preciso valorar los costes de ejecutar la
sentencia SQL que define la vista frente a los costes de almacenamiento y actualización de la vista materializada.
Para definir una vista materializada, el patrón de sentencia SQL es el siguiente:
Comparación entre vistas y tablas derivadas
Las vistas son simples definiciones que se utilizan cuando hace falta (para materializar o reescribir una vista). En cambio, las
tablas derivadas existen físicamente.
/
1. CREATE MATERIALIZED VIEW MaterializedViewName
2. [TABLESPACE tablespace_name]
3. [PARALELL (DEGREE n)]
4. [BUILD {IMMEDIATE|DEFERRED}]
5. [REFRESH {FAST|COMPLETE|FORCE|NEVER|ON COMMIT|ON DEMAND}]
6. [{ENABLE|DISABLE} QUERY REWRITE]
7. AS SELECT ... FROM ... WHERE ...
0 0
La cláusula BUILD permite elegir si en el momento de crear la vista, aparte de la creación de la tabla que almacenará los
resultados, ésta se informa o no. En la primera opción, hay que usar INMEDIATE; en la segunda, DEFERRED, en cuyo
caso los datos no informarán la tabla hasta que se realice la primera actualización de la vista materializada.
Ver anotacionse
La cláusula REFRESH permite indicar el mecanismo que utilizará el SGBD para actualizar la vista materializada. El método
de actualización que se escoja dependerá de la frecuencia de actualización de las tablas base. Este puede ser:
COMPLETE (’completo’): se recalcula toda la tabla derivada según la consulta que la define.
FAST (’rápido’): este método de actualización especifica un método de refresco incremental; los cambios se efectuarán
agregando los nuevos datos que se han añadido a las tablas base.
FORCE (’forzado’): aplica, si es posible, el refresco rápido, y si no es posible, el refresco completo.
NEVER (’nunca’): indica que la vista materializada nunca será refrescada.
La actualización se puede producir en dos momentos diferentes:
Reflexión
Hay que tener en cuenta que las vistas que usan funciones de agregación SUM, AVG, MAX, MIN o COUNT no admiten la
actualización FAST.
1) Actualización manual. Hay que definir la vista con la restricción ON DEMAND. El refresco se produce cuando el
usuario ejecuta manualmente algún proceso de actualización sobre la vista materializada. Las actualizaciones manuales
de las vistas materializadas se realizan utilizando el paquete PL/SQL estándar DBMS_MVIEW.
El paquete PL/SQL DBMS_MVIEW
Este paquete incluye un conjunto de funciones y procedimientos que permiten gestionar las vistas materializadas. Entre ellos
podemos destacar:
DBMS_MVIEW.REFRESH(‘MaterializedViewName’): actualiza una vista materializada a partir de su nombre.
DBMS_MVIEW.REFRESH_DEPENDENT(‘Table1, Table2, ...’): actualiza todas las vistas materializadas que utilicen como
tabla base alguna de las tablas o vistas indicadas en la lista.
DBMS_MVIEW.REFRESH_ALL_MVIEWS(n): actualiza todas las vistas materializadas devolviendo un número (n) que indica
la cantidad de registros que se han actualizado.
2) Actualización automática. Esta forma de actualización puede ser:
/
ON COMMIT: el refresco se produce cuando la transacción que modifica alguna de las tablas base se confirma. Esto
Diseño
significa quede bases de
la ejecución del datos
COMMIT(../PID_00223656/PID_00223656.html)
tendrá un mayor coste temporal, lo que puede afectar al rendimiento.
Actualización programada: la actualización se programa para que suceda en un determinado instante.
Ejemplo de actualización programada
Una vista se podría programar para que se actualice todos los días a una hora determinada mediante el uso de
las cláusulas START WITH y NEXT. La cláusula START WITH permite indicar el momento de la primera
0 0
actualización automática y NEXT indica el intervalo entre actualizaciones automáticas.
1. CREATE MATERIALIZED VIEW NAME_VIEW

2. ...
Ver anotacionse
3. REFRESH START WITH ROUND(SYSDATE + 1) + 5/24
4. NEXT NEXT_DAY(TRUNC(SYSDATE), ';SUNDAY';) + 15/24
5. AS SELECT ...;
Actualización con tablas derivadas
Consideremos el siguiente modelo relacional de la base de datos operativa de un supermercado:
ProductLine(idLine, description)
Products(productCode, productLine, description, priceEach),

{productLine} REFERENCES ProductLine(idLine)
Order(orderNumber, orderDate)
OrderDetail(orderNumber, orderLineNumber, productCode,

quantityOrdered), {orderNumber} REFERENCES Order(orderNumber)
La tabla derivada correspondiente a los ingresos realizados en los pedidos por línea de producto se podría crear
con la sentencia siguiente:
1. CREATE MATERIALIZED VIEW ProductLineIncomeMView

2. BUILD IMMEDIATE
3. REFRESH FAST ON DEMAND
4. ENABLE QUERY REWRITE
5. AS SELECT pl.description as description,
6. SUM(od.quantityOrdered * p.priceEach) as amount
7. FROM ProductLine pl, Products p, Order o, OrderDetail od
8. WHERE pl.idLine = p.productLine
9. AND p.productCode = od.productCode
10. GROUP BY pl.description;
La cláusula ENABLE/DISABLE QUERY REWRITE sirve para determinar si el optimizador de consultas puede reescribir o no
las sentencias SQL, de forma que, a ser posible, se utilice la vista materializada en lugar de las tablas base empleando el
mecanismo de reescritura. Esta opción sólo está disponible cuando se utiliza el optimizador basado en costes, puesto que
el SGBD utiliza las estadísticas para determinar si la ejecución de la sentencia SQL o su reescritura utilizando la vista
materializada es la que tiene menor coste. /
Mecanismos
Diseño de debases
reescritura de consultas
de datos (../PID_00223656/PID_00223656.html)
Siguiendo con la base de datos del supermercado del ejemplo anterior, imaginemos la consulta siguiente:
1. SELECT pl.description, SUM(od.quantityOrdered * p.priceEach)

2. FROM ProductLine pl, Product sp, Order o, OrderDetail od
3. WHERE pl.idLine = p.productLine
4. AND p.productCode = od.productCode
0 0
5. AND pl.description IN (';vegetables';, ';meat';, ';drinks';)
6. GROUP BY pl.description;
7. HAVING SUM(od.quantityOrdered<i> * </i>p.priceEach) > 2500000.00;
Ver anotacionse
El sistema podría aprovechar la vista materializada y reescribiría la consulta como:
1. SELECT description, amount

2. FROM ProductLineIncomeMView
3. WHERE description IN (';vegetables';, ';meat';, ';drinks';)
4. AND amount > 2500000.00;
Reflexión
Todos estos procedimientos y funciones admiten parámetros adicionales que se pueden consultar en la documentación de
Oracle 11g.
Las vistas materializadas siempre se calculan a partir de las tablas básicas y la única operación que se puede hacer con
ellas es la consulta; no tiene sentido hablar de actualización, borrado o inserción. Dado que se acostumbran a usar para
almacenar y precalcular datos agregados, a menudo se denominan resúmenes.
Tablas derivadas en entornos distribuidos
En entornos distribuidos, las tablas derivadas se pueden utilizar para replicar datos en las diferentes sedes, lo que permitiría
sincronizar las actualizaciones sin crear ningún tipo de conflicto.
2.5. Tablas temporales

Otro tipo de tablas especiales son las tablas temporales, que deberían llamarse, de forma más exacta, tablas de
contenido temporal.
En las tablas temporales, el contenido tiene vigencia en el transcurso de una sesión (o una transacción) y
desaparece en el momento en que ésta finaliza.
La sentencia SQL para la creación de una tabla temporal es la siguiente:
/
1. CREATE {GLOBAL|LOCAL} TEMPORARY TABLE TableName
2. table_definition
3. ON COMMIT {PRESERVE ROWS|DELETE ROWS}
Estas tablas son muy útiles para almacenar resultados intermedios de una transacción.
Ejemplo de utilización de una tabla temporal
0 0
Consideremos la creación de la tabla siguiente, referente a las calificaciones obtenidas por los alumnos en una
asignatura:
Ver anotacionse
1. CREATE LOCAL TEMPORARY TABLE SubjectMarks (
2. student VARCHAR2(50),
3. mark NUMBER(3,1))
4. ON COMMIT DELETE ROWS;
Cuando se realice la primera inserción, la tabla se materializará y será posible trabajar con ella hasta el momento
en que se ejecute la cláusula COMMIT; en ese momento la tabla se destruirá y solo permanecerá su definición en
el diccionario de datos.
2.6. Ventajas e inconvenientes en la utilización de vistas

Como en cualquier decisión de diseño, la utilización de vistas presenta una serie de ventajas e inconvenientes. Las
ventajas más importantes son las siguientes:
a) Independencia de los datos y las aplicaciones: Si se usan vistas como interfaz, se puede llegar a una
independencia completa entre la estructura real de los datos y la que ven las aplicaciones. En cualquier momento se
puede cambiar el nombre de una tabla, añadir nuevas columnas o variar completamente su estructura. Todos estos
cambios resultan invisibles para las aplicaciones clientes. Solo habría que redefinir la interfaz, es decir, las vistas.
b) Simplificación del uso para el usuario: La utilización de las vistas permite tener almacenadas consultas bastante
complejas con múltiples combinaciones de tablas, condiciones y funciones agregadas, y utilizarlas por medio de una
consulta muy simple.
c) Mejora de la seguridad: El usuario no conoce las tablas y las columnas que forman realmente la base de datos, de
modo que ni siquiera puede intentar acceder a ellas; sólo tiene noticia de aquellas información y estructura que se le dan
a partir de las vistas.
d) Integridad de los datos: Con la cláusula WITH CHECK OPTION el usuario no puede llevar datos fuera de los límites
que tiene marcados; todos los cambios que haga sobre la vista (si es actualizable) tendrán que dejar la fila consistente
con las condiciones de la vista.
e) Rendimiento: Si es posible determinar el tipo de consultas que se llevarán a cabo a partir de la vista, también es
posible determinar caminos de acceso que mejoren la eficiencia de la consulta.
Pero no todo son ventajas; los inconvenientes más importantes son los siguientes:
/
a) Restricciones de actualización: No todas las vistas son actualizables (en realidad, la mayoría no lo son); no es
Diseño
posible dediseño
hacer un bases de datos
externo sólo con(../PID_00223656/PID_00223656.html)
vistas (salvo que se usen disparadores de sustitución).
b) Restricciones de estructura: Algunos SGBD, siguiendo una norma ISO, no permiten construir una vista a partir de
cualquier consulta: una vista creada a partir de una sentencia SELECT no tendrá en cuenta las columnas nuevas que se
hayan podido añadir a la tabla original.
0 0
Ver anotacionse
3. La seguridad
En un sistema de información, generalmente, las diferentes aplicaciones y usuarios de la organización utilizan un único
conjunto de datos (base de datos corporativa) con el SGBD. Por un lado, esto resuelve problemas de redundancia,
inconsistencia e independencia entre los datos y los programas, y por otro, hace que la seguridad se convierta en uno de
los problemas más importantes en estos entornos.
La palabra seguridad engloba diferentes conceptos. Los más importantes son:
a) Confidencialidad: hay que proteger el uso de la información por parte de personas no autorizadas. Esto implica que
un usuario sólo tiene que poder leer la información para la cual tiene autorización y que no podrá inferir información
secreta a partir de la información a la que tiene acceso.
b) Integridad: la información se tiene que proteger de modificaciones no autorizadas; esto incluye tanto insertar datos
falsos como destruirlos.
c) Disponibilidad: la información debe estar disponible en el momento en que le haga falta al usuario.
Las violaciones de la base de datos consisten en lecturas o modificaciones incorrectas de los datos. Por modificación
entendemos las altas y las bajas de información y las modificaciones de información existente. Las consecuencias de
estas violaciones se pueden agrupar en tres categorías:
a) Liberación incorrecta de la información. Está causada por la lectura de datos por parte de usuarios impropios
mediante un acceso intencionado o accidental. En esta categoría se incluyen las violaciones del secreto derivadas de
deducir información confidencial a partir de lecturas de información autorizada.
b) Modificación impropia de los datos. Corresponde a todas las violaciones de la integridad de los datos por
tratamientos o modificaciones fraudulentas de éstos. Las modificaciones impropias no se deben necesariamente a
lecturas no autorizadas, puesto que los datos pueden ser falsificados sin ser leídos.
c) Denegación de servicios. Corresponde a acciones que puedan impedir que los usuarios accedan a los datos o
utilicen determinados recursos.
Las amenazas a la seguridad se clasifican según la forma en que pueden producirse:
1) Amenazas no fraudulentas. Son accidentes casuales, entre los cuales se pueden distinguir los siguientes:
a) Desastres accidentales o naturales: terremotos, inundaciones o incendios, que originan accidentes que dañan el
hardware del sistema.
b) Errores en el hardware o en el software: pueden conducir a accesos no autorizados.
c) Errores humanos: causados de forma no intencionada al introducir datos o utilizar aplicaciones.

/
2) Violaciones intencionadas o violaciones fraudulentas. Son causadas por dos tipos de usuarios diferentes:
a) Usuarios autorizados que abusan de sus privilegios.
b) Agentes hostiles, usuarios impropios (internos o externos) que ejecutan acciones de vandalismo sobre el software
y/o el hardware del sistema, o lecturas o escrituras de datos; en ambos casos, los usos legales de los datos y las
aplicaciones pueden enmascarar el propósito fraudulento real.
A continuación se detallan los principales mecanismos de seguridad:
1) Identificación y autenticación. Se trata de mecanismos que identifican al usuario y se aseguran de que es quien
0 0
dice ser.
Problemas de disponibilidad
Ver anotacionse
Los problemas de disponibilidad incluyen la seguridad física (por problemas de hardware), la no saturación del sistema, la
denegación de servicio (DoS) por parte de la red, etc.
2) Control de acceso. Son mecanismos que se cercioran de que los usuarios accedan sólo a los lugares a los que están
autorizados para ejecutar las acciones para las cuales han sido autorizados.
3) Integridad y consistencia. Son mecanismos para que la base de datos permanezca siempre en un estado que
cumpla todas las reglas del negocio del modelo de datos, aunque se produzcan cambios.
4) Auditoría. Consiste en mecanismos para saber quién ha realizado qué, es decir, para llevar un registro de quién lleva
a cabo todos los cambios y consultas en la base de datos. Más que un mecanismo para proporcionar seguridad, se trata
de un mecanismo que permite monitorizar a los usuarios del sistema.
Ved también
El control de acceso al SGBD Oracle 11g se puede consultar en el subapartado 4.3. de este módulo didáctico.
3.1. Identificación y autenticación

Los sistemas de información y los datos que almacenan y procesan son recursos muy valiosos que hay que proteger. Uno
de los primeros pasos hacia la seguridad en un sistema de información es la capacidad de verificar la identidad del
usuarios. Este proceso está formado por dos partes: identificación (ver quién es) y autenticación (comprobar que es
quien dice ser).
La identificación implica la forma en que un usuario proporciona su identidad al sistema.
La identidad tiene que ser única para que el sistema pueda diferenciarla entre los distintos usuarios. Según los requisitos
operacionales, una identidad puede describir a un individuo, a más de un individuo o a uno o más individuos sólo una
parte del tiempo.
La autenticación es el proceso de asociar a un individuo con su identidad única, es decir, es el proceso que
verifica que un usuario es quien dice que es.
/
Hay tres recursos de identificación básicos para poder demostrar quién es realmente un individuo:
1) Algo que conoce una persona: una contraseña, un número de identificación personal, etc.
2) Algo que posee una persona: una tarjeta, una clave, etc.
3) Algo que caracteriza a una persona: la huella dactilar, la voz, etc.
Estos métodos básicos se pueden emplear individualmente o se pueden combinar para obtener un nivel de seguridad más
alto.
0 0
Las contraseñas son el mecanismo clásico de autenticación. Las contraseñas son palabras (o mejor dicho,
combinaciones de caracteres) que solo conoce un usuario. La seguridad de un esquema de contraseñas depende de la
capacidad de mantenerlas en secreto. Una contraseña se tiene que elegir de forma que sea fácil de recordar y difícil de
adivinar.
Ver anotacionse
Criterios para elegir contraseñas
A continuación presentamos algunos criterios que conviene tener en cuenta a la hora de elegir una contraseña:
1) Seleccionar contraseñas largas: ocho caracteres es una medida adecuada.
2) Combinar diferentes tipos de caracteres: mayúsculas, minúsculas, números, espacios en blanco y signos de
puntuación.
3) No usar palabras con significado.
4) Utilizar contraseñas diferentes para acceder a sistemas diferentes.
5) Cambiar la contraseña de manera periódica.
6) No escribir la contraseña en lugares a los que otra persona pueda acceder.
7) Que no sea la misma que el identificador de usuario.
8) Al cambiarla, que difiera de la anterior en al menos tres caracteres.
9) Cambiar la contraseña la primera vez que se inicie una sesión.
Las tarjetas dan mayor seguridad. Pueden ser simples trozos de plástico con una banda magnética o incluso pueden
incorporar chips, como hacen las tarjetas inteligentes. En ambos casos, la contraseña personal tiene que coincidir con la
que hay escrita en la tarjeta, o bien la contraseña y cierta información de la tarjeta deben coincidir con la que hay en el
ordenador.
La tendencia actual es ir hacia sistemas biométricos, que son métodos automatizados de reconocimiento de una
persona que se basan en una característica fisiológica o de comportamiento. Los sistemas biométricos se pueden utilizar
como método de identificación, en el que se identifica a una persona dentro de una población buscando la coincidencia de
una característica suya registrada en una base de datos. También se pueden utilizar en modo de verificación: el sistema
autentica la identidad reclamada de una persona con su patrón previamente grabado.
Ámbito de la identidad
Una diferencia importante entre la identificación y la autenticación es que las identidades son públicas, mientras que la
información de autenticación se mantiene en secreto. Esto proporciona el recurso gracias al cual una persona prueba que es
realmente quien dice ser.
Para la validación de un usuario de un sistema de gestión de bases de datos, se pueden usar cuatro métodos:
/
1) Autenticación por el mismo SGBD. Es la más utilizada, puesto que las cuentas son más fáciles de controlar y
Diseño
gestionar de bases
y el mismo SGBD de datos
tiene (../PID_00223656/PID_00223656.html)
recursos que permiten la administración de pequeñas comunidades de usuarios.
2) Autenticación por el sistema operativo Es una forma de validación externa. Sólo es posible en aquellos sistemas
que permitan la validación de usuarios (UNIX, Windows NT, etc.).
3) Autenticación por el servicio de red. Utiliza productos especializados de red, como Kerberos, CyberSafe, Identix,
Radius u otros.
0 0
Sistema de autenticación de la red Kerberos
En sistemas en los que se requiere un cierto nivel de seguridad, es habitual la utilización de sistemas externos (máquinas
diferentes de aquella a la que queremos conectarnos) como servicios de autenticación. El más conocido es Kerberos, el cual,
utilizando diferentes técnicas de cifrado y emisión de tiques (tickets), proporciona un buen nivel de seguridad para la mayoría
Ver anotacionse
de los sistemas.
4) Autenticación por una capa intermedia. En un sistema cliente/servidor de tres niveles, la capa intermedia podría
ser el software intermediario (13) o la aplicación misma.
En todos los casos, la comunicación puede ser cifrada.
Figura 3. El sistema de autenticación de la red Kerberos
La figura muestra el sistema de autenticación de la red Kerberos, que usa una máquina específica para llevar a cabo la autenticación y, de este modo, libera
al SGBD de esta tarea.
(13) En inglés, middleware.
3.2. Control de acceso

/
Se denomina control de acceso aquella parte del SGBD que tiene la función de asegurar que los accesos al
sistema se llevan a cabo de acuerdo con los modelos y reglas fijadas por la política de protección.
El control de acceso controla la interacción (lectura, escritura...) entre los sujetos (usuarios, aplicativos, procesos...) y los
objetos a los que acceden.
El control de acceso está formado por dos componentes:
0 0
1) Políticas de acceso: definen los principios según los cuales se autoriza a un usuario, se deniega o se filtra un acceso
específico a un objeto.
Ver anotacionse
2) Mecanismos de seguridad: procedimientos que se aplican a las consultas de los usuarios para que cumplan las
normas anteriores.
Las diferentes implementaciones de las políticas de acceso se pueden clasificar en control de acceso discrecional y control
de acceso obligatorio.
Filtrado
El filtrado devuelve sólo una parte de la información requerida.
3.2.1. Control de acceso discrecional
El control de acceso discrecional (DAC (14) ) se basa en la identidad de los usuarios o grupos de usuarios para restringir el
acceso a objetos. El control discrecional es el mecanismo de control más común que se implementa en los sistemas de
información actuales.
(14) DAC es la sigla de discretionary access controls.
Uso del DAC
El control de acceso discrecional (DAC) es el más utilizado en los SGBD comerciales.
Las políticas discrecionales se fundamentan en el conocimiento de los derechos que cada usuario tiene sobre
cada objeto. Estas políticas podrán ser definidas por el administrador de la base de datos o por el propietario del
objeto.
La mejor manera de representar la estructura del control de acceso discrecional es la matricial:
Objeto 1 Objeto 2 ... Objeto n
Usuario 1 ...
/
Objeto 1 Objeto 2 ... Objeto n
Usuario 2 ... Derechos del usuario 2 sobre el objeto n
... ... ... ...
Usuario n ...
0 0
La intersección entre filas y columnas indica los derechos de cada usuario sobre cada objeto.
Una ampliación habitual de la tabla anterior consiste en incorporar los grupos de usuarios como si se trataran de otro
Ver anotacionse
usuario y los privilegios sobre el sistema como si se trataran de un objeto. La descentralización de las autorizaciones
provoca que surjan problemas de propagación tanto de autorizaciones como de revocaciones. Si un usuario M ha recibido
una autorización de un usuario N que tenía el derecho de administrar la seguridad sobre un objeto P, ¿qué derechos
tendrá el usuario M si a N se le revocan todos los permisos? El sistema deberá prever políticas de autorización y
revocación en cadena.
Los derechos del usuario
Los derechos del usuario pueden incluir el de administrar la seguridad del objeto.
3.2.2. Control de acceso obligatorio
El control de acceso obligatorio (MAC (15) ) se suele usar en aquellas bases de datos en las que los datos tienen una
estructura de clasificación muy rígida y estática, como por ejemplo las bases de datos militares y gubernamentales.
Las políticas de control de acceso obligatorio se basan en la idea de que cada dato tiene un nivel de
clasificación (por ejemplo, muy secreto, secreto, confidencial...) y cada usuario tiene un nivel de acreditación
(con las mismas posibilidades que el nivel de clasificación). Los diferentes niveles están ordenados de forma
estricta e incremental: muy secreto > secreto > confidencial...
Las normas de funcionamiento del control de acceso obligatorio son las siguientes:
Un usuario puede ver un objeto sólo si su nivel de acreditación es mayor o igual que el nivel de clasificación del objeto.
Un usuario puede modificar un objeto sólo si su nivel de acreditación es igual al nivel de clasificación del objeto.
Los usuarios deberán tener, primero, el acceso discrecional autorizado y los privilegios adecuados sobre el objeto de la
base de datos antes de que se compruebe el sistema de acceso obligatorio. El sistema de seguridad MAC se basa en el
concepto de etiqueta.
Una etiqueta indica el nivel del usuario (acreditación) o del objeto (clasificación). Los valores bajos denotan información
no clasificada o menos sensible; los valores altos denotan información más restrictiva o accesible a menos usuarios. Las
etiquetas se pueden refinar añadiendo otros subniveles.
Ejemplo de utilización de etiquetas
/
En el ejército se podría tener una clasificación como la que se presenta en la tabla siguiente, donde a cada
Diseño
etiqueta de bases un
le corresponde devalor:
El sistema de gestión de bases de datos comprueba primero todas las autorizaciones de acceso discrecional; en
caso de que el usuario cumpla todos los niveles de seguridad, el sistema de acceso obligatorio compara las
etiquetas del usuario y del objeto para decidir quien tiene acceso.
Nombre Valor Descripción
0 0
General 80 Alto secreto, personal.
Oficial 60 Alta seguridad, no distribuir.
Ver anotacionse
Suboficial 40 Moderadamente seguro.
Militar 20 Nivel básico, no demasiado sensible.
Civil 1 Público conocimiento, libre distribución.
(15) MAC es la sigla de mandatory access controls.
Uso del MAC
El control de acceso obligatorio se suele utilizar en los SGBD que trabajan con información sensible (de alta seguridad).
3.2.3. Clasificación de los sistemas de seguridad
Los sistemas de seguridad se clasifican en cuatro niveles de seguridad, que son, de menos a más protección:
1) Protección mínima: la clase D no incorpora ningún mecanismo de seguridad.
2) Protección discrecional: la clase C soporta el control de acceso discrecional y tiene las subclases C1 y C2. La clase
C1 requiere separación entre los datos y los usuarios. La clase C2 requiere además procesos de registro, auditoría y
aislamiento de recursos.
Los sistemas de seguridad habituales
Aunque algunos productos comerciales proporcionan un nivel de seguridad B1, lo más normal es que solo lleguen al nivel C2.
3) Protección estructurada: la clase B soporta el control de acceso obligatorio y tiene las subclases B1, B2 y B3. La
clase B1 requiere protección de seguridad etiquetada: todos los objetos están etiquetados con un nivel de seguridad. La
/
clase B2 requiere además una sentencia formal para cada cosa, así como que los canales de cobertura sean identificados
DiseñoLa
y eliminados. declase
bases de datos
B3 requiere (../PID_00223656/PID_00223656.html)
también apoyos de recuperación y auditoría.
4) Protección verificada: la clase A, la más segura, requiere una demostración matemática de que los mecanismos de
seguridad son consistentes y adecuados para soportar la política de seguridad especificada.
Ejemplo de cobertura
Un ejemplo de cobertura podría ser inferir la respuesta de una consulta ilegal a partir de una consulta legal.
0 0
La clasificación de los sistemas de seguridad que se ha presentado aquí fue creada por el Pentágono como sistema
de clasificación estándar, y se encuentra definida en el Orange Book, en el que se definen los requisitos de seguridad para
Ver anotacionse
cualquier TCB (16) , y en el Lavender Book, en el que se define la interpretación de los requisitos para un sistema gestor
de bases de datos específico.
(16) TCB es la sigla de la expresión inglesa trusted computing base.
3.3. Implementación del control de acceso discrecional a SQL:2011

El estándar actual define la siguiente sintaxis de autorizaciones:
/
1. <sentencia de autorización> ::=
2. <sentencia autorización privilegios>
3. | <sentencia autorización roles>
4. <sentencia autorización privilegios> ::=
5.
6. GRANT <privilegios> TO <autorizado> [{ , <autorizado>}]
7. [WITH HIERARCHY OPTION]
8. [WITH GRANT OPTION]
0 0
9. [GRANTED BY <autorizador>]
10. <privilegios> ::=
11. <privilegios de objeto> ON <nombre objeto>
12.
Ver anotacionse
13. <privilegios de objeto> ::=
14. ALL PRIVILEGES|<acción>[{ , <acción>}]
15. <acción> ::=
16. DELETE |
17. SELECT [(<nombre columna> [ , <nombre columna>] ... ])]|
18. SELECT [(<nombre rutina> [ , <nombre rutina>] ... ])]|
19. INSERT [(<nombre columna> [ , <nombre columna>] ... ])]|
20. UPDATE [(<nombre columna> [ , <nombre columna>] ... ])]|
21. REFERENCES [(<nombre columna> [ , <nombre columna> ... ])]|
22. USAGE |
23. UNDER |
24. TRIGGER |
25. EXECUTE
26.
27. <nombre objeto> ::=
28. [ TABLE ] <nombre tabla>
29. |DOMAIN <nombre dominio>
30. | COLLATION <nombre compaginador de caracteres>
31. | CHARACTER SET <nombre juego de caracteres>
32. | TRANSLATION <nombre transcripción>
33. | TYPE <nombre tipo>
34. | SEQUENCE <nombre generador de secuencias>
35. | <designador específico de rutina>
36. | MODULE <nombre módulo>
37.
38. <autorizado> ::=
39. PUBLIC | <nombre autorizado>
40.
41. <autorizador> ::=
42. CURRENT_USER | CURRENT_ROLE
El significado de los privilegios que se pueden dar a un usuario sobre un objeto son los siguientes:
Ejemplos de autorizaciones
/
A continuación presentamos un ejemplo de cómo se implementan las autorizaciones tratadas en este
Diseño de
subapartado: bases de datos (../PID_00223656/PID_00223656.html)
1. GRANT SELECT ON TABLE Empleado TO Auditor2;

2. GRANT USAGE ON DOMAIN d_Telefono TO PUBLIC;
Privilegios Definición
0 0
INSERT [(lista-nombres-columnas)] Insertar valores en las columnas relacionadas de una fila.
Ver anotacionse
UPDATE [(lista-nombres-columnas)] Actualizar valores en las columnas relacionadas de una fila.
DELETE Borrar filas.
SELECT [(lista-nombres-columnas)] Consultar valores en las columnas relacionadas.
REFERENCES [(lista-nombres-columnas)] Referenciar a valores de las columnas relacionadas.
TRIGGER Crear un disparador sobre una tabla.
EXECUTE Ejecutar un procedimiento incorporado.
1. GRANT ALL PRIVILEGES ON VIEW Vista1 TO Joan, Pere, Maria
Es relativamente frecuente asociar los mismos privilegios a diferentes usuarios, por ejemplo a todo el departamento de
contabilidad. Para facilitar este tipo de autorización, se usa el rol.
Un rol se puede definir como un conjunto de uno o más privilegios. Si se autoriza un rol a un usuario,
automáticamente se le autorizan todos los privilegios incorporados en el rol.
La sintaxis del rol es la siguiente:
Cláusulas de autorización de privilegios
La opción WITH GRANT OPTION permite transmitir los privilegios que se tienen a otro. La cláusula WITH HIERARCHY OPTION
permite trasladar los permisos a todas las subtablas.
/
1. <definición rol> ::=
2. CREATE ROLE <nombre rol>
3. [WITH ADMIN <autorizador>]
4.
5. <sentencia autorización rol> ::=
6. GRANT <nombre rol> [ { , <nombre rol>}]
7. TO <autorizado> [ { , <autorizado>}]
8. [WITH GRANT OPTION]
0 0
La cláusula WITH ADMIN permite indicar quién administrará el rol.
Ver anotacionse
Ejemplos de creación y utilización de roles
Considerad los ejemplos siguientes de creación y utilización de roles:
1. CREATE ROLE Auditores WITH ADMIN CURRENT_USER;

2. GRANT SELECT, UPDATE (Salario, Comision) TO Auditores;
3. GRANT Auditores TO Pere, Joan, Maria;
Del mismo modo que se dan autorizaciones, se pueden revocar.
La sintaxis para revocar roles es la siguiente:
1. <sentencia revocar privilegios> ::=

2. REVOKE [{GRANT OPTION FOR|HIERARCHY OPTION FOR}]
3. <privilegios> FROM <autorizado> [{ , <autorizado>} ... ]
4. [ FROM {CURRENT_USER|CURRENT_ROLE}]
5. [ GRANTED BY <autorizador>]
6. { RESTRICT|CASCADE}
7.
8. <sentencia revocar rol> ::=
9. REVOKE [ADMIN OPTION FOR] <nombre rol> [{ , <nombre rol> } ... ]
10. FROM <autorizado> [ { , <autorizado >} ... ]
11. [FROM {CURRENT_USER|CURRENT_ROLE}]
13. {RESTRICT|CASCADE}
Problemas de propagación de privilegios
Las opciones RESTRICT y CASCADE tienen una importancia especial para resolver los problemas de propagación. Cuando A
autoriza un privilegio a B (con la opción de administrarlo) y B lo autoriza a C, si a A se le quitan los privilegios que tenía, B y
C se quedarán con los privilegios “abandonados”. Si la opción elegida cuando se revocan los privilegios a A es CASCADE, los
permisos de B y C también serán revocados. Si la opción es RESTRICT, sólo se podrá revocar los permisos de A si
previamente se han eliminado los permisos candidatos a ser abandonados. Si no se utiliza ninguna de las opciones, B y C se
quedarán con los permisos, y se correrá el riesgo de que B vuelva a dar privilegios a A.
/
3.4. Auditoría
La auditoría es el registro y monitorización de algunas acciones específicas de usuarios sobre la base de datos.
0 0
La auditoría se utiliza normalmente para los casos siguientes:
1) La investigación de una actividad sospechosa.
Ver anotacionse
Ejemplo de investigación de una actividad sospechosa
Si un usuario no autorizado intenta borrar datos de las tablas, el administrador de seguridad podrá decidir si
audita todas las conexiones de la base de datos y todos los intentos (con éxito o no) de borrar datos.
2) La monitorización y la recogida de actividades específicas de la base de datos.
Ejemplo de monitorización de la base de datos
El administrador de la base de datos puede recoger datos sobre qué tablas se actualizan o cuántos usuarios
están conectados en momentos punta.
El sistema de auditoría debe permitir diferentes formas de utilización:
La carga del trabajo de auditoría
El trabajo de auditoría puede representar una sobrecarga superior al nivel del trabajo normal.
Auditar sentencias. La auditoría (17) indicará cuándo y quién ha utilizado un tipo de sentencia concreta; por ejemplo,
auditar todas las inserciones o los borrados.
Auditar objetos. El sistema registrará cada vez que se realice alguna operación sobre un objeto determinado.
Auditar sentencias sobre objetos, una versión combinada de las dos anteriores.
Auditar a usuarios o grupos.
(17) Auditar significa averiguar quién es el autor de cualquier cambio en la base de datos.
También se puede decidir si se desea un único registro por sesión, por sentencia o por acceso; y si sólo se quiere
registrar cuándo tiene éxito, cuándo fracasa o en ambos casos. La información que suele registrar la auditoría es el
nombre del usuario, el identificador de sesión, el identificador de terminal, el nombre del objeto al que se ha accedido, la
operación ejecutada o que se ha intentado, el código completo de la operación, la fecha y la hora.
La creación de auditorías
/
Algunos SGBD incorporan sentencias SQL que permiten generar una auditoría de manera declarativa; en otros casos habrá que
Diseño
crear de bases
disparadores derellenando
que vayan datos (../PID_00223656/PID_00223656.html)
las tablas de auditoría conforme se producen acontecimientos.
3.5. La Ley de protección de datos de carácter personal

La legislación española recoge una serie de derechos y deberes relativos a la utilización de los datos personales en el
0 0
ámbito de los sistemas de información. Las principales fuentes de derechos son las siguientes: la Ley Orgánica 5/1992,
de 29 de octubre, de Regulación del Tratamiento Automatizado de los Datos de Carácter Personal (LORTAD) y el Real
Decreto 1332/1994, de 30 de junio, que desarrolla la ley anterior. Esta ley ha sido derogada por la Ley Orgánica 15/1999,
Ver anotacionse
de 13 de diciembre, de Protección de Datos de Carácter Personal (LOPDCP).
Las leyes LORTAD y LOPDCP han sido creadas en cumplimiento del mandato constitucional contenido en el
artículo 18.4, que dice: “la ley limitará el uso de la informática para garantizar el honor y la intimidad personal y
familiar de los ciudadanos y el pleno ejercicio de sus derechos”.
Las normativas actuales también siguen la Directiva del Parlamento europeo 95/46 de 24 de octubre, relativa a la
protección de las personas físicas por lo que respecta al tratamiento de datos personales y a la libre distribución de esos
datos.
3.5.1. Principios generales de protección de datos
1) Calidad de datos. Los datos de carácter personal sólo podrán ser recopilados para su tratamiento y sometidos a
dicho tratamiento cuando resulte adecuado, pertinente y no excesivo en relación con el ámbito y las finalidades para las
que se han obtenido.
2) Derecho de información en la recogida de datos. Se deberá informar con antelación a los interesados a quienes
se soliciten los datos personales de los puntos siguientes:
de la existencia de un fichero o tratamiento de datos de carácter personal;
del carácter obligatorio o facultativo de la respuesta dada a las preguntas que se planteen;
de las consecuencias de la obtención de los datos o la negativa a suministrarlos;
de la posibilidad de ejercitar los derechos de acceso, rectificación, cancelación y oposición, y
de la identidad y dirección del responsable del tratamiento o, según el caso, de su representante.
3) Consentimiento del afectado. Salvo que la ley disponga otra cosa, se requerirá el consentimiento inequívoco de los
afectados para el tratamiento de los datos de carácter personal.
4) Datos especialmente protegidos. Los datos de carácter personal que revelen ideología, afiliación sindical, religión y
creencias sólo podrán ser objeto de tratamiento con el consentimiento, expreso y por escrito, del afectado.
5) Datos relativos a la salud. Las instituciones y los centros sanitarios públicos y privados y los profesionales
correspondientes podrán proceder al tratamiento de los datos de carácter personal relativos a la salud de las personas
siempre que sean facilitados por el titular por motivo de asistencia sanitaria.
/
Finalidad del tratamiento de los datos
Los datos de carácter personal objeto del tratamiento no se podrán utilizar para finalidades incompatibles con aquellas para las
que se habrían recogido.
6) Seguridad de los datos y deber de secreto. Tanto el responsable del fichero como el encargado del tratamiento
deberán adoptar las medidas técnicas y organizativas necesarias que garanticen la seguridad de los datos de carácter
personal y eviten la alteración, la pérdida o el tratamiento o acceso no autorizados.
0 0
7) Comunicación de datos. Los datos de carácter personal objeto de tratamiento sólo se podrán comunicar a un
tercero para el cumplimiento de finalidades directamente relacionadas con las funciones legítimas del cedente y del
cesionario, si hay consentimiento por parte del interesado.
Ver anotacionse
8) Acceso a los datos por parte de terceras personas. Los tratamientos que realicen terceras personas deberán
estar regulados en un contrato por escrito o de cualquier otra forma que permita acreditar su suscripción y contenido. El
acceso de un tercero a estos datos no se considerará comunicación de datos cuando sea necesario para la prestación de
un servicio al responsable del tratamiento.
Las personas tienen los derechos siguientes:
1) Impugnación de valoraciones: los ciudadanos tienen derecho a no verse sometidos a una decisión con efectos
jurídicos que se fundamente únicamente en un tratamiento de datos destinados a evaluar aspectos de su personalidad.
Impugnación de valoraciones
El afectado podrá impugnar los actos administrativos o las decisiones privadas que impliquen una valoración de su
comportamiento.
2) Derecho de acceso: el interesado tendrá derecho a solicitar y obtener gratuitamente información de sus datos de
carácter personal sometidos a tratamiento, del origen y de las comunicaciones efectuadas o que se prevea hacer.
Derecho de acceso
El Registro General será de consulta pública y gratuita.
3) Derecho de rectificación y cancelación: cuando los datos de carácter personal resulten inexactos o incompletos,
serán rectificados o cancelados. El responsable del tratamiento tiene la obligación de hacer efectivo este derecho de
rectificación o cancelación del interesado.
4) Derecho a una indemnización: si el responsable o el encargado del tratamiento incumplen la ley, y si como
consecuencia de este incumplimiento los interesados tienen algún perjuicio o lesión en sus bienes o derechos, estos
tendrán derecho a ser indemnizados.
Tutela de los derechos
Las actuaciones contrarias a la presente Ley pueden ser objeto de reclamación por pare de los interesados ante la Agencia de
Protección de Datos.
Secreto profesional de los datos personales
Los datos de carácter personal que hagan referencia a su origen racial, a la salud y a la vida sexual se podrán tratar o ceder
sólo cuando así lo disponga la ley.
3.5.2. Los niveles de seguridad /

Según el Real Decreto 994/1999, se establecen tres niveles de seguridad: básico, medio y alto. La aplicación de uno u
Diseño de
otro dependerá de bases de datos
la naturaleza (../PID_00223656/PID_00223656.html)
de la información.
Para aplicar las medidas de seguridad correspondientes en los ficheros con datos de carácter personal, deben observar
los aspectos que se consideran a continuación.
Nivel básico
Todos los ficheros que contengan datos de carácter personal deben seguir las medidas de seguridad del nivel
0 0
básico.
Ver anotacionse
Las medidas del nivel básico de seguridad se definirán en el documento de seguridad que habrá elaborado e
implementado el responsable del fichero. Este documento tendrá que incluir los puntos siguientes:
a) El ámbito de aplicación del documento con especificación detallada de los recursos protegidos.
b) Las medidas, normas, procedimientos, reglas y estándares dirigidos a garantizar el nivel de seguridad exigido por el
Real Decreto 994/1999.
c) Las funciones y obligaciones del personal.
d) La estructura de los ficheros que contengan datos de carácter personal y descripción de los sistemas de información
que los tratan.
e) El procedimiento de notificación, gestión y respuesta de las incidencias (tanto tecnológicas como funcionales y de
acceso). El procedimiento de notificación y gestión de incidencias debe incluir un registro donde conste como mínimo el
tipo de incidencia, el momento en el que se ha producido, la persona que realiza la notificación, la persona a quien se
notifica la incidencia y los efectos derivados de ésta.
f) Los procedimientos de realización de copias de seguridad y de recuperación de datos.
g) La periodicidad con la que hay que sustituir las contraseñas de los usuarios.
h) El personal autorizado para conceder, modificar y/o alterar los accesos autorizados a los datos y/o recursos, así como
los criterios para realizar estas acciones.
i) El personal autorizado para acceder al lugar donde se almacenan datos de carácter personal.
Nivel medio
Los ficheros que contengan datos relativos a la comisión de infracciones penales y/o administrativas, la Hacienda
Pública y servicios financieros (solvencia patrimonial y crédito, cumplimiento o incumplimiento de las
obligaciones monetarias) tendrán que seguir las medidas del nivel medio.
Las medidas de seguridad de nivel medio incluyen todas las del nivel básico y, además, exigen el cumplimiento de los
puntos siguientes:
a) Identificación del responsable o responsables de seguridad.
El responsable de seguridad
Muy a menudo, el administrador de la base de datos (ABD) suele ser la persona responsable de la seguridad y de mantener
el documento de seguridad.
b) Controles periódicos (auditoría) para verificar el cumplimiento de lo establecido en el propio documento de seguridad.
Auditorías /
Los informes de auditoría deberán depositarse en la Agencia de Protección de Datos.
c) Medidas que adoptar cuando un soporte que contenga datos de carácter personal esté a punto de ser destruido o
reutilizado.
d) Personal autorizado para acceder a los locales donde se encuentren físicamente ubicados los sistemas de información
con datos de carácter personal.
Nivel alto
0 0
Ficheros con datos cuyo contenido esté relacionado con la ideología, religión, creencias, origen racial, salud o
vida sexual seguirán las medidas del nivel alto.
Ver anotacionse
Las medidas de seguridad de nivel alto incluyen todas las del nivel medio y, además, exigen el cumplimiento de los
puntos siguientes:
a) Los datos de carácter personal que se tengan que distribuir en cualquier tipo de soporte se cifrarán.
b) Para cada acceso se guardará como mínimo la identificación del usuario, la fecha y la hora en que se hizo el acceso, el
nombre del fichero al que se ha accedido y el tipo de acceso.
c) Se deberá guardar un copia de seguridad y de los procedimientos de recuperación de los datos en un lugar diferente
de donde se encuentren físicamente los sistemas de información.
d) Se cifrarán los datos de carácter personal que se transmitan por la red de telecomunicaciones.
El documento de seguridad
El documento de seguridad deberá mantenerse siempre actualizado y deberá revisarse cada vez que se produzcan cambios
relevantes en el sistema de información o en su organización. El documento se tendrá que adecuar siempre a la normativa
vigente en materia de protección de datos de carácter personal.
3.5.3. La Agencia de Protección de Datos

La Agencia es un ente de derecho público, con personalidad jurídica propia y plena capacidad pública y privada. Actúa con
total independencia de las administraciones públicas en el ejercicio de sus funciones. Su finalidad principal consiste en
velar por el cumplimiento de la legislación sobre protección de datos personales y controlar su aplicación, en especial en
todo lo relacionado con los derechos de información, acceso, oposición, rectificación y cancelación de datos.
La Agencia de Protección de Datos en Internet
En la página de Internet de la Agencia de Protección de Datos (http://www.agpd.es), podéis encontrar las últimas
actualizaciones de todas las normativas y realizar notificaciones y registros de inscripción de los ficheros de datos personales.
La página también dispone de una sección de FAQ (preguntas más frecuentes), que tiene el objetivo de que el ciudadano
pueda ejercer sus derechos y aclarar a las organizaciones cómo tienen que cumplir con sus deberes.
/
4. Diseño
Anexosde bases de datos (../PID_00223656/PID_00223656.html)
4.1. Reglas de equivalencia de operaciones de álgebra relacional

Las reglas de equivalencia se utilizan para reestructurar el árbol de operaciones de álgebra relacional que se ha obtenido
0 0
en el proceso de descomposición de la consulta.
A continuación, vamos a definir algebraicamente cada una de ellas y a mostrar algunos ejemplos aclaratorios:
1) Las operaciones conjuntivas de selección pueden transformarse en una cascada de operaciones individuales de
Ver anotacionse
selección.
σ(p)∧(q)∧(r)(R) = σp(σq(σr(R))), donde p ∈ {A1, ... , An}
Ejemplo
σ(salary>2.500)∧(hireDate>‘01-SEP-97’)(Emp) = σ(salary>2.500)(σ(hireDate>‘01-SEP-97’)(Emp))
2) Las operaciones de selección son conmutativas.
σq(σp(R)) = σp(σq(R)), donde p, q ∈ {A1, ... , An}
Ejemplo
σ(salary>2.500)(σ(hireDate>‘01-SEP-97’)(Emp)) = σ(hireDate>‘01-SEP-97’)(σ(salary>2.500)(Emp))
3) En una secuencia de operaciones de proyección, sólo es necesaria la última proyección de la secuencia.
∏L∏M∏N(R) = ∏L(R)
Ejemplo
∏empId∏empId;firstName;lastName(Emp) = ∏empId(Emp)
4) Conmutatividad de la selección y la proyección.
∏L(σp(R)) = σp(∏L(R)), donde p ∈ {A1, ... , An}
Ejemplo
∏empId;firstName;lastName(σ(salary>2.500)(Emp)) =
= σ(salary>2.500)(∏empId;firstName;lastName(Emp))
5) Conmutatividad de la combinación Theta (y del producto cartesiano).
R⋈ p S=S⋈ p R
R×S=S×R
Ejemplo
/
Emp ⋈ (Emp:deptId=Dept:deptId)Dept = Dept ⋈ (Dept:deptId=Emp:deptId)Emp
6) Conmutatividad de la selección y de la combinación Tetha (y del producto cartesiano).
σp(R ⋈ t S) = (σp(R)) ⋈ t S, donde p ∈ {A1, ... , An}

σp(R × S) = σp(R) × S
En caso de que el predicado sea de la forma (p ∧ q), donde p corresponde a los atributos de R y q a los de S, entonces
las operaciones son conmutativas de la forma siguiente:
0 0
σ(p∧q)(R ⋈ t S) = (σp(R)) ⋈ t (sq(S))
σ(p∧q)(R × S) = (σp(R)) × (σq(S))
Ver anotacionse
Ejemplo
σ(deptName=‘IT’)∧(hireDate>‘01-SEP-97’)(Emp ⋈ (Emp:deptId=Dept:deptId)Dept) =
= σ(deptName=‘IT’)(Emp ⋈ (Emp:deptId=Dept:deptId) (σ(hireDate>‘01-SEP-97’)Dept)
7) Conmutatividad de la proyección y de la combinación Tetha (y del producto cartesiano).
∏ L1∪L2(R ⋈ t S) = (∏L1(R)) ⋈ t (∏L2(S))
Ejemplo
∏firstName;lastName;deptId;deptName((Emp) ⋈ Emp:deptId=Dept:deptId (Dept)) =

= (∏firstName;lastName;deptId(Emp)) ⋈ Emp:deptId=Dept:deptId (∏deptId;deptName(Dept))
8) Conmutatividad de la unión y de la intersección, pero no de la diferencia.
R∪S=S∪R
R∩S=S∩R
9) Conmutatividad de la selección y de las operaciones de conjuntos (unión, intersección y diferencia).
σp(R ∪ S) = σp(R) ∪ σp(S)
σp(R ∩ S) = σp(R) ∩ σp(S)
σp(R – S) = σp(R) – σp(S)
10) Conmutatividad de la proyección y de la unión.
∏L(R ∪ S) = ∏L(R) ∪ ∏L(S)
11) Asociatividad de la combinación Tetha (y del producto cartesiano).
((R ⋈ S) ⋈ T) = (R ⋈ (S ⋈ T))
((R × S) × T) = (R × (S × T))
Ejemplo
((Emp ⋈ Emp:deptId=Dept:deptIdDept) ⋈ Dept:locid=Locs:locid Locs) =

= (Emp ⋈ Emp:deptId=Dept:deptId (Dept ⋈ Dept:locid=Locs:locid Locs))
/
12) Asociatividad de la unión y de la intersección (pero no de la diferencia de conjuntos).
(R ∪ S) ∪ T = S ∪ (R ∪ T)
(R ∩ S) ∩ T = S ∩ (R ∩ T)
Combinaciones Theta
Combinaciones que utilizan los operadores de comparación como condición de combinación.
0 0
Ver anotacionse
4.2. Consideraciones sobre vistas en el SGBD Oracle 11g
En este apartado del anexo nos detendremos a considerar diferentes aspectos referentes al uso de las vistas en el SGBD
Oracle 11g:
1) las vistas del diccionario de datos, y
2) las operaciones de actualización sobre vistas.
4.2.1. Vistas del diccionario de datos

El diccionario de datos está formado por un conjunto de tablas y vistas que proporcionan información sobre el contenido
de una base de datos:
estructuras de almacenamiento;
usuarios y sus derechos, y
los objetos: tablas, vistas, índices, procedimientos y funciones.
El diccionario de datos se carga en la memoria y se utiliza internamente para el tratamiento de las consultas. Existen dos
grupos de tablas/vistas en el diccionario de datos:
1) Vistas estáticas. Se basan en tablas almacenadas en el espacio de tablas (18) SYSTEM. Hay tres grandes grupos de
vistas estáticas:
a) USER_%. Contienen información sobre los objetos que pertenecen al usuario.
b) ALL_%. Contienen información sobre los objetos a los que el usuario tiene acceso.
c) DBA_%. Contienen información sobre todos los objetos de la base de datos.
Después del prefijo, el nombre describe la información a la que se accede cuando se hace una consulta sobre la vista.
2) Vistas dinámicas de rendimiento. Aunque contienen información que no se basa en tablas ni en otras vistas, sino
que se captura de la memoria o de partes del fichero de control, estas vistas permiten efectuar consultas sobre el
rendimiento de la base de datos o del SGBD. Se caracterizan porque tienen el prefijo V$ y a continuación su nombre
describe la información que representan. Estas vistas son accesibles sólo si el usuario tiene el privilegio DBA.
/
(18) En inglés, tablespace.
4.2.2. Operaciones de actualización sobre vistas
0 0
Las operaciones de actualización (INSERT, DELETE y UPDATE) son un tema conflictivo para los varios SGBD, ya que las
vistas se basan en sentencias SELECT, en las que pueden intervenir muchas o pocas tablas e incluso otras vistas, y por lo
tanto hay que decidir a cuál de estas tablas y/o vistas corresponde la operación de actualización solicitada.
Ver anotacionse
En el SGBD Oracle 11g, el concepto de tabla key-preserved es fundamental para entender las restricciones en las
actualizaciones sobre vistas basadas en operaciones de combinación (JOIN).
Una tabla es key-preserved en una operación de combinación (JOIN) si cada valor clave de la tabla también
puede ser valor clave en el resultado del JOIN.
La propiedad key-preserved de una tabla en un JOIN no depende de los datos de las tablas, sino que es una propiedad
deducida a partir de su definición.
Ejemplo
En la vista EmpDeptView, a la que nos hemos referido ya en este módulo, la tabla Emp es key-preserved en un
join con la tabla Dept, dado que la columna empId (clave primaria de la tabla Emp) continúa siendo única en el
resultado de la combinación. En cambio, la tabla Dept no es key-preserved, puesto que la columna deptId (clave
primaria de la tabla Dept) no es única en el resultado de la combinación.
El SGBD Oracle 11g proporciona la vista USER_UPDATABLE_COLUMNS, que permite conocer todas las columnas que
podemos actualizar en las tablas y vistas a las que tenemos acceso.
Reflexión
Habrá que conocer muy bien las operaciones de actualización sobre las vistas que permite cada SGBD.
Analizando el descriptor de la vista USER_UPDATABLE_COLUMNS, podemos observar que ésta nos muestra todas las
columnas de todas las tablas y vistas a las que el usuario tiene acceso junto con las operaciones que puede ejecutar.
La columna owner muestra el esquema que contiene la tabla o vista.
La columna table_name contiene los nombres de las tablas y de las vistas a las que el usuario tiene acceso.
La columna column_name nos indica las diversas columnas.
Ejemplos de columnas actualizables en una vista
Recordemos las dos vistas creadas sobre el esquema COMPANY. En caso de que queramos saber qué operaciones
podemos realizar sobre las columnas de estas dos vistas, podemos consultar la vista
USER_UPDATABLE_COLUMNS:
/
1. SELECT table_name, column_name, updatable, insertable, deletable
2. FROM user_updatable_columns
3. WHERE owner = ';COMPANY';
4. AND (table_name in (';EmpDeptView';, ';EvenDeptView';));
Fijaos en que la columna deptName de la vista EmpDeptView no es actualizable, y que tampoco se pueden
efectuar inserciones ni eliminaciones, es decir:
0 0
Si ejecutamos una sentencia DELETE sobre la vista EmpDeptView, eliminaremos filas de la tabla Emp (a la que
pertenecen las columnas que tienen YES como deletable), pero no eliminaremos ninguna fila de la tabla Dept
(a la que pertenece la columna deptName).
Ver anotacionse
Podemos ejecutar INSERT sobre la vista EmpDeptView para rellenar filas de la tabla Emp, pero no para
rellenar ninguna fila en la tabla Dept.
Podemos ejecutar UPDATE sobre la vista EmpDeptView para modificar el contenido de las columnas
provenientes de la tabla Emp, pero no podremos hacerlo para la columna deptName proveniente de la tabla
Dept.
Condiciones para que una vista sea actualizable
La vista USER_UPDATABLE_COLUMNS sólo muestra las vistas que el sistema considera que pueden ser actualizables.
Para que una vista tenga este reconocimiento, deben darse las condiciones siguientes:
a) Cada columna de la vista tiene que corresponder con una columna de una tabla simple.
b) La vista no puede contener ninguno de los componentes siguientes:
operador DISTINCT;
funciones de agrupamiento;
cláusulas GROUP BY, ORDER BY, CONNECT BY o START WITH;
subconsultas en la cláusula SELECT;
vista creada con la opción WITH READ ONLY, y
operaciones de combinación, con algunas excepciones, que están recogidas en la documentación de Oracle.
c) Además, si una vista actualizable contiene pseudocolumnas o expresiones, la operación de actualización no puede
hacer referencia a ninguna de ellas.
d) Para que una vista basada en un JOIN sea actualizable, deben cumplirse todas las condiciones siguientes:
La sentencia de actualización sólo puede afectar a una única tabla de las que forman parte de la operación de
combinación.
Para una sentencia INSERT, la vista no puede haber sido creada con WITH CHECK OPTION, y todas las columnas para
las que se insertarán valores deben de pertenecer a una tabla key-preserved.
Para una sentencia UPDATE, la vista no puede haber sido creada con WITH CHECK OPTION, y todas las columnas
modificadas deben de pertenecer a una tabla key-preserved.
Para las sentencias DELETE sobre vistas basadas en JOIN, si el JOIN está formado por más de una tabla key-
preserved, se efectúa la eliminación sobre la primera tabla indicada en la cláusula FROM.
/
Ejemplo
Diseñodede
operaciones
bases dededatos
actualización sobre vistas
(../PID_00223656/PID_00223656.html)
Supongamos que efectuamos algunas actualizaciones de departamentos pares por medio de la vista
EvenDeptView.
1. INSERT INTO EvenDeptView VALUES (50,';R & D, ';Barcelona';);
0 0
Esta instrucción provoca la inserción sin ningún problema de una fila en la tabla Dept.
Ahora bien, si la vista EvenDeptView hubiera sido creada con la opción WITH CHECK OPTION, al ejecutar
cualquiera de las sentencias siguientes:
Ver anotacionse
1. INSERT INTO EvenDeptView VALUES (55, ';Design';, ';Girona';);
2. UPDATE EvenDeptView SET deptId = deptId + 1 WHERE deptId = 50;
no se podrían realizar las acciones solicitadas, ya que no verifican la cláusula WHERE de la definición de la vista.
Descriptor de una tabla o vista
Para visualizar la descripción de una tabla o vista, se utiliza la sentencia SQL:
DESC [table_name|table_view]
4.3. Aspectos referentes a la seguridad en el SGBD Oracle 11g
4.3.1. Gestión de usuarios
a) Creación de usuarios
La sentencia SQL CREATE USER permite crear un nuevo usuario.
La sintaxis es:
Rol DBA
El rol DBA es aquel papel, rol, que permite a un usuario realizar tareas que corresponden al administrador de la base de datos.
/
1. CREATE USER user_name
2. IDENTIFIED {BY password|EXTERNALLY}
3. [DEFAULT TABLESPACE tablespace_name]
4. [TEMPORARY TABLESPACE tablespace_name]
5. [QUOTA {value [K|M]|UNLIMITED} ON tablespace_name [ , ... ]]
6. [PROFILE profile_name]
7. [PASSWORD EXPIRE]
8. [ACCOUNT {LOCK|UNLOCK}];
0 0
Espacios de tablas SYSTEM y SYSAUX
Ver anotacionse
Cuando se instala el SGBD Oracle, automáticamente se crean los espacios de tablas SYSTEM, en los que se almacenan los
objetos del sistema, y SYSAUX, que se utiliza para hacer operaciones auxiliares del sistema.
Para que un usuario pueda conectarse, hay que darle los derechos sobre lo que puede hacer, en este caso atribuyéndole
los privilegios de sistema CREATE SESSION.
Descripción de los parámetros:
IDENTIFIED: esta cláusula indica si el usuario es identificado por el sistema operativo (EXTERNALLY) o mediante
contraseña (BY contraseña). Desde la versión 11, las contraseñas son por defecto sensibles a mayúsculas/minúsculas
(parámetro SEC_CASE_SENSITIVE_LOGON = TRUE por defecto). Esto significa que podemos tener creadas cuentas de
usuario sin permiso de conexión. Aunque puede ser útil en la preparación de las cuentas de usuario sin activarlas de
inmediato y deshabilitar así el acceso a un usuario de forma temporal, hoy en día se bloquea o desbloquea
explícitamente una cuenta mediante ACCOUNT LOCK|UNLOCK.
DEFAULT NAMESPACE: esta cláusula indica en qué espacio de tablas se crearán por defecto los segmentos del usuario
en caso de que no se explicite ninguna cláusula TABLESPACE en el momento de la creación del segmento. Si esta
cláusula se omite, permanece el espacio de tablas definido por defecto en la base de datos.
TEMPORARY NAMESPACE: esta cláusula indica en qué espacio de tablas se crearán por defecto los segmentos de
temporales del usuario, por ejemplo, creados en el momento de ejecución de una operación de ordenación. Si esta
cláusula se omite, permanece el espacio de tablas temporal por defecto que haya definido en la base de datos.
QUOTA: este concepto permite limitar el espacio que un usuario puede emplear en un espacio de tablas. Esta
funcionalidad sólo afecta a los usuarios que pueden crear segmentos, y en ningún caso a los usuarios finales de una
aplicación, dado que éstos se limitan a manipular datos. Por defecto, los usuarios no tienen ninguna cuota en ningún
espacio de tablas, y en cambio los DBA tienen una cuota ilimitada en todos los espacio de tablas. En todo caso, hay
que evitar dar cuotas a los usuarios en el TABLESPACE SYSTEM y en el SYSAUX.
PROFILE: esta cláusula indica el perfil asignado al usuario.
PASSWORD EXPIRE: esta cláusula permite forzar la modificación de la contraseña en el momento de la primera
conexión. Carece de sentido si el usuario se identifica mediante el sistema operativo.
ACCOUNT: esta cláusula admite uno de los dos parámetros siguientes: LOCK, si la cuenta existe pero evitamos que el
usuario pueda conectarse a ella, o UNLOCK, donde la conexión está autorizada.
Ejemplo
/
1. CREATE USER joan IDENTIFIED BY passtemp
2. DEFAULT TABLESPACE tablespace1
3. QUOTA UNLIMITED ON tablespace1
4. PASSWORD EXPIRE;
b) Modificación de un usuario
0 0
La sentencia SQL ALTER USER permite modificar un usuario. Las cláusulas son las mismas que para la creación.
Ejemplo
Ver anotacionse
1. ALTER USER joan
2. IDENTIFIED BY otherpasstemp
3. PASSWORD EXPIRE;
4.
5. ALTER USER joan
6. DEFAULT TABLESPACE tablespace2
7. QUOTA UNLIMITED ON tablespace2;
8.
9. ALTER USER joan ACCOUNT LOCK;
El primer ejemplo cambia la contraseña de un usuario y le obliga a volver a cambiarla en la primera conexión. El
segundo ejemplo modifica el espacio de tablas y asigna una cuota sin límite. El tercer ejemplo prohíbe
temporalmente la conexión al usuario especificado.
C) Eliminación de un usuario
La sentencia SQL DROP USER permite eliminar a un usuario.
1. DROP USER user_name [CASCADE];
Si un usuario es propietario de un conjunto de objetos, la opción CASCADE es necesaria para eliminar tales objetos. En
caso contrario, nos devolverá el código de error ORA-01922.
Reflexión
No se puede eliminar a un usuario que está conectado. En este caso nos devuelve el error ORA-01940.
También hay que recordar que con DROP USER no hay posibilidad de ROLLBACK porque es una sentencia DDL.
/
4.3.2. Definición de perfiles
Un perfil es un conjunto de limitaciones de recursos identificadas por un nombre que pueden asignarse a un usuario.
a) Creación de un perfil
La sentencia SQL CREATE PROFILE permite crear un nuevo perfil.
0 0
1. CREATE PROFILE profile_name LIMIT constraint_resources
Las limitaciones pueden ser de restricciones de recursos o para la gestión de contraseñas:
Ver anotacionse
Parámetros para definir restricciones:
1) Resticciones de recursos:
SESSION_PER_USER: número de sesiones simultáneas.
CPU_PER_SESSION: asignación de CPU total por sesión.
CPU_PER_CALL: asignación de CPU total por llamada.
CONNECT_TIME: duración total de la conexión, en minutos.
IDLE_TIME: tiempo de inactividad.
LOGICAL_READS_PER_SESSION: número de lecturas lógicas por sesión.
LOGICAL_READS_PER_CALL: número de lecturas lógicas por llamada.
COMPOSITE_LIMIT: suma ponderada de CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION y

PRIVATE_SGA. La vista RESOURCE_COST permite consultar las ponderaciones empleadas y la sentencia ALTER
RESOURCE COST permite modificar las ponderaciones.
2) Restricciones sobre contraseñas:
FAILED_LOGIN_ATTEMPTS: número de intentos de conexión fallidos como paso previo al bloqueo de cuenta.
PASSWORD_LOCK_TIME: duración del bloqueo.
PASSWORD_LIFE_TIME: duración de vida de la contraseña.
PASSWORD_GRACE_TIME: periodo de gracia después de la caducidad de la contraseña.
PASSWORD_REUSE_TIME: número de cambios de contraseña antes de que una contraseña pueda reutilizarse.
PASSWORD_VERIFY_FUCTION: función de verificación de la complejidad de la contraseña. El script utlpwdmg:sql del

repositorio $ORACLE_HOME/rdms/admin contiene un ejemplo de función de verificación.
En la mayoría de las restricciones mencionadas, se pueden emplear también las palabras clave UNLIMITED y DEFAULT.
b) Modificación de un perfil
La sentencia ALTER PROFILE permite modificar un perfil. Por ejemplo:
/
1. ALTER PROFILE default LIMIT
2. SESSION_PER_USER 2
3. IDLE_TIME 15
4. FAILED_LOGIN_ATTEMPTS 3;
Los valores de los otros parámetros conservan su valor por defecto (UNLIMITED).
0 0
c) Asignación de un perfil a un usuario
Puede asignarse un perfil a un usuario en las situaciones siguientes:
1) En el momento de la creación de un usuario. Por ejemplo:
Ver anotacionse
1. CREATE USER jordi IDENTIFIED BY password
2. PROFILE default
3. PASSWORD EXPIRE;
2) Cuando se modifica un usuario. Por ejemplo:
1. ALTER USER joan PROFILE default;
d) Información referente a usuarios y sus perfiles
Para obtener información sobre usuarios y perfiles, se pueden consultar las siguientes vistas del diccionario de datos:
DBA_USERS: información sobre los usuarios.
DBA_TS_QUOTAS: información sobre cuotas de usuarios.
DBA_PROFILES: información sobre los perfiles.
4.3.3. Identificación de usuarios

Un usuario puede identificarse desde el SGBD Oracle o bien desde el propio sistema operativo.
1) Identificación por Oracle: el usuario se conecta a la base de datos utilizando un nombre y una contraseña. Por
ejemplo:
SQL > CONNECT joan/GNB9175$

Conectado.
2) Identificación por el sistema operativo: Oracle no verifica la contraseña. Debe configurarse el parámetro
OS_AUTHEN_PREFIX = OPS si se quiere tener esta funcionalidad habilitada.
/
Diseño
4.3.4. de bases
Gestión dedelos
privilegios
En una base de datos Oracle, los derechos de los usuarios se gestionan a partir del concepto de privilegio. Un privilegio
es:
el derecho a ejecutar una sentencia SQL general, por ejemplo de crear una tabla (este concepto se denomina
privilegio de sistema);
el derecho a acceder a un objeto de otro usuario (este concepto se denomina privilegio de objeto).
0 0
1) Privilegios de sistema
Un privilegio de sistema es el derecho a ejecutar una sentencia SQL en general. Cada sentencia SQL presenta como
Ver anotacionse
mínimo un privilegio de sistema asociado que tiene el mismo nombre que la sentencia SQL. Así pues, la sentencia SQL
CREATE TABLE tiene un privilegio de sistema asociado llamado CREATE TABLE, que otorga el derecho de crear una tabla
en su propio esquema. También hay que tener en cuenta que el privilegio CREATE ANY TABLE proporciona el derecho de
crear tablas en cualquier esquema de la base de datos.
Los privilegios de sistema son una fuente de riesgo, sobre todo aquellos relacionados con la gestión de usuarios y sus
derechos (CREATE USER, ALTERUSER, DROP USER, GRANT ANY PRIVILEGE, GRANT ANY ROLE) y todos aquellos que
permiten eliminar objetos (DROP ANY TABLE, DROP TABLESPACE, etc.).
Los privilegios de sistema se utilizan principalmente para controlar el uso de las sentencias DDL; por lo general, están
destinados a administradores y/o desarrolladores y a la cuenta propietaria de la aplicación, rara vez a usuarios finales.
La sentencia para otorgar privilegios es:
Algunos privilegios
CREATE SESSION: otorga a un usuario el derecho a conectarse. Si un usuario carece de este privilegio, se devuelve el error
ORA-01045.
SELECT ANY DICTIONARY: permite consultar cualquier objeto que pertenezca al diccionario de datos del esquema SYS.
1. GRANT privilege_name [ , ... ] TO (authorized|PUBLIC ) [ , ... ]

2. [WITH ADMIN OPTION];
Un privilegio puede asignarse a un usuario, a un grupo de usuarios o a todo el mundo (PUBLIC). El privilegio asignado
está activo inmediatamente. La cláusula WITH ADMIN OPTION proporciona al beneficiario el derecho a transmitir este
privilegio de sistema.
Es necesario que un usuario que quiera asignar o revocar un privilegio de sistema haya recibido previamente:
el mismo privilegio con la cláusula WITH ADMIN OPTION, y
el privilegio de sistema GRANT ANY PRIVILEGE.
Para revocar un privilegio a un usuario, se utiliza la sentencia REVOKE.
La sentencia de revocación de privilegios es la siguiente: /

1. REVOKE privilege_name [ , ... ] FROM (authorized|PUBLIC) [ ,... ]
No hay cascada en la revocación de un privilegio de sistema que haya sido transmitido gracias a la cláusula WITH ADMIN
OPTION.
0 0
Ejemplo
Si se asigna a Juan un privilegio con la opción WITH ADMIN OPTION y este lo ha transmitido a Jorge, revocar
este privilegio a Juan no tiene ningún efecto sobre el privilegio transmitido por él a Jorge.
Ver anotacionse
Si se ha asignado un privilegio a un usuario con la opción WITH ADMIN OPTION y se desea eliminar esta opción, hay que
revocar el privilegio y asignarlo de nuevo sin la opción WITH ADMIN OPTION.
También hay que tener en cuenta que la sentencia REVOKE permite revocar privilegios que un usuario haya recibido
directamente, no los que el usuario recibe vía PUBLIC. Se pueden revocar todos los privilegios de sistema mediante la
sentencia:
1. REVOKE ALL PRIVILEGES FROM autorizado;
2) Privilegios de objeto
Un privilegio sobre un objeto es el derecho a acceder a un objeto de otro usuario. Por defecto, el propietario es el único
que tiene derecho a acceder al objeto.
Los privilegios de objeto se utilizan fundamentalmente para permitir a los usuarios finales de una aplicación acceder a los
objetos de la aplicación creados en una cuenta propietaria de la aplicación. Para que otro usuario pueda acceder al
objeto, es preciso que el propietario le asigne un privilegio objeto:
Tabla 6. Ejemplos de privilegios.
Privilegio Definición Tabla Vista Secuencia Programa
SELECT [(columnas)] Derecho de lectura de datos. √ √ √ √
INSERT [(columnas)] Derecho de creación de datos. √ √
UPDATE [(columnas)] Derecho de actualización de datos*. √ √
DELETE Derecho de eliminación de datos*. √ √
EXECUTE Derecho de ejecución de un programa. √
* Para lograr los privilegios UPDATE y DELETE, hay que conceder también el privilegio SELECT.
La sentencia SQL GRANT permite asignar un privilegio objeto. /

La sentencia de otorgamiento de privilegios es la siguiente:
1. GRANT {privilege_name [(column_list)][ , ... ]|ALL [PRIVILEGES]}

2. ON [schema_name.] object_name
3. TO {authorized|PUBLIC} [ , ... ]
4. [WITH GRANT OPTION];
0 0
Para los privilegios INSERT y UPDATE, se pueden especificar las columnas para indicar a cuáles de ellas se limita el
privilegio.
Ver anotacionse
Un privilegio puede asignarse a un usuario, a un grupo de usuarios o a todo el mundo (PUBLIC).
La cláusula WITH GRANT OPTION otorga al beneficiario el derecho a transmitir este privilegio objeto.
Para asignar o revocar un privilegio objeto, es necesario:
ser el propietario del objeto;
haber recibido el mismo privilegio con la cláusula WITH ADMIN OPTION, y
haber recibido el privilegio de sistema ANY OBJECT PRIVILEGE.
Cuando queremos acceder a un objeto del que se han recibido privilegios con la opción WITH GRANT OPTION, es preciso
calificarlo con el nombre del propietario, porque el SGBD asume por defecto que este objeto se encuentra en el propio
esquema.
Para facilitar la escritura de sentencias y que el esquema propietario de los objetos sea transparente, es necesario
emplear los sinónimos. La existencia de un sinónimo, y aunque éste sea público, no da ningún derecho sobre el objeto
subyacente.
La sentencia SQL REVOKE permite revocar un privilegio objeto.
La sentencia de revocación de privilegios es la siguiente:
1. REVOKE {privilege_name [ , ... ]|ALL [PRIVILEGES]}

2. ON [schema_name.] object_name
3. FROM {authorized|PUBLIC}[ , ... ];
También hay que tener en cuenta que la sentencia REVOKE permite revocar privilegios que un usuario haya recibido
directamente, no los que el usuario recibe vía PUBLIC.
Se pueden revocar todos los privilegios de sistema mediante la sentencia:
1. REVOKE ALL PRIVILEGES ON...FROM... autorizado;
Se produce cascada en la revocación de un privilegio de objeto que haya sido transmitido gracias a la cláusula WITH
GRANT OPTION.
/
Ejemplo
Si se asigna a Juan un privilegio con la opción WITH GRANT OPTION y éste lo ha transmitido a Jorge, el hecho de
revocar después este mismo privilegio a Juan comporta también la revocación inmediata sobre Jorge.
Si se ha asignado un privilegio a un usuario con la opción WITH GRANT OPTION y se desea eliminar esta opción, es
necesario revocar el privilegio y asignarlo de nuevo sin la opción WITH GRANT OPTION.
Algunas consideraciones sobre privilegios en vistas y programas almacenados
0 0
El hecho de que un usuario disponga de un derecho sobre una vista no implica que tenga ningún derecho sobre los
objetos subyacentes a la vista. En cambio, por defecto, un programa almacenado se ejecuta con los derechos del
propietario. El comportamiento deseado se define en el momento de la creación del programa almacenado gracias a la
Ver anotacionse
cláusula AUTHID.
La sintaxis de la cláusula AUTHID es:
1. AUTHID {CURRENT_USER|DEFINER}
Consulta de información al catálogo sobre privilegios
Existen diferentes vistas en el catálogo sobre los privilegios de sistema:
DBA_SYS_PRIVS: muestra los privilegios de sistema asignados a los usuarios o a los roles.
SESION_PRIVS: muestra los privilegios de sistema actualmente activos en la sesión, ya sean obtenidos directamente o
mediante un rol.
SYSTEM_PRIVILEGE_MAP: lista todos los privilegios de sistema.
DBA_TAB_PRIVS: muestra los privilegios objeto asignados a los usuarios o a los roles sobre la totalidad del objeto.
DBA_COL_PRIVS: muestra los privilegios objeto asignados únicamente sobre ciertas columnas del objeto.
TABLE_PRIVILEGE_MAP: muestra la lista de todos los privilegios de objeto.
También podemos obtener del diccionario de datos información sobre los privilegios objeto a través de las vistas siguientes:
4.3.5. Roles
Dentro de una organización, los roles se crean para modelizar funciones de trabajo diferentes. Los permisos para realizar
determinadas operaciones están asignados a roles específicos. A los usuarios del sistema se les asigna determinados
roles, a través de los cuales adquieren los diferentes permisos que les permiten ejercer funciones específicas en el
sistema informático. Dado que los usuarios no tienen permisos de forma directa, sino que los adquieren a través de su
rol, la gestión de los derechos de cada usuario se convierte simplemente en una cuestión de asignar los roles apropiados
a la cuenta del usuario, cosa que simplifica las operaciones más comunes, como por ejemplo añadir un usuario o cambiar
las funciones de un usuario.
/
El modelo basado en roles RBAC define las siguientes tres normas primarias:
1) Asignación de roles: un sujeto puede ejercer un permiso sólo si el sujeto ha sido seleccionado o se le ha asignado
un rol.
2) Autorización de rol: es necesario que el rol activo de un sujeto esté autorizado por el sujeto. Junto con el artículo 1,
esta norma garantiza que los usuarios puedan asumir roles sólo para los cuales están autorizados.
3) Autorización de permiso: un usuario puede ejercer un permiso sólo si el permiso es autorizado por el rol activo del
usuario. Junto con las normas 1 y 2, esta norma garantiza que los usuarios ejerzan sólo los permisos para los cuales
0 0
están autorizados.
Como restricciones adicionales, los roles se pueden combinar en una jerarquía de niveles, donde los roles de nivel
superior asumen los permisos de propiedad de sus subroles.
Ver anotacionse
Figura 4. Modelo RBAC
Los privilegios se pueden asignar directamente a los usuarios o mediante roles. El uso de RBAC para administrar los
privilegios de usuario está ampliamente aceptado como una buena práctica. Oracle permite modelizar RBAC.
Un rol es un agrupamiento de privilegios definido por un nombre que puede atribuirse a un usuario, de modo
que el usuario recibe automáticamente los privilegios contenidos en el rol. Los roles se utilizan para la gestión de
los derechos.
Figura 4
En la figura podemos observar las reglas siguientes:
Un sujeto puede tener varios roles.
Un rol puede tener varios sujetos.
Un rol puede tener muchos permisos.
Un permiso puede ser asignado a múltiples roles.
Una operación puede tener asignados muchos permisos.
Un permiso puede estar asignado a muchas operaciones. /

La sentencia SQL CREATE ROLE permite crear un rol.
La sentencia de creación de un rol presenta la sintaxis siguiente:
Reflexión
Para crear un rol un usuario debe tener el privilegio de sistema CREATE ROLE.
0 0
Ver anotacionse
1. CREATE ROLE role_name
2. [IDENTIFIED {BY password|EXTERNALLY|USING package}
3. |NOT IDENTIFIED];
En el momento de creación del rol es posible precisar con qué mecanismo se podrá activar, ya sea mediante una
contraseña, con autenticación externa (por ejemplo, mediante el sistema operativo) o mediante un paquete.
Los requisitos y la sintaxis referente a la asignación y/o revocación de privilegios de sistema y de objeto por roles son los
mismos que para los usuarios. Los privilegios son inmediatamente asignados o revocados según cuál sea la instrucción, y
su efecto es inmediato sobre los usuarios conectados que tienen el rol activo.
La sentencia de asignación de roles a usuarios presenta la sintaxis siguiente:
Reflexión
Un usuario puede tener varios roles, en cuyo caso los privilegios se acumulan (no hay efecto “negativo”).
1. GRANT role_name [ , ... ]

2. TO {user_name|role_name|PUBLIC} [ , ... ]
3. [WITH ADMIN OPTION];
Los requisitos y la sintaxis referentes a la revocación de un rol a un usuario o a otro rol y sobre la eliminación de un rol
son parecidos a los explicados anteriormente.
Un rol asignado a un usuario se activa por defecto de forma automática en el momento de la conexión del usuario. Si el
usuario ya estaba conectado en el momento de la asignación, la activación inmediata del rol no es automática, sino que
hay que activar el rol con la sentencia SET ROLE.
Disponer de la posibilidad de emplear diferentes roles sin que estén activos al mismo tiempo es interesante porque:
el parámetro MAX_ENABLED_ROLES (por defecto, treinta) limita el número de roles activos simultáneos para un
usuario;
los roles protegidos con contraseña se pueden asignar a los usuarios aunque permanezcan inactivos y, sin dar la
contraseña al usuario, encargar a las aplicaciones la activación de los roles, proporcionando las contraseñas cuando
sea necesario.
/
La sentencia ALTER USER permite definir los roles por defecto de un usuario.
La sintaxis de la sentencia ALTER USER es la siguiente:
1. ALTER USER user_name

2. DEFAULT ROLE {role_name [ , ... ]|ALL {EXCEPT role_name [ , ... ]|NONE};
0 0
La sentencia SET ROLE permite activar o desactivar un rol.
La sintaxis de la sentencia SET ROLE es la siguiente:
Ver anotacionse
1. SET ROLE {role_name [IDENTIFIED BY password][ ,... ]|ALL {EXCEPT role_name[ , ... ]|NONE};
Consulta al catálogo para obtener información sobre roles
Además de las ya consideradas (DBA_SYS_PRIVS, DBA_TAB_PRIVS y DBA_COL_PRIVS), existen varias vistas del diccionario
de datos que permiten obtener información sobre los roles:
DBA_ROLES: listado de los roles existentes en la base de datos.
DBA_APLICATION_ROLES: descripción de los roles que tienen los sistema de activación por medio de un paquete.
DBA_ROLE_PRIVS: roles asignados a usuarios o a otros roles.
ROLE_SYS_PRIVS: privilegios de sistema asignados a roles.
ROLE_TAB_PRIVS: privilegios de objeto asignados a roles.
ROLE_ROLE_PRIVS: roles asignados a otros roles.
SESION_ROLES: roles actualmente activos en la sesión.
Resumen
En este módulo hemos estudiado el procesamiento de consultas y vistas, así como la seguridad en las bases de datos.
Hemos visto que es responsabilidad del SGBD transformar la consulta efectuada por un usuario en otra equivalente pero
que se pueda calcular de una forma más eficiente. Este proceso de búsqueda de una buena estrategia para realizar el
procesamiento de una consulta recibe el nombre de optimización de consultas. En primer lugar se comprueba la
correctitud semántica y léxica de la consulta SQL, y después se transforma en un árbol que permite su análisis y
optimización. Después se decide cuál es la mejor estrategia de implementación física.
/
Una vista es una relación virtual que no existe físicamente en la base de datos, sino que se genera cada vez que un
Diseño
usuario efectúade
unabases deEldatos
solicitud. (../PID_00223656/PID_00223656.html)
mecanismo de las vistas contribuye a la seguridad al permitir ocultar los detalles de la
base de datos a ciertos usuarios. Con el uso de disparadores de sustitución se puede conseguir que las vistas sean
actualizables.
Hemos expuesto el concepto de seguridad de una base de datos como el conjunto de mecanismos que protegen a la base
de datos frente a amenazas intencionadas o accidentales. La mayoría de SGBD proporcionan un mecanismo denominado
control de acceso discrecional (DAC) que gestiona los privilegios empleando el lenguaje SQL. Aunque algunos SGBD
proporcionan técnicas de control de acceso obligatorio (MAC) basadas en políticas de nivel de sistema que no pueden ser
0 0
alteradas por los usuarios, el estándar SQL no incluye soporte para MAC. Finalmente, hemos introducido brevemente la
legislación vigente de protección de datos, así como la necesidad de velar por cualquier tipo de datos de carácter
personal que deben estar protegidos por ley.
Ver anotacionse
Glosario
amenaza f
Cualquier situación o suceso intencionado o accidental que pueda afectar de manera adversa al sistema y, en
consecuencia, a la organización.
autenticación f
Mecanismo por el cual se determina si un usuario es quien dice ser.
autorización f
Concesión de un derecho o privilegio que permite a un sujeto acceder legítimamente al sistema o a un objeto del
sistema.
cifrado m
Codificación de datos mediante un algoritmo especial que provoca que estos datos no sean legibles para ningún programa
que no disponga de la clave de descifrado.
heurístico -a adj.
Cualidad de los métodos que utilizan el razonamiento y las experiencias pasadas para encontrar la mejor solución a un
problema.
mecanismo de copia de seguridad m

Proceso de realizar de forma periódica una copia de la base de datos, del archivo de registro y, posiblemente, de algún
programa, y almacenarla en un dispositivo de almacenamiento fuera de línea.
optimización f
Proceso por el cual se transforma una consulta en otra equivalente pero más eficiente. La optimización se puede realizar
en el ámbito semántico, sintáctico y físico.
plan de ejecución m
Conjunto de operaciones (lógicas o físicas) necesarias para obtener el resultado de una consulta.
registro m
Proceso de mantener un diario donde se almacenen los cambios efectuados en la base de datos con el objetivo de realizar
la recuperación de manera efectiva en caso de quiebra del sistema.
/
vista f
Diseño
Resultado de bases
dinámico de una de datos
o más (../PID_00223656/PID_00223656.html)
operaciones relacionales sobre una base de datos con el objetivo de producir otra
relación.
Bibliografía
0 0
Connolly, T.; Begg, C. (2005). Sistemas de bases de datos (4.ª ed.). Madrid: Pearson.
Huey, P. (2011). Oracle database security guide 11g release 1. Oracle.
Ver anotacionse
Lorentz, D.; Roeser, M. B. (2011). Oracle database SQL language reference, 11g Release 2. Oracle.
Silberschatz, A.; Korth, H.; Sudarshan, S. (2006). Fundamentos de bases de datos (5.ª ed.). Madrid: McGraw-Hill.
Edición de 2011 en eBook.
Weinberg, P.; Groff, J.; Oppel, A. (2009). SQL. The complete reference (3.ª ed.). McGraw-Hill.

Diseño de Bases de Datos

Cargado por

Información del documentohacer clic para expandir la información del documentoDiseño de bases de datos

Información del documentohacer clic para expandir la información del documento

Copyright:

Formatos disponibles

Diseño de Bases de Datos

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Diseño de Bases de Datos

Cargado por

Copyright:

Formatos disponibles

Diseño de bases de datos (../PID_00223656/PID_00223656.

Procesamiento de consultas y vistas

1.1.3. Análisis semántico

1.3. Optimización sintáctica

1.4.1. Estadísticas de la base de datos

1.4.4. Operación de proyección

2.5. Tablas temporales

3.2.3. Clasificación de los sistemas de seguridad

4.1. Reglas de equivalencia de operaciones de álgebra relacional

4.3.3. Identificación de usuarios

3. Interpretar y optimizar el plan de ejecución de una consulta de forma adecuada.

6. Conocer el alcance de los mecanismos de seguridad de una base de datos.

Figura 1. Etapas del procesamiento de consultas.

La descomposición de consultas implica la traducción de consultas expresadas en lenguaje SQL a una

Relaciones R, S La lista de atributos de cada relación se define según el

Predicados p, q Estos predicados se evaluarán lógicamente en las operaciones

Selección σp(R) Operación que devuelve las tuplas de la relación R que

Proyección ∏L(R) Donde L corresponde a una lista de atributos que se mostrarán

Supongamos el esquema de la base de datos COMPANY definido a continuación:

{jobId} REFERENCES Jobs(jobId),

{manager} REFERENCES Emp(empId),

{deptId} REFERENCES Dept(deptId)

Dept(deptId, deptName, managerId, locId),

{locId} REFERENCES Locs(locId),

{managerId} REFERENCES Emp(empId)

b) Se combinan las tablas y después se realizan las operaciones de selección:

σ(deptName=’IT’)∧ (hireDate>′01-SEP-07’)(Emp ⋈ (Emp:deptId=Dept:deptId) Dept)

1.1. Descomposición de consultas

Las etapas típicas de la descomposición de consultas son:

1) validación léxica y sintáctica,

1.1.1. Validación léxica y sintáctica

Ejemplo de validación léxica y sintáctica

Supongamos el esquema de la base de datos COMPANY y la siguiente consulta:

Esta consulta sería rechazada por dos motivos:

1) Por cada relación base de la consulta se crea un nodo hoja.

3) El resultado de la consulta se representa como la raíz del árbol.

4) La secuencia de operaciones se dirige de los nodos hoja al nodo raíz.

Ejemplo de transformación de una consulta en un árbol de operaciones relacionales

Consideremos la siguiente consulta:

1. SELECT e.firstName, e.lastName, e.hireDate, d.deptName

Esta consulta genera el plan de evaluación siguiente:

∏firstName;lastName((σ(hireDate>′01-SEP-07’)(Emp)) ⋈ (Emp:deptId=Dept:deptId) (σ(deptName=′IT’)(Dept)))

Los derechos de acceso se estudian en el apartado 3 de este módulo didáctico.

1.1.2. Normalización de la consulta

(jobId =′STClerk′ ∨ salary > 2000) ∧ manager = 121

(jobId =′STClerk′ ∧ salary > 2000) ∨ (jobId =′STClerk′ ∧ manager = 121)

1.1.3. Análisis semántico

Ejemplo de consulta incorrecta

1. SELECT e.firstName, e.lastName

Ejemplo de consulta contradictoria

1. SELECT firstName, lastName, salary

Consideremos la siguiente consulta:

1. SELECT firstName, lastName, salary

Esta consulta se puede simplificar, ya que la segunda condición incluye la primera:

Ejemplo de simplificación de predicados redundantes

1. SELECT firstName, lastName, salary, managerId, jobId

Se puede transformar en otra consulta con una condición WHERE equivalente: