Uprawnienia w MySQL

Pora zająć się użytkownikami i ich uprawnieniami w bazie MySQL

Jakby trzeba było na początku uruchomić bazę danych to:

mysqld --defaults-file=/mysqldata/data/my.cnf &

Jesteśmy zalogowani do bazy, ale na przykład nie pamiętamy na jakiego usera. Żeby to sprawdzić wydajemy polecenie:

mysql> SELECT USER(),CURRENT_USER();

+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

Możemy też dać polecenie STATUS, gdzie jest podany CURRENT USER:

MariaDB [testowadb1]> status

--------------
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.17 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    cp1250
Db     characterset:    cp1250
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /mysqldata/data/baza_mysql.sock
Uptime:                 4 min 40 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 111  Flush tables: 1  Open tables: 27  Queries per second avg: 0.039
--------------

Jakbyśmy chcieli zobaczyć co dany użytkownik wykonuje:

mysql> show full processlist;

+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                  |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  |  314 | Waiting on empty queue | NULL                  |
|  4 | root            | localhost | NULL | Query   |    0 | starting               | show full processlist |
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+
2 rows in set (0.00 sec)

Sprawdzamy uprawnienia dla naszego użytkownika ROOT

mysql> show grants for root@localhost;

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Informacje o użytkownikach

mysql> select user, host from mysql.user;

+-----------+-----------+
| host      | user      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

Jakbyśmy chcieli np zobaczyć uprawnienia userów do odczytu, zapisu itd to możemy rozszerzyć zapytanie;

mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;

+-----------+-----------+-------------+-------------+-------------+-------------+
| user      | host      | select_priv | insert_priv | update_priv | delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| root      | localhost | Y           | Y           | Y           | Y           |
| mysql.sys | localhost | N           | N           | N           | N           |
+-----------+-----------+-------------+-------------+-------------+-------------+
2 rows in set (0.00 sec)

Tych uprawnień jest naprawdę sporo 🙂

mysql> desc mysql.user;

+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

najważniejsze to:
• SELECT– odczytanie danych z tabeli
• INSERT – wstawianie danych do tabeli
• UPDATE – modyfikowanie danych w tabeli
• DELETE – usunięcie danych z tabeli
• REFERENCE – odwoływanie się do innych tabel
• CREATE – tworzenie nowych tabel i baz danych
• DROP – usuwanie tabel oraz baz danych
• ALL PRIVILEGES – wszystkie dostępne uprawnienia

W bazie MySQL jest trochę inaczej (można odpytać np o hasło):

MariaDB [(none)]> SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user where user like 'root';

+------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+
| user | host                  | password                                  | select_priv | insert_priv | shutdown_priv | grant_priv |
+------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+
| root | localhost             | *54958E764CE10E50764C2EECBB71D01F08549980 | Y           | Y           | Y             | Y          |
| root | localhost.localdomain |                                           | Y           | Y           | Y             | Y          |
| root | 127.0.0.1             |                                           | Y           | Y           | Y             | Y          |
| root | ::1                   |                                           | Y           | Y           | Y             | Y          |
+------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+
4 rows in set (0.01 sec)

I to samo wyświetlone w wierszach, a nie w kolumnach dla usera ROOT na host LOCALHOST:

mysql> select * from mysql.user where user like 'root' \G;

*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
      password_expired: N
 password_last_changed: 2017-04-11 20:22:52
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR:
No query specified

W systemie mamy usera MYSQL i takiego samego użytkownika założymy w bazie (zgodnie z zaleceniami bezpieczeństwa)

Aby wygenerować skrypt do tworzenia użytkownika wydajemy polecenie:

mysql> show create user 'root'@'localhost';

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@localhost                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

za jego pomocą stworzymy usera MYSQL posiadającego takie same uprawnienia jak ROOT:

mysql> CREATE USER 'mysql'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'mysql'@'localhost';

ERROR 1141 (42000): There is no such grant defined for user 'mysql' on host 'localhost'

A to dlatego że mamy zdefiniowanego usera 'MYSQL’@’%' czyli usera za pomocą którego możemy się podłączyć z każdego miejsca z sieci a nie tylko z lokalnego serwer (localhost)

mysql> show grants for 'mysql'@'%';

+--------------------------------------+
| Grants for newuser2@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'newuser2'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

Możemy się już zalogować do bazy ale user widzi tylko jedną bazę:
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

Porównujemy uprawnienia obu userów:

mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;

+-----------+-----------+-------------+-------------+-------------+-------------+
| user      | host      | select_priv | insert_priv | update_priv | delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| root      | localhost | Y           | Y           | Y           | Y           |
| mysql.sys | localhost | N           | N           | N           | N           |
| mysql     | %         | N           | N           | N           | N           |
+-----------+-----------+-------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

Generujemy skrypt uprawnień dla usera ROOT:

mysql> show grants for 'root'@'localhost';

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

i na jego podstawie tworzymy polecenia przyznające uprawnienia dla naszego nowego usera:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT PROXY ON ''@'' TO 'mysql'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Po zalogowaniu widać wszystkie bazy i user otrzymał uprawnienia takie same jak ROOT:

mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;

+-----------+-----------+-------------+-------------+-------------+-------------+
| user      | host      | select_priv | insert_priv | update_priv | delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| root      | localhost | Y           | Y           | Y           | Y           |
| mysql.sys | localhost | N           | N           | N           | N           |
| mysql     | %         | Y           | Y           | Y           | Y           |
+-----------+-----------+-------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

Stworzymy teraz nowego użytkownika do naszych testów :

MariaDB [(none)]> CREATE USER 'newuser1'@'%' IDENTIFIED BY 'newuser1';

