Salta el contingut

6. DCL - Control d'usuaris

Fins ara hem fet servir només l'usuari root, que és l'administrador, i que disposa de tots els privilegis en MySQL. Però no convé que tots els usuaris tinguen tots els permisos.

MySQL permet definir usuaris i assignar-los permisos. Vorem només algunes opcions de les sentències sobre usuaris i permisos, encara que MySQL en permet moltes més.

Començarem explicant com crear usuaris i com esborrar-los. Després vorem com assignar-los permisos (de fer select, update, delete) sobre quins objectes (camps, taules, bases de dades...). Finalment vorem la creació de rols (grups de permisos) per a poder-los assignar a usuaris (en compte de permisos solts).

6.1. Usuaris

Creació d'usuaris

SQL
1
2
3
4
5
CREATE USER alumne;

CREATE USER alumne IDENTIFIED BY 'clau';

CREATE USER pep IDENTIFIED BY 'kkk', pepa IDENTIFIED BY 'lll';

Nota

També es podrien crear d'estes formes:

  • Amb la mateixa sentència que serveix per a donar permisos:

    SQL
    1
    GRANT USAGE ON *.* TO alumne IDENTIFIED BY 'clau';

  • Fent un insert en la taula on es guarden els usuaris:

    SQL
    1
2
3
4
    INSERT INTO mysql.user VALUES ('localhost','alumne',PASSWORD('clau'),
'N','N','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);
FLUSH PRIVILEGES;

Obrir sessió en un usuari

Una volta hem creat l'usuari, podrem obrir una sessió MySQL amb eixe usuari (però encara no podrà vore cap BD ja que encara no li hem donat permisos).

  • Des de consola:

    SQL
    1
    mysql -u alumne -p

  • O bé, en el gestor Workbench, obrint una sessió amb l'usuari que siga.

Esborrar usuaris

SQL
1
2
3
DROP USER alumne;

DROP USER pep, pepa;

Consultar usuaris existents

SQL
1
SELECT * FROM mysql.user;

Noms d'usuaris

Quan creem un usuari, quan l'esborrem, o quan li donem o llevem permisos, estarem indicant un nom d'usuari. Eixe nom pot estar format per dos parts, separats pel signe @:

Text Only
1
nom_usuari@nom_maquina

El nom_maquina indica des de quina màquina (host) tindrà eixe usuari els permisos que se li donen. El nom_maquina pot ser localhost o una IP. Esta IP també admet el comodí %.

Exemples:

SQL
1
CREATE USER alumne@'10.28.56.15';

L'usuari alumne només pot connectar-se des d'un ordinador amb eixa IP.

SQL
1
CREATE USER alumne@localhost;

L'usuari alumne només pot connectar-se des del mateix ordinador on està executant-se el servidor de MySQL.

SQL
1
CREATE USER alumne@'%';

L'usuari alumne podrà connectar-se des de qualsevol màquina. És l'opció per defecte si no s'indica el @nom_maquina.

SQL
1
CREATE USER alumne@'192.168.1.%';

L'usuari alumne podrà connectar-se des de màquines amb una adreça IP compresa entre 192.168.1.1 i 192.168.1.255.

Estos són exemples de creació d'usuaris indicant el host des d'on es pot accedir, però quan s'assignen permisos a un usuari (ho vorem a l'apartat següent) també podrem indicar el host en eixe usuari, per a dir des de quin host té eixe usuari eixos permisos.

Noms d'usuaris en entorns Mac

En alguna versió pot donar problemes la gestió d'usuaris si no indiquem el host o si posem el comodí %. Per tant, usarem sempre: nomUsuari@localhost

6.2. Permisos

Concedir permisos

La sintaxi resumida per a concedir permisos és:

SQL
1
2
3
GRANT permisos
ON sobre què
TO usuaris;

Veiem què podem posar en cada apartat:

Permisos que volem donar Sobre què donem els permisos A qui li donem els permisos
SELECT Per a fer consultes de taules (permet especificar columnes) nom_bd.nom_taula Sobre una taula TO usuaris
INSERT Per a inserir dades en una taula (permet especificar columnes) nom_bd.* Sobre una base de dades WITH GRANT OPTION (opcional): li donem permís per a que puga donar eixos permisos
UPDATE Per a modificar dades d'una taula (permet especificar columnes) *.* Sobre totes les bases de dades
DELETE Per a esborrar dades d'una taula
CREATE Per a crear taules
DROP Per a eliminar taules
ALTER Per a modificar l'estructura de les taules
ALL Tots els permisos
USAGE Cap permís (és altra forma de crear usuaris)

Exemples

SQL
1
2
3
GRANT SELECT
ON institut.assignatures
TO alumne;

Estem donant permís a l'usuari alumne per a fer selects sobre la taula assignatures (de la BD institut).

Si ara obrim una sessió amb l'usuari alumne, veiem què passa si fem...

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
show databases;                 -- Només mostrarà la BD institut

use ciclisme;                   -- Error perquè l'usuari alumne no té permís

use institut;

show tables;                    -- Només mostrarà la taula assignatures

select * from assignatures;     -- Mostrarà les dades de la taula

update assignatures...          -- Error perquè no tenim permís d'update

delete from assignatures...     -- Error perquè no tenim permís de delete

create table ...                -- Error perquè no tenim accés de create

Els permisos d'un usuari es poden saber amb:

SQL
1
SHOW GRANTS FOR nom_usuari;
SQL
1
GRANT ALL ON *.* TO user_admin;    -- Dona tots els permisos a l'usuari user_admin;
SQL
1
2
3
4
GRANT SELECT(assig, ava, nota), UPDATE(domicili, tel)
ON institut.notes
TO alumne, paremare
WITH GRANT OPTION;

Estem donant als usuaris alumne i paremare permís per a consultar (fer select) els camps assig, ava i nota de la taula notes de la BD institut. I també per a modificar el domicili i tel d'eixa taula de notes. També donem permís a alumne i paremare per a que donen eixos permisos a qui vulguen.

SQL
1
2
3
GRANT USAGE
ON *.*
TO pep IDENTIFIED BY 'passpep';

Permís que indica "cap permís". És altra forma de crear un usuari. Si creem un usuari amb CREATE USER... també es crea eixe permís.

La sintaxi de GRANT, un poc més detallada

SQL
1
2
3
4
GRANT tipus_de_permís [(llista_de_camps)]
    ON {nom_taula | * | *.* | nom_bd.*}
    TO usuari [IDENTIFIED BY 'contrasenya']
    [WITH GRANT OPTION]

Al mateix temps que donem un permís es pot canviar la contrasenya de l'usuari.

Per tant, hem vist que hi ha distints tipus de permisos en MySQL:

  • Globals: s'apliquen a totes les BD d'un servidor.

    SQL
    1
    GRANT ... ON *.* TO ...

  • De base de dades: per a una BD individual (i per a tots els seus objectes).

    SQL
    1
    GRANT ... ON nom_bd.* TO ...

  • De taula: per a una taula o vista individual (i per a totes les seues columnes).

    SQL
    1
    GRANT ... ON nom_taula TO ...

  • De columna: per a una columna d'una taula concreta.

    SQL
    1
2
    GRANT SELECT(col1, col2, ...) ON nom_taula TO ...
GRANT UPDATE(col1, col2, ...) ON nom_taula TO ...

  • De files: per a unes files d'alguna taula. Ho farem amb les vistes.

    SQL
    1
2
    CREATE VIEW vista_meua AS SELECT ... FROM ... WHERE condició_files;
GRANT ... ON vista_meua ... TO ...;

  • De rutina: Ja vorem que MySQL permet crear funcions i procediments, i podrem donar permisos per a poder-los executar, etc.

Revocar permisos

Per a revocar (llevar) permisos s'usa la sentència REVOKE.

SQL
1
2
3
REVOKE tipus_de_permís [(llista_de_camps)]
    ON {nom_taula | * | *.* | nom_bd.*}
    FROM usuari
SQL
1
2
3
REVOKE SELECT(nota)
ON institut.assignatures
FROM alumne;

Estem llevant a l'usuari alumne el permís per a vore la nota en la taula assignatures.

SQL
1
2
3
REVOKE INSERT, UPDATE, DELETE
ON assignatures
FROM pep, pepa;

Estem llevant als usuaris pep i pepa els permisos per a inserir, modificar o esborrar de la taula assignatures.

Veiem ara uns casos especials:

  • Revocar tots els permisos, llevat de GRANT OPTION:

    SQL
    1
    REVOKE ALL ON *.* FROM usuari

  • Revocar el GRANT OPTION:

    SQL
    1
    REVOKE GRANT OPTION ON *.* FROM usuari

  • Revocar tots els permisos, inclòs el GRANT OPTION:

    SQL
    1
    REVOKE ALL, GRANT OPTION FROM usuari

    Nota

    En este cas no es posa el ON *.*.

6.3. Rols

Un rol és un conjunt de permisos. Vegem la utilitat.

Suposem que cada alumne ha de tindre el seu usuari però volem que tots tinguen els mateixos permisos. A vegades n'afegirem o en llevarem, però volem que tots els alumnes tinguen sempre els mateixos permisos.

La solució és crear un rol alumne i assignar-li (o llevar-li) tots els permisos que ha de tindre qualsevol alumne. Després només haurem d'indicar quins usuaris tindran el rol alumne.

Nota

La gestió de rols en MySQL està a partir de la versió 8. Per vore la versió que tens instal·lada recorda que has d'executar:

SQL
1
SELECT version();

Vegem amb exemples què es pot fer amb els rols:

Creació de rols

SQL
1
2
CREATE ROLE 'alumne';
CREATE ROLE 'professor', 'secretari', 'tutor';

Esborrar rols

SQL
1
DROP ROLE 'secretari';

Assignar permisos a un rol

SQL
1
2
GRANT SELECT ON institut TO 'alumne';
GRANT INSERT, UPDATE, DELETE ON institut TO 'professor', 'secretari';

Assignar rols a usuaris

SQL
1
2
GRANT 'alumne' TO 'pep', 'pepa', 'pepeta', 'pepet';
GRANT 'professor', 'tutor' TO 'abdo', 'espe';

Crear usuaris amb algun rol ja assignat

SQL
1
CREATE USER 'joanjo' DEFAULT ROLE 'professor', 'cap';

Podem passar permisos d'usuari a rol, rol a usuari, usuari a usuari o usuari a rol

SQL
1
2
3
4
5
6
7
8
CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

Exercicis sobre usuaris i permisos (BD lliga1213)

  1. Crea els usuaris entrenador, jugador, aficionat, prova i prova2, que tinguen com a clau la mateixa que el nom d'usuari.

  2. Canvia el password de l'entrenador i posa-li: mister.

  3. Esborra l'usuari prova2.

  4. Dóna permís a l'usuari entrenador per a vore totes les dades de la BD, de forma que entrenador també puga donar eixos permisos a qui vullga.

  5. Mostra els permisos de l'usuari entrenador.

  6. Dóna permís a l'usuari aficionat per a vore les dades de la vista classif2.

  7. Dóna permís de lectura a prova sobre la taula equips per a vore totes les dades excepte del pressupost.

    Notes

    • No podem donar permís de lectura a tota la taula en general, sense especificar columnes (grant select on equips) i després llevar-li permís de lectura però només del pressupost (revoke select(pressupost) on equips) ja que només es pot fer revoke del mateix grant que s'havia fet.
    • Podríem pensar en donar permís de lectura a tota la taula equips, després tornar a donar permís de lectura del pressupost i després llevar la lectura del pressupost, però no seria correcte ja que encara tindrem permís per a vore el pressupost, ja que haurem llevat el segon grant, però no el grant primer de lectura de totes les columnes.
    • Per tant, l'única solució serà donar permís camp a camp de totes les columnes d'equips excepte del pressupost.

  8. Dóna permís a entrenador per a modificar el lloc i el nom dels jugadors.

  9. Dóna permís a entrenador per a esborrar jugadors, golejadors i porters.

  10. Lleva tots els permisos a l'usuari prova.

  11. Dóna permís a prova per a vore només les dades dels partits del Barça. Esta té truc.

  12. Crea el rol proves. Fes que l'usuari prova tinga eixe rol. Dona al rol proves permís per a consultar el pressupost de la taula equips. Comprova que l'usuari prova pot consultar eixe camp pressupost.