• Author / Uploaded
• Amanda Trujillo

• Categories
• Tabla (Base de datos)
• Bases de datos
• Software de gestión de datos
• Gestión de datos
• Ciencias de la Información

Citation preview

Tema 3.- Acceso a la información Índice de contenido Vistas (create view)..............................................................................................................................2 Vistas (información).............................................................................................................................8 Vistas eliminar (drop view)..................................................................................................................9 Vistas (modificar datos a través de ella).............................................................................................11 Vistas (with read only)........................................................................................................................16 Vistas modificar (create or replace view)...........................................................................................19 Vistas (with check option)..................................................................................................................22 Vistas (otras consideraciones)............................................................................................................25 Vistas materializadas (materialized view)..........................................................................................28 Seguridad y acceso a Oracle...............................................................................................................31 Usuarios (crear)..................................................................................................................................32 Permiso de conexión...........................................................................................................................35 Privilegios del sistema (conceder)......................................................................................................40 Privilegios del sistema (with admin option).......................................................................................45 Privilegios sobre los objetos...............................................................................................................46 Retirada de privilegios...................................................................................................................47 Conceder privilegios a los roles.....................................................................................................48 Límites en privilegios sobre roles..................................................................................................48 Perfiles................................................................................................................................................50

Vistas (create view) Una vista es un objeto. Una vista es una alternativa para mostrar datos de varias tablas; es como una tabla virtual que almacena una consulta. Los datos accesibles a través de la vista no están almacenados en la base de datos, en la base de datos se guarda la definición de la vista y no el resultado de ella. Entonces, una vista almacena una consulta como un objeto para utilizarse posteriormente. Las tablas consultadas en una vista se llaman tablas base. En general, se puede dar un nombre a cualquier consulta y almacenarla como una vista. Una vista suele llamarse también tabla virtual porque los resultados que retorna y la manera de referenciarlas es la misma que para una tabla. Las vistas permiten: - simplificar la administración de los permisos de usuario: se pueden dar al usuario permisos para que solamente pueda acceder a los datos a través de vistas, en lugar de concederle permisos para acceder a ciertos campos, así se protegen las tablas base de cambios en su estructura. - mejorar el rendimiento: se puede evitar escribir instrucciones repetidamente almacenando en una vista el resultado de una consulta compleja que incluya información de varias tablas. Podemos crear vistas con: un subconjunto de registros y campos de una tabla; una unión de varias tablas; una combinación de varias tablas; un subconjunto de otra vista, combinación de vistas y tablas. Una vista se define usando un "select". La sintaxis básica para crear una vista es la siguiente: create view NOMBREVISTA as SUBCONSULTA;

El contenido de una vista se muestra con un "select": select *from NOMBREVISTA;

En el siguiente ejemplo creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 4 campos: create view vista_empleados as select (apellido||' '||e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados e join secciones s on codigo=seccion;

Para ver la información contenida en la vista creada anteriormente escribimos: select *from vista_empleados;

Podemos realizar consultas a una vista como si se tratara de una tabla: select seccion,count(*) as cantidad from vista_empleados;

Los nombres para vistas deben seguir las mismas reglas que cualquier identificador. Para distinguir

una tabla de una vista podemos fijar una convención para darle nombres, por ejemplo, colocar el sufijo “vista” y luego el nombre de las tablas consultadas en ellas. Los campos y expresiones de la consulta que define una vista DEBEN tener un nombre. Se debe colocar nombre de campo cuando es un campo calculado o si hay 2 campos con el mismo nombre. Note que en el ejemplo, al concatenar los campos "apellido" y "nombre" colocamos un alias; si no lo hubiésemos hecho aparecería un mensaje de error porque dicha expresión DEBE tener un encabezado, Oracle no lo coloca por defecto. Los nombres de los campos y expresiones de la consulta que define una vista DEBEN ser únicos (no puede haber dos campos o encabezados con igual nombre). Note que en la vista definida en el ejemplo, al campo "s.nombre" le colocamos un alias porque ya había un encabezado (el alias de la concatenación) llamado "nombre" y no pueden repetirse, si sucediera, aparecería un mensaje de error. Otra sintaxis es la siguiente: create view NOMBREVISTA (NOMBRESDEENCABEZADOS) as SUBCONSULTA;

Creamos otra vista de "empleados" denominada "vista_empleados_ingreso" que almacena la cantidad de empleados por año: create view vista_empleados_ingreso (fecha,cantidad) as select extract(year from fechaingreso),count(*) from empleados group by extract(year from fechaingreso);

La diferencia es que se colocan entre paréntesis los encabezados de las columnas que aparecerán en la vista. Si no los colocamos y empleamos la sintaxis vista anteriormente, se emplean los nombres de los campos o alias (que en este caso habría que agregar) colocados en el "select" que define la vista. Los nombres que se colocan entre paréntesis deben ser tantos como los campos o expresiones que se definen en la vista. Las vistas se crean en la base de datos activa. Al crear una vista, Oracle verifica que existan las tablas a las que se hacen referencia en ella; no se puede crear una vista que referencie tablas inexistentes. No se puede crear una vista si existe un objeto con ese nombre. Se aconseja probar la sentencia "select" con la cual definiremos la vista antes de crearla para asegurarnos que el resultado que retorna es el imaginado. Una vista siempre está actualizada; si modificamos las tablas base (a las cuales referencia la vista), la vista mostrará los cambios. Se pueden construir vistas sobre otras vistas. Problema: Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas: drop table empleados; drop table secciones;

Creamos las tablas: create table secciones(

codigo number(2), nombre varchar2(20), sueldo number(5,2) constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo number(5), documento char(8), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar2(20), nombre varchar2(20), domicilio varchar2(30), seccion number(2) not null, cantidadhijos number(2) constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso date, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo), constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values(1,'Administracion',300); insert into secciones values(2,'Contaduría',400); insert into secciones values(3,'Sistemas',500); insert into empleados values(100,'22222222','f','Lopez','Ana','Colon 123',1,2,'casado','10/10/1990'); insert into empleados values(102,'23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','02/10/1990'); insert into empleados values(103,'24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','12/07/1998'); insert into empleados values(104,'25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','10/09/1998'); insert into empleados values(105,'26666666','f','Perez','Laura','Peru 1254',3,3,'casado','05/09/2000');

Eliminamos la vista "vista_empleados". Aún no hemos aprendido a eliminar vistas, lo veremos próximamente: drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido||' '||e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados e join secciones s on codigo=seccion;

Vemos la información de la vista: select *from vista_empleados;

Realizamos una consulta a la vista como si se tratara de una tabla: select seccion,count(*) as cantidad from vista_empleados group by seccion;

Eliminamos la vista "vista_empleados_ingreso": drop view vista_empleados_ingreso;

Creamos otra vista de "empleados" denominada "vista_empleados_ingreso" que almacena la cantidad de empleados por año: create view vista_empleados_ingreso (fecha,cantidad) as select extract(year from fechaingreso),count(*) from empleados group by extract(year from fechaingreso);

Vemos la información: select *from vista_empleados_ingreso;

Hemos aprendido que los registros resultantes de una vista no se almacena en la base de datos, sino la definición de la vista, por lo tanto, al modificar las tablas referenciadas por la vista, el resultado de la vista cambia. Modificamos una fecha en la tabla "empleados" y luego consultamos la vista para verificar que está actualizada: update empleados set fechaingreso='10/09/2000' where fechaingreso='10/09/1998'; select *from vista_empleados_ingreso;

Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas. El director no quiere que los empleados de administración conozcan la estructura de las tablas ni algunos datos de los profesores y socios, por ello se crean vistas a las cuales tendrán acceso. 1- Elimine las tablas y créelas nuevamente: drop drop drop drop

table table table table

inscritos; cursos; socios; profesores;

create table socios( documento char(8) not null, nombre varchar2(40), domicilio varchar2(30), primary key (documento) ); create table profesores( documento char(8) not null, nombre varchar2(40), domicilio varchar2(30), primary key (documento) );

create table cursos( numero number(2), deporte varchar2(20), dia varchar2(15), documentoprofesor char(8), constraint CK_inscritos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), constraint FK_documentoprofesor foreign key (documentoprofesor) references profesores(documento), primary key (numero) ); create table inscritos( documentosocio char(8) not null, numero number(2) not null, matricula char(1), constraint CK_inscritos_matricula check (matricula in('s','n')), constraint FK_documentosocio foreign key (documentosocio) references socios(documento), constraint FK_numerocurso foreign key (numero) references cursos(numero), primary key (documentosocio,numero) );

2- Ingrese algunos registros para todas las tablas: insert insert insert insert

into into into into

socios socios socios socios

values('30000000','Fabian Fuentes','Caseros 987'); values('31111111','Gaston Garcia','Guemes 65'); values('32222222','Hector Huerta','Sucre 534'); values('33333333','Ines Irala','Bulnes 345');

insert insert insert insert

into into into into

profesores profesores profesores profesores

insert insert insert insert insert insert insert insert

into into into into into into into into

cursos cursos cursos cursos cursos cursos cursos cursos

insert insert insert insert insert insert

into into into into into into

inscritos inscritos inscritos inscritos inscritos inscritos

values('22222222','Ana Acosta','Avellaneda 231'); values('23333333','Carlos Caseres','Colon 245'); values('24444444','Daniel Duarte','Sarmiento 987'); values('25555555','Esteban Lopez','Sucre 1204');

values(1,'tenis','lunes','22222222'); values(2,'tenis','martes','22222222'); values(3,'natacion','miercoles','22222222'); values(4,'natacion','jueves','23333333'); values(5,'natacion','viernes','23333333'); values(6,'futbol','sabado','24444444'); values(7,'futbol','lunes','24444444'); values(8,'basquet','martes','24444444'); values('30000000',1,'s'); values('30000000',3,'n'); values('30000000',6,null); values('31111111',1,'s'); values('31111111',4,'s'); values('32222222',8,'s');

3- Elimine la vista "vista_club": drop view vista_club;

4- Cree una vista en la que aparezca el nombre del socio, el deporte, el día, el nombre del profesor y el estado de la matrícula (deben incluirse los socios que no están inscritos en ningún deporte, los cursos para los cuales no hay inscritos y los profesores que no tienen designado deporte también)

5- Muestre la información contenida en la vista (11 registros) 6- Realice una consulta a la vista donde muestre la cantidad de socios inscritos en cada deporte (agrupe por deporte y día) ordenados por cantidad 7- Muestre (consultando la vista) los cursos (deporte y día) para los cuales no hay inscritos (3 registros) 8- Muestre los nombres de los socios que no se han inscrito en ningún curso (consultando la vista) (1 registro) 9- Muestre (consultando la vista) los profesores que no tienen asignado ningún deporte aún (1 registro) 10- Muestre (consultando la vista) el nombre de los socios que deben matrículas (1 registro) 11- Consulte la vista y muestre los nombres de los profesores y los días en que asisten al club para dictar sus clases (9 registros) 12- Muestre la misma información anterior pero ordenada por día 13- Muestre todos los socios que son compañeros en tenis los lunes (2 registros) 14- Intente crear una vista denominada "vista_inscritos" que muestre la cantidad de inscritos por curso, incluyendo el número del curso, el nombre del deporte y el día 15- Elimine la vista "vista_inscritos" y créela para que muestre la cantidad de inscritos por curso, incluyendo el número del curso, el nombre del deporte y el día 16- Consulte la vista (9 registros)

Vistas (información) Las vistas son objetos, así que para obtener información de ellos pueden consultarse los siguientes catálogos. "user_catalog" nos muestra todos los objetos del usuario actual, incluidas las vistas. En la columna "table_type" aparece "view" si es una vista. Ejemplo: select *from user_catalog where table_type='VIEW';

"user_objects" nos muestra información sobre todos los objetos del usuario actual. En la columna "OBJECT_TYPE" muestra "view" si es una vista, aparece la fecha de creación y demás información que no analizaremos por el momento. Para ver todos los objetos del usuario actual que son vistas tipeamos: select *from user_catalog where object_type='VIEW';

"user_views" nos muestra información referente a todas las vistas del usuario actual, el nombre de la vista, la longitud del texto, el texto que la define, etc. Con la siguiente sentencia obtenemos información sobre todas las vistas cuyo nombre comience con la cadena "VISTA": select *from user_views where view_name like 'VISTA%';

Vistas eliminar (drop view) Para quitar una vista se emplea "drop view": drop view NOMBREVISTA;

Eliminamos la vista denominada "vista_empleados": drop view vista_empleados;

Si se elimina una tabla a la que hace referencia una vista, la vista no se elimina, hay que eliminarla explícitamente. Problema: Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas: drop table empleados; drop table secciones;

Creamos las tablas: create table secciones( codigo number(2), nombre varchar2(20), sueldo number(5,2), constraint CK_secciones_sueldo check (sueldo>=0), constraint PK_secciones primary key (codigo) ); create table empleados( legajo number(3), documento char(8), sexo char(1), constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar2(20), nombre varchar2(20), domicilio varchar2(30), seccion number(2) not null, cantidadhijos number(2), constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10), constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso date, constraint PK_empleados primary key (legajo), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo), constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values(1,'Administracion',300); insert into secciones values(2,'Contaduría',400);

insert into secciones values(3,'Sistemas',500); insert into empleados values(100,'22222222','f','Lopez','Ana','Colon 123',1,2,'casado','10/10/1990'); insert into empleados values(101,'23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','02/10/1990'); insert into empleados values(102,'24444444','m','Garcia','Marcos','Sarmiento 1234',2,3,'divorciado','07/12/1998'); insert into empleados values(103,'25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','10/09/1998'); insert into empleados values(104,'26666666','f','Perez','Laura','Peru 1254',3,3,'casado','05/09/2000');

Eliminamos la vista "vista_empleados": drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido||' '||e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados e join secciones s on codigo=seccion;

Veamos la información de la vista: select *from vista_empleados;

Eliminamos la tabla "empleados": drop table empleados;

Verificamos que la vista aún existe consultando "user_objects": select *from user_objects where object_name='VISTA_EMPLEADOS';

Verificamos que la vista "vista_empleados" aún existe consultando "user_catalog": select *from user_catalog where table_type='VIEW';

Si consultamos la vista, aparecerá un mensaje de error, pues la tabla "empleados" a la cual hace referencia la vista, no existe: select *from vista_empleados;

Eliminamos la vista: drop view vista_empleados;

Verificamos que la vista ya no existe: select *from user_catalog where table_name='VISTA_EMPLEADOS';

Vistas (modificar datos a través de ella) Si se modifican los datos de una vista, se modifica la tabla base. Se puede insertar, actualizar o eliminar datos de una tabla a través de una vista, teniendo en cuenta lo siguiente, las modificaciones que se realizan a las vistas: - no pueden afectar a más de una tabla consultada. Pueden modificarse y eliminarse datos de una vista que combina varias tablas pero la modificación o eliminación solamente debe afectar a una sola tabla. - no se pueden cambiar los campos resultado de un cálculo. - pueden generar errores si afectan a campos a las que la vista no hace referencia. Por ejemplo, si se ingresa un registro en una vista que consulta una tabla que tiene campos not null que no están incluidos en la vista. Problema: Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas: drop table empleados; drop table secciones;

Creamos las tablas: create table secciones( codigo number(2), nombre varchar2(20), constraint PK_secciones primary key (codigo) ); create table empleados( legajo number(4) not null, documento char(8), sexo char(1), constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar2(20), nombre varchar2(20), domicilio varchar2(30), seccion number(2) not null, cantidadhijos number(2), constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10), constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso date, constraint PK_empleados primary key (legajo), sueldo number(6,2), constraint CK_empleados_sueldo check (sueldo>=0), constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo), constraint UQ_empleados_documento unique(documento) );

Ingresamos algunos registros: insert into secciones values(1,'Administracion'); insert into secciones values(2,'Contaduría'); insert into secciones values(3,'Sistemas'); insert into empleados values(100,'22222222','f','Lopez','Ana','Colon 123',1,2,'casado','10/10/1990',600); insert into empleados values(101,'23333333','m','Lopez','Luis','Sucre 235',1,0,'soltero','02/10/1990',650); insert into empleados values(103,'24444444', 'm', 'Garcia', 'Marcos', 'Sarmiento 1234', 2, 3, 'divorciado', '07/12/1998',800); insert into empleados values(104,'25555555','m','Gomez','Pablo','Bulnes 321',3,2,'casado','10/09/1998',900); insert into empleados values(105,'26666666','f','Perez','Laura','Peru 1254',3,3,'casado','05/09/2000',700);

Eliminamos la vista "vista_empleados": drop view vista_empleados;

Creamos la vista "vista_empleados", que es resultado de una combinación en la cual se muestran 5 campos: create view vista_empleados as select (apellido||' '||e.nombre) as nombre,sexo, s.nombre as seccion, cantidadhijos from empleados e join secciones s on codigo=seccion;

Vemos la información contenida en la vista: select *from vista_empleados;

Eliminamos la vista "vista_empleados2": drop view vista_empleados2;

Creamos otra vista de "empleados" denominada "vista_empleados2" que consulta solamente la tabla "empleados": create view vista_empleados2 as select legajo,nombre,apellido,fechaingreso,seccion,sueldo from empleados where sueldo>=600;

Consultamos la vista: select *from vista_empleados2;

No podemos ingresar un registro en la vista "vista_empleados" porque tal vista tiene campos calculados ("nombre", que es una concatenación de "apellido" y "nombre"), además afecta a 2 tablas ("empleados" y "secciones") y hay campos no accesibles desde la vista que no admiten valores nulos. Si ejecutamos el siguiente "insert", Oracle mostrará un mensaje de error: insert into vista_empleados values('Pedro Perez','m','Sistemas',2);

Podemos ingresar un registro en la vista "vista_empleados2" porque tal vista afecta a una sola tabla y los campos de ""empleados" no accesibles desde la vista admiten valores nulos: insert into vista_empleados2 values(200,'Pedro','Perez','10/10/2000',2,800);

Vemos la tabla "empleados" para comprobar que el nuevo registro insertado desde la vista está presente en "empleados", los campos para los cuales no se ingresaron datos, almacenan el valor "null": select *from empleados;

Actualizamos el campo "nombre" de un registro de la vista "vista_empleados2": update vista_empleados2 set nombre='Beatriz' where nombre='Ana';

Verificamos que se actualizó la tabla: select *from empleados;

Si intentamos actualizar el campo "nombre" de un empleado a través de la vista "vista_empleados", Oracle no lo permite porque es una columna calculada (concatenación de dos campos): update vista_empleados set nombre='Lopez Carmen' where nombre='Lopez Beatriz';

Si podemos actualizar otros campos, por ejemplo, el campo "cantidadhijos" de un empleado a través de la vista "vista_empleados": update vista_empleados set cantidadhijos=3 where nombre='Lopez Beatriz';

Verificamos que se actualizó la tabla: select *from empleados;

