Usuaris a MySQL

De wikijoan
Salta a la navegació Salta a la cerca

Referències

Teoria

Creació d'usuaris amb CREATE USER

Hem de connectar-nos al docker com a root.

Recordatori de com entrar com a root: Docker_al_mòdul_DAM-M02#Entrar_com_a_root

Per saber quins usuaris hi ha a la base de dades:

mysql> select User,Host from mysql.user;  
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| alumne           | %         |
| alumne           | localhost |
| healthchecker    | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

Ja podem crear un usuari nou:

mysql> create user joan@localhost identified by 'password';
mysql> exit

També podem fer:

create user 'joan'@'localhost' identified by 'password';
create user `joan`@`localhost` identified by 'password';

I ara podem reconnectar-nos al mysql amb el nou usuari:

$ docker exec -it mysql_m02bd mysql -u joan -ppassword

Però aquest usuari encara no pot fer res. No pot veure totes les bases de dades que hi ha:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

Tornem a l'usuari root, i creem la base de dades joandb:

$ docker exec -it mysql_m02bd mysql -u root -p

mysql> create database joandb;
mysql> use joandb (per comprovar que s'ha creat bé)

I donem permisos a l'usuari joan per treballar amb aquesta nova base de dades:

mysql> grant all privileges on joandb.* to joan@localhost;
mysql> exit

Podem veure els permisos que ara té aquest usuari:

mysql> SHOW GRANTS FOR joan@localhost;

Tornem a connectar-nos amb l'usuari joan:

$ docker exec -it mysql_m02bd mysql -u joan -ppassword

I ara sí que veiem la nova base de dades:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| joandb             |
+--------------------+

mysql> use joandb

i creem una taula en aquesta base de dades. Inserim dades i fem una select:

create table lists(
id int auto_increment primary key,
todo varchar(100) not null,
completed bool default false
);

insert into lists(todo) values('Learn MySQL');

select * from lists;

Resumint: l'usuari root ha creat una base dades nova, un usuari nou, i ha donat permisos a aquest usuari per treballar amb la base de dades. El nou usuari ja pot operar amb normalitat amb la nova base de dades (crear taules, fer inserts, fer selects).

Comanda GRANT per donar permisos

Hem creat un usuari amb tots els permisos sobre la base de dades joandb, però encara no pot fer res sobre les altres bases de dades.

Anem a donar permisos. Els permisos es poden donar sobre objectes concrets, i sobre camps concrets. Per exemple, podem donar permisos de lectura (SELECT)) sobre la taula municipis de la base de dades municipis:

GRANT SELECT ON municipis.municipis TO joan@localhost;

mysql> SHOW GRANTS FOR joan@localhost;
+------------------------------------------------------------------------------------------------------+
| Grants for joan@localhost                                                                            |
+---------------------------------------------------------------------------
| GRANT USAGE ON *.* TO `joan`@`localhost`                                                             | GRANT ALL PRIVILEGES ON `joandb`.* TO `joan`@`localhost`                                             | GRANT SELECT ON `municipis`.`municipis` TO `joan`@`localhost` |

Efectivament, la primera consulta es pot fer, i la segona no:

(joan) mysql> select * from municipis;
...
8000 files

(joan) mysql> select * from provincies;
ERROR 1142 (42000): SELECT command denied to user 'joan'@'localhost' for table 'provincies'

No podem fer inserts de moment:

(joan) mysql> insert into municipis(id_mun,municipi)  values(9000,'municipi nou');
...permission denied...

Anem a donar permisos de insert, delete, i update (només de la columna superfície):

(root) mysql> GRANT INSERT, UPDATE (superficie), DELETE
ON municipis.municipis
TO joan@localhost;

Ho comprovem:

(joan) mysql> update municipis set superficie=1000 where id_mun=9000;
OK 1 row affected

(joan) mysql> update municipis set habitants=1000 where id_mun=9000;
ERROR 1143 (42000): UPDATE command denied to user 'joan'@'localhost' for column 'habitants' in table 'municipis'

(joan) mysql> delete from municipis where id_mun=9000;
Query OK, 1 row affected (0.01 sec)
mysql> SHOW GRANTS FOR joan@localhost;
--------------------------+
| Grants for joan@localhost                                                                            |
--------------------------+
| GRANT USAGE ON *.* TO `joan`@`localhost`                                                             | GRANT ALL PRIVILEGES ON `joandb`.* TO `joan`@`localhost`                                             | GRANT SELECT, INSERT, UPDATE (`superficie`), DELETE ON `municipis`.`municipis` TO `joan`@`localhost` |

MySQL revoke: revocar privilegis

Revoke one or more privileges

Donem tres privilegis a un usuari:

GRANT SELECT, UPDATE, INSERT 
ON classicmodels.*
TO rfc@localhost;
mysql> SHOW GRANTS FOR rfc@localhost;
+------------------------------------------------------------------------+
| Grants for rfc@localhost                                               |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rfc`@`localhost`                                |
| GRANT SELECT, INSERT, UPDATE ON `classicmodels`.* TO `rfc`@`localhost` |
+------------------------------------------------------------------------+

I ara revoquem dos d'aquests privilegis:

REVOKE INSERT, UPDATE
ON classicmodels.*
FROM rfc@localhost;

Podem veure quins privilegis han quedat:

SHOW GRANTS FOR rfc@localhost;

Veiem que continua el privilegi de SELECT, però el INSERT i el UPDATE ja no hi són.

mysql> SHOW GRANTS FOR rfc@localhost;
+--------------------------------------------------------+
| Grants for rfc@localhost                               |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `rfc`@`localhost`                |
| GRANT SELECT ON `classicmodels`.* TO `rfc`@`localhost` |
+--------------------------------------------------------+

Revoke all privileges

REVOKE ALL, GRANT OPTION
FROM rfc@localhost;

Mirem els privilegis que té:

SHOW GRANTS FOR rfc@localhost;

i veiem que només li queda el privilegi de USAGE. Pot connectar-se, no pot por fer res.

mysql> SHOW GRANTS FOR rfc@localhost;
+-----------------------------------------+
| Grants for rfc@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `rfc`@`localhost` |
+-----------------------------------------+

NOTA: posar GRANT OPTION és necessari, sinó dóna un error de sintaxi.

Eliminació d'usuaris amb DROP USER

(root) mysql> drop user joan@localhost;

(root) mysql> select user, host from mysql.user;

ja no apareix l'usuari.

Usuari alumne

Fins ara has estat treballant amb l'usuari alumne. Quins privilegis té?:

mysql> SHOW GRANTS FOR alumne@localhost;
+----------------------------------------------------------------------+
| Grants for alumne@localhost                                          |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `alumne`@`localhost`                           |
| GRANT ALL PRIVILEGES ON `HR`.* TO `alumne`@`localhost`               |
| GRANT ALL PRIVILEGES ON `bikeshop`.* TO `alumne`@`localhost`         |
| GRANT ALL PRIVILEGES ON `classicmodels`.* TO `alumne`@`localhost`    |
| GRANT ALL PRIVILEGES ON `empresa`.* TO `alumne`@`localhost`          |
| GRANT ALL PRIVILEGES ON `englishresources`.* TO `alumne`@`localhost` |
| GRANT ALL PRIVILEGES ON `langtrainer`.* TO `alumne`@`localhost`      |
| GRANT ALL PRIVILEGES ON `municipis`.* TO `alumne`@`localhost`        |
| GRANT ALL PRIVILEGES ON `northwind`.* TO `alumne`@`localhost`        |
| GRANT ALL PRIVILEGES ON `sakila`.* TO `alumne`@`localhost`           |
| GRANT ALL PRIVILEGES ON `vestuari`.* TO `alumne`@`localhost`         |
+----------------------------------------------------------------------+

creat per Joan Quintana Compte, febrer 2022