M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions Administració Oracle

1. Crear un tablespace de nombre compras asociándole un fichero en disco llamado ‘COMPRAS.ORA’ de 5 Mb.

CREATE TABLESPACE COMPRAS DATAFILE 'COMPRAS.ORA' SIZE 5M;

2. Modificar el tablespace anterior para que pueda autoextenderse automáticamente, sin límite de espacio en disco.

ALTER TABLESPACE COMPRAS ADD DATAFILE 'COMPRAS1.ORA' 
SIZE 1M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
o
ALTER DATABASE DATAFILE 'compras.ora' AUTOEXTEND ON MAXSIZE UNLIMITED;

3. Crear el usuario RH. Tablespace por defecto: users, Tablespace temporal: Temp. Asignar al usuario RH los roles CONNECT i RESOURCE.

CREATE USER RH IDENTIFIED BY RH
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT CONNECT TO RH;
GRANT RESOURCE TO RH;

4. Crear las tablas como RH.

CREATE TABLE CLIENTE (
DNI VARCHAR2(10),
NOMBRE VARCHAR2(20),
FECHA_NAC DATE NOT NULL,
DIRECCION VARCHAR2(200),
TFNO VARCHAR2(10),
APELLIDOS VARCHAR2(100),
CONSTRAINT CLIENTE_PK PRIMARY KEY (DNI));

CREATE TABLE PROVEEDOR(
NIF VARCHAR2(10),
NOMBRE VARCHAR2(20),
DIRECCION VARCHAR2(200),
TFNO VARCHAR2(10),
CONSTRAINT PROVEEDOR_PK PRIMARY KEY (NIF));

5.Amb l'usuari SYSTEM, Crear un rol que tenga los siguientes privilegios: INSERT y SELECT en las tablas RH.CLIENTE y RH.PROVEEDOR, CREATE SESSION, CREATE DATABASE LINK, CREATE TABLE y CREATE VIEW.

CREATE ROLE UN_ROL;

GRANT INSERT, SELECT ON RH.CLIENTE TO UN_ROL;
GRANT INSERT, SELECT ON HR.PROVEEDOR TO UN_ROL;
GRANT CREATE SESSION, CREATE DATABASE LINK,CREATE TABLE, CREATE VIEW TO UN_ROL;

6. Crear un usuario llamado COMPRADOR. El tablespace por defecto es COMPRAS. Se le asigna 1Mb en el tablespace COMPRAS y 0 de cuota en el tablespace SYSTEM. El tablespace temporal será TEMP. Asignarle el rol anterior.

CREATE USER COMPRADOR IDENTIFIED BY COMPRADOR
DEFAULT TABLESPACE COMPRAS TEMPORARY TABLESPACE TEMP
QUOTA 1M ON COMPRAS  QUOTA 0 ON SYSTEM;

GRANT UN_ROL TO COMPRADOR;

7. Crear un usuario de base de datos llamado ‘administrador’ que tenga funciones de administrador, ponedle de parword administrador

CREATE USER ADMINISTRADOR IDENTIFIED BY ADMINISTRADOR;
GRANT DBA TO ADMINISTRADOR;

8. Conectarse con el nombre de usuario creado anteriormente.

CONNECT ADMINISTRADOR

9. Crear varias tablas como administrador.

CREATE TABLE CLIENTE (
DNI VARCHAR2(10),
NOMBRE VARCHAR2(20),
FECHA_NAC DATE NOT NULL,
DIRECCION VARCHAR2(200),
TFNO VARCHAR2(10),
APELLIDOS VARCHAR2(100),
CONSTRAINT CLIENTE_PK PRIMARY KEY (DNI));

CREATE TABLE PROVEEDOR(
NIF VARCHAR2(10),
NOMBRE VARCHAR2(20),
DIRECCION VARCHAR2(200),
TFNO VARCHAR2(10),
CONSTRAINT PROVEEDOR_PK PRIMARY KEY (NIF));

 
CREATE TABLE PRODUCTO(
NOMBRE VARCHAR2(50),
CODIGO VARCHAR2(50),
PRECIO NUMBER(10) NOT NULL,
PRV_NIF VARCHAR2(10),
CONSTRAINT PRODUCTO_PK PRIMARY KEY (CODIGO),
CONSTRAINT PROVEEDOR_FK FOREIGN KEY(PRV_NIF) REFERENCES PROVEEDOR (NIF));

CREATE TABLE CLIENTE_PRODUCTO(
PRD_CODIGO VARCHAR(50),
CLN_DNI VARCHAR(10),
CONSTRAINT CLIENTE_PRODUCTO_PK PRIMARY KEY (PRD_CODIGO, CLN_DNI),
CONSTRAINT PRODUCTOCP_FK FOREIGN KEY (PRD_CODIGO) REFERENCES PRODUCTO(CODIGO),
CONSTRAINT CLIENTECP_FK FOREIGN KEY (CLN_DNI) REFERENCES CLIENTE(DNI));

10. Crear 5 usuarios nuevos ( USU1, USU2, USU3, USU4, USU5) con la misma contraseña que el nombre, asignándoles el tablespace COMPRAS y una cuota de 2M

CREATE USER USU1 IDENTIFIED BY USU1 DEFAULT TABLESPACE 
COMPRAS
QUOTA 2M ON COMPRAS;

CREATE USER USU2 IDENTIFIED BY USU2 DEFAULT TABLESPACE 
COMPRAS
QUOTA 2M ON COMPRAS;

CREATE USER USU3 IDENTIFIED BY USU3 DEFAULT TABLESPACE
COMPRAS
QUOTA 2M ON COMPRAS;

CREATE USER USU4 IDENTIFIED BY USU4 DEFAULT TABLESPACE
COMPRAS
QUOTA 2M ON COMPRAS;

CREATE USER USU5 IDENTIFIED BY USU5 DEFAULT TABLESPACE
COMPRAS
QUOTA 2M ON COMPRAS;

11. Dar permiso a USU1 sólo para que pueda conectarse a la BD.

GRANT CREATE SESSION TO USU1;

12. Crear un rol que permita conectarse a la BD y hacer SELECT sobre las tablas definidas en el ejercicio 7.

CREATE ROLE ROL_USU;
GRANT CREATE SESSION TO ROL_USU;
GRANT SELECT ON CLIENTE TO ROL_USU;
GRANT SELECT ON PROVEEDOR TO ROL_USU;
GRANT SELECT ON PRODUCTO TO ROL_USU;
GRANT SELECT ON CLIENTE_PRODUCTO TO ROL_USU;

13. Asignar el rol creado a los usuarios USU2 y USU3.

GRANT ROL_USU TO USU2, USU3;

14. Conceder al usuario USU4 privilegios INSERT y DELETE sobre las tablas CLIENTE y PROVEEDOR con la opción de poder concedérselos a otros usuarios.

GRANT INSERT, DELETE ON CLIENTE TO USU4 WITH GRANT OPTION;
GRANT INSERT, DELETE ON PROVEEDOR TO USU4 WITH GRANT OPTION;

15. Conceder al usuario USU5 cuatro privilegios de sistema, dos de ellos con la opción de poder concedérselos a otros usuarios.

GRANT CREATE ANY TABLE,CREATE USER TO USU5 WITH ADMIN
OPTION;
GRANT ALTER TABLESPACE, CREATE TABLESPACE TO USU5;

16. Conceder a todos los usuarios de la BD privilegios para que puedan modificar las columnas DIRECCION y TELEFONO de las tablas CLIENTE y PROVEEDOR.

GRANT UPDATE(DIRECCION, TFNO) ON CLIENTE TO PUBLIC;
GRANT UPDATE(DIRECCION, TFNO) ON PROVEEDOR TO PUBLIC;

17. Quitar a los usuarios USU3 y USU4 todos los privilegios que tenían asignados.

REVOKE ROL_USU FROM USU3;
REVOKE ALL ON CLIENTE FROM USU4;
REVOKE ALL ON PROVEEDOR FROM USU4;
o
REVOKE ALL PRIVILEGES FROM USU4;  // Per als privilegis de sistema.

18. Comprovar la limitació de recursos. En cas de que no estigui activa, activar-la.

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';

ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';

19. Hacer que USU5 sólo pueda conectarse en dos sesiones concurrentes a la vez.

CREATE PROFILE LIM_SESION LIMIT SESSIONS_PER_USER 2;
ALTER USER USU5 IDENTIFIED BY USU5 PROFILE LIM_SESION;

20. Limitar el tiempo de conexión a la BD a 5 minutos a los usuarios USU2 y USU3.

CREATE PROFILE TIEMPOLIMITE LIMIT CONNECT_TIME 5;
ALTER USER USU2 IDENTIFIED BY USU2 PROFILE TIEMPOLIMITE;
ALTER USER USU3 IDENTIFIED BY USU3 PROFILE TIEMPOLIMITE;