Eliminamos un registro de "empleados" a través de la vista "vista_empleados2": delete from vista_empleados2 where apellido='Lopez' and nombre='Beatriz';

Verificamos que se eliminó tal registro de la tabla "empleados": select *from empleados;

Podemos eliminar registros de empleados a través de la vista "vista_empleados": delete from vista_empleados where seccion='Administracion';

Verificamos que no hay registros en "empleados" de la sección "1" ("Administracion"): select *from empleados;

Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas. 1- Elimine las tabla "inscritos", "socios" y "cursos": drop table inscritos; drop table socios; drop table cursos;

2- Cree las tablas: create table socios( documento char(8) not null, nombre varchar2(40), domicilio varchar2(30), constraint PK_socios_documento primary key (documento) );

create table cursos( numero number(2), deporte varchar2(20), dia varchar2(15), constraint CK_inscritos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), profesor varchar2(20), constraint PK_cursos_numero primary key (numero) ); create table inscritos( documentosocio char(8) not null, numero number(2) not null, matricula char(1), constraint PK_inscritos_documento_numero primary key (documentosocio,numero), constraint FK_inscritos_documento foreign key (documentosocio) references socios(documento), constraint FK_inscritos_numero foreign key (numero) references cursos(numero) );

3- Ingrese algunos registros para todas las tablas: insert insert insert insert

into into into into

socios socios socios socios

values('30000000','Fabian Fuentes','Caseros 987'); values('31111111','Gaston Garcia','Guemes 65'); values('32222222','Hector Huerta','Sucre 534'); values('33333333','Ines Irala','Bulnes 345');

insert insert insert insert insert insert insert

into into into into into into into

cursos cursos cursos cursos cursos cursos cursos

values(1,'tenis','lunes','Ana Acosta'); values(2,'tenis','martes','Ana Acosta'); values(3,'natacion','miercoles','Ana Acosta'); values(4,'natacion','jueves','Carlos Caseres'); values(5,'futbol','sabado','Pedro Perez'); values(6,'futbol','lunes','Pedro Perez'); values(7,'basquet','viernes','Pedro Perez');

insert insert insert insert insert insert insert

into into into into into into into

inscritos inscritos inscritos inscritos inscritos inscritos inscritos

values('30000000',1,'s'); values('30000000',3,'n'); values('30000000',6,null); values('31111111',1,'s'); values('31111111',4,'s'); values('32222222',1,'s'); values('32222222',7,'s');

4- Realice un join para mostrar todos los datos de todas las tablas, sin repetirlos (7 registros) 5- Elimine la vista "vista_cursos" 6- Cree la vista "vista_cursos" que muestre el número, deporte y día de todos los cursos. 7- Consulte la vista ordenada por deporte (7 registros) 8- Ingrese un registro mediante la vista "vista_cursos" y vea si afectó a "cursos" 9- Actualice un registro sobre la vista y vea si afectó a la tabla "cursos" 10- Elimine un registro de la vista para el cual no haya inscritos y vea si afectó a "cursos" 11- Intente eliminar un registro de la vista para el cual haya inscritos 12- Elimine la vista "vista_inscritos" y créela para que muestre el documento y nombre del socio, el

numero de curso, el deporte y día de los cursos en los cuales está inscrito 13- Intente ingresar un registro en la vista: insert into vista_inscritos values('32222222','Hector Huerta',6,'futbol','lunes');

No lo permite porque la modificación afecta a más de una tabla base. 14- Intente actualizar el documento de un socio (no lo permite) 15- Elimine un registro mediante la vista 16- Verifique que el registro se ha eliminado de "inscritos"

Vistas (with read only) Con la cláusula "with read only" (sólo lectura) evitamos que se puedan realizar inserciones, actualizaciones y eliminaciones mediante una vista. Sintaxis: create view NOMBREVISTA as SUBCONSULTA with read only;

Evitamos que Oracle acepte "insert", "update" o "delete" sobre la vista si colocamos "with read only" luego de la subconsulta que define una vista. Por ejemplo, creamos la siguiente vista: create view vista_empleados as select apellido, nombre, sexo, seccion from empleados with read only;

Oracle responde con un mensaje de error ante cualquier "insert", "update" o "delete" realizado sobre la vista. Problema: Una empresa almacena la información de sus empleados en una tabla llamada "empleados". Eliminamos la tabla: drop table empleados;

Creamos las tablas: create table empleados( documento char(8), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar2(20), nombre varchar2(20), domicilio varchar2(30), seccion varchar2(30), cantidadhijos number(2), constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')), fechaingreso date );

Ingresamos algunos registros: insert into empleados values('22222222','f','Lopez','Ana','Colon 123','Administracion',2,'casado','10/10/1990'); insert into empleados values('23333333','m','Lopez','Luis','Sucre 235','Administracion',0,'soltero','02/10/1990'); insert into empleados values('24444444','m','Garcia','Marcos','Sarmiento 1234','Contaduria',3,'divorciado','07/12/1998'); insert into empleados values('25555555','m','Gomez','Pablo','Bulnes

321','Contaduria',2,'casado','10/09/1998'); insert into empleados values('26666666','f','Perez','Laura','Peru 1254','Sistemas',3,'casado','05/09/2000');

Eliminamos las vistas "vista_empleados" y "vista_empleados2": drop view vista_empleados; drop view vista_empleados2;

Creamos la vista "vista_empleados", que muestra solamente algunos campos de "empleados": create view vista_empleados as select apellido, nombre, sexo, seccion from empleados;

Creamos la vista "vista_empleados2", igual que "vista_empleados", pero ahora colocamos "with read only" para impedir que puedan ejecutarse "insert", "update" y "delete" sobre esta vista: create view vista_empleados2 as select apellido, nombre, sexo, seccion from empleados with read only;

Actualizamos el nombre de un empleado a través de la vista "vista_empleados": update vista_empleados set nombre='Beatriz' where nombre='Ana';

Veamos si la modificación se realizó en la tabla: select *from empleados;

Intentamos actualizar el nombre de un empleado a través de la vista "vista_empleados2": update vista_empleados2 set nombre='Pedro' where nombre='Marcos';

No lo permite. Ingresamos un registro en la tabla "empleados" a través de la vista "vista_empleados": insert into vista_empleados values('Juarez','Juan','m','Sistemas');

Oracle acepta la inserción. Verificamos que la inserción se realizó en la tabla: select *from empleados;

Intentamos ingresar un registro a través de la vista "vista_empleados2": insert into vista_empleados2 values('Gimenez','Julieta','f','Sistemas');

Oracle no lo permite porque la vista fue definida con "with read only". Eliminamos un registro en la tabla "empleados" a través de la vista "vista_empleados": delete from vista_empleados where apellido='Juarez';

Oracle acepta la eliminación. Verificamos que la eliminación se realizó en la tabla: select *from empleados;

Intentamos eliminar registros a través de la vista "vista_empleados2": delete from vista_empleados2 where apellido='Lopez';

Oracle no lo permite porque la vista fue definida con "with read only". Primer problema: Una empresa almacena la información de sus clientes en una tabla llamada "clientes". 1- Elimine la tabla: drop table clientes;

2- Cree la tabla: create table clientes( nombre varchar2(40), documento char(8), domicilio varchar2(30), ciudad varchar2(30) );

3- Ingrese algunos registros: insert into clientes insert into clientes 254','Cordoba'); insert into clientes insert into clientes Fe'); insert into clientes insert into clientes Fe'); insert into clientes Aires'); insert into clientes Aires');

