Diferència entre revisions de la pàgina «Usuaris a MySQL»

De wikijoan
Salta a la navegació Salta a la cerca
 
(Hi ha 9 revisions intermèdies del mateix usuari que no es mostren)
Línia 1: Línia 1:
 
=Referències=
 
=Referències=
 
*https://www.mysqltutorial.org/mysql-create-user.aspx
 
*https://www.mysqltutorial.org/mysql-create-user.aspx
 +
*https://www.mysqltutorial.org/mysql-grant.aspx
 +
*https://www.mysqltutorial.org/mysql-administration/mysql-show-grants/
 +
*https://www.mysqltutorial.org/mysql-revoke.aspx
  
 
=Teoria=
 
=Teoria=
 +
==Creació d'usuaris amb CREATE USER==
 
Hem de connectar-nos al docker com a root.
 
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:
 
Per saber quins usuaris hi ha a la base de dades:
Línia 25: Línia 31:
 
mysql> exit
 
mysql> exit
 
</pre>
 
</pre>
 +
També podem fer:
 +
<pre>
 +
create user 'joan'@'localhost' identified by 'password';
 +
create user `joan`@`localhost` identified by 'password';
 +
</pre>
 +
 
I ara podem reconnectar-nos al mysql amb el nou usuari:
 
I ara podem reconnectar-nos al mysql amb el nou usuari:
 
<pre>
 
<pre>
Línia 49: Línia 61:
 
mysql> grant all privileges on joandb.* to joan@localhost;
 
mysql> grant all privileges on joandb.* to joan@localhost;
 
mysql> exit
 
mysql> exit
 +
</pre>
 +
Podem veure els permisos que ara té aquest usuari:
 +
<pre>
 +
mysql> SHOW GRANTS FOR joan@localhost;
 
</pre>
 
</pre>
 
Tornem a connectar-nos amb l'usuari ''joan'':
 
Tornem a connectar-nos amb l'usuari ''joan'':
Línia 79: Línia 95:
 
</pre>
 
</pre>
 
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).
 
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'':
 +
<pre>
 +
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` |
 +
</pre>
 +
Efectivament, la primera consulta es pot fer, i la segona no:
 +
<pre>
 +
(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'
 +
</pre>
 +
No podem fer inserts de moment:
 +
<pre>
 +
(joan) mysql> insert into municipis(id_mun,municipi)  values(9000,'municipi nou');
 +
...permission denied...
 +
</pre>
 +
Anem a donar permisos de insert, delete, i update (només de la columna superfície):
 +
<pre>
 +
(root) mysql> GRANT INSERT, UPDATE (superficie), DELETE
 +
ON municipis.municipis
 +
TO joan@localhost;
 +
</pre>
 +
Ho comprovem:
 +
<pre>
 +
(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)
 +
</pre>
 +
<pre>
 +
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` |
 +
</pre>
 +
==MySQL revoke: revocar privilegis==
 +
===Revoke one or more privileges===
 +
Donem tres privilegis a un usuari:
 +
<pre>
 +
GRANT SELECT, UPDATE, INSERT
 +
ON classicmodels.*
 +
TO rfc@localhost;
 +
</pre>
 +
<pre>
 +
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` |
 +
+------------------------------------------------------------------------+
 +
</pre>
 +
I ara revoquem dos d'aquests privilegis:
 +
<pre>
 +
REVOKE INSERT, UPDATE
 +
ON classicmodels.*
 +
FROM rfc@localhost;
 +
</pre>
 +
Podem veure quins privilegis han quedat:
 +
<pre>
 +
SHOW GRANTS FOR rfc@localhost;
 +
</pre>
 +
Veiem que continua el privilegi de ''SELECT'', però el ''INSERT'' i el ''UPDATE'' ja no hi són.
 +
<pre>
 +
mysql> SHOW GRANTS FOR rfc@localhost;
 +
+--------------------------------------------------------+
 +
| Grants for rfc@localhost                              |
 +
+--------------------------------------------------------+
 +
| GRANT USAGE ON *.* TO `rfc`@`localhost`                |
 +
| GRANT SELECT ON `classicmodels`.* TO `rfc`@`localhost` |
 +
+--------------------------------------------------------+
 +
</pre>
 +
 +
===Revoke all privileges===
 +
<pre>
 +
REVOKE ALL, GRANT OPTION
 +
FROM rfc@localhost;
 +
</pre>
 +
Mirem els privilegis que té:
 +
<pre>
 +
SHOW GRANTS FOR rfc@localhost;
 +
</pre>
 +
i veiem que només li queda el privilegi de ''USAGE''. Pot connectar-se, no pot por fer res.
 +
<pre>
 +
mysql> SHOW GRANTS FOR rfc@localhost;
 +
+-----------------------------------------+
 +
| Grants for rfc@localhost                |
 +
+-----------------------------------------+
 +
| GRANT USAGE ON *.* TO `rfc`@`localhost` |
 +
+-----------------------------------------+
 +
</pre>
 +
'''NOTA''': posar ''GRANT OPTION'' és necessari, sinó dóna un error de sintaxi.
 +
 +
==Eliminació d'usuaris amb DROP USER==
 +
<pre>
 +
(root) mysql> drop user joan@localhost;
 +
 +
(root) mysql> select user, host from mysql.user;
 +
</pre>
 +
ja no apareix l'usuari.
 +
 +
==Usuari alumne==
 +
Fins ara has estat treballant amb l'usuari ''alumne''. Quins privilegis té?:
 +
<pre>
 +
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`        |
 +
+----------------------------------------------------------------------+
 +
</pre>
  
 
{{Autor}}, febrer 2022
 
{{Autor}}, febrer 2022

Revisió de 15:55, 7 març 2022

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

Obtingut de «http://wiki.joanillo.org/index.php?title=Usuaris_a_MySQL&oldid=298636»