SQL usuaris

Per poder crear i administrar els usuaris i permisos al mysql s'ha d'entrar com a usuari root, és a dir, un superusuari amb privilegis sobre tota la base de dades. També és possible crear usuaris amb un usuari que tingui permisos administratius.

Creació d'usuaris

Pots també revisar l'article original de creació d'usuaris de MySQL aquí.

Abans de crear un nou usuari s'ha de tenir en compte:

• nom de l'usuari
• contrassenya
• màquina des de la que es connectarà
• permisos

Si es fa des de la línia de comandes, caldrà que entrem com a root amb:

$> mysql -u root -p -h <host_ip>

el -h <host_ip> només cal posar-ho si ens connectem al mysql des de una màquina externa, com pot ser el cas si ens connectem a un servidor.

Dintre de mysql, per crear l'usuari, farem:

mysql> CREATE USER '<usuari>'@'<màquina>' IDENTIFIED BY '<contrassenya>';
mysql> FLUSH PRIVILEGES;

OJU:

• Els noms d'usuari, màquina i contrassenya han d'anar entre cometes simples.
• La màquina remota des de la que ens connectem pot ser una adreça IP, localhost o '%' per qualsevol màquina.
• La darrera instrucció "flush privileges" de vegades no és imprescindible però sol fer falta per consolidar els canvis a la base de dades interna 'mysql'.

Per exemple, per crear l'usuari "manolo" amb contrassenya "secret" i que només es connectarà des de la màquina local, executarem:

mysql> CREATE USER 'manolo'@'localhost' IDENTIFIED BY 'secret';

Si, en canvi, volem crear l'usuari "manolo" que es connecti des de qualsevol màquina, farem:

mysql> CREATE USER 'manolo'@'%' IDENTIFIED BY 'secret';

Canvi de contrassenya

Les dades dels permisos i contrassenyes estan emmagatzemades en una base de dades anomenada "mysql" a la qual només tenen accés els usuaris administradors.

Canviar la contrassenya només ho pot fer un superusuari amb l'instrucció UPDATE sobre la taula 'user' de la base de dades 'mysql'. Per exemple, si volem canviar la password de l'usuari 'manolo' quan es connecta en local, farem:

mysql> use mysql;
mysql> UPDATE user
       SET password=PASSWORD('secret')
       WHERE user='manolo' AND host='localhost';
mysql> FLUSH PRIVILEGES;

IMPORTANT:

• Aquí sí és molt imporant fer el darrer pas "FLUSH PRIVILEGES" per fer actius els canvis a la base de dades interna 'mysql'.

Concessió de permisos

Els permisos es concedeixen amb la sentència GRANT i només ho pot fer un usuari administrador. Consulteu l'article original aquí.

IMPORTANT: al final de cada sentència de permisos cal posar un FLUSH PRIVILEGES per tal que els permisos prenguin efecte immediatament.

Resumint:

mysql> GRANT <tipus_de_privilegi>
       ON <base_de_dades>.<taula>
       TO <usuari>@<host>;
mysql> FLUSH PRIVILEGES;

Per retirar permisos s'utilitza la sentència REVOKE de manera molt similar, només canvia el TO per un FROM:

mysql> REVOKE <tipus de privilegi>
       ON <base_de_dades>.<taula>
       FROM <usuari>@<host>;
mysql> FLUSH PRIVILEGES;

Per exemple, per concedir tots els permisos (all privileges) sobre la taula 'films' de la base de dades 'videoclub' a l'usuari "manolo", faríem:

mysql> GRANT ALL PRIVILEGES
       ON videoclub.films
       TO 'manolo'@'localhost';
mysql> FLUSH PRIVILEGES;

Wildcards (Comodins)

En el lloc de les bases de dades i les taules es poden posar asteriscs per dir "tot", com en el sistema d'arxius. Per donar TOTS ELS PRIVILEGIS a un usuari sobre TOTES LES BASES DE DADES podem posar:

mysql> GRANT ALL PRIVILEGES
       ON *.*
       TO <usuari>@<host>;
mysql> FLUSH PRIVILEGES;

Si volem donar els permisos només de lectura a totes les taules de la base de dades 'mundo', utiltizarem l'asterisc (*):

mysql> GRANT SELECT
       ON mundo.*
       TO <usuari>@<host>;
mysql> FLUSH PRIVILEGES;

IMPORTANT: per poder donar permisos a diverses bases de dades d'un sol cop ens cal el comodí (%). Per exemple, per donar permisos de lectura a l'usuari enric a totes les bases de dades que comencin per 'enric_%' farem:

mysql> GRANT SELECT
       ON `enric_%`.*
       TO 'enric'@'localhost';
mysql> FLUSH PRIVILEGES;

MOLT IMPORTANT: `enric_%` NO va entre cometes, sinó amb accents oberts. Si no ho poseu correctament, us donarà un error.