values('Juan Perez','22222222','Colon 1123','Cordoba'); values('Karina Lopez','23333333','San Martin values('Luis Garcia','24444444','Caseros 345','Cordoba'); values('Marcos Gonzalez','25555555','Sucre 458','Santa values('Nora Torres','26666666','Bulnes 567','Santa Fe'); values('Oscar Luque','27777777','San Martin 786','Santa values('Pedro Perez','28888888','Colon 234','Buenos values('Rosa Rodriguez','29999999','Avellaneda 23','Buenos

4- Cree o reemplace la vista "vista_clientes" para que recupere el nombre y ciudad de todos los clientes 5- Cree o reemplace la vista "vista_clientes2" para que recupere el nombre y ciudad de todos los clientes no permita modificaciones. 6- Consulte ambas vistas 7- Intente ingresar el siguiente registro mediante la vista que permite sólo lectura Oracle no lo permite. 8- Ingrese el registro anterior en la vista "vista_clientes" 9- Intente modificar un registro mediante la vista que permite sólo lectura 10- Actualice el registro anterior en la vista "vista_clientes" 11- Intente eliminar un registro mediante la vista "vista_clientes2" 12- Elimine todos los clientes de "Buenos Aires" a través de la vista "vista_clientes"

Vistas modificar (create or replace view) Para modificar una vista puede eliminarla y volver a crearla o emplear "create or replace". Sintaxis: create or replace view NOMBREVISTA as SUBCONSULTA;

Con "create or replace view" se modifica la definición de una vista existente o se crea si no existe. Problema: Una empresa almacena la información de sus empleados en dos tablas llamadas "empleados" y "secciones". Eliminamos las tablas: drop table empleados; drop table secciones;

Creamos las tablas: create table secciones( codigo number(2), nombre varchar2(20), constraint PK_secciones primary key (codigo) ); create table empleados( documento char(8), nombre varchar2(30), domicilio varchar2(30), seccion number(2) not null, constraint FK_empleados_seccion foreign key (seccion) references secciones(codigo), constraint PK_empleados primary key (documento) );

Ingresamos algunos registros: insert into secciones values(1,'Administracion'); insert into secciones values(2,'Contaduría'); insert into secciones values(3,'Sistemas'); insert insert insert insert insert

into into into into into

empleados empleados empleados empleados empleados

values('22222222','Lopez Ana','Colon 123',1); values('23333333','Lopez Luis','Sucre 235',1); values('24444444','Garcia Marcos','Sarmiento 1234',2); values('25555555','Gomez Pablo','Bulnes 321',3); values('26666666','Perez Laura','Peru 1254',3);

Eliminamos la vista "vista_empleados": drop view vista_empleados;

Creamos la vista "vista_empleados" que muestre algunos campos de los empleados de la sección 1: create view vista_empleados as

select documento,nombre,seccion from empleados where seccion=1;

Consultamos la vista: select *from vista_empleados;

Veamos el texto de la vista consultando "user_views": select view_name,text from user_views where view_name='VISTA_EMPLEADOS';

Modificamos la vista para que muestre el domicilio: create or replace view vista_empleados as select documento,nombre,seccion, domicilio from empleados where seccion=1;

Consultamos la vista para ver si se modificó: select *from vista_empleados;

Aparece el nuevo campo. Veamos el texto de la vista consultando "user_views": select view_name,text from user_views where view_name='VISTA_EMPLEADOS';

Primer problema: Un club dicta cursos de distintos deportes. Almacena la información en varias tablas. 1- Elimine las tablas "inscritos", "socios" y "cursos": drop table inscritos; drop table socios; drop table cursos;

2- Cree las tablas: create table socios( documento char(8) not null, nombre varchar2(40), domicilio varchar2(30), constraint PK_socios_documento primary key (documento) ); create table cursos( numero number(2), deporte varchar2(20), dia varchar2(15), constraint CK_inscritos_dia check (dia in('lunes','martes','miercoles','jueves','viernes','sabado')), profesor varchar2(20), constraint PK_cursos_numero primary key (numero) ); create table inscritos( documentosocio char(8) not null, numero number(2) not null, matricula char(1),

constraint PK_inscritos_documento_numero primary key (documentosocio,numero), constraint FK_inscritos_documento foreign key (documentosocio) references socios(documento), constraint FK_inscritos_numero foreign key (numero) references cursos(numero) );

3- Ingrese algunos registros para todas las tablas: insert insert insert insert

into into into into

socios socios socios socios

values('30000000','Fabian Fuentes','Caseros 987'); values('31111111','Gaston Garcia','Guemes 65'); values('32222222','Hector Huerta','Sucre 534'); values('33333333','Ines Irala','Bulnes 345');

insert insert insert insert insert insert insert

into into into into into into into

cursos cursos cursos cursos cursos cursos cursos

values(1,'tenis','lunes','Ana Acosta'); values(2,'tenis','martes','Ana Acosta'); values(3,'natacion','miercoles','Ana Acosta'); values(4,'natacion','jueves','Carlos Caseres'); values(5,'futbol','sabado','Pedro Perez'); values(6,'futbol','lunes','Pedro Perez'); values(7,'basquet','viernes','Pedro Perez');

insert insert insert insert insert insert insert

into into into into into into into

inscritos inscritos inscritos inscritos inscritos inscritos inscritos

values('30000000',1,'s'); values('30000000',3,'s'); values('30000000',6,null); values('31111111',1,'n'); values('31111111',4,'s'); values('32222222',1,'n'); values('32222222',7,'n');

4- Cree o reemplace la vista "vista_inscritos" que muestre el documento y nombre del socio, el deporte, el día y la matrícula, de todas las inscripciones no pagas 5- Consulte la vista 6- Veamos el texto de la vista 7- Modifique la vista para que muestre el domicilio 8- Consulte la vista para ver si se modificó 9- Vea el texto de la vista

Vistas (with check option) Es posible obligar a todas las instrucciones de modificación de datos que se ejecutan en una vista a cumplir ciertos criterios. Por ejemplo, creamos la siguiente vista: create view vista_empleados as select apellido, nombre, sexo, seccion from empleados where seccion='Administracion' with check option;

La vista definida anteriormente muestra solamente algunos registros y algunos campos de "empleados", los de la sección "Administracion". Con la cláusula "with check option", no se permiten modificaciones en aquellos campos que afecten a los registros que retorna la vista. Es decir, no podemos modificar el campo "sección" porque al hacerlo, tal registro ya no aparecería en la vista; si podemos actualizar los demás campos. Por ejemplo, si intentamos actualizar a "Sistemas" el campo "seccion" de un registro mediante la vista, Oracle muestra un mensaje de error. La misma restricción surge al ejecutar un "insert" sobre la vista; solamente podemos ingregar registros con el valor "Administracion" para "seccion"; si intentamos ingresar un registro con un valor diferente de "Administracion" para el campo "seccion", Oracle mostrará un mensaje de error. Sintaxis básica: create view NOMBREVISTA as SUBCONSULTA with check option;

Problema: Una empresa almacena la información de sus empleados en una tabla llamada "empleados". Eliminamos la tabla: drop table empleados;

Creamos la tabla: create table empleados( documento char(8), sexo char(1) constraint CK_empleados_sexo check (sexo in ('f','m')), apellido varchar2(20), nombre varchar2(20), seccion varchar2(30), cantidadhijos number(2), constraint CK_empleados_hijos check (cantidadhijos>=0), estadocivil char(10) constraint CK_empleados_estadocivil check (estadocivil in ('casado','divorciado','soltero','viudo')) );

Ingresamos algunos registros:

insert into empleados values('22222222','f','Lopez','Ana','Administracion',2,'casado'); insert into empleados values('23333333','m','Lopez','Luis','Administracion',0,'soltero'); insert into empleados values('24444444','m','Garcia','Marcos','Sistemas',3,'divorciado'); insert into empleados values('25555555','m','Gomez','Pablo','Sistemas',2,'casado'); insert into empleados values('26666666','f','Perez','Laura','Contaduria',3,'casado');

Creamos o reemplazamos (si existe) la vista "vista_empleados", para que muestre el nombre, apellido, sexo y sección de todos los empleados de "Administracion" agregando la cláusula "with check option" para evitar que se modifique la sección de tales empleados a través de la vista y que se ingresen empleados de otra sección: create or replace view vista_empleados as select apellido, nombre, sexo, seccion from empleados where seccion='Administracion' with check option;

Consultamos la vista: select *from vista_empleados;

Actualizarmos el nombre de un empleado a través de la vista: update vista_empleados set nombre='Beatriz' where nombre='Ana';

Oracle aceptó la actualización porque el campo "nombre" no está restringido. Veamos si la modificación se realizó en la tabla: select *from empleados;

Intentamos actualizar la sección de un empleado a través de la vista: update vista_empleados set seccion='Sistemas' where nombre='Beatriz';

Oracle no aceptó la actualización porque el campo "nombre" está restringido. Ingresamos un registro mediante la vista: insert into vista_empleados values('Gomez','Gabriela','f','Administracion');

Oracle acepta la inserción porque ingresamos un valor para "seccion" que incluirá el registro en la vista. Intentamos ingresar un empleado de otra sección: insert into vista_empleados values('Torres','Tatiana','f','Sistemas');

Oracle no acepta la inserción porque ingresamos un valor para "seccion" que excluirá el nuevo registro de la vista. Primer problema: Una empresa almacena la información de sus clientes en una tabla llamada "clientes". 1- Elimine la tabla: drop table clientes;

2- Cree la tabla: create table clientes( nombre varchar2(40), documento char(8), domicilio varchar2(30), ciudad varchar2(30) );

3- Ingrese algunos registros: insert into clientes insert into clientes 254','Cordoba'); insert into clientes insert into clientes Fe'); insert into clientes insert into clientes Fe'); insert into clientes Aires'); insert into clientes Aires');

values('Juan Perez','22222222','Colon 1123','Cordoba'); values('Karina Lopez','23333333','San Martin values('Luis Garcia','24444444','Caseros 345','Cordoba'); values('Marcos Gonzalez','25555555','Sucre 458','Santa values('Nora Torres','26666666','Bulnes 567','Santa Fe'); values('Oscar Luque','27777777','San Martin 786','Santa values('Pedro Perez','28888888','Colon 234','Buenos values('Rosa Rodriguez','29999999','Avellaneda 23','Buenos

4- Cree o reemplace la vista "vista_clientes" para que recupere el nombre y ciudad de todos los clientes que no sean de "Cordoba" sin emplear "with check option" 5- Cree o reemplace la vista "vista_clientes2" para que recupere el nombre y ciudad de todos los clientes que no sean de "Cordoba" empleando "with check option" 6- Consulte ambas vistas 7- Intente modificar la ciudad del cliente "Pedro Perez" a "Cordoba" través de la vista que está restringida. 8- Realice la misma modificación que intentó en el punto anterior a través de la vista que no está restringida 9- Actualice la ciudad del cliente "Oscar Luque" a "Buenos Aires" mediante la vista restringida 10- Verifique que "Oscar Luque" aún se incluye en la vista 11- Intente ingresar un empleado de "Cordoba" en la vista restringida 12- Ingrese el empleado anterior a través de la vista no restringida 13- Ingrese un empleado de "Salta" en la vista restringida 14- Verifique que el nuevo registro está incluido en la vista

Vistas (otras consideraciones) Cuando creamos una vista, Oracle verifica que las tablas a las cuales se hace referencia en ella existan. Si la vista que se intenta crear hace referencia a tablas inexistentes, Oracle muestra un mensaje de error. Podemos "forzar" a Oracle a crear una vista aunque no existan los objetos (tablas, vistas, etc.) que referenciamos en la misma. Para ello debemos agregar "force" al crearla: create force view NOMBREVISTA as SUBCONSULTA;

De esta manera, podemos crear una vista y después las tablas involucradas; luego, al consultar la vista, DEBEN existir las tablas. Al crear la vista la opción predeterminada es "no force". Se recomienda crear las tablas y luego las vistas necesarias. Otra cuestión a considerar es la siguiente: si crea una vista con "select *" y luego agrega campos a la estructura de las tablas involucradas, los nuevos campos no aparecerán en la vista; esto es porque los campos se seleccionan al ejecutar "create view"; debe volver a crear la vista (con "create view" o "create or replace view"). Problema: Una empresa almacena la información de sus empleados en una tabla llamada "empleados". Eliminamos la tabla: drop table empleados;

Eliminamos la vista "vista_empleados": drop view vista_empleados;

Creamos la vista "vista_empleados" que muestre algunos campos de "empleados", pero la tabla "empleados" no existe, por ello, debemos agregar, al crear la vista "force": create force view vista_empleados as select documento,nombre,seccion from empleados;

Creamos la tabla: create table empleados( documento char(8), nombre varchar2(30), domicilio varchar2(30), seccion varchar2(30) );

Ingresamos algunos registros: insert into empleados insert into empleados insert into empleados 1234','Contaduria'); insert into empleados

values('22222222','Lopez Ana','Colon 123','Sistemas'); values('23333333','Lopez Luis','Sucre 235','Sistemas'); values('24444444','Garcia Marcos','Sarmiento values('25555555','Gomez Pablo','Bulnes

321','Contaduria'); insert into empleados values('26666666','Perez Laura','Peru 1254','Secretaria');

Consultamos la vista: select *from vista_empleados;

Veamos el texto de la vista consultando "user_views": select view_name,text from user_views where view_name='VISTA_EMPLEADOS';

Creamos o reemplazamos (si existe) la vista "vista_empleados" que muestre todos los campos de la tabla "empleados": create or replace view vista_empleados as select *from empleados;

Consultamos la vista: select *from vista_empleados;

Agregamos un campo a la tabla "empleados": alter table empleados add sueldo number(6,2);

Consultamos la vista "vista_empleados": select *from vista_empleados;

Note que el nuevo campo agregado a "empleados" no aparece, a pesar que la vista indica que muestre todos los campos de dicha tabla; esto sucede porque los campos se seleccionan al ejecutar "create view", para que aparezcan debemos volver a crear la vista: create or replace view vista_empleados as select *from empleados;

Consultemos la vista: select *from vista_empleados;

Ahora si aparece el nuevo campo "sueldo" de "empleados"; Primer problema: Una empresa almacena la información de sus clientes en una tabla llamada "clientes". 1- Elimine la tabla: drop table clientes;

2- Elimine la vista "vista_clientes": drop view vista_clientes;

3- Intente crear o reemplazar la vista "vista_clientes" para que muestre el nombre, domicilio y ciudad de todos los clientes de "Cordoba" (sin emplear "force") Mensaje de error porque la tabla referenciada no existe. 4- Cree o reemplace la vista "vista_clientes" para que recupere el nombre, apellido y ciudad de todos los clientes de "Cordoba" empleando "force"

5- Cree la tabla: create table clientes( nombre varchar2(40), documento char(8), domicilio varchar2(30), ciudad varchar2(30) );

6- Ingrese algunos registros: insert into clientes insert into clientes 254','Cordoba'); insert into clientes insert into clientes Fe'); insert into clientes insert into clientes Fe'); insert into clientes Aires'); insert into clientes Aires');

values('Juan Perez','22222222','Colon 1123','Cordoba'); values('Karina Lopez','23333333','San Martin values('Luis Garcia','24444444','Caseros 345','Cordoba'); values('Marcos Gonzalez','25555555','Sucre 458','Santa values('Nora Torres','26666666','Bulnes 567','Santa Fe'); values('Oscar Luque','27777777','San Martin 786','Santa values('Pedro Perez','28888888','Colon 234','Buenos values('Rosa Rodriguez','29999999','Avellaneda 23','Buenos

7- Cree o reemplace la vista "vista_clientes" para que muestre todos los campos de la tabla "clientes" 8- Consulte la vista 9- Agregue un campo a la tabla "clientes" 10- Consulte la vista "vista_clientes"El nuevo campo agregado a "clientes" no aparece, pese a que la vista indica que muestre todos los campos de dicha tabla. 11- Modifique la vista para que aparezcan todos los campos 12- Consulte la vista:Ahora si aparece el campo.

Vistas materializadas (materialized view) Una vista materializada se define como una vista común, pero en lugar de almacenar la definición de la vista, almacena el resultado de la consulta, es decir, la materializa, como un objeto persistente en la base de datos. Sintaxis: create materialized view NOMBREVISTAMATERIALIZADA as SUBCONSULTA;

Existen varias cláusulas que podemos agregar al crear una vista materializada, pero no las estudiaremos. En el "from" de la consulta pueden listarse tablas, vistas y vistas materializadas. Entonces, una vista materializada almacena su resultado físicamente. Una vista materializada (materialized view) es una instantánea (snapshot), son sinónimos. Para obtener información acerca de las vistas materializadas podemos consultar el diccionario "user_objects", en la columna "object_type" aparecerá "materialized view" si es una vista materializada. Ejemplo: select *from user_objects where object_type='MATERIALIZED VIEW';

También podemos consultar "user_mviews" para obtener información de todas las vistas materializadas del usuario actual: select *from user_mviews;

Este diccionario muestra mucha información que no explicaremos en detalle. Para eliminar una vista materializada empleamos "drop materialized view": drop materialized view NOMBREVISTAMATERIALIZADA;

Ejemplo: drop materialized view vm_promedios;

No se permite realizar "insert", "update" ni "delete" en las vistas materializadas. Problema: Un profesor almacena los datos y notas de sus alumnos en dos tablas "alumnos" y notas". Eliminamos las tablas: drop table notas; drop table alumnos;

Creamos las tablas: create table alumnos( documento char(8), apellido varchar(30), nombre varchar(30), domicilio varchar2(40), primary key(documento) );

create table notas( documento char(8), fecha date, nota number(4,2), constraints FK_notas_documento foreign key (documento) references alumnos(documento) );

Ingresamos algunos registros: insert insert insert insert

into into into into

alumnos alumnos alumnos alumnos

insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

notas notas notas notas notas notas notas notas notas notas

values('23333333','Acosta','Ana','Avellaneda 111'); values('24444444','Bustos','Betina','Bulnes 222'); values('25555555','Caseros','Carlos','Colon 333'); values('26666666','Duarte','Daniel','Dinamarca 444');

values('23333333','10/05/2007',5.3); values('23333333','15/07/2007',8.3); values('23333333','20/09/2007',7.4); values('24444444','10/05/2007',8.6); values('24444444','15/07/2007',9.4); values('25555555','10/05/2007',9); values('25555555','15/07/2007',6); values('26666666','10/05/2007',3.2); values('26666666','15/07/2007',5.3); values('26666666','20/09/2007',3.5);

Creamos o reemplazamos una vista normal que muestre el documento del alumnos y el promedio de sus notas: create or replace view vista_promedios as select a.documento,avg(nota) as promedio from alumnos a join notas n on a.documento=n.documento group by a.documento;

Eliminamos la vista materializada "vm_promedios: drop materialized view vm_promedios;

Creamos una vista materializada que muestre el documento del alumnos y el promedio de sus notas: create materialized view vm_promedios as select a.documento,avg(nota) as promedio from alumnos a join notas n on a.documento=n.documento group by a.documento;

Consultamos ambas vistas: select *from vista_promedios; select *from vm_promedios;

El resultado es el mismo. Ahora agregamos algunas notas: insert into notas values('23333333','12/10/2007',9); insert into notas values('24444444','12/10/2007',7.5);

insert into notas values('25555555','12/10/2007',3); insert into notas values('26666666','12/10/2007',4);

Consultamos ambas vistas y comparamos los promedios: select *from vista_promedios; select *from vm_promedios;

Los promedios de la vista actualizable han cambiado porque al ejecutar el primer "select" se consultaron las tablas "notas" y "alumnos"; los promedios de la vista materializada no han cambiado, porque almacenaron el resultado de la consulta, al ejecutar el segundo "select" no se consultaron las tablas "alumnos" y "notas".

Seguridad y acceso a Oracle Una de las tareas al administrar Oracle es permitir el acceso a bases de datos y asignar permisos sobre los objetos que conforman una base de datos. Para conectarnos con un servidor Oracle necesitamos un modo de acceso que incluye los permisos que dispondremos durante la conexión; estos permisos se definen a partir de un nombre de usuario. Un USUARIO es un identificador necesario para acceder a una base de datos. Un usuario es un conjunto de permisos que se aplican a una conexión de base de datos. Un usuario es además propietario de ciertos objetos. Los PRIVILEGIOS (permisos) especifican qué operaciones puede realizar un usuario y sobre qué objetos de la base de datos tiene autorización, es decir, qué tarea puede realizar con esos objetos y si puede emitir determinadas instrucciones. Estas operaciones pueden ser de dos tipos: de sistema y sobre objeto. Un rol de base de datos es una agrupación de permisos de sistema y de objeto. Un ROL (role) es un grupo de usuarios; permite agrupar usuarios para aplicarles permisos; así, al agregar un nuevo usuario a la base de datos, no es necesario concederle permiso para cada objeto, sino que lo agregamos a un rol; cuando asignamos permisos sobre un objeto al rol, automáticamente el permiso afectará a los usuarios que pertenezcan a tal rol. Los permisos controlan el acceso a los distintos objetos de una base de datos; pueden concederse a nivel de usuario (individualmente) o a nivel de rol (a todos los usuarios de un grupo). Los permisos que un usuario tiene en una base de datos dependen de los permisos de usuario y de los roles al que pertenezca dicho usuario. Usuarios, roles y permisos son la base de los mecanismos de seguridad de Oracle.

Usuarios (crear) Puede haber varios usuarios diferentes de la base de datos. Cada uno es propietario de sus objetos. Para crear un usuario debemos conectarnos a la base datos como administradores (por ejemplo "system"). Sintaxis básica para crear un usuario: create user NOMBREUSUARIO identified by CONTRASEÑA default tablespace NOMBRETABLESPACEPORDEFECTO quota CANTIDAD on TABLEESPACE; ** [default role ROLE, ALL];

La cláusula "identified by" permite indicar una contraseña. La cláusula "default tablespace" será el tablespace (espacio de tablas) por defecto en la creación de objetos del usuario. Si se omite se utilizará el tablespace SYSTEM. Los tablespaces son unidades lógicas en las cuales de divide una base de datos, en las cuales se almacenan los objetos (tablas, secuencias, etc.); todos los objetos están almacenados dentro de un tablespace. La cláusula "quota" permite configurar un espacio en bytes, Kb o Mb en la base de datos. Si no se especifica, por defecto es cero y no podrá crear objetos. La cláusula "default role" permite asignar roles de permisos durante la creación del usuario. Ejemplo: create user ana identified by anita;

Con la sentencia anterior se crea un usuario denominado "ana" con la clave "anita", el tablespace por defecto es "system" porque no se especificó otro. Con la siguiente sentencia se crea un usuario denominado "juan" con la clave "juancito", se le asigna un espacio de 100 mb: create user juan identified by juancito default tablespace system quota 100M on system;

Si intentamos crear un usuario que ya existe, Oracle muestra un mensaje de error indicando tal situación. El diccionario "dba_users" muestra información sobre todos los usuarios; el nombre de usuario (username), contraseña (password), estado (account_status), espacio (default_tablespace), fecha de expiración (expiry_date), fecha de creación (created), entre otros. Luego de crear un usuario, aún no podemos conectarnos, ya que no tenemos permiso para crear una sesión. Los permisos se aprenderán próximamente. Problema: Sabemos que para crear un usuario debemos conectarnos a la base datos como administradores (por ejemplo "system"). Necesitamos crear un usuario "ana"; antes vamos a eliminarlo por si existe (luego veremos detenidamente cómo eliminar usuarios y explicaremos la siguiente sentencia): drop user ana cascade;

Creamos un usuario denominado "ana" con la contraseña "anita": create user ana identified by anita;

Aparece un mensaje indicando que el usuario "ana" ha sido creado. Necesitamos crear un usuario denominado "juan"; antes vamos a eliminarlo por si existe: drop user juan cascade;

Creamos el usuario "juan" con la contraseña "juancito", le asignamos un espacio de 100 mb en "system": create user juan identified by juancito default tablespace system quota 100M on system;

Si intentamos crear un usuario que ya existe, Oracle muestra un mensaje de error indicando tal situación. create user juan identified by juancito;

Mensaje de error. Consultamos el diccionario "dba_users" y analizamos la información que nos muestra: select username, password, default_tablespace, created from dba_users;

El resultado nos muestra el nombre de usuario, si tiene o no contraseña, el espacio asignado (tablespace) y fecha de creación. Problema: Sabemos que para crear un usuario debemos conectarnos a la base datos como administradores (por ejemplo "system"). Necesitamos crear un usuario "ana"; antes vamos a eliminarlo por si existe (luego veremos detenidamente cómo eliminar usuarios y explicaremos la siguiente sentencia): drop user ana cascade;

Creamos un usuario denominado "ana" con la contraseña "anita": create user ana identified by anita;

Aparece un mensaje indicando que el usuario "ana" ha sido creado. Necesitamos crear un usuario denominado "juan"; antes vamos a eliminarlo por si existe: drop user juan cascade;

Creamos el usuario "juan" con la contraseña "juancito", le asignamos un espacio de 100 mb en "system": create user juan identified by juancito default tablespace system quota 100M on system;

Si intentamos crear un usuario que ya existe, Oracle muestra un mensaje de error indicando tal situación. create user juan identified by juancito;

Mensaje de error.

Consultamos el diccionario "dba_users" y analizamos la información que nos muestra: select username, password, default_tablespace, created from dba_users;

El resultado nos muestra el nombre de usuario, si tiene o no contraseña, el espacio asignado (tablespace) y fecha de creación.

Permiso de conexión Los usuarios necesitan permisos para poder acceder a la base de datos y a los objetos de la misma. Los privilegios pueden ser de dos tipos: del sistema y sobre objetos. Como mínimo, un usuario debe tener permiso para conectarse. El permiso "create session" es un privilegio de sistema. Para conceder permiso de conexión a un usuario empleamos la instrucción "grant". Sintaxis básica: grant create session to USUARIO;

En el siguiente ejemplo concedemos al usuario "juan" permiso para conectarse: grant create session to juan;

Podemos consultar el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a los usuarios. Nos mostrará el nombre del usuario (grantee) y el permiso (privilege), entre otra información que analizaremos próximamente. Luego de tener permiso para crear sesión, puede crear una sesión presionando el ícono "new connection" en la solapa "connections"; se abrirá una ventana en la cual deberá colocar un nombre de conexión ("connection name", puede ser el mismo nombre de usuario), el nombre del usuario ("username") y la contraseña ("password"), luego presionar el botón "connect"; se abrirá una nueva solapa (nueva conexión) con el nombre del usuario; no se abrirá la nueva conexión si: a) el usuario para quien quiere abrir una nueva sesión no existe, b) la contraseña es incorrecta o c) el usuario existe pero no tiene permiso "create session". Si consultamos el diccionario "user_sys_privs" obtendremos la misma información que "dba_sys_privs" pero únicamente del usuario actual. Podemos averiguar el nombre del usuario conectado con la siguiente sentencia: select user from dual;

Problema: Creamos un usuario denominado "ana", con contraseña "anita", le asignamos espacio en "system" (100M). Antes lo eliminamos por si existe: drop user ana cascade; create user ana identified by anita default tablespace system quota 100M on system;

Creamos un usuario denominado "juan", con contraseña "juancito", le asignamos espacio en "system" (100M). Antes lo eliminamos por si existe: drop user juan cascade; create user juan identified by juancito

default tablespace system quota 100M on system;

Consultamos el diccionario "dba_users" y analizamos la información que nos muestra: select username, password, default_tablespace, created from dba_users;

Verificamos que los usuarios "ana" y "juan" existen. Consultamos el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a nuestros usuarios. Nos mostrará el nombre del usuario (grantee) y el permiso (si lo tiene): select grantee, privilege from dba_sys_privs where GRANTEE='ANA' or grantee='JUAN';

Nos muestra que estos usuarios no tienen ningún privilegio concedido. Concedemos a "juan" permiso para conectarse: grant create session to juan;

Consultamos el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a "juan": select grantee,privilege from dba_sys_privs where grantee='JUAN';

Tiene permiso "create session". Abrimos una nueva conexión para "juan": Presionamos el ícono "new connection" en la solapa "connections"; se abre una ventana en la cual colocamos: - "connection name" (nombre de la conexión): juan; - "username" (nombre del usuario): juan y - "password" (contraseña): juancito. Luego presionamos "connect"; se abre una nueva solapa (nueva conexión) con el nombre del usuario (juan). En la conexión de "juan" podemos consultar sus privilegios: select username, privilege from user_sys_privs;

Note que únicamente aparecen los permisos del usuario actual. Para obtener el nombre del usuario conectado, empleamos la siguiente sentencia: select user from dual;

Aparece Juan. Volvemos a la conexión "system" (la otra solapa). Comprobamos el usuario actual: select user from dual;

Aparece System. Ya sabemos abrir una nueva sessión de usuario. Aprendimos que existen 3 razones por las cuales una nueva sesión no se pueda iniciar; una de ellas es que el usuario no exista. Intentemos abrir una nueva conexión para un usuario inexistente:

Presionamos el ícono "new connection" en la solapa "connections"; se abre una ventana en la cual colocamos: - "connection name" (nombre de la conexión): pedro; - "username" (nombre del usuario): pedro y - "password" (contraseña): pedrito. Luego presionamos "connect"; la sessión no se abre, un mensaje de error indica que el nombre de usuario o la contraseña son inválidas y que la conexión se deniega. Cancelamos. Otra razón por la cual la apertura de una nueva sesión puede fallar es que el usuario no tenga permiso de conexión. Intentemos abrir una nueva conexión para un usuario que no tenga tal permiso, caso de "ana": Presionamos el ícono "new connection" en la solapa "connections"; se abre una ventana en la cual colocamos: - "connection name" (nombre de la conexión): ana; - "username" (nombre del usuario): ana y - "password" (contraseña): anita. Luego presionamos "connect"; la sessión no se abre, un mensaje de error indica que el usuario "ana" no tiene permiso "create session" por lo cual se deniega la conexión. Cancelamos. Concedemos a "ana" permiso de conexión: grant create session to ana;

Consultamos el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a "ana": select grantee,privilege from dba_sys_privs where grantee='ANA';

Tiene permiso "create session". La tercera razón por la cual puede no iniciarse una nueva sesión es que coloquemos la contraseña incorrecta. Intentemos abrir una nueva conexión para un usuario que tenga permiso, pero le demos una contraseña incorrecta: Presionamos el ícono "new connection" en la solapa "connections"; se abre una ventana en la cual colocamos: - "connection name" (nombre de la conexión): ana; - "username" (nombre del usuario): ana y - "password" (contraseña): ana. Luego presionamos "connect"; la sessión no se abre, un mensaje de error indica que el nombre de usuario o la contraseña son inválidas y que la conexión se deniega. Abramos una nueva conexión para "ana" colocando los datos correctos: Presionamos el ícono "new connection" en la solapa "connections"; se abre una ventana en la cual colocamos: - "connection name" (nombre de la conexión): ana; - "username" (nombre del usuario): ana y

- "password" (contraseña): anita. Presionamos "connect"; se abre una nueva solapa (nueva conexión) con el nombre del usuario (ana). Consultamos el diccionario "user_sys_privs": select username,privilege from user_sys_privs;

Note que únicamente aparecen los permisos del usuario actual. Comprobamos que estamos en la sesión de "ana": select user from dual;

Primer problema: Una escuela necesita crear 3 usuarios diferentes en su base de datos. Uno denominado "director", otro "profesor" y un tercero "alumno". Conéctese como administrador (por ejemplo "system"). 1- Elimine el usuario "director", porque si existe, aparecerá un mensaje de error: drop user director cascade;

2- Cree un usuario "director", con contraseña "dire" y 100M de espacio en "system" 3- Elimine el usuario "profesor": drop user profesor cascade;

4- Cree un usuario "profesor", con contraseña "profe" y espacio en "system" 5- Elimine el usuario "alumno" y luego créelo con contraseña "alu" y espacio en "system" 6- Consulte el diccionario "dba_users" y analice la información que nos muestra. Deben aparecer los tres usuarios creados anteriormente. 7- Consulte el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a nuestros tres usuarios Nos muestra que estos usuarios no tienen ningún privilegio concedido. 8- Conceda a "director" permiso para conectarse 9- Conceda a "profesor" permiso para conectarse 10- Consulte el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a nuestros 3 usuarios 11- Abra una nueva conexión para "director". Se debe abrir una nueva solapa (nueva conexión) con el nombre del usuario (director) 12- En la conexión de "director" consulte sus privilegios 13- Obtenga el nombre del usuario conectado 14- Vuelva a la conexión "system" (la otra solapa) y compruebe el usuario actual 15- Intente abrir una nueva conexión para el usuario inexistente. Debe aparecer un mensaje de error y denegarse la conexión. Cancele. 16- Intente abrir una nueva conexión para el usuario "profesor" colocando una contraseña incorrecta. Debe aparecer un mensaje de error y denegarse la conexión. Cancele. 17- Abra una nueva conexión para "profesor" colocando los datos correctos. Se debe abrir una nueva solapa (nueva conexión) con el nombre del usuario (profesor).

18- Intentemos abrir una nueva conexión para el usuario "alumno", el cual no tiene permiso. Un mensaje de error indica que el usuario "alumno" no tiene permiso "create session" por lo cual se deniega la conexión. Cancele. 19- Conceda a "alumno" permiso de conexión 20- Consulte el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a "alumno" 21- Abra una nueva conexión para "ALUMNO". Se debe abrir una nueva solapa (nueva conexión) con el nombre del usuario (profesor) 22- Consulte el diccionario "user_sys_privs" 23- Compruebe que está en la sesión de "alumno"

Privilegios del sistema (conceder) Aprendimos que los usuarios necesitan permisos para poder acceder a la base de datos y a los objetos de la misma. Dijimos que los privilegios pueden ser de dos tipos: a) del sistema y b) sobre objetos. Hemos aprendido a conceder un privilegio de sistema: "create session", que es necesario para poder conectarse a la base de datos, es decir, para iniciar una sesión. Pero teniendo únicamente este permiso, no podemos hacer mucho, solamente iniciar una sesión, pero no podemos crear tablas, ni ningún otro objeto; por ello son importantes los permisos de creación de objetos. Aprendamos más sobre los privilegios de sistema. Los privilegios de sistema son permisos para realizar ciertas operaciones en la base de datos. Los siguientes son algunos de los privilegios de sistema existentes: - create session: para conectarse a la base de datos; - create table: crear tablas; - create sequence: crear secuencias; - create view: crear vistas; - create trigger: crear disparadores en su propio esquema; - create procedure: crear procedimientos y funciones; - execute any procedure: ejecutar cualquier procedimiento en cualquier esquema; - create user: crear usuarios y especificar claves; - create role: crear roles; - drop user: eliminar usuarios. Se asignan privilegios de sistema a un usuario mediante la instrucción "grant": Sintaxis básica: grant PERMISODESISTEMA to USUARIO;

Oracle permite conceder múltiples privilegios a múltiples usuarios en una misma sentencia, debemos separarlos por comas. En el siguiente ejemplo se concede el permiso para crear sesión a los usuarios "juan" y "ana": grant create sesion to juan, ana;

En el siguiente ejemplo se conceden los permisos para crear tablas y vistas al usuario "ana": grant create table, create view to ana;

En el siguiente ejemplo se conceden 2 permisos a 2 usuarios en una sola sentencia: grant create trigger, create procedure to juan, ana;

Consultando el diccionario "dba_sys_privs" encontramos los privilegios concedidos a los distintos usuarios; y consultando "user_sys_privs" obtendremos la misma información pero únicamente del usuario actual. Problema: Creamos un usuario denominado "ana", con contraseña "anita", le asignamos espacio en "system" (100M). Antes lo eliminamos por si existe: drop user ana cascade; create user ana identified by anita default tablespace system quota 100M on system;

Creamos un usuario denominado "juan", con contraseña "juancito", le asignamos espacio en "system" (100M). Antes lo eliminamos por si existe: drop user juan cascade; create user juan identified by juancito default tablespace system quota 100M on system;

Concedemos a ambos usuarios permiso para conectarse: grant create session to ana, juan;

Concedemos permiso para crear tablas y vistas al usuario "ana": grant create table, create view to ana;

Concedemos permiso para crear disparadores y procedimientos a ambos usuarios: grant create trigger, create procedure to juan, ana;

Consultamos el diccionario "dba_sys_privs" para ver los privilegios concedidos a "ana" y "juan": select grantee, privilege from dba_sys_privs where grantee='ANA' or grantee='JUAN' order by grantee; Obtenemos la siguiente información: GRANTEE PRIVILEGE ------------------------------ANA CREATE VIEW ANA CREATE PROCEDURE ANA CREATE TRIGGER ANA CREATE SESSION ANA CREATE TABLE JUAN CREATE SESSION JUAN CREATE PROCEDURE JUAN CREATE TRIGGER

Iniciamos una nueva sesión como "ana". Como "ana" creamos una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

La tabla ha sido creada, porque "ana" tiene pivilegio "create table".

Podemos consultar el diccionario "user_sys_privs" para corroborar sus privilegios: select privilege from user_sys_privs;Obtenemos la siguiente información: PRIVILEGE --------CREATE TRIGGER CREATE TABLE CREATE SESSION CREATE VIEW CREATE PROCEDURE

Iniciamos una nueva sesión como "juan". Como "juan" intentamos crear una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

Mensaje de error "privilegios insuficientes". Esto sucede porque "juan", no tiene permiso para crear tablas. Vemos los permisos de "juan": select privilege from user_sys_privs;

No tiene permiso para crear tablas. Cambiamos a la conexión "system" y concedemos a "juan" permiso para crear tablas: grant create table to juan;

Cambiamos a la solapa "juan" y creamos una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

Podemos hacerlo porque "juan" ahora tiene el permiso. Vemos los permisos de "juan": select privilege from user_sys_privs;

Cambiamos a la conexión "system". Veamos todas las tablas denominadas "PRUEBA": select *from dba_objects where object_name='PRUEBA';

Note que hay una tabla propiedad de "ana" y otra que pertenece a "juan". Primer problema: Una escuela necesita crear 3 usuarios diferentes en su base de datos. Uno denominado "director", otro "profesor" y otro "estudiante". Luego se les concederán diferentes permisos para retringir el acceso a los diferentes objetos. Conéctese como administrador (por ejemplo "system"). 1- Cree un usuario denominado "director", con contraseña "escuela", asignándole 100M de espacio en "system" (100M). Antes elimínelo por si existe: 2- Intente iniciar una sesión como "director". No es posible, no hemos concedido el permiso correspondiente. Aparece un mensaje indicando que el usuario "director" no tiene permiso "create session" por lo tanto no puede conectarse.

3- Vea los permisos de "director" No tiene ningún permiso. 4- Conceda a "director" permiso para iniciar sesion y para crear tablas 5- Vea los permisos de "director" Tiene permiso "create session" y para crear tablas. 6- Inicie una sesión como "director". 7- Como "administrador", elimine los usuarios "profesor" y "alumno", por si existen 8- Cree un usuario denominado "profesor", con contraseña "maestro", asigne espacio en "system" (100M) 9- Cree un usuario denominado "estudiante", con contraseña "alumno" y tablespace "system" (no asigne "quota") 10- Consulte el diccionario de datos correspondiente para ver si existen los 3 usuarios creados 11- Conceda a "profesor" y a "estudiante" permiso para conectarse 12- Conceda a "estudiante" permiso para crear tablas 13- Consulte el diccionario de datos "sys_privs" para ver los permisos de los 3 usuarios creados "director" y "estudiante" tienen permisos para conectarse y para crear tablas, "profesor" tiene permiso para conectarse. 14- Retome su sesión como "director" y cree una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

Podemos hacerlo poque "director" tiene el permiso necesario y espacio en "system". 15- Inicie una sesión como "profesor" e intente crear una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

Mensaje de error "privilegios insuficientes". Esto sucede porque "profesor" NO tiene permiso para crear tablas. 16- Consulte los permisos de "profesor" No tiene permiso para crear tablas, únicamente para crear sesión. 17- Cambie a la conexión de administrador y conceda a "profesor" permiso para crear tablas 18- Cambie a la sesión de "profesor" y cree una tabla Ahora si podemos hacerlo, "profesor" tiene permiso "create table". 19- Consulte nuevamente los permisos de "profesor" Tiene permiso para crear tablas y para crear sesión. 20- Inicie una sesión como "estudiante" e intente crear una tabla: create table prueba( nombre varchar2(30), apellido varchar2(30) );

Mensaje de error "no existen privilegios en tablespace SYSTEM". Esto sucede porque "estudiante",

si bien tiene permiso para crear tablas, no tiene asignado espacio (recuerde que al crearlo no especificamos "quota", por lo tanto, por defecto es cero). 21- Vuelva a la conexión de "administrador" y consulte todas las tablas denominadas "PRUEBA" Note que hay una tabla propiedad de "director" y otra que pertenece a "profesor".

Privilegios del sistema (with admin option) Hemos aprendido la sintaxis básica para conceder permisos de sistema a los usuarios, mediante la instrucción "grant". Agregando a la sentencia "grant", la cláusula "with admin option" concedemos permiso para ceder a terceros los privilegios de sistema obtenidos. Es decir, la cláusula "with admin option" permite que el privilegio concedido a un usuario (o rol) pueda ser otorgado a otros usuarios por el usuario al que estamos asignándoselo; es decir, se concede permiso para conceder el permiso obtenido, a otros usuarios. Sintaxis: grant PERMISODESISTEMA to USUARIO with admin option;

En el siguiente ejemplo, concedemos el permiso de crear tablas al usuario "juan" y con "with admin option", el usuario "juan" podrá conceder este permiso de crear tablas a otros usuarios: grant create table to juan with grant option;

Podemos consultar el diccionario "dba_sys_privs" para encontrar los privilegios concedidos a los usuarios. Nos mostrará una tabla con las siguientes columnas: - grantee: el nombre del usuario, - privilege: el permiso y - admin_option: si el permiso adquirido puede ser cedido a otros o no, YES o NO.

Privilegios sobre los objetos Estos privilegios nos permiten acceder y realizar cambios en los datos de los objetos de otros usuarios. Por ejemplo, el privilegio de consultar la tabla de otro usuario es un privilegio sobre objetos. Se dispone de los siguientes privilegios sobre los objetos tablas, vistas, secuencias y procedures: Privilegio sobre los objetos

Tabla

ALTER

X

DELETE

X

Vista

Secuencia

Procedure

X X

EXECUTE

X

INDEX

X

INSERT

X

REFERECES

X

SELECT

X

X

UPDATE

X

X

X X

Problema: El usuario ana crea una tabla de nombre libros de la siguiente forma: create table libros( codigo number(5) not null, titulo varchar2(40) not null, autor varchar2(20) default 'Desconocido', editorial varchar2(20), edicion date, precio number(6,2) );

Concede a juan los privilegios de SELECT e INSERT en libros: grant select, insert on libros to juan;

ahora juan puede acceder a la tabla libros de ana de la siguiente manera: select * from ana.libros;

ana concede a juan todos los privilegios sobre libros: grant all on libros to juan;

ana concede a juan todos los privilegios sobre libros a todos los usuarios, incluyendo a los que se crean después de ejecutar esta orden: grant all on libros to public;

ana concede a juan sobre visitas para que se pueda modificar sólo la columna pais: grant update (pais) on visitas to juan;

juan puede modificar la columna a la que tiene acceso:

update ana.visitas set pais='España' where pais='Argentina';

Con la opción with grant option se puede dar al usuario que recibe los privilegios el privilegio para que él pueda concederlos a otros. Ana concede a juan privilegio para insertar en tabla1 y, además, para que él pueda pasar este privilegio a otros usuarios: grant insert on tabla1 to juan with grant option;

Problema 1 : •

Crea los usuarios, prof_luis, prof_marisa, alumno_pepa y alumno_miguel.

•

prof_luis crea una tabla llamada alumno con los campos: ◦ ◦ ◦ ◦ ◦ ◦ ◦

dni nombre dirección telefono calif examen calif ejercicios calif clase

• prof_luis concede todos los privilegios a prof_marisa sobre la tabla. • prof_marisa concede privilegios a alumno_pepa y alumno_miguel para modificar las columnas nombre, dirección y teléfono.

Retirada de privilegios Al igual que se conceden privilegios, se pueden retirar. Para eso sirve la orden SQL REVOQUE, que retira privilegios o roles concedidos a los usuarios y privilegios concedidos a los roles. El formato para retirar privilegios de objetos a los usuarios o roles es: REVOKE {priv_objeto [,priv_objeto] … | ALL [PRIVILEGES]} ON [usuario.]objeto FROM {usuario|rol|PUBLIC} [,{usuario | rol | PUBLIC}] ...;

Y el formato para retirar privilegios de sistema o roles a usuarios o para retirar privilegios a roles es el siguiente: REVOKE {priv_sistema | rol} [,{priv_sistema | rol}] ... FROM {usuario | rol | PUBLIC} [usuario | rol | PUBLIC}] ...;

Ejemplo: el usuario MILAGROS retira los privilegios SELECT y UPDATE sobre TABLA1 al usuario FRANCISCO: REVOKE SELECT, UPDATE ON TABLA1 FROM FRANCISCO;

Roles Supongamos que un conjunto de usuarios del departamento de contabilidad requiere el mismo conjunto de privilegios para trabajar con ciertos datos. Este conjunto de privilegios se puede agrupar en un rol, de tal manera que es posible asignar el mismo rol a cada uno de los usuarios. Un rol es un conjunto de privilegios que recibe un nombre común para facilitar la tarea de asignación de éstos a los usuarios o a otros roles. En primer lugar creamos el rol con la orden SQL CREATE ROLE y, a continuación, asignamos privilegios con la orden GRANT. El formato para crear un rol es: CREATE ROLE NombreRol;

Para crear un rol se requiere el privilegio de sistema CREATE ROLE. Ejemplo: creamos un rol llamado PRUEBA: CREATE ROLE PRUEBA;

Conceder privilegios a los roles Una vez creado, hemos de concederle privilegios usando la orden GRANT. Por ejemplo, asignamos los privilegios al rol ACCESO: SELECT e INSERT sobre la tabla EMPLE, INSERT en la tabla DEPART, y CREATE SESSION para poder iniciar sesión en Oracle. El usuario que conceda estos privilegios ha de ser el propietario de las tablas EMPLE y DEPART (o debe tener autorización para conceder privilegios sobre estas tablas) y debe tener el privilegio CREATE SESSION con la posibilidad de concedérselo a otros usuarios: GRANT SELECT, INSERT ON EMPLE TO PRUEBA; GRANT INSERT ON DEPART TO PRUEBA; GRANT CREATE SESSION TO PRUEBA;

Para conceder el rol a un usuario escribimos: GRANT PRUEBA TO USUARIO;

El usuario podrá conectarse a la base de datos y hacer SELECT e INSERT en la tabla EMPLE, e INSERT en la tabla DEPART. Se pueden añadir privilegios al rol ejecutando otra orden GRANT. Por ejemplo, añadimos el privilegio SELECT sobre la tabla DEPART al rol creado anteriormente: GRANT SELECT ON DEPART TO PRUEBA;

Actividad Crea un usuario y concédele el rol creado (PRUEBA). Añade el privilegio CREATE TALE al rol. Consulta los privilegios del sistema que tiene asignados el usuario creado.

Límites en privilegios sobre roles Un rol puede decidir el acceso de un usuario a un objeto, pero no puede permitir la creación de

objetos. Supongamos que el usuario creado anteriormente puede crear vistas (tiene el privilegio CREATE VIEW). También puede hacer SELECT en la tabla EMPLE, ya que tienen asignado el rol ACCESO. Pero no puede crear una vista sobre la tabla EMPLE debido a que recibió el privilegio SELECT a través del rol ACCESO.

Supresión de privilegios en los roles La orden REVOKE permite suprimir los privilegios dados a los roles. Por ejemplo, para retirar del rol ACCESO el privilegio INSERT en la tabla EMPLE escribiremos lo siguiente: REVOKE INSERT ON EMPLE FROM ACCESO; Ahora retiramos del rol ACCESO el privilegio CREATE TABLE: REVOKE CREATE TABLE FROM ACCESO;

Supresión de un rol La orden drop role permite eliminar un rol de la base de datos. Oracle retira el rol concedido a todos los usuarios y roles a los que se les concedió. Para poder eliminar un rol es necesario ser administrador o tener el privilegio DROP ANY ROL. Éste es el formato: DROP ROLE NombreRol;

Por ejemplo, para eliminar el rol ACCESO escribimos: DROP ROLE ACCESO;

Perfiles Un perfil es un conjunto de límites a los recursos de la base de datos. Se pueden utilizar perfiles para poner límites a la cantidad de recursos del sistema y de la base de datos disponibles para un usuario y para gestionar las restricciones de contraseña.