Gestió d'usuaris

MySQL emmagatzema en les taules de la base de dades (BD) mysql la informació corresponents als usuaris.

La comanda CREATE USER crea nous usuaris MySQL.

Per utilitzar-la, cal tenir el permís global CREATE USER o el permís INSERT a la base de dades mysql. Per a cada compte nou, CREATE USER crea una nova fila a la taula mysql.user sense privilegis específics. Es produeix un error si el compte ja existeix.

El compte pot tenir opcionalment una contrasenya amb la clàusula IDENTIFIED BY.

ESTRUCTURA

CREATE USER nom_user [IDENTIFIED BY [PASSWORD] 'password'] [, nom_user [IDENTIFIED BY [PASSWORD] 'password']] ...

 Exemple: CREATE USER ‘usuari_prova'@'localhost' identified by 'pwd';

Per eliminar: DROP USER nom_user [, nom_user] ...

Un compte està format per un 'nom d'usuari' i un 'nom de host', units per '@'. El nom d'usuari és imprescindible però el nom del host pot ser: una direcció IP, nom de servidor,.. local 'localhost‘, qualsevol màquina '%'.

Si només s'especifica la part del 'nom d'usuari' al compte, s'assigna com a nom de host '%' . Què és el mateix que 'usuari'@'%’

La sentència GRANT permet als administradors de sistemes crear comptes d'usuari de MySQL i concedir drets a aquests, en una sola sentència.

ESTRUCTURA

GRANT <privilegis> ON <objecte> TO <usuaris> 

[WITH GRANT OPTION];

Per veure els permisos que té un compte, utilitzeu SHOW GRANTS

1. Exemples de privilegis poden ser:
   1. ALL PRIVILEGES: tots els privilegis sobre l'objecte especificat.
   2. SELECT: [(columnes)]: consultes. Es pot concretar a quines columnes.
   3. INSERT: insercions.
   4. UPDATE [(columnes)]: modificacions. Es pot concretar a quines columnes.
   5. DELETE: esborrats.
2. Exemples d'objecte poden ser a una taula o una vista.
3. Usuaris pot ser tothom: PUBLIC, o bé una llista dels usuaris que volem autoritzar.
4. L'opció WITH GRANT OPTION permet que l'usuari que autoritzem pugui, al seu torn, autoritzar d'altres usuaris a autoritzar aquell privilegi.

Per utilitzar GRANT, cal tenir el permís GRANT OPTION, i els privilegis que s’estiguin atorgant.

Amb REVOKE podem eliminar els privilegis del compte.

REVOKE [GRANT OPTION FOR] <privilegis> ON <objecte> FROM <usuaris> [RESTRICT|CASCADE];

1. Privilegis, objecte i usuaris són els mateixos que per a la sentència GRANT.
2. L'opció GRANT OPTION FOR s'usaria en el cas que volguéssim desautoritzar el dret a autoritzar (WITH GRANT OPTION).
3. RESTRICT / CASCADE
   • L'opció CASCADE fa que, si un usuari que hem autoritzat, n'ha autoritzat al seu torn d'altres, que alhora poden haver fet més autoritzacions, quedin desautoritzats tots de cop.
   • L'opció RESTRICT no ens permet desautoritzar un usuari sí aquest n'ha autoritzat d'altres.

• MySQL no elimina automàticament cap permís si s'esborra una base de dades o taula.
• REVOKE no treu les entrades de la taula mysql.user, és a dir, no s'esborra l'usuari!!! Cal fer ús de DROP USER o DELETE per esborrar-lo.

PRIVILEGIS GLOBALS

Els permisos globals s'apliquen a totes les bases de dades (i en totes les taules de les bases de dades) en un servidor determinat. Per assignar privilegis global, utilitzeu la sintaxi ON *.

Altres permisos poden donar-se globalment o nivells més específics. Els permisos globals s'emmagatzemen a la taula mysql.user.

GRANT ALL ON *.* TO 'usuari'@'host';

REVOKE ALL ON *.* TO 'usuari'@'host';

GRANT SELECT, INSERT ON *.* TO 'usuari'@'host';

Privilegis de base de dades

Els privilegis de base de dades s'apliquen a tots els objectes en una base de dades donada. Per assignar privilegis a nivell de base de dades, utilitza ON db_name .*

Els privilegis de base de dades s'emmagatzemen en les taules mysql.db i mysql.host. GRANT i REVOKE afecten la taula db, però no la taula de hosts, que s'utilitza molt poc. 

GRANT ALL ON mydb.* TO 'usuari'@'host';

GRANT SELECT, INSERT ON mydb.* TO 'usuari'@'host';

Privilegis de taula

Els permisos de taula s'apliquen a totes les columnes d'una taula donada.

Per assignar privilegis a nivell de taula, utilitzeu la sintaxi db_name.tbl_name

Si especifiqueu tbl_name en lloc de db_name.tbl_name, la declaració s'aplica a tbl_name a la base de dades per defecte.

Els permisos de taula s'emmagatzemen a la taula mysql.tables_priv.

GRANT ALL ON mydb.mytbl TO 'usuari'@'host';

GRANT SELECT, INSERT ON mydb.mytbl TO 'usuari'@'host';

Privilegis de columna i de rutina

Els permisos de columna s'apliquen a columnes en una taula donada. Cada privilegi atorgat a nivell de columna ha de ser seguit de la columna o columnes, tancada entre parèntesis.

Els valors permissibles per priv_type una columna (és a dir, quan s'utilitza una clàusula column_list) són inserir, seleccionar i actualitzar.

Els permisos de columna s'emmagatzemen a la taula mysql.columns_priv.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'usuari'@'host';

També hi ha privilegis referents als procediments emmagatzemats i es desen a mysql.procs_priv

CONTRASENYES

En la clàusula IDENTIFIED BY, la contrasenya s'ha de donar com un literal.

No cal utilitzar la funció PASSWORD ()

GRANT ... IDENTIFIED BY 'mypass';

Si no desitja enviar la contrasenya en text clar i sap el valor hash que PASSWORD () retornaria per al pas a la paraula, podeu especificar el valor hash precedit per la paraula clau PASSWORD.

GRANT ... IDENTIFIED BY PASSWORD

'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

Clausula WITH

La clàusula WITH s'utilitza per a diversos propòsits:

• Per a permetre a un usuari atorgar privilegis a altres usuaris
• Per especificar les limitacions d'ús de recursos d'un usuari
• Per quan i com l'usuari ha d'utilitzar connexions segures amb el servidor

La clàusula WITH GRANT OPTION li dóna a l'usuari la capacitat de donar a altres usuaris els privilegis que l'usuari té en el nivell especificat. Cal anar amb compte a qui li donen aquest privilegi, pel fet que dos usuaris amb diferents privilegis poden ser capaços de combinar-se els privilegis!

No es pot donar a un altre usuari un privilegi no es té.

Suposem que atorgueu a un usuari el privilegi INSERT a una base de dades. Si a continuació, li donem el permís SELECT a la base de dades i s'especifica WITH GRANT OPTION, l'usuari pot donar a altres usuaris no només el permís SELECT, sinó també INSERT.

MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, i MAX_CONNECTIONS_PER_HOUR limiten el nombre de consultes, actualitzacions, i inicis de sessió d'un usuari pot realitzar durant qualsevol període d'una hora. Si count és 0 (el per defecte), això significa que no hi ha cap limitació per a aquest usuari.

MAX_USER_CONNECTIONS limita el nombre màxim de connexions simultànies que el compte pot fer. Si count és 0 (per defecte), la variable sistema max_user_connections determina el nombre de connexions simultànies per al compte.

REQUIRE NONE indica que el compte no té requeriments de SSL o X509.

Aquesta és l'opció per defecte. Les connexions sense xifrar es permeten si el nom d'usuari i contrasenya són vàlids. Es poden xifrar les connexions, a elecció del client, si el client té el certificat correcte i els arxius clau. És a dir, el client no necessita especificar les opcions de comandes SSL, i en aquest cas serà la connexió sense xifrar. Per utilitzar una connexió encriptada, s'ha d'especificar l'opció - ssl-ca, o les tres de la - SSL-ca, - ssl-key, i - ssl-cert.

Renombrar usuari

RENAME USER old_user TO new_user [, old_user TO new_user] ...

Tanmateix no migra els objectes ni els privilegis

Per canviar la contrasenya:

SET PASSWORD [FOR user] = { PASSWORD('some password') | OLD_PASSWORD('some password') | 'encrypted password'}

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
UPDATE mysql.user SET PASSWORD = PASSWORD (‘nova_pwd’);
Canvi del password pels comptes anònims:

shell> mysql -u root

mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

EXEMPLES

$> mysql – u root – p

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost'

GRANT USAGE ON *.* TO 'dummy'@'localhost'; 

Dos dels comptes tenen un nom d'usuari de monty i una contrasenya de some_pass. Ambdós comptes són comptes de root amb plens permisos per fer qualsevol cosa. Un compte ('monty' @ 'localhost') pot utilitzar-se només quan es connecti des de l'equip local. L'altra ('monty'@'%') es pot utilitzar per connectar-se des de qualsevol altre equip.

Un compte té un nom d'usuari de admin i no té contrasenya. Aquest compte es pot utilitzar només des de l'equip local. Té els privilegis administratius RELOAD i PROCESS. Aquests permeten a l'usuari admin executar les ordres mysq-ladmin reload, mysqladmin refresh, i mysqladmin flush-xxx, així com mysqladmin processlist. No es donen autorització per accedir a cap base de dades.

Un compte té un nom d'usuari sense contrasenya. Aquest compte es pot utilitzar només des de l'equip local. No té cap privilegi. El permís USAGE a la comanda GRANT permet crear un compte sense donar-li cap privilegi. S'assumeix que s'atorgaran privilegis específics posteriorment.

Canvis en els privilegis

Quan Mysqld s’inicia, tots els continguts de les taules grant es llegeixen i es fan efectius en el control d'accés.

Quan el servidor càrrega les taules grant, els privilegis per les connexions dels clients existents, es veuen afectades de la següent manera:

• Els canvis en els privilegis de taula i columna, són efectius en la següent petició del client.
• Els canvis en els privilegis de la base de dades, son efectius en la següent sentencia USE db_name.
• Els canvis de privilegis globals i les claus d'accés tenen efecte la propera vegada que el client es connecta.

Si es modifiquen les taules grant utilitzant GRANT, REVOKE, o SET PASSWORD, el servidor se’n adona dels canvis i recarrega les taules grant en la memòria immediatament.

Si es modifiquen les taules grant directament utilitzant INSERT, UPDATE, o DELETE, els canvis no tindran efecte fins que es reinicií el servidor, o se li comuniqui que ha de recarregar les taules.

Per recarregar les taules manualment, cal executar la comanda FLUSH PRIVILEGES o les comandes mysqladmin flushprivileges o mysqladmin reload.

Sinó es recarreguen les taules manualment, els canvis no seran efectius fins a la propera connexió.

ACTIVITATS

Script empresa.

1. Crea un usuari de nom ALUMNE i amb contrasenya ALUMNE
   CREATE USER 'ALUMNE'@'localhost' identified by 'ALUMNE';
   
   
2. L'usuari ALUMNE intenta accedir a alguna taula de la BD EMPRESA, pot fer-ho?
   No, sortirá error perque no tenim permis del creador de la BD, el root.
3. Permet que ALUMNE seleccioni les dades de la BD empresa.
   GRANT SELECT ON empresa.* TO ALUMNE@'localhost';
4. L'usuari ALUMNES intenta esborrar, modificar, i inserir dades a PRODUCTE, pot fer-ho? Permet-li
   INSERT INTO empresa.PRODUCTE (PROD_NUM, DESCRIPCIO)
   VALUES (100866, 'ACE TENNIS RACKET II');
   
   UPDATE empresa.PRODUCTE
   SET DESCRIPCIO='ACE TENNIS RACKET III'
   WHERE PROD_NUM=100866;
   
   DELETE FROM empresa.PRODUCTE
   WHERE PROD_NUM=100866;
   
   No ho pot fer perque no te els permisos necesaris, per donar-los:
   
   GRANT INSERT, UPDATE, DELETE ON empresa.* TO 'ALUMNE'@'localhost';  (amb l'usuari root user)
5. L'usuari ALUMNE intenta crear la taula VENTES amb 2 camps (escull 2 columnes), pot fer-ho? Permet que l'usuari creï la taula anterior i insereixi 3 tuples (pot inserir? Si no, permet-li)
   No ho pot fer, per fer-ho necesitem donarli a l'usuari (amb root):
   GRANT CREATE ON empresa.* TO 'ALUMNE'@'localhost';
   CREATE TABLE VENTES(
       DNI VARCHAR(10),
       Nom VARCHAR(20),
       Cognom VARCHAR(20),
       CONSTRAINT PRIMARY KEY (DNI)
   );
   
   INSERT INTO VENTES VALUES('53666748-N','Francisco','Mece');
   INSERT INTO VENTES VALUES('53666738-N','Francisca','Mici');
   INSERT INTO VENTES VALUES('53666338-N','Franciscu','Moci');
6. L'usuari ALUMNE intenta crear un nou usuari anomenat "PROFE", pot fer-ho?
   No, perque no te permís de CREATE USER.
7. Fes que l'usuari ALUMNE pugui crear l'usuari "PROFE" amb contrasenya "PROFE" i comprova-hoGRANT CREATE USER ON *.* TO 'ALUMNE'@'localhost'; (amb root)
   
   CREATE USER 'PROFE'@'localhost' identified by 'PROFE'; (amb alumne)
8. L'usuari ALUMNE dóna privilegis totals sobre la taula 'VENTES' a l'usuari PROFE. Comprova-ho. Pot esborrar-la?
   No deixa donar privilegis perque el grant option no esta activat, per activar-lo tindrem que posar:
   GRANT GRANT OPTION ON empresa.VENTES TO 'ALUMNE'@'localhost'; (amb l'usuari root)
   
   No podrá borrarla perque PROFE no te privilegis.
   
   Tot i això, no podrem permetre privilegis totals degut a que no tenim aquesta.
   
   GRANT SELECT, INSERT, UPDATE, DELETE ON empresa.* TO 'PROFE'@'localhost';  (això si podrem perque tenim aquests permisos.)
9. L'usuari ALUMNE crea un nou usuari (CAP/CAP)CREATE USER 'CAP'@'localhost' identified by 'CAP';
10. L'usuari ROOT li dóna a l'usuari ALUMNE el privilegi de donar privilegis a nivell de la BD de EMPRESA
    GRANT GRANT OPTION ON EMPRESA.* TO 'ALUMNE'@'localhost';
11. L'usuari ROOT li dóna a l’usuari CAP tots els privilegis sobre la taula VENTES.
    GRANT ALL PRIVILEGES ON empresa.VENTES TO 'CAP'@'localhost'; (with root user)  (Si ponemos --all tambien vale.)
12. L'usuari PROFE intenta passar el privilegi de seleccionar i modificar dades de la taula VENTES a l'usuari CAP, pot fer-ho?
    No, perque no te el grant option, en tot cas, seria:
    GRANT SELECT,UPDATE,INSERT,DELETE ON empresa.VENTES TO 'CAP'@'localhost';
13. L'usuari ALUMNE fa el necessari per què l'usuari PROFE pugui passar el privilegi anterior a l'usuari CAP
    GRANT GRANT OPTION ON empresa.VENTES TO 'PROFE'@'localhost'; (with the user ALUMNE)
    GRANT SELECT,UPDATE,INSERT,DELETE ON empresa.VENTES TO 'CAP'@'localhost'; (with the user PROFE)
14. L'usuari ALUMNE vol retirar el privilegi d'esborrar dades d’una taula de la BD empresa (pex CLIENTS) a l'usuari CAP, pot fer-ho?
    No pot fer-ho perque l'usuari cap no te cap privilegi sobre la taula de la BD empresa CLIENTS, en tot cas, seria:
    REVOKE DELETE ON empresa.CLIENT FROM 'CAP'@'localhost';
15. L'usuari ALUMNE vol retirar el privilegi de seleccionar dades de ventes a l'usuari CAP, pot fer-ho?
    Si pot.
    REVOKE SELECT ON empresa.VENTES FROM 'CAP'@'localhost';
16. L'usuari ALUMNE retira el privilegi de passar el privilegi de la taula VENTES a l'usuari PROFE. Pot fer-ho? L'usuari CAP pot continuar seleccionant les dades?
    REVOKE GRANT OPTION ON empresa.VENTES FROM 'PROFE'@'localhost'; (amb l'usuari ALUMNE)
    No, no pot, perque en exercicis anteriors ja l'hem tret.
17. No permetre modificar la columna codi_client (per exemple) de la taula clients (per exemple) a l'usuari CAP. Comprovar-ho
    REVOKE UPDATE(CLIENT_COD),INSERT(CLIENT_COD) empresa.CLIENT FROM 'CAP'@'localhost'; (amb l'usuari PROFE)
18. Mostrar els permisos que tenen els diferents usuaris.
    GRANT GRANT OPTION ON empresa.* TO 'ALUMNE'@'localhost';

EXERCICIS DE VISTES I PRIVILEGIS

Script empresa.

1. L’usuari ROOT crea una primera vista que permeti veure a la taula departaments tots els camps menys la Localitat.CREATE USER 'ALUMNE1'@'localhost' identified by 'ALUMNE1';
   
   use empresa; -- departaments es dept.
   
   CREATE VIEW depcamps AS
   SELECT d.DEPT_NO, d.DNOM
   FROM DEPT d;
   
2. L’usuari ROOT crea una segona vista que permeti veure el nom dels clients, el seu codi I el numero de comanda.
   CREATE VIEW cli AS
   SELECT c.NOM,c.CLIENT_COD,co.COM_NUM
   FROM CLIENT c NATURAL JOIN COMANDA co;
   
3. L’usuari ROOT permet control total a l’usuari ALUMNE sobre la primera vista.
   GRANT ALL ON depcamps TO 'ALUMNE1'@'localhost';
4. Comprova si l’usuari ALUMNE pot modificar el nom del clients amb la primera vista.
   UPDATE depcamps
   SET DNOM="I+D"
   WHERE DNOM="INVESTIGACIÓ";
   
   Si pot.
5. L’usuari ROOT permet seleccionar i inserir a l’usuari ALUMNE sobre la segona vista.
   GRANT INSERT,SELECT ON cli TO 'ALUMNE1'@'localhost';
6. Comprova si l’usuari ALUMNE pot modificar el nom dels clients en la segona vista i/o el seu codi.
   UPDATE cli
   SET NOM="EVERY_MOUNTAIN"
   WHERE NOM="EVERY MOUNTAIN";
   
   UPDATE cli
   SET CLIENT_COD="99"
   WHERE CLIENT_COD="102";
   
   No podrá, no te permisos UPDATE.
7. L’usuari ROOT crea una tercera vista que permeti veure tots els camps de la taula CLIENT, però, només els que no són de Santa Clara.
   CREATE VIEW clisanta AS
   SELECT *
   FROM CLIENT 
   WHERE CIUTAT!="SANTA CLARA";
8. L’usuari ROOT crea una quarta vista basada en la tercera que permeti veure tots els camps, però, només els de la ciutat Cupertino.
   CREATE VIEW cuper AS
   SELECT *
   FROM clisanta 
   WHERE CIUTAT="CUPERTINO";
9. L’usuari ROOT permet seleccionar i inserir a l’usuari ALUMNE sobre la tercera i quarta vista.
   GRANT INSERT,SELECT ON clisanta TO 'ALUMNE1'@'localhost'; 
   GRANT INSERT,SELECT ON cuper TO 'ALUMNE1'@'localhost';
10. Comprova si l’usuari ALUMNE pot modificar el nom dels clients amb la tercera vista.
    UPDATE clisanta
    SET NOM="TKB_SPORT_SHOP"
    WHERE NOM="TKB SPORT SHOP";
    NO podrá, no s'han donat permisos d'UPDATE (modificar).
11. Dona-li permisos per a què sigui possible.
    GRANT UPDATE (NOM) ON clisanta TO 'ALUMNE1'@'localhost';
    UPDATE clisanta
    SET NOM="TKB_SPORT_SHOP"
    WHERE NOM="TKB SPORT SHOP";
    Ara será posible

EL SISTEMA DE PRIVILEGIS D'ACCÉS

1. Connecteu-vos com a root. Mostreu tots els usuaris que hi ha en el SGBD actualment.
   SELECT User FROM mysql.user GROUP BY user;
   
2. Creació d'un DBA, anomenat dba1. Amb permisos per a fer qualsevol cosa. dba1 ha de poder connectar-se desde qualsevol màquina, apart de la pròpia màquina. Tingueu en compte que el compte anònim per defecte per a localhost té preferència en el sistema d'autenticació.CREATE USER 'dba1'@'localhost' identified by 'dba1';
   CREATE USER 'dba1'@'%' identified by 'dba1';
   GRANT ALL PRIVILEGES ON *.* TO 'dba1'@'%';
   GRANT ALL PRIVILEGES ON *.* TO 'dba1'@'localhost';
   GRANT GRANT OPTION ON *.* TO 'dba1'@'%';
   GRANT GRANT OPTION ON *.* TO 'dba1'@'localhost';
3. Connecteu-vos com dba1 i verifiqueu que podeu accedir al catàleg de MySQL. Mostreu els privilegis que té assignats dba1@localhost i l'usuari.mysql -u dba1 -p
   show databases;
   (per als privilegis:) show grants;
   (per veure l'usuari actual: ) select current_user;
4. Connectats com dba1 creeu una base de dades anomenada etpc. Podeu sempre saber quin es l'usuari amb el que esteu treballant des del client amb la sentència “select current_user”;
   CREATE SCHEMA etpc;
   select current_user;
5. Creació de l'usuari1. Aquest usuari només es pot connectar des de la mateixa adreça de xarxa on està el servidor. La contrasenya serà usuari1. Li assignem el privilegi global de creació. Tingueu en compte que el compte anònim per defecte per a localhost té preferència en el sistema d'autenticació, per la qual cosa caldrà crear un usuari usuari1 per a localhost. NOTA: utilitzeu la adreça de xarxa '192.168.1.0/255.255.255.0'.
   use etpc;
   CREATE USER 'usuari1'@'localhost' identified by 'usuari1';
   CREATE USER 'usuari1'@'192.168.1.0/255.255.255.0' identified by 'usuari1';
   GRANT CREATE ON etpc.* TO 'usuari1'@'localhost';
   GRANT CREATE ON etpc.* TO 'usuari1'@'192.168.1.0/255.255.255.0';
   
6. Creació de l'usuari2. Aquest usuari només es pot connectar des de la IP de classe C 192.168.1.10. Inicialment no assignarem cap contrasenya d'accés. No li assignem cap permís, només el de connexió. Tingueu en compte que el compte anònim per defecte per a localhost té preferència en el sistema d'autenticació, per la qual cosa caldrà crear un usuari usuari2 per a localhost.
   use etpc;
   CREATE USER 'usuari2'@'localhost';
   CREATE USER 'usuari2'@'192.168.1.10';
   GRANT USAGE ON etpc.* TO 'usuari2'@'localhost';
   GRANT USAGE ON etpc.* TO 'usuari2'@'192.168.1.10';
7. Assignació/canvi de la contrasenya de l'usuari2.
   SET PASSWORD FOR 'usuari2'@'localhost' = PASSWORD('usuari2');
   SET PASSWORD FOR 'usuari2'@'192.168.1.10' = PASSWORD('usuari2');
8. Verifiqueu els usuaris creats (host i user), els passwords assignats, i la base de dades on estan els usuaris.
   select user,host,db from mysql.db;
   select user,Password from mysql.user;
   select m.user,m.host,m.db,my.password from mysql.db m RIGHT OUTER JOIN mysql.user my using(User);
9. Com a usuari1 creeu una taula anomenada ASI2 a la base de dades ETPC.
   create table ASI2(
       Nom char(10)
   );
10. Com a usuari2 creeu una taula anomenada ASI1 a la base de dades ETPC. Que és el que no funciona?
    Que només tenim accés per poder veure la bd, no per fer modificacions ni crear taules.

PREGUNTES TEORIA

1. Diferencia entre Restrict / CascadeL'opció CASCADE fa que, si un usuari que hem autoritzat, n'ha autoritzat al seu torn d'altres, que alhora poden haver fet més autoritzacions, quedin desautoritzats tots de cop.
   L'opció RESTRICT no ens permet desautoritzar un usuari sí aquest n'ha autoritzat d'altres.
2. Quina comanda crea i dona permisos a la vegada?GRANT
   
3. Defineix tres formes de crear un usuari. Com s’esborra un usuari? I com li trèiem els privilegis?
   
   Amb la sentencia GRANT
   GRANT SELECT, INSERT ON test.* TO 'adolfo'@'localhost' IDENTIFIED BY 'pass_adolfo';
   
   
   Amb la sentencia create user
   CREATE USER ‘usuari_prova'@'localhost' identified by 'pwd';
   Insertant en la taula user
   INSERT INTO user VALUES ('localhost','mariano',PASSWORD('pass_mariano'),'Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);
   Per eiliminar a un usuari
   DROP USER nom_user [, nom_user] …
   Para eliminar privilegis
   REVOKE ALL ON BD.* from USER@localhost 
   1. Esborrar un usuari és el mateix que treure-li els privilegis?
      No es el mateix perque REVOKE no treu les entrades de la taula mysql.user, és a dir, no s'esborra l'usuari!!! Cal fer ús de DROP USER o DELETE per esborrar-lo.
4. Comprova si tens usuaris “anònims” dins del teu mysql.
   Select user from mysql.user;
5. En quina BD mysql emmagatzema la informació dels usuaris?
   mysql.user
6. Com veiem els permisos que té un usuari?Posant show grants;
1. Mira els teus permisos
   SHOW GRANTS;
7. Com pot un usuari atorgar els privilegis que té?Posant GRANT OPTION;
8. Quina diferencia hi ha entre els privilegis de taula i els privilegis de Base de dades?Que en la taula només tens accés a una taula I si tens de la base de dades pots accedir a totes les taules d'aquesta.
9. On es guarden els privilegis de columna?mysql.columns_priv;
10. Crea un usuari amb accés localhost amb contrasenya, comprova que pots entrar, canvia-li la contrasenya per una altra i comprova que pots entrar de nou.CREATE USER 'USUARI'@'localhost' identified by 'USUARI';
    mysql -u USUARI -p
    SET PASSWORD FOR 'USUARI'@'localhost' = PASSWORD('USUARIO');
11. Quin permís permet crear un usuari nou sense cap privilegi?El permís USAGE a la comanda GRANT permet crear un compte sense donar-li cap privilegi
12. Quan es recarreguen automàticament les taules de privilegis? Amb quina comanda ho podem forçar?
    Es recarreguen en la petició del client, per forçar-ho cal executar FLUSH PRIVILEGES.
13. Com està format un compte d’usuari?
    Per un usuari, un host i posiblement una contraseña. També podem trobar permisos o privilegis.
14. Per defecte, quan creem un usuari només amb el nom del user, amb quin Host es crea?
    Será % = 'usuari'@'%'
15. Per defecte, al entrar al mysql només amb el nom del user, amb quin Host entrem?
    Entrem com a localhost, tot i que després de realitzar unes proves, si la creació d'un usuari extern es fa amb el mateix nom que la d'un usuari intern i es crea abans, accedirem amb l'usuari extern.

Disparadors (triggers)

CONCEPTES BÀSICS

Un disparador és un objecte que s'asocia a una taula i s'activa quan succeeix alguna acció contra aquesta taula.

Exemple d'utilització

Un disparador està programat per disparar-se quan insertem una nota nova, diferent del rang (0-10). Al intentar insertar la nota 11, NO ens permetrà fer-ho.

Cada SGBD implementa els seus disparadors, en el cas de mysql tenim algunes restriccions:

• El nom del disparador és ÚNIC per a tot el sistema, no podem utilitzar un nom igual contra diferents taules.
• El disparador NO pot cridar a un procediment emmagatzemat (utilitzant la sentència CALL).
• No poden existir dos disparadors que s'executin contra una mateixa taula en el mateix moment, (però si en diferents moments).

En els disparadors, el moment en el que s'han d'executar, és molt important. Els disparadors s'executen ABANS (before) de fer l'acció o DESPRES (after) de fer l'acció.

EXEMPLE TRIGGER

El disparador s'encarrega d'acumular les ventes realitzades al llarg del dia. Esta associat a la taula comptes (num, quantitat).

mysql> CREATE TRIGGER disp1 BEFORE INSERT ON comptes FOR EACH ROW SET @sum=@sum+NEW.quantitat;

INSERT INTO comptes VALUES (1,100),(2,50);

El nom del trigger sera disp1, s'activarà abans de fer l'acció i el que fará es sumar cada venta a una variable global anomenada sum.

SENTÈNCIES

Hem vist en l'exemple anterior, que les columnes de la taula que estan associades al disparador, es poden referencia utilitzant els alias OLD i NEW.
OLD.nom_columna fa referència a una columna d'una fila existent, abans de ser actualitzada o esborrada.
NEW.nom_columna fa referència a una columna en una nova fila a punt de ser insertada, o en una fila existent desprès de ser actualitzada.

Eliminar un disparador

Per esborrar un disparador, és necessari el privilegi super 

DROP TRIGGER nombre_disp

Gestió dels errors

Durant l'execució dels disparadors, MySQL gestiona els errors de la següent manera:

• Si falla un disparador BEFORE, no s'executa l'operació.
• Un disparador AFTER s'executa solament sí el disparador BEFORE (en cas d’existir) i l’operació s’executa exitosament.
• Un error durant l’execució d’un disparador deriva en l’error de la sentencia que el va provocar.
• En taules transaccionals, l’error d’un disparador tindria que provocar la cancel·lació (rollback) de tots els canvis realitzats. En taules no transaccionals, qualsevol canvi realitzat abans de l’error no es tindria que veure afectat. (Una taula transaccional sería per exemple el procès de retirar diners dels caixers dels bancs)
  

Constructores de control de flujo

IF, LOOP, ITERATE, LEAVE, REPEAT/UNTIL y WHILE/DO.

La sentencia IF:

Qué es?

Es una declaración, también existe el if como función y no lo debemos confundir.

Sintaxis

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

Funcionamiento

IF implementa un constructor condicional básico. Si search_condition se evalúa a cierto, el comando SQL correspondiente “THEN” se ejecuta. Si no coincide ninguna search_condition, salta a la sucesión de ELSEIF/ELSE en caso de haberla.

Ejemplo

Crearemos un pequeño procedimiento. El siguiente ejemplo utiliza la sentencia IF para comprobar si un número es múltiplo de 100.

delimiter //

create procedure select_arbitre(in n int)

begin

if (mod(n,100) = 0) then

select n/100;

end if;

end //

La sentencia CASE:

Qué es?

El comando CASE para procedimientos almacenados implementa un constructor condicional complejo.

Sintaxis

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

Funcionamiento

Si una search_condition se evalúa a cierto, el comando SQL correspondiente se ejecuta. Si no coincide ninguna condición de búsqueda, el comando en la cláusula ELSE se ejecuta.

Nota: La sitaxis de un comando CASE mostrado aquí para uso dentro de procedimientos almacenados difiere ligeramente de la expresión CASE. El comando CASE no puede tener una cláusula ELSE NULL y termina con END CASE en lugar de END.

La sentencia LOOP:

Qué es?

Loop implementa un constructor de bucle simple que permite la ejecución repetida de comandos particulares.

Sintaxis

[begin_label:] LOOP

statment_list

END LOOP [end_label]

Funcionamiento

 

La ejecución del comando se repite hasta acabar el bucle, normalmente por el comando “Leave”.

Normalmente los comandos LOOP se etiquetan:

“end_label” no puede darse si no esta presente “begin_label” , y si ambos lo están, deben ser el mismo.

Normalmente se usa conjuntamente con los constructores “Leave”, para abandonar el bucle en caso de cumplirse una condición, y “Iterate” para volver a hacer el bucle si cumple una condición.

La sentencia LEAVE:

Qué es?

Esta instrucción es utilizada para salir de alguna estructura de control. Puede ser usada dentro de un BEGIN ... END o dentro de algún ciclo (Bucles).

Sintaxis

label1: LOOP

SET p1 = p1 + 1;

IF p1 < 10 THEN

ITERATE label1;

END IF;

LEAVE label1; ---> Aquí rompemos un Bucle

END LOOP label1;

Funcionamiento

 

Con este comando logramos romper el bucle y salir de él.

La sentencia ITERATE:

Qué es?

ITERATE significa “vuelve a hacer el bucle”.

ITERATE sólo puede aparecer en comandos LOOP, REPEAT, y WHILE .

Sintaxis

CREATE PROCEDURE doiterate(p1 INT)

BEGIN

label1: LOOP

SET p1 = p1 + 1;

IF p1 < 10 THEN

ITERATE label1;

Funcionamiento

Si se cumple la condición, el Iterate repite el loop.

La sentencia REPEAT/UNTIL

Qué es?

La sentencia REPEAT/UNTIL es un comando que repite el comando que se encuentra en su interior hasta que se cumple una condición.

Sintaxis

Esta sentencia está formada por dos parámetros básicos:

1. La sentencia o lista de sentencias que se han de repetir.

2. La condición de salida del bucle.

La sentencia ha de terminar con una sentencia END REPEAT;

Además la sentencia REPEAT puede estar etiquetada por una etiqueta de inicio (BEGIN) y una de final (END) iguales.

Funcionamiento

En esta sentencia se repite la lista de sentencias hasta que la condición es cierta. El bucle siempre entra por lo menos una vez y la lista de sentencias puede consistir de más de una, separadas por un punto coma (;) después de haber delimitado la //.

La sentencia WHILE/DO

Qué es?

La sentencia WHILE/DO es un comando que repite el comando que se encuentra en su interior mientras la condición del WHILE es cierta.

Sintaxis

[begin_label:]

WHILE search_condition DO

statement_list

END WHILE

[end_label]

Esta sentencia está formada por dos parámetros básicos:

1. La condición de permanencia en el bucle.

2. La sentencia o lista de sentencias que se han de repetir.

La sentencia ha de terminar con una sentencia END WHILE;

Además la sentencia WHILE/DO puede estar etiquetada por una etiqueta de inicio (BEGIN) y una de final (END) iguales.

Funcionamiento

En esta sentencia se repite la lista de sentencias mientras la condición es cierta. El bucle siempre entra por lo menos una vez y la lista de sentencias puede consistir de más de una, separadas por un punto coma (;) después de haber delimitado la //.

Además la sentencia WHILE/DO puede estar etiquetada por una etiqueta de inicio (BEGIN) y una de final (END) iguales.

Cursores

¿Qué es un cursor?

Un cursor permite acceder en tiempo real a los datos de cada fila de una consulta. Este mecanismo es de gran utilidad cuando vayamos a comunicar MySQL con aplicativos o realizar consultas complejas.

Declaración

DECLARE cursor_name CURSOR FOR select_statement

Ejemplo:

DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

Sentencia OPEN

Este comando abre un cursor declarado préviamente.

Sintaxi: OPEN cursor_name

Ejemplo: OPEN cur1;

Sentencia FETCH

La lectura de los resultados de un cursor se hace con el comando FETCH. Este nos permite acceder a la primera fila generada por la consulta. Si se vuelve a usar el cursor pasa a apuntar a la segunda fila, luego a la tercera y así sucesivamente hasta que el cursor no tenga resultados que referenciar.

Incluir el comando FETCH dentro de un bucle permite leer todos los resultados de un cursor. Cuando el cursor llegue al final de los resultados de la consulta, entonces el bucle termina. Pero terminar un bucle de este tipo necesita una condición de parada especial en MySQL.

Sintaxi:

FETCH cursor_name INTO var_name [, var_name] ...

Ejemplo:

FETCH cur1 INTO a, b; donde DECLARE a CHAR(16); DECLARE b,c INT

Sentencia CLOSE

Una vez leído todos los resultados del cursor, procedemos a cerrar y limpiar espacios de memoria con CLOSE.

Sintaxi:
CLOSE nombre_cursor;
Ejemplo:
CLOSE cur1; 

Ejemplo

BEGIN
DECLARE variable TIPOVARIABLE;
DECLARE error INT DEFAULT 0;
DECLARE cursor1 cursor for SELECT X FROM Y;
DECLARE CONTINUE handler for sqlstate '02000' set error = 1;

open cursor1;

repeat
fetch cursor1 into variable;
if not error then
[HACER ALGO]
end if;
until error end repeat;

Variables i Handlers

VARIABLES

Les variables serveixen per emmagatzemar informació de manera local.

• Es poden utilitzar per guardar constants o resultats de consultes.
• S’eliminen quan es reinicia el mysql.
• La definició és divideix en tres parts: Declare, Set i Select...Into.

DECLARE

• Ha d’estar dintre d’un bloc BEGIN...END.
• Serveix per a declarar una variable, com fem a altres llenguatges de programació.
• Sintaxi: DECLARE nom_variable tipus [DEFAULT valor]
• Si posem el DEFAULT podem instanciar una variable a la declaració.

SET

• La instrucció SET assigna un valor a una variable prèviament creada.
• Es poden guardar subconsultes com a valor de les variables.
• Sintaxi:
• SET var_name = expr [var_name = expr]

 SELECT ... INTO

• SELECT emmagatzema les columnes seleccionades en variables.
• Només es pot retornar un registre.
• Sintaxi:
• SELECT nom_columna INTO nom_variable

HANDLERS

CONCEPTE

Permet fer un tractament específic de certes condicions (normalment relacionades amb errors)

TIPUS

Per a un handler CONTINUE, continua la rutina actual després de l'execució de la comanda del

Per a un handler EXIT, acaba l'execució de la comanda compost BEGIN ... END actual.

El handler de tipus UNDO encara no suporta.

SINTAXIS

DECLARE condition_name HANDLER FOR condition_value acció

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

Procedimientos almacenados y funciones

Definición: Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor. Un procedimiento almacenado es un programa que se guarda físicamente en una base de datos. Su implementación varía de un gestor de bases de datos a otro. Este programa esta hecho con un lenguaje propio de cada Gestor de BD y esta compilado, por lo que la velocidad de ejecución es muy rápida.

Ventajas: El S.G.B.D. es capaz de trabajar más rápido con los datos que cualquier programa externo, ya que posee acceso directo a los datos a manipular y sólo necesita enviar el resultado final al usuario. Sólo realizamos una conexión al servidor y este ya es capaz de realizar todas las comprobaciones sin tener que volver a establecer una conexión. Podemos reutilizar el procedimiento y este puede ser llamado desde diferentes aplicaciones y lenguajes. Sólo lo programaremos una vez.

Desventajas: Los procedimientos almacenados se guardan en la BD por lo que si ésta se corrompe perderemos todos los procedimientos almacenados.

Utilidad de los procedimientos almacenados: Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas, pero necesitan realizar la misma operación en la base de datos. Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados para todas las operaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se loguea apropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos, sólo pueden ejectuar algunos procedimientos almacenados.

Procedmientos almacenados: Los procedimientos almacenados pueden mejorar el rendimiento ya que se necesita enviar menos información entre el servidor y el cliente. El intercambio que hay es que aumenta la carga del servidor de la base de datos ya que la mayoría del trabajo se realiza en la parte del servidor y no en el cliente.

Que contiene un Procedimiento Almacenado: Un nombre. Puede tener una lista de parámetros. Tiene un contenido (también llamada definición del procedimiento). Ese contenido puede estar compuesto por instrucciones sql, estructuras de control, declaración de variables locales, control de errores, etcétera.

Sintaxis de procedimientos almacenados: Los procedimientos almacenados y rutinas se crean con comandos CREATE PROCEDURE y CREATE FUNCTION. Una rutina es un procedimiento o una función. Un procedimiento se invoca usando un comando CALL , y sólo puede pasar valores usando variables de salida. Una función puede llamarse desde dentro de un comando como cualquier otra función (esto es, invocando el nombre de la función), y puede retornar un valor escalar. Las rutinas almacenadas pueden llamar otras rutinas almacenadas.

Estructura:

CREATE PROCEDURE sp_name ([parameter[,...]]) [characteristic ...] routine_body

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Parametros:

• IN: pasa un valor en un procedimiento. El procedimiento podría modificar el valor, pero la modificación no es visible para la persona que llama.
• OUT: Su valor inicial es NULL en el procedimiento, y su valor es visible para la persona que llama.
• INOUT: es inicializado por la persona que llama, puede ser modificada por el procedimiento, y cualquier cambio realizado por el procedimiento es visible para la persona que llama cuando el procedimiento retorna.

Exemple

delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM t;

END//

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

La sentencia ‘Show create procedure o function' retorna la cadena exacta que puede usarse para recrear la rutina nombrada.

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

Retorna características de rutinas, como el nombre de la base de datos, nombre, tipo, creador y fechas de creación y modificación. Si no se especifica un patrón, le lista la información para todos los procedimientos almacenados, en función del comando que use.

El comando CALL invoca un procedimiento definido préviamente con CREATE PROCEDURE.

CALL puede pasar valores al llamador usando parámetros declarados como OUT o INOUT.

La sentencia BEGIN ... END se emplea para delimitar subsentencias compuestas que aparecen en los procedimientos. Cada subsentencia dentro del BEGIN ... END debe acabar con el delimitador ‘ ; ’. Puede haber una sentencia BEGIN ... END sin subsentencias.

FUNCIONS

Què és CREATE PROCEDURE i CREATE FUNCTION?

Aquestes comandes crean una rutina enmagatzemada. Des de MySQL 5.0.3, per crear una rutina, és necessari tenir el permís CREATE ROUTINE, i els permissos ALTER ROUTINE i EXECUTE s’assignen automàticament al seu creador.

La comanda CREATE FUNCTION s’utilitza en versions anteriors de MySQL per soportar UDFs (User Defined Functions) (Funcions Definides per l’Usuari).

ALTER PROCEDURE I ALTER FUNCTION

Aquesta comanda pot utilitzar-se per canviar les característiques d’un procediment o funció emmagatzemada.

DROP PROCEDURE I DROP FUNCTION

Aquesta comanda s’utilitza per esborrar un procediment o funció emmagatzemada. Aixó és, la rutina especificada s’esborra del servidor. Ha de tenir el permís ALTER ROUTINE per les rutines fins MySQL 5.0.3. Aquest permís s’atorga automàticament al creador de la rutina.

La cláusula IF EXISTS és una extensió de MySQL. Evita que ocurreixi un error si la funció o procediment no existeix. Es genera una advertència que pot veure’s amb SHOW WARNINGS.

SHOW CREATE PROCEDURE I SHOW CREATE FUNCTION

Aquest comandament és una extensió de MySQL. Similar a SHOW CREATE TABLE, retorna la cadena exacta que pot utilitzar-se per recrear la rutina nombrada.

SHOW PROCEDURE STATUS I SHOW FUNCTION STATUS

Aquesta comanda és una extensió de MySQL. Retorna característiques de rutines, com el nom de la base de dades, nombre, tipus, creador i dates de creació i modificació. Si no s’especifica un patró, li llista la informació per tots els procediments emmagatzemats, en funció de la comanda que utilitzi.

LA SENTENCIA CALL

El comandament CALL invoca un procediment definit prèviament amb CREATE PROCEDURE.

CALL pot passar valors al cridador utilitzant paràmetres declarats com OUT o INOUT.

Tambè “retorna” el número de registres afectats, que amb un programa client pot obtenir-se a nivell SQL cridant la funció ROW_COUNT() i des de C cridant la funció de la API C mysql_affected_rows() .

BEGIN...END

Begin SQL es una paraula clau que permet indicar en el editor de métodes l’escomençament d’una sequencia de comandes SQL que ha de ser interpretada per la font de dades actual del procés.

Una secuencia de comandes SQL comença por Begin SQL y ha d’acabar amb la paraula clau End SQL.

Aquestas paraules clau funcionen d’aquesta forma:

• Pot posar un o més blocs d’etiquetes Begin SQL/End SQL en el mateix métode.

• Pot escriure diverses instruccions SQL en la mateixa línia o en diferentes línies separades per punt i coma ";".

PARA CREAR UNA FUNCIÓN

CREATE FUNCTION sp_name ([parameter[,...]])

RETURNS type

[characteristic ...] routine_body

parameter:

[ IN | OUT | INOUT ] param_name type

type:

Any valid MySQL data type

features:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body:

procedimientos almacenados o comandos SQL válidos

ALTER FUNCTION

nom_funció [characteristic ...]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

[etiqueta_inicio:] BEGIN

[lista_sentencias]

END [etiqueta_fin]

FUNCIONAMENT

Aquestes commandes permeten l’enmagatzenament de funcions en el servidor MySQL:

CREATE FUNCTION

Aquesta comanda afegeix una funció definida per l’usuari, normalment asociada amb la base de dades en la que l’usuari està treballant.

ALTER FUNCTION

La comanda “ALTER” és utilitzada per a canviar les característiques d’una funció emmagatzemada.

DROP FUNCTION

La comanda “DROP FUNCTION” s’utilitza per a borrar una funció emmagatzemada (es borra del servidor). L’usuari que ho fa ha de tenir el permís “ALTER ROUTINE”, que és atorgat automàticament al creador de la rutina (una rutina és un terme que engloba els procediments i les funcions).

La clàusula “IF EXISTS” és una extensió de MySQL que evita que ocorri un error si la funció no existeix. Així es genera una advertència que pot veure’s amb “SHOW WARNINGS”.

SHOW CREATE FUNCTION

Aquesta comanda és una extensió de MySQL, semblant a “SHOW CREATE TABLE”, que retorna la cadena exacta que pot fer-se servir per a recrear la funció anomenada.

SHOW FUNCTION STATUS

Aquesta comanda és una extensió de MySQL que retorna característiques de funcions, com el nom de la base de dades, el propi nom, el tipus, el creador i les dates de creació i modificació. Si no s’especifica un patró, llista la informació per a totes les funcions, segons la comanda que es faci servir.

 

VISTES

Amb les vistes, treballarem amb els esquemes externs.

Per a crear una vista cal fer servir la sentència CREATE VIEW

ESTRUCTURA

CREATE VIEW nom_vista [(llista_columnes)] AS (consulta) [WITH CHECK OPTION];

Les vistes no existeixen realment com un conjunt de valors emmagatzemats a la base de dades, sinó que són taules fictícies, anomenades derivades (no materialitzades). Es construeixen a partir de taules reals (materialitzades) emmagatzemades a la base de dades.

Per a esborrar una vista cal fer servir la sentència DROP VIEW, que presenta el format:

DROP VIEW nom_vista {RESTRICT|CASCADE};

Les vistes poden ser actualitzables o no.

AVANTATGES

• Independència
• Simplificació
• Millora de les dades i de les aplicacions de l’ús per a l’usuari de la seguretat
• Integritat de les dades
• Rendiment

DESAVENTATGES

• Restriccions d’actualitzacions
• Restriccions d’estructura; alguns SGBD no permeten construir una vista a partir d’una consulta qualsevol.

 ACTUALITZACIÓ DE LES VISTES

Les vistes sempre es poden consultar, però no sempre es poden actualitza...

Una vista no és actualitzable quan:

• Hi ha una clau primària, atribut not null o UNIQUE d’alguna taula que no intervé en la vista generada.
• Generalment les vistes definides sobre més d’una taula (Join)
• Les vistes que inclouen clàusules DISTINCT, HAVING, GROUP BY o funcions d’agregació (AVG, MIN,..),

Els disparadors permeten efectuar modificacions sobre taules que NO són actualitzables.

Creem una vista sobre la base de dades Empresa_a que ens doni per a cada client el número de projectes que té encarregats el client:

CREATE VIEW projectes_per_client (codi_cli, nombre_projectes) AS (

   SELECT c.codi_cli, COUNT(*)

   FROM projectes p, clients c

   WHERE p.codi_client = c.codi_cli

   GROUP BY c.codi_cli

);

La clàusula WITH CHECK OPTION assegura que mai podrem actuar sobre parts d’una taula que no estan a la vista.

ACTIVITATS

Descarregar script exposicions.

1. Donada la vista següent:
   CREATE VIEW AdrecesFotografs AS SELECT Nom, Adreca, Pais FROM Fotografs;
   INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU');
   
   Explica breument quin seria l'efecte sobre la vista anterior i la taula Fotografs si executem la sentència SQL següent:
   INSERT INTO AdrecesFotografs VALUES ('Jack Shephard','St. Sebastian hospital, Los Angeles', 'EEUU');
   Que actualitzaria la view AdrecesFotografs i Fotografs però en fotografs faltaría la PRIMARY KEY.
   
   Canviaria alguna cosa si la vista tingués la clàusula WITH CHECK OPTION?
   En aquest cas no succeeix res diferent perque no hi ha un WHERE, i no afegeixes res que no estigui a la llista, si tinguesim alguna cosa de fora de la llista, no ho permetria.
   
2. Crear una vista anomenada FotografsDeRenom que obtingui totes les dades de la taula Fotografs i que a més, per a cada fotògraf, ens doni el preu mig que van pagar els visitants per entrar a les exposicions on aquest fotògraf ha exposat, i que el llistat surti ordenat pel preu mig de forma descendent. És actualitzable aquesta vista? Per què?
   CREATE VIEW FotografsDeRenom AS
       SELECT f.passaport, f.nom, f.adreca, f.telefon, f.pais, f.classificacio_fotograf, avg(e.preu) as 'preu mig'
       FROM Fotografs f, Exposicions e, Exposen ex
       WHERE f.passaport=ex.passaport AND e.codi_exposicio=ex.codi_exposicio
       GROUP BY (*)
       ORDER BY preu_mig DESC;
   
   No es actualitzable perque te un GROUP BY i es una vista definida sobre més d'una taula.

Activitat vistes: Manipulació de la base de dades Exposicions

Descarregar script exposicions.

1. Crear una vista d'aquelles exposicions que s'han portat a terme entre el 3-6-2010 i el 15-11-2010. Mostrar totes les dades de les exposicions i dels seus participants, i que tinguin un preu superior a 9€.
   DROP VIEW Exposicions_estiu;
   
   CREATE VIEW Exposicions_estiu AS
       SELECT ex.*, f.* FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE (ex.data_inici BETWEEN '2010-6-3' AND '2010-11-15') AND (ex.data_final BETWEEN '2010-6-3' AND '2010-11-15') AND ex.preu>9
       GROUP BY codi_exposicio;
   
   select * FROM Exposicions_estiu;
   
   No es actualitzable, perque utilitza JOIN's + GROUP BY
   
2. Crear una vista que mostri el passaport, el nom i el país dels fotògrafs que participen a alguna exposició amb preu superior o igual a la mitjana de totes les exposicions.
   
   DROP VIEW Participants_de_preu_elevat;
   
   CREATE VIEW Participants_de_preu_elevat AS
       SELECT f.passaport, f.nom, f.pais FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE ex.preu>=(select avg(ex2.preu) FROM Exposicions ex2)
       GROUP BY passaport;
      
   select * FROM Participants_de_preu_elevat;
   
   No es actualitzable, perque utilitza JOIN's + GROUP BY
3. Crear una vista que contingui els codis d'exposició de les exposicions fetes al 2010, on els fotografs siguin d'Anglaterra. Cal mostrar els codis d'exposició, la data i nom i cognom dels fotografs.
   DROP VIEW Expos2010;
   
   CREATE VIEW Expos2010 AS
       SELECT ex.codi_exposicio, ex.data_inici, ex.data_final, f.nom FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE 2010=YEAR(data_inici) AND 2010=YEAR(data_final) AND f.pais="Anglaterra";
      
   select * FROM Expos2010;
   
   No es actualitzable, perque utilitza JOIN's.
4. Crea una vista on a partir de les exposicions fetes al 2010 els fotografs participants hi hagin exposat més de 2 fotografies. Mostra nom i cognom del fotograf, exposició (codi i nom) i número de fotografies.
   
   DROP VIEW Expos2010mes2f;
   
   CREATE VIEW Expos2010mes2f AS
       SELECT f.nom, ex.codi_exposicio, ex.titol, e.num_fotos FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       WHERE YEAR(data_inici)=2010 AND e.num_fotos>2;
     
   select * FROM Expos2010mes2f;
   
   No es actualitzable, perque utilitza JOIN's
5. Inventa una vista que sigui actualitzable i una altre que no ho sigui, a partir de la BD Exposicions.
1. Volem crear una vista actualitzable per a que el personal pugui afegir el passaport, el nom i l'adreça dels fotografs que entrin.
   DROP VIEW VistaActualitzable;
   
   CREATE VIEW VistaActualitzable AS
       SELECT passaport,nom,adreca FROM Fotografs
       WITH CHECK OPTION;
      
   select * FROM VistaActualitzable;
   
2. Volem crear una vista que mostri el nom dels fotografs amb el titol de les exposicions.
   DROP VIEW VistaNoActualitzable;
   
   CREATE VIEW VistaNoActualitzable AS
       SELECT f.nom, ex.titol FROM Fotografs f NATURAL JOIN Exposen e JOIN Exposicions ex ON ex.codi_exposicio=e.codi_exposicio
       GROUP BY f.nom asc,ex.titol
       WITH CHECK OPTION;
      
   select * FROM VistaNoActualitzable;

EXERCICIS VISTES SOBRE LA BD MATRICULES

Descarregar script matricules.

Per a cada exercici argumenta si és o no actualitzable, és a dir, si admet o no operacions d'inserció, d'esborrat i de modificació.

1. Crear una vista que recuperi el dni, el nom i l'any d'inici de tots els estudiants que porten més de cinc anys a l'escola.
   DROP VIEW Alumnes_antics;
   
   CREATE VIEW Alumnes_antics AS
       SELECT e.DNI, e.nom, m.Any FROM ESTUDIANT e NATURAL JOIN MATRICULES m
       WHERE (YEAR(CURDATE())-(m.Any))>5
       GROUP BY DNI;
   
   select * FROM Alumnes_antics;
   
   No es actualitzable perquè utilitza JOIN i GROUP BY.
2. Creeu una vista que recuperi el codi de l'assignatura, el nom, el responsable, l'àrea, i el número d'estudiants que l'estan cursant, per a totes les assignatures de l'àrea "M".
   CREATE VIEW Estudiants_cursen_M AS
       SELECT e.codi_assignatura, e.nom, e.responsable, e.area, count(c.dni) FROM Assignatures e NATURAL JOIN Cursen c
       WHERE e.area="M"
       GROUP BY c.codi_assignatura;
   
   select * FROM Estudiants_cursen_M;
   
   No es actualitzable, com a l'exercici anterior, l'utilització de JOIN fa que no ho sigui i el GROUP BY.
3. Crea una vista que mostri el número de matriculacions de cada assignatura cada any, concretament ha de mostrar l'any, el nom de l'assignatura i el número de matriculacions. Cal ordenar els resultats per any i número de semestre de forma descendent.
   CREATE VIEW N_MATRICULACIONS AS
       SELECT m.any, a.nom, count(m.codi_matricula) FROM MATRICULES m NATURAL JOIN ASSIGNATURA a JOIN ESTUDIANT e ON m.codi_matricula=e.codi_matricula
       GROUP BY a.nom, m.any, m.numero
       ORDER BY m.any desc,m.numero desc
   ;
   
   select * FROM N_MATRICULACIONS;
   No es actualitzable, com als exercicis anteriors, l'utilització de JOIN i GROUP BY.