Query OK, 0 rows affected (0.00 sec)

Możemy mu po prostu dać uprawnienia do odczytu na wszystkich tabelach we wszystkich bazach:

mysql> GRANT SELECT ON *.* to 'newuser1'@'%';

Query OK, 0 rows affected (0.00 sec)

dać uprawnienia tylko dla jednej bazy i wszystkich tabel w niej utworzonych

mysql> GRANT ALL PRIVILEGES ON testbaza1.* TO 'newuser1'@'%';

lub nadać my tylko do konkretnej tabeli w konkretnej bazie (TESTTABLE1 w TESTBAZA1);

mysql> grant select on testbaza1.testtable1 to 'newuser1'@'%';

Query OK, 0 rows affected (0.00 sec)

Zaczytujemy uprawnienia (uruchamiamy to za każdym razem gdy zmieniamy uprawnienia):
MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

Sprawdzamy w globalnych uprawnieniach i tu nic się nie pojawiło

mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;

+-----------+-----------+-------------+-------------+-------------+-------------+
| user      | host      | select_priv | insert_priv | update_priv | delete_priv |
+-----------+-----------+-------------+-------------+-------------+-------------+
| root      | localhost | Y           | Y           | Y           | Y           |
| mysql.sys | localhost | N           | N           | N           | N           |
| mysql     | %         | Y           | Y           | Y           | Y           |
| newuser1  | %         | Y           | N           | N           | N           |
+-----------+-----------+-------------+-------------+-------------+-------------+
5 rows in set (0.00 sec)

ale user uprawnienia ma:

mysql> show grants for 'newuser1'@'%';

+------------------------------------------------------------+
| Grants for newuser1@%                                      |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser1'@'%'                       |
| GRANT SELECT ON `testbaza1`.`testtable1` TO 'newuser1'@'%' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

Możemy ograniczyć prawa użytkownika tylko do wybranych kolumn w tabeli:
najpierw zabierzemy mu uprawnienia do całej tabeli:
mysql> revoke SELECT ON testbaza1.testtable1 from 'newuser1'@'%';

Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;

mysql> show grants for 'newuser1'@'%';

+--------------------------------------+
| Grants for newuser1@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'newuser1'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

a następnie damy tylko do konkretnej kolumny:

mysql> grant select (firstname) on testbaza1.testtable1 to 'newuser1'@'%';

Query OK, 0 rows affected (0.00 sec)

Użytkownik próbując wyświetlić tabelę otrzyma komunikat:
mysql> select * from testtable1;

ERROR 1143 (42000): SELECT command denied to user 'newuser1'@'localhost' for column 'id' in table 'testtable1'

Ale już wyświetlając tylko jedną kolumnę:
mysql> select firstname from testtable1;

+-----------+
| firstname |
+-----------+
| Anna      |
| Katarzyna |
| Robert    |
| Andrzej   |
| Agnieszka |
| Hubert    |
+-----------+
6 rows in set (0.00 sec)

Uprawnienia do kolumn możemy „mieszać” czyli np:
mysql> GRANT SELECT (col1), INSERT (col1,col2) ON testbaza1.testtabel1 TO 'newuser1'@'%';

Wydajemy polecenie
mysql> grant select on testbaza1.testtable1 to 'newuser1'@'%';
i user może znowu czytać całą tabelę 🙂

Jak widać jest kilka poziomów uprawnień:
• globalny – przyznane uprawnienia obowiązują we wszystkich istniejących bazach,
• baza danych – uprawnienia w zakresie danej bazy danych,
• tabela – w zakresie jednej tabeli,
• kolumna – w zakresie jednej kolumny konkretnej tabeli

Dla sprawdzenie czy user ma prawidłowe uprawnienia, zalogowujemy się do bazy na NEWUSER1:
[root@localhost ~]# mysql -u newuser1 -p

mysql> select * from testbaza1.testtable1;

+----+-----------+------------+------------------------------+
| id | firstname | lastname   | email                        |
+----+-----------+------------+------------------------------+
|  1 | Jan       | Kowalski   | jan.kowalski@mail.pl         |
|  3 | Anna      | Nowak      | anna.nowak@mail.pl           |
|  4 | Katarzyna | Polska     | katarzyna.polska@mail.pl     |
|  5 | Robert    | Barski     | robert.barski@mail.pl        |
|  6 | Andrzej   | Dulski     | andrzej.dulski@mail.pl       |
|  7 | Agnieszka | Frankowska | agnieszka.frankowska@mail.pl |
|  8 | Hubert    | Goglewski  | hubert.goglewski@mail.pl     |
+----+-----------+------------+------------------------------+
7 rows in set (0.00 sec)

Spróbujemy wyświetlić jakąś inną tabelę
mysql> select * from mysql.user;

ERROR 1142 (42000): SELECT command denied to user 'newuser'@'localhost' for table 'user'

Aby zabrać uprawnienie używamy polecenia REVOKE

Zabranie wszytskich uprawnień do bazy:
mysql> REVOKE ALL ON *.* FROM 'newuser1'@'%';

Query OK, 0 rows affected (0.00 sec)


mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

lub jakiś konkretnych np prawa do INSERT’ów

mysql> REVOKE INSERT ON *.* FROM 'newuser1'@'%';

Usunięcie użytkownika

Usuwamy go poleceniem DROP USER

MariaDB [(none)]> DROP USER 'newuser1'@'localhost';

Query OK, 0 rows affected (0.00 sec)

lub „ręcznie” usuwając go z tabeli mysql.user

MariaDB [(none)]> DELETE FROM mysql.user WHERE User='testusr2.localhost';

Query OK, 1 row affected (0.00 sec)

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz