Administración de BBDD (WORKBENCH)

Este pequeño cuestionario de pruebas esta especialmente pensado para MySQL Workbench, pero también puede ser usado con comandos similares en PostgreSQL y/o PhpMyAdmin

1.Desde un usuario con privilegios(root) . Haz que el usuario us2 solo se pueda conectar desde la máquina en la que estas y a la base de datos bdprova. También, solo podrá utilizar las ordenes: SELECT, INSERT i LOCK TABLES:
Desde root, vamos a la pestaña de users & privileges, y clicamos en el usuario usu2.
Vamos a schema privileges y a add entry , le ponemos los datos que requiere: el host Localhost ,en Schema (bdprova) y los Privilegios se ponen desde la pestaña inferior. En Object rights marcamos: SELECT e INSERT. Y en Other Rights: LOCK TABLES.
2.Qué privilegios tiene SecurityAdmin? Qué privilegios se activan al escoger esta opción?
Se activan los privilegios “CREATE USER, GRANT OPTION, RELOAD y SHOW DATABASES".
3.Qué diferencias hay entre DBManager y BackupAdmin?
DBManager tiene todo tipos de derechos para ejercer sobre la administración en todas las bases de datos. BackupAdmin por otro lado, solo tiene el mínimo de derechos para hacer un backup (copia de seguridad) de cualquier base de datos.
En la siguiente imagen veréis la pantalla para establecer los permisos deseados al usuario seleccionado.Si los permisos no están establecidos solo podrá hacer uso de las bases de datos

Como veis en la imagen de a bajo lo hemos configuradop ara que podamos conectar desde cualquier host y a cualquier base de datos.

5. Haz el usuario/a “a3@%” con los privilegios que necesite y limita el número de consultes a 2 por hora. Después conecta con este usuario y prueba a hacer 3 consultas seguidas.
Con un límite de 2 consultas por hora ,cuando hacemos la tercera consulta en menos de una hora , nos dice que hemos sobrepasado el límite de consultes.
6.¿Como se actualizan las tablas de privilegios?
Con el comando, mediante terminal: Flush_privileges .
7.Cuál o cuales privilegios son necesarios para cambiar la contraseña de los usuarios/as? Pon de nueva contraseña del usuario3 “usuario3usuario3” utilitzando mysqladmin y la orden SET. Escribe las ordenes .
El privilegio será: Grant options ,cuando se hace en el usuario que queremos que dé privilegios.

SET PASSWORD

mysql> UPDATE mysql.user SET Password=PASSWORD('usuario3usuario3')
    -> WHERE User='usuario3';
mysql> FLUSH PRIVILEGES;

8.Es posible guardar la contraseña de administrador en el archivo de configuración? Dónde?
Es posible modificando el archivo: my.cnf: sudo nano /etc/myslq/my.cnf de Ubuntu.
En windows modificaríamos el my.ini que esta en el directorio de la instalación.
Hay que añadir estas lineas en el apartado cliente:

[client]
user = nombredeusuario
password = passworddeusuario
port = 3306
host = localhost

9.Desde administrador revoca todos los permisos para el usuario3 (que se quede solo con USAGE).
REVOKE ALL PRIVILEGES ON *.* FROM 'usuario3'@'%'; GRANT USAGE ON *.* TO 'usuario3'@'%';
10.Escribe la orden para mostrar los permisos de usuario3.
SHOW GRANTS FOR usuario3;
ROLLBACK / COMMIT
Al hacer una consulta o transacción en MySQL, se suele empezar con un BEGIN, y acabar con un COMMIT para guardar las ordenes o ROLLBACK para deshacer y no guardar.
11.Como se comprueba si autocommit esta activado? Desactivalo.
Para desactivar el autocommit hemos de hacer :
SET AUTOCOMMIT= 0;
Pero eso se suele hacer cuando usamos tables transicionales, es decir con motor de almacenamiento InnoDB o BDB.
MyISAM, el que estamos utilitzando en esta tabla, es un motor no transicional.
O Sino hacer uso del comando START TRANSACTION + comandos a ejecutar. En este caso no se ejecutará el autocommit, pero solo en la primera acción.
12.Si quieres hacer una transacción con autocommit activado, pero no lo quieres desactivar, como la haces?
Con el comando START TRANSACTION. En vez de BEGIN. I después , añadimos el resto de comandos+ COMMIT para finalizar y que se ejecute o ROLLBACK para que no se ejecute.
13.Cuál es la orden para cambiar una tabla MyIsam a InnoDB?
ALTER TABLE nombretabla ENGINE=InnoDB;
14.Diseña una transacción que ejecutarás desde la línea de ordenes.
Ponemos el Autocommit a 0: SET AUTOCOMMIT =0;
Después ejecutamos la transacción:

BEGIN;
SELECT * FROM Molinero_myisam WHERE ASIGNATURA ='BDD';
COMMIT;

15.Crear una vista a partir de una tabla ya creada.

USE dbuoc;
CREATE VIEW clients_moli AS SELECT nom_cli, nif,telefon FROM clients;

16.Haz un SELECT donde se vean todos los registros de la tabla virtual.
SELECT * FROM clients_moli;
17.Modifica la vista para incluir o excluir algún campo más.

USE dbuoc;

ALTER VIEW clients_moli AS SELECT  codi_cli,nom_cli, nif, telefon, ciutat FROM clients;

18.borra la vista DROP VIEW clients_moli;
CREAR DISPARADOR (TRIGGER):
crear disparador que se ejecute al insertar un empleado y que actualice el presupuesto sumándolo al sueldo del nuevo empleado.

DROP TRIGGER  Dbuoc IF EXIST ;
DELIMITER |

CREATE TRIGGER  Dbuoc  BEFORE INSERT ON departamentos
FOR EACH ROW
BEGIN
     
   SET  new.empleado= (SELECT sueldo FROM empleats);

   UPDATE  presupuesto SET new.presupuesto= (SELECT sueldo + presupuesto  FROM empleados,departamentos )
  
END ;
 |


DELIMITER ;

SELECT * FROM departamentos;

añadir nuevo empleado en la tabla empleados: INSERT INTO empleados VALUES ('18','desiree', 'moli','3000','DIR','Barcelona','1');

20.Hacer un disparador que se ejecute al borrar un empleado y que actualice el presupuesto del departamento restando el sueldo.

DROP TRIGGER IF EXISTS   Dbuoc2 ;
DELIMITER
|
CREATE TRIGGER  Dbuoc2
BEFORE DELETE ON empleados  
FOR EACH ROW
 UPDATE departamentos SET  presupuesto=presupuesto -  old.sueldo WHERE  old.nom_dpt=departamentos.nom_dpt AND old.ciudad_dpt=departamentos.
ciudad_dpt;
|
DELIMITER

SELECT * FROM departamentos;

21.Escribe un procedimiento que es llame proc_apellido, donde apellido es tu apellido, que muestre de todos los clientes de la ciudad que le pones como un parametro, su nombre, su DNI y su núm. de teléfono. Hazlo utilizando parámetros siempre que se pueda. Si no hay ningún cliente, ha de mostrar: “En aquesta trista ciutat no hi ha cap client...encara!”. Ejecuta el procedimiento para la ciudad de Girona.

DROP PROCEDURE IF EXISTS PROC_MOLI;
DELIMITER |
create procedure PROC_MOLI (IN ciudad VARCHAR(50))
Begin
  IF (SELECT nom_cli FROM clientes WHERE  ciudad=ciudad) IS NULL THEN
     SELECT CONCAT ('EN LA CIUDAD', ciudad, 'NO HAY CLIENTES') AS ciudad;
   ELSE
     SELECT nom_cli,nif,telefon FROM clientes WHERE  ciudad=ciudad;
  END IF;
END;
|
DELIMITER

CALL PROC_MOLI (@w);

22.Escribe una función que se llame “func_apellido”, donde apellido es tu apellido, que devuelva la fecha de inicio del proyecto del empleado que cobra más. Utiliza la función MAX y las variables que necesites.

DROP FUNCTION IF EXISTS FUNC_MOLI;
  DELIMITER |

CREATE FUNCTION  func_moli ( )   RETURNS  DATE
-RETURN  (SELECT data_inici FROM proyectos INNER JOIN empleados ON
proyectos.codi_proj=empleados.num_proj WHERE  codi_empl=(SELECT codi_empl
FROM empleados WHERE sueldo= (SELECT max(sueldo) FROM empleados)));
|
DELIMITER
SELECT FUNC_MOLI();

Para más info suscribete a Desy Repara