Tipus de privilegis

Com podreu imaginar, hi ha molt tipus de persmisos. Els més importants son:

• ALL PRIVILEGES
• SELECT
• DELETE
• UPDATE
• CREATE TABLE
• CREATE VIEW
• ...

Consulteu l'article original per més referència.

Comprovació

Per saber els permisos amb els que compta un usuari, executem:

mysql> SHOW GRANTS FOR '<usuari>'@'<màquina>';

Exercicis

En aquesta pràctica es requereix tenir alguna BD de prova. Si no en tens cap, llegeix l'article SQL creacio o bé directament descarrega alguna de les BBDD de proves oficials de MySQL: sakila, menagerie, world i employee.

Crea els següents usuaris amb contrassenya i persmisos indicats (amb l'usuari root):

1. Usuari admin des de localhost amb contrassenya admin
   • doneu-li permisos de SELECT i UPDATE sobre totes les bases de dades (*.*)
2. Usuari convidat des de qualsevol màquina amb contrassenya buida.
   • doneu-li permisos de lectura (SELECT) sobre una base de dades que tingueu, per exemple, la del videoclub, shakila o world
3. Usuari bartolo des de qualsevol màquina amb contrassenya bartolo
   • doneu-li tots els permisos sobre totes les bases de dades que comencin per "bartolo_"
4. Comprova que aquests permisos atorgats funcionen per a cada usuari i des de màquines diferents (no només en local).

Amb la clàusula WITH GRANT OPTION es pot fer que usuaris administradors puguin concedir permisos a altres. Consulteu-ho a MySQL Grant Syntax i realitza el següent:

1. Comprova què passa si "admin" intenta concedir permisos a "bartolo".
2. Atorga-li els permisos adequats a "admin" per tal que pugui concedir permisos de SELECT i UPDATE sobre la BD world.
3. Comprova que "admin" no pot concedir permisos de DELETE.
4. Amb l'usuari "admin", atorga permisos de SELECT sobre la BD world a l'usuari "bartolo".
5. Comprova que "bartolo" pot realitzar selects i no updates o deletes.
6. Comprova que "admin" no pot donar permisos de SELECT sobre una base de dades que no sigui world.

Altres cosetes

Tunejant root i phpmyadmin

Canvieu la contrassenya de l'usuari root per "root" (en principi ve buida).

Al realitzar aquest canvi el phpmyadmin deixarà de funcionar perquè la contrassenya de root ha canviat. El forçarem a que demana la contrassenya modificant l'arxiu x:\xampp\phpmyadmin\config.inc.php. Mireu la secció "Authentication type and info" i desactiveu les línies posant amb un comentari (des d'on comença el símbol /* fins on arriba a un */ no es considera instruccions, sinó comentaris, pel que desactivarem els paràmetres per loguejar-se). Originalment està així:

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type']            = 'config';
$cfg['Servers'][$i]['user']                 = 'root';
$cfg['Servers'][$i]['password']             = '';
$cfg['Servers'][$i]['AllowNoPassword']      = true;

I finalment ens ha de quedar així:

/* Authentication type and info */
/*
$cfg['Servers'][$i]['auth_type']            = 'config';
$cfg['Servers'][$i]['user']                 = 'root';
$cfg['Servers'][$i]['password']             = '';
*/
$cfg['Servers'][$i]['AllowNoPassword']      = true;

D'aquesta manera els paràmetres 'auth_type', 'user' i 'password' queden anulats. Quan s'iniciï el phpmyadmin hauria de demanar l'usuari i contrassenya per entrar.

Comprovació

Un cop creats els usuaris i les modificacions, comproveu que podeu tornar a entrar en el phpmyadmin. Si no podeu, reviseu l'arxiu de configuració x:\xampp\phpmyadmin\config.inc.php tal i com s'ha explicat. Ara hauria de demanar-nos la contrassenya per poder entrar.

Per comprovar l'usuari convidat hem de provar des d'una altra màquina la instrucció:

mysql> mysql -u convidat -h <ip_màquina>

...i si ens deixa entrar, vol dir que l'usuari ha estat creat correctament. Per veure si heu posat correctament els permisos feu

mysql> SHOW DATABASES;

...i us hauria d'aparèixer la base de dades sobre la que heu donat permisos de lectura (SELECT). Proveu de fer una consulta aviam si funciona.

També podeu comprovar si funciona des de la màquina remota i amb el Firefox, posant l'adreça:

http://<la_ip_remota>/phpmyadmin

i podem provar de posar el nom d'usuari i contrassenya dels usuaris bartolo i convidat (que tenen permisos des de qualsevol màquina) per atacar la base de dades. Igualment, l'usuari admin no ens ha de permetre entrar des d'una màquina